Oracle练习总结二

暂时之将代码贴上来，后续慢慢总结：

第二次sql语句练习
//大写字母的答案是自己写的，小写是答案，大写前面带注释符的表示自己代码有偏差或错误。

1、  查询Student表中的所有记录的Sname、Ssex和Class列  
SELECT SNAME,SSEX,CLASSES FROM STUDENT

2、  查询教师所有的单位即不重复的Depart列。
select distinct DEPART FROM TEACHER

3、 查询Student表的所有记录。
SELECT * FROM STUDENT

4、 查询Score表中成绩在60到80之间的所有记录。
SELECT * FROM SCORE WHERE DEGREES BETWEEN 60 AND 80
SELECT * FROM SCORE

5、 查询Score表中成绩为85，86或88的记录。
SELECT * FROM SCORE WHERE DEGREES=85 OR DEGREES=86 OR DEGREES=88
SELECT * FROM SCORE WHERE DEGREES IN (85，86，88)

6、 查询Student表中“95031”班或性别为“女”的同学记录。
SELECT * FROM STUDENT WHERE CLASSES='95031' OR SSEX='女'

7、 以Class降序查询Student表的所有记录。
SELECT * FROM STUDENT ORDER BY CLASSES DESC

8、 以Cno升序、Degree降序查询Score表的所有记录。
SELECT * FROM SCORE ORDER BY CNO ASC, DEGREES DESC

9、 查询“95031”班的学生人数。
SELECT DISTINCT COUNT('SNO') 总人数 FROM STUDENT WHERE CLASSES='95031'

10、查询Score表中的最高分的学生学号和课程号。
SELECT SNO 学号,CNO FROM SCORE WHERE DEGREES =(SELECT MAX(DEGREES) FROM SCORE)

11、查询‘3-105’号课程的平均分。
SELECT AVG(DEGREES) AS "3班平均分" FROM SCORE WHERE Cno='3-105'

12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
//SELECT AVG(DEGREES) FROM SCORE WHERE (SELECT COUNT('SNO') FROM SCORE WHERE CNO LIKE '3%')>4
SELECT CNO,AVG(DEGREES) FROM SCORE WHERE CNO LIKE '3%' GROUP BY CNO HAVING COUNT('SNO')>4

13、查询最低分大于70，最高分小于90的Sno列。
//SELECT SNO FROM SCORE WHERE DEGREES BETWEEN 70 AND 90 ORDER BY SNO
SELECT SNO FROM SCORE GROUP BY SNO HAVING MIN(DEGREES)>70 AND MAX(DEGREES)<90

14、查询所有学生的Sname、Cno和Degree列。
SELECT SNAME,CNO,DEGREES FROM STUDENT S,SCORE SR WHERE S.SNO=SR.SNO

15、查询所有学生的Sno、Cname和Degree列。
SELECT SNO,CNAME,DEGREES FROM SCORE S,COURSE C WHERE S.CNO=C.CNO

16、查询所有学生的Sname、Cname和Degree列。
SELECT SNAME,CNAME,DEGREES FROM COURSE C,STUDENT S,SCORE SR WHERE C.CNO=SR.CNO AND S.SNO=SR.SNO 

17、查询“95033”班所选课程的平均分。
SELECT CNO,AVG(DEGREES) FROM SCORE SR,STUDENT S WHERE SR.SNO=S.SNO AND S.CLASSES='95033' GROUP BY CNO 

18、假设使用如下命令建立了一个grade表：
create table grade(low   number(3,0),upp   number(3),rankS   char(1));
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');

insert into grade values(0,59,'E');
select* from grade
commit;
现查询所有同学的Sno、Cno和rank列

SELECT SNO,CNO,RANKS FROM SCORE S,GRADE G WHERE DEGREES BETWEEN G.LOW AND G.UPP ORDER BY RANKS


19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
SELECT * FROM SCORE WHERE DEGREES>(SELECT DEGREES FROM SCORE WHERE SNO='109' AND CNO='3-105')AND CNO='3-105'

20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
SELECT * FROM SCORE S WHERE S.SNO IN (SELECT SNO FROM SCORE GROUP BY SNO HAVING COUNT(*)>1) AND S.DEGREES<
(SELECT MAX(DEGREES) FROM SCORE)



21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
//SELECT * FROM SCORE S WHERE S.CNO='3-105' AND S.DEGREES>(SELECT DEGREES FROM SCORE WHERE SNO=109 AND CNO='3-105')

select cno,sno,degrees from score   where degrees >(select degrees from score where sno='109' and cno='3-105')

22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
SELECT SNO,SNAME,SBIRTHDAY  FROM STUDENT S
WHERE TO_CHAR(S.SBIRTHDAY,'YYYY') = (SELECT TO_CHAR(SBIRTHDAY,'YYYY') FROM STUDENT WHERE SNO=108)



SELECT SNO,SNAME,SBIRTHDAY FROM STUDENT S
WHERE TRUNC(SBIRTHDAY,'YYYY')=(SELECT TRUNC(SBIRTHDAY,'YYYY') FROM STUDENT WHERE SNO=108)

23、查询“张旭“教师任课的学生成绩。
SELECT SCO.SNO,SCO.CNO,SCO.DEGREES FROM SCORE SCO,COURSE CO,TEACHER TEA 
WHERE SCO.CNO=CO.CNO AND CO.TNO=TEA.TNO AND TEA.TNAME='张旭'

select cno,sno,degrees from score where cno=(select x.cno from course x,teacher y where x.tno=y.tno and y.tname='张旭');

24、查询选修某课程的同学人数多于5人的教师姓名。
SELECT DISTINCT TNAME FROM TEACHER TEA,COURSE CO,SCORE SCO 
WHERE CO.TNO=TEA.TNO AND SCO.CNO=CO.CNO AND SCO.CNO=(SELECT CNO FROM SCORE GROUP BY CNO HAVING COUNT(*)>5)

select tname from teacher where tno in
(select x.tno from course x,score y where x.cno=y.cno group by x.tno having count(x.tno)>5);

25、查询95033班和95031班全体学生的记录。
SELECT * FROM STUDENT WHERE CLASSES IN('95033','95031')



26、查询存在有85分以上成绩的课程Cno.
SELECT DISTINCT CO.CNO,CO.CNAME FROM COURSE CO,SCORE SCO WHERE CO.CNO=SCO.CNO AND SCO.DEGREES>85

select distinct cno from score where degrees in (select degrees from score where degrees>85);

27、查询出“计算机系“教师所教课程的成绩表。
SELECT SCO.SNO,SCO.CNO,SCO.DEGREES,TEA.TNAME,CO.CNAME FROM SCORE SCO,COURSE CO,TEACHER TEA 
WHERE TEA.DEPART='计算机系' AND CO.TNO=TEA.TNO AND CO.CNO=SCO.CNO

select * from score where cno in(select x.cno from course x,teacher y where y.tno=x.tno and y.depart='计算机系');

28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。 [题目与答案不符]
SELECT TNAME,PROF FROM TEACHER WHERE DEPART='计算机系' AND PROF
NOT IN (SELECT PROF FROM TEACHER WHERE DEPART='电子工程系')

select tname,prof from teacher where depart='计算机系' 
and prof not in (select prof from teacher where depart='电子工程系');

29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
SELECT SCO.CNO,SCO.SNO,SCO.DEGREES FROM SCORE SCO,COURSE CO WHERE CO.CNO='3-105'AND CO.CNO=SCO.CNO 
AND SCO.DEGREES>(SELECT MIN(DEGREES) FROM SCORE WHERE CNO='3-245') ORDER BY DEGREES DESC


select * from score where cno='3-105' and degree>
any (select degree from score where cno='3-245')order by degree desc;

30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
SELECT SCO.CNO,SCO.SNO,SCO.DEGREES FROM SCORE SCO,COURSE CO WHERE CO.CNO='3-105'AND CO.CNO=SCO.CNO 
AND SCO.DEGREES>(SELECT MAX(DEGREES) FROM SCORE WHERE CNO='3-245') ORDER BY DEGREES DESC

select * from score where cno='3-105' and degrees>all(select degrees from score where cno='3-245');

31、查询所有教师和同学的name、sex和birthday.
//SELECT DISTINCT STU.SNAME,STU.SSEX,STU.SBIRTHDAY FROM STUDENT STU,TEACHER TEA

SELECT * FROM TEACHER
SELECT TNAME,TSEX,TBIRTHDAY FROM TEACHER UNION SELECT SNAME,SSEX,SBIRTHDAY FROM STUDENT 


32、查询所有“女”教师和“女”同学的name、sex和birthday.
SELECT TNAME,TSEX,TBIRTHDAY FROM TEACHER WHERE TSEX='女' 
UNION SELECT SNAME,SSEX,SBIRTHDAY FROM STUDENT WHERE SSEX='女'

select tname,tsex,tbirthday from teacher where tsex='女'
union select sname,ssex,sbirthday from student where ssex='女';


33、查询成绩比该课程平均成绩低的同学的成绩表。
SELECT * FROM SCORE SCO WHERE SCO.DEGREES<(SELECT AVG(DEGREES) FROM SCORE 
WHERE SCO.CNO=CNO GROUP BY CNO)

select * from score a where degrees<(select avg(degrees)
from score b where a.cno=b.cno);


34、查询所有任课教师的Tname和Depart.
SELECT TNAME,DEPART FROM TEACHER TEA WHERE EXISTS (SELECT * FROM COURSE CO WHERE TEA.TNO=CO.TNO)

35、查询所有未讲课的教师的Tname和Depart.
SELECT TNAME,DEPART FROM TEACHER TEA WHERE NOT EXISTS (SELECT * FROM COURSE CO WHERE TEA.TNO=CO.TNO)

select tname,depart from teacher a where not exists
(select * from course b where a.tno=b.tno);


36、查询至少有2名男生的班号。
SELECT DISTINCT STU.CLASSES FROM STUDENT STU WHERE 
(SELECT COUNT(*) FROM STUDENT WHERE SSEX='男' AND STU.CLASSES=CLASSES GROUP BY CLASSES)>=2

SELECT CLASSES FROM STUDENT WHERE SSEX='男' GROUP BY CLASSES HAVING COUNT(*)>=2



37、查询Student表中不姓“王”的同学记录。
SELECT * FROM STUDENT WHERE SNAME NOT LIKE '王%'

select * from student where sname not like'王_';



38、查询Student表中每个学生的姓名和年龄。
SELECT SNAME AS NAME,TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(SBIRTHDAY,'YYYY')  年龄 FROM STUDENT 

select sname as 姓名,(trunc(sysdate,'yyyy')-trunc(sbirthday,'yyyy')) as "年 龄" from student

39、查询Student表中最大和最小的Sbirthday日期值。
SELECT MAX(TO_CHAR(SBIRTHDAY,'YYYY-MM-DD')),MIN(TO_CHAR(SBIRTHDAY,'YYYY-MM-DD')) FROM STUDENT

select sname,sbirthday as "最大" from student where   sbirthday =(select min (sbirthday) from student)
union select sname,sbirthday as "最小" from student where sbirthday =(select max(sbirthday) from student)  


40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
SELECT * FROM STUDENT ORDER BY 
CLASSES DESC,sbirthday

select classes,sname,sbirthday from student order by classes desc,sbirthday;
41、查询“男”教师及其所上的课程。
SELECT * FROM TEACHER TEA,COURSE CO WHERE TEA.TNO=CO.TNO AND TSEX='男'

select x.tname,y.cname from teacher x,course y where x.tno=y.tno and x.tsex='男';

42、查询最高分同学的Sno、Cno和Degree列。
SELECT SNO,CNO,DEGREES FROM SCORE WHERE DEGREES = (SELECT MAX(DEGREES) FROM SCORE)

select * from score where degrees=(select max(degrees)from score);

43、查询和“李军”同性别的所有同学的Sname.
SELECT SNAME FROM STUDENT WHERE SSEX=(SELECT SSEX FROM STUDENT WHERE SNAME='李军')

select sname from student where ssex=(select ssex from student where sname='李军');

44、查询和“李军”同性别并同班的同学Sname.
SELECT SNAME FROM STUDENT STU1 WHERE SSEX=
(SELECT SSEX FROM STUDENT STU2 WHERE SNAME='李军' AND STU1.CLASSES=STU2.CLASSES) 

select sname from student where ssex=(select ssex from student where sname='李军') 
and classes=(select classes from student where sname='李军');

45、查询所有选修“计算机导论”课程的“男”同学的成绩表
SELECT SCO.SNO,SCO.CNO,SCO.DEGREES FROM SCORE SCO,STUDENT STU,COURSE CO 
WHERE SCO.SNO=STU.SNO AND STU.SSEX='男' AND CO.CNO=SCO.CNO AND CO.CNAME='计算机导论' 

select * from score where sno in(select sno from student where ssex='男') and cno=(select cno from course
where cname='计算机导论');