Course(Cid,Cname,Tid) 课程表

SC(Sid,Cid,score) 成绩表

Teacher(Tid,Tname) 教师表

练习内容：

1.查询“某1”课程比“某2”课程成绩高的所有学生的学号；

SELECT a.sid FROM (SELECT sid,score FROM SC WHERE cid=1) a,(SELECT sid,score FROM SC WHERE cid=3) b WHERE a.score>b.score AND a.sid=b.sid;

此题知识点，嵌套查询和给查出来的表起别名

2.查询平均成绩大于60分的同学的学号和平均成绩；

SELECT sid,avg(score)  FROM sc  GROUP BY sid having avg(score) >60;

此题知识点，GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。group by后面不能接where，having代替了where

3.查询所有同学的学号、姓名、选课数、总成绩

SELECT Student.sid,Student.Sname,count(SC.cid),sum(score)FROM Student left Outer JOIN SC on Student.sid=SC.cid GROUP BY Student.sid,Sname

4.查询姓“李”的老师的个数；

select count(teacher.tid)from teacher where teacher.tname like'李%'

5.查询没学过“叶平”老师课的同学的学号、姓名；

SELECT Student.sid,Student.Sname FROM Student WHERE sid not in (SELECT distinct( SC.sid) FROM SC,Course,Teacher WHERE  SC.cid=Course.cid AND Teacher.id=Course.tid AND Teacher.Tname='叶平');

 此题知识点，distinct是去重的作用

6.查询学过“```”并且也学过编号“```”课程的同学的学号、姓名；

标准答案（但是好像不好使）SELECT Student.S#,Student.Sname FROM Student,SC WHERE Student.S#=SC.S# AND SC.C#='001'and exists( SELECT * FROM SC as SC_2 WHERE SC_2.S#=SC.S# AND SC_2.C#='002');  

此题知识点，exists是在集合里找数据，as就是起别名

7.查询学过“叶平”老师所教的所有课的同学的学号、姓名；

标准答案：SELECT sid,Sname FROM Student WHERE sid in (SELECT sid FROM SC ,Course ,Teacher WHERE SC.cid=Course.cid AND Teacher.tid=Course.tid AND Teacher.Tname='杨巍巍' GROUP BY sid having count(SC.cid)=(SELECT count(cid) FROM Course,Teacher  WHERE Teacher.tid=Course.tid AND Tname='杨巍巍'))

8.查询课程编号“”的成绩比课程编号“”课程低的所有同学的学号、姓名；

标准答案：SELECT sid,Sname FROM (SELECT Student.sid,Student.Sname,score ,

(SELECT score FROM SC SC_2 WHERE SC_2.sid=Student.sid AND SC_2.cid=1) score2 FROM Student,SC

WHERE Student.sid=SC.sid AND cid=1) S_2 WHERE score2 <score;

9.查询所有课程成绩小于分的同学的学号、姓名；

SELECT sid,Sname FROM Student WHERE sid not in (SELECT Student.sid FROM Student,SC WHERE Student.sid=SC.sid AND score>60); 

此题知识点，先查出大于60分的，然后not in 就是小于60分的了

10.查询没有学全所有课的同学的学号、姓名；