Oracle练习总结二

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

第二次sql语句练习//大写字母的答案是自己写的，小写是答案，大写前面带注释符的表示自己代码有偏差或错误。1、  查询Student表中的所有记录的Sname、Ssex和Class列  SELECT SNAME,SSEX,CLASSES FROM STUDENT2、  查询教师所有的单位即不重复的Depart列。select distinct DEPART FROM TEACHER3、 查询Student表的所有记录。SELECT * FROM STUDENT4、 查询Score表中成绩在60到80之间的所有记录。SELECT * FROM SCORE WHERE DEGREES BETWEEN 60 AND 80SELECT * FROM SCORE5、 查询Score表中成绩为85，86或88的记录。SELECT * FROM SCORE WHERE DEGREES=85 OR DEGREES=86 OR DEGREES=88SELECT * 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 DESC8、 以Cno升序、Degree降序查询Score表的所有记录。SELECT * FROM SCORE ORDER BY CNO ASC, DEGREES DESC9、 查询“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%')>4SELECT CNO,AVG(DEGREES) FROM SCORE WHERE CNO LIKE '3%' GROUP BY CNO HAVING COUNT('SNO')>413、查询最低分大于70，最高分小于90的Sno列。//SELECT SNO FROM SCORE WHERE DEGREES BETWEEN 70 AND 90 ORDER BY SNOSELECT SNO FROM SCORE GROUP BY SNO HAVING MIN(DEGREES)>70 AND MAX(DEGREES)<9014、查询所有学生的Sname、Cno和Degree列。SELECT SNAME,CNO,DEGREES FROM STUDENT S,SCORE SR WHERE S.SNO=SR.SNO15、查询所有学生的Sno、Cname和Degree列。SELECT SNO,CNAME,DEGREES FROM SCORE S,COURSE C WHERE S.CNO=C.CNO16、查询所有学生的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 gradecommit;现查询所有同学的Sno、Cno和rank列SELECT SNO,CNO,RANKS FROM SCORE S,GRADE G WHERE DEGREES BETWEEN G.LOW AND G.UPP ORDER BY RANKS19、查询选修“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 SWHERE TO_CHAR(S.SBIRTHDAY,'YYYY') = (SELECT TO_CHAR(SBIRTHDAY,'YYYY') FROM STUDENT WHERE SNO=108)SELECT SNO,SNAME,SBIRTHDAY FROM STUDENT SWHERE 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>85select 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.CNOselect * 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 PROFNOT 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 DESCselect * 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 DESCselect * 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 TEASELECT * FROM TEACHERSELECT 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)>=2SELECT CLASSES FROM STUDENT WHERE SSEX='男' GROUP BY CLASSES HAVING COUNT(*)>=237、查询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 student39、查询Student表中最大和最小的Sbirthday日期值。SELECT MAX(TO_CHAR(SBIRTHDAY,'YYYY-MM-DD')),MIN(TO_CHAR(SBIRTHDAY,'YYYY-MM-DD')) FROM STUDENTselect 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,sbirthdayselect 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 coursewhere cname='计算机导论');