数据库sql查询语句练习习题结果单世民.doc

现在有一教学管理系统，具体的关系模式如下：Student (no, name, sex, birthday, class)Teacher (no, name, sex, birthday, prof, depart)Course (cno, cname, tno)Score (no, cno, degree)其中表中包含如下数据：Course表：Score表：Student表：Teacher表：根据上面描述完成下面问题：（注意：注意保存脚本，尤其是DDL和DML，以便进行数据还原）DDL1. 写出上述表的建表语句。2. 给出相应的INSERT语句来完成题中给出数据的插入。单表查询3. 以class降序输出student的所有记录（student表全部属性）命令：select * from Student order by class desc;4. 列出教师所在的单位depart（不重复）。命令：select distinct depart from Teacher;5. 列出student表中所有记录的name、sex和class列命令：select name,sex,class from Student;6. 输出student中不姓王的同学的姓名。命令：select name from Student except select name from Student where name like 王%;或select name from Student where name not like 王%;7. 输出成绩为85或86或88或在60-80之间的记录（no,cno,degree）命令：select no,cno,DEGREE from Score where degree=85 or degree=86 or degree=88 or degree between 60 and 80;8. 输出班级为95001或性别为女 的同学（student表全部属性）命令：select * from Student where class=95001 or sex=女;9. 以cno升序、degree降序输出score的所有记录。（score表全部属性）命令：select * from Score order by cno asc,degree desc;10. 输出男生人数及这些男生分布在多少个班级中命令：select COUNT(*),count(distinct class) from Student where sex=男;11. 列出存在有85分以上成绩的课程编号。命令：select distinct cno from Score where degree85;12. 输出95001班级的学生人数命令：select COUNT(*) from Student where class=95001;13. 输出3-105号课程的平均分命令：select avg(cast(degree as float) from Score where cno=3-105;14. 输出student中最大和最小的birthday日期值命令：select MAX(birthday),MIN(birthday) from Student;15. 显示95001和95004班全体学生的全部个人信息（不包括选课）。（student表全部属性）命令：select * from Student where class=95001 or class=95004;聚合查询16. 输出至少有5个同学选修的并以3开头的课程的课程号，课程平均分，课程最高分，课程最低分。命令：select cno,avg(cast(degree as float),MAX(degree),MIN(degree) from Score where cno like 3% group by cno having COUNT(cno)5;或者：select cno,AVG(cast(DEGREE as float),MAX(degree),MIN(DEGREE) from Score group by cno having COUNT(cno)=5 and cno like 3%17. 输出所选修课程中最低分大于70分且最高分小于90分的学生学号及学生姓名命令：select Student.no,name from Student join Score on Student.no=Score.no group by Student.no,name having MAX(Score.degree)70;18. 显示所教课程选修人数多于5人的教师姓名命令：select name from Teacher join Course on Teacher.no=Course.tno where Course.cno in(select cno from Score group by cno having COUNT(Score.cno)5);19. 输出95001班级所选课程的课程号和平均分命令：select cno,avg(cast(degree as float) from Score where no in(select no from Student where class=95001) group by cno;或者：select cno,AVG(cast(degree as float) from Score join Student on Score.no=Student.no group by cno,class having class=9500120. 输出至少有两名男同学的班级编号。命令：select class from Student where sex=男 group by class having COUNT(class)=2;或者：select a.class from (select * from Student where sex=男) a group by a.class having COUNT(a.class)=2多表查询21. 列出与108号同学同年出生的所有学生的学号、姓名和生日命令：select no,name,birthday from Student where year(birthday) =(select year(birthday) from Student where no=108);或者：select b.no,b.name,b.birthday from Student a join Student b on datediff(YEAR,a.birthday,b.birthday)=0 and a.no=10822. 列出存在有85分以上成绩的课程名称命令：select cname from Course where cno in (select distinct cno from Score where degree85);或select distinct cname from Course join Score on Course.cno=Score.cno where degree85;23. 列出“计算机系”教师所教课程的成绩表（课程编号，课程名，学生名，成绩）。命令：select Course.cno,cname,Student.name,DEGREE from Teacher join Course on Teacher.no=Course.tno join Score on Course.cno=Score.cno join Student on Score.no=Student.no where Teacher.depart=计算机系;24. 列出所有可能的“计算机系”与“电子工程系”不同职称的教师配对信息，要求输出每个老师的姓名（name）和（职称）命令：select a.name,a.prof,b.name,b.prof from (select name,prof,depart from Teacher where depart=计算机系 or depart=电子工程系) a join (select name,prof,depart from Teacher where depart=电子工程系 or depart=计算机系) b on not a.prof=b.prof and not a.depart=b.depart;25. 列出所有处于不同班级中，但具有相同生日的学生，要求输出每个学生的学号和姓名。（提示：使用datediff函数，具体用法可以参考：http:/hcmfys.javaeye.com/blog/588844）命令：select a.no,a.name,b.no,b.name from Student a join Student b on not a.class=b.class and a.birthday=b.birthday;26. 显示张三教师任课的学生姓名，课程名，成绩命令：select Student.name,cname,DEGREE from Teacher join Course on Teacher.no=Course.tno join Score on Course.cno=Score.cno join Student on Score.no=Student.no where Teacher.name=张三;27. 列出所讲课已被选修的教师的姓名和系别命令：select distinct name,depart from Teacher join Course on Teacher.no=Course.tno join Score on Course.cno=Score.cno;28. 输出所有学生的name、no和degree。（degree为空的不输出和为空的输出两种情况）。命令：select name,Student.no,DEGREE from Student left join Score on Student.no=Score.no;select name,Student.no,DEGREE from Student join Score on Student.no=Score.no;29. 列出所有任课教师的name和depart。（从课程选修和任课两个角度考虑）命令：select distinct name,depart from Teacher join Course on Teacher.no=Course.tno;select distinct name,depart from Teacher join Course on Teacher.no=Course.tno join Score on Course.cno=Score.cno;30. 输出男教师所上课程名称。命令：select cname from Teacher join Course on Teacher.no=Course.tno where Teacher.sex=男;31. 出与“李军”同性别的所有同学的name。命令：select name from Student where sex=(select sex from Student where name=李军);32. 输出选修“数据结构”课程的男同学的成绩。命令：select DEGREE from Score join Student on Score.no=Student.no join Course on Score.cno=Course.cno where sex=男 and cname=数据结构;33. 列出选修编号为3-105课程并且该门课程成绩比课程 3-111的最高分要高的cno,no和degree。命令：select Course.cno,Score.no,DEGREE from Course join Score on Course.cno=Score.cno join Student on Score.no=Student.no where Course.cno=3-105 and Score.degree(select max(degree) from Score where Score.cno=3-111);子查询34. 输出score中成绩最高的学号和课程号命令：select no,cno from Score where degree in(select MAX(degree) from Score);35. 输出选修3-105课程，其成绩高于109号同学在此课程所得成绩的所有同学的学号，姓名命令：select Student.no,name from Student join Score on Student.no =Score.no where Score.cno=3-105 and degree(select degree from Score where no=109 and cno=3-105);36. 列出成绩比该课程平均成绩低的同学的学号，成绩和该门课的平均成绩命令：select no,DEGREE,a.avg_degree from Score join (select cno,AVG(cast(degree as float) from Score group by cno) a(cno,avg_degree) on Score.cno=a.cno where Score.degree(select MAX(degree) from Score where cno=4-109);39. *列出符合下述条件的所有可能的同学配对（sno1,sname1,sno2,sname2,difference）。其中要求学号为sno1的sname1同学的所学课程的平均分大于学号为sno2的sname2同学的所学课程平均分，两个同学的课程平均分的差值difference为（sno1同学平均分-sno2同学平均分）命令：select a.no,a.name,b.no,b.name,a.avg_degree-b.avg_degree as difference from (select Student.no,name,avg(degree) from Student join Score on Student.no=Score.no group by Student.no,name) a(no,name,avg_degree) join (select Student.no,name,avg(degree) from Student join Score on Student.no=Score.no group by Student.no,name) b(no,name,avg_degree) on a.avg_degreeb.avg_degree;