SQL50题(国外英文资料).docVIP

SQL50题(国外英文资料) student (s #, sname, sage, sex) 学生表 course (c #, cname, t #) 课程表 sc (s #, c #, score) 成绩表 teacher (t #, tname) 教师表 问题: 1、查询 001 课程比 002 课程成绩高的所有学生的学号; select # a.s. from (select s #, score from sc where c # = 001) a, (select s #, score from sc where c # = 002) b where a.score b.score and a.s. # = b.s #; 2、查询平均成绩大于60分的同学的学号和平均成绩; select avg (score), it #, from sc group by s # having avg (score) 60; 3、查询所有同学的学号、姓名、选课数、总成绩; select student.s #, student.sname, count (sc.c #), sum (score) from a left outer join sc on student.s # = sc.s # group by student.s #, sname 4、查询姓 李 的老师的个数; select count (distinct (tname)) from teacher where tname like 李%; 5、查询没学过 叶平 老师课的同学的学号、姓名; select student.s #, student.sname from student wheres # not in (select distinct (sc.s #) from sc, course, teacher where sc.c # = course.c # and teacher.t # = course.t # and teacher.tname = 叶平); 6、查询学过 001 并且也学过编号 002 课程的同学的学号、姓名; select student.s #, student.sname from student, sc where student.s # = sc.s # and sc.c # = 001 and exists (select * from sc or sc _ 2 where sc _ 2.s # = sc.s # and sc _. c # = 002); 7、查询学过 叶平 老师所教的所有课的同学的学号、姓名; select s #, sname from student wheres # in (select s # from sc, course, teacher where sc.c # = course.c # and teacher.t # = course.t # and teacher.tname = 叶平 group by s # having count (sc.c #) = (select count (c #) from course, teacher where teacher.t # = course.t # and tname = 叶平); 8、查询课程编号 002 的成绩比课程编号 001 课程低的所有同学的学号、姓名; select sname from s #, select student.s #, student.sname, score (the score from sc sc _ 2 where sc _ 2.s # = student.s # and sc _. c # = 002) score2 from student, sc where student.s # = sc.s # and c # = 001) _ 2 where score2 score; 9、查询所有课程成绩小于60分的同学的学号、姓名; select s #, sname from student wheres # not in (select student.s # from student, sc where s. # = sc.s # and score 60); 10、查询没有学全所有课的同学的学号、姓名; select student.s #, student.sname from student, sc where student.s # = sc.s # group by student.s #, student.sname having count (c #) (select