统计计算机学院在每个专业的开课数量,数据库部分课后习题答案

2、查询计算机系的学生的姓名和年龄

Select sname,sage from student

Where sdept =’计算机系’

4、查询计算机系年龄在18~20岁之间性别为男的学生姓名和年龄

Select sname,sage from student

Where sdept=’计算机系’and ssex=’男’

And sage between 18 and 20

6、查询计算机系学生的最大年龄和最小年龄

Select max(sage) as max_age,min(sage) as min_age from student

Where sdept =’计算机系’

8、统计每门课程的选课人数和考试最高分

Select cno,count(*) as ‘选课人数’，max(grade) from sc

Group by cno

9、统计每个学生的选课门数和考试总成绩，按选课门数升序显示

Select sno,count(*) as 选课门数，sum(grade) from sc Group by sno

Order by 2 asc

10、查询总成绩超过200分的学生，要求列出学号、总成绩Select sno,sum(grade) as 总成绩 from sc

Group by sno

Having sum(grade)>200

12、查询80分以上的学生姓名、课程号和成绩，并按成绩降序排列

Select sname ,cno ,grade from student s join sc on s.sno=sc.sno

Where grade >80

Order by grade desc

13、查询哪些学生没选课，列出学号姓名所在系

Select student.sno,sname,sdept from student s left join sc

On s.sno=sc.sno

Where sc.sno is null

14、查询与VB在同一学期开设的课程的课程名和开课学期