数据库sql查询语句练习2 - 习题 - 结果（单世民）

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 degree>85;

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)<90 and MIN(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='95001'

20. 输出至少有两名男同学的班级编号。

命令：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='108'

22. 列出存在有85分以上成绩的课程名称

命令：select cname from Course where cno in (select distinct cno from

Score where degree>85);或

select distinct cname from Course join Score on Course.cno=Score.cno where degree>85;

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='张三';