SQL（三）-- 练习题

来源：互联网发布：蜂窝移动数据开关无效编辑：程序博客网时间：2024/05/20 03:37
表：
题：
//1.统计每门课程的修课人数,考试最高分SELECT COUNT(*) courseSum,MAX(grade),cno FROM sc GROUP BY cno ORDER BY courseSum DESC//2.统计每个学生的选课门数,并按选课门数的递增顺序显示结果SELECT COUNT(*) stuCount,s.cno,c.cname FROM sc s,course c WHERE s.cno = c.cno GROUP BY s.sno ORDER BY stuCount//3.统计选修课的学生总数和考试的平均成绩SELECT COUNT(DISTINCT(sno)) stuNum,AVG(grade) FROM sc//4.查询选课门数超过2门的学生的平均成绩和选课门数SELECT * FROM (SELECT sno,COUNT(cno) courseNum, AVG(grade) FROM sc GROUP BY sno) a WHERE a.courseNum > 2//5.列出总成绩超过200分的学生,要求列出学号、总成绩1) SELECT * FROM (SELECT sno,SUM(grade) scoreSum FROM sc GROUP BY sno) aWHERE a.scoreSum > 2002) SELECT sno,SUM(grade) scoreSum FROM sc GROUP BY sno HAVING scoreSum > 200//6.查询选修了c02号课程的学生的姓名和所在系SELECT stu.sname, stu.sdept FROM student stu,sc s WHERE s.cno = 'c02'//7.查询成绩80分以上的学生的姓名、课程号和成绩,并按成绩的降序排列结果SELECT stu.sname,s.cno,s.grade FROM student stu,sc s WHERE stu.sno = s.sno AND s.grade > 80 ORDER BY s.grade DESC//8.查询哪些课程没有人选,要求列出课程号和课程名SELECT cno,cname FROM course WHERE cno NOT IN (SELECT DISTINCT cno FROM sc) //9.查询数学系成绩80分以上的学生的学号、姓名SELECT a.sname, a.sno FROM (SELECT * FROM student WHERE sdept = '数学系') a,sc b WHERE a.sno = b.sno AND b.grade > 80//10.查询学生都选修了哪些课程,要求列出课程号SELECT DISTINCT cno FROM sc //11.统计每个系的学生人数SELECT sdept,COUNT(*) FROM student GROUP BY sdept
0 0