SQL语句的练习

第一题：查询101课程比102课程成绩高的所有学生的学号
select S ,score from SC where C=101;
select S ,score from SC where C=102;

最终：select a.S from (select S ,score from SC where C=101) a,(select S ,score from SC where C=102) b  where a.score > b.score and a.S=b.S;
总结：如果写成select S from (select S ,score from SC where C=101) a,(select S ,score
from SC where C=102) b  where a.score > b.score and a.S=b.S;
则会报错如下 ：Column 'S' in field list is ambiguous
原因：原因：表中包含共同字段'xxxx'
如：select name from a,b where a.id=b.id    因为a、b2表里都有name字段
解决：字段前加上具体表名，如： select a.name from a,b where a.id=b.id

第二题：查询平均成绩大于60分的同学的学号和平均成绩
select S ,avg(score) from SC group by S having avg(score)>60;
总结：having是用来帅选组，where是帅选字段的

第三题：查询所有同学的学号，姓名，选课数，总成绩
select Student.S,Student.Sname,count(SC.C),sum(score) from Student left join SC on Student.S=SC.S group by Student.S,Sname;
总结：注意左连接和右连接的区别

第四题：查询姓“李”老师的个数
select count(distinct(Tname)) from Teacher where Tname like '李%';
总结：注意like的用法

第五题：查询没有学过“张华”老师课的同学的学号，姓名
select Student.S,Student.Sname from Student where S not in (select distinct(SC.S) from SC,Course,Teacher where SC.C=Course.C and Teacher.T=Course.T and Teacher.Tname='张华');
总结：注意not in的用法，在范围里面选择

第六题：查询学过“101”并且也学过编号“102”课程的同学的学号，姓名
select Student.S,Student.Sname from Student,SC where Student.S=SC.S and SC.C =101 and exists(select * from SC as SC_2 where SC.S=SC_2.S and SC_2.C =102);
总结：注意exists的用法：并且存在，即后面的结果与前面的结果有关

第七题：查询学过“张华”老师所教的所有课的同学的学号，姓名
select S,Sname from Student where S 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='张华'));
总结：注意count(SC.C)=(select count(C) from course,Teacher where Teacher.T=Course.T and Tname='张华')这句


第八题：查询所有课程成绩小于90分的同学的学号，姓名
select S,Sname from Student where S not in (select Student.S from Student,SC where Student.S=SC.S and score>90);
总结：是所有成绩，所以采用not in，用反面来求

第九题：查询没有学全所有课的同学的学号
select Student.S,Student.Sname from Student,SC where Student.S=SC.S group by Student.S,Student.Sname having count(C)<(select count(C) from Course);

第十题：查询至少有一门课与学号为“3”的同学所学相同的同学的学号和姓名
select Student.S,Student.Sname from Student,SC where Student.S=SC.S and C in(select C from SC where S=3);

第十一题：查询各科成绩最高和最低分：如以下形式显示：课程ID，最高分，最低分
select L.C 课程ID,L.score 最高分,R.score 最低分 from SC L,SC R where L.C=R.C and L.score=(select MAX(IL.score) from SC IL ,Student IM where IL.C=L.C and IM.S=IL.S group by IL.C) and R.score=(select min(IR.score) from SC IR where IR.C=R.C group by IR.C);

第十二题：查询学生平均成绩及名次
select 1+(select count(distinct 平均成绩) from (select S,AVG(score) 平均成绩 from SC group by S )T1 where 平均成绩>T2.平均成绩)名次 ,S 学生学号，平均成绩 from (select S,avg(score) 平均成绩 from SC group by S)T2 order by 平均成绩 desc;