较复杂的SQL例句
S(Sno,Sname,Sage,Sdept, Ssex) 学生表
C(Cno,Cname,Teacher) 课程表
SC(Sno,Cno,score) 成绩表
1、查询“001”课程比“002”课程成绩低的所有学生的学号；
  select a.Sno from (select sno, score from SC where Cno='001') a, (select sno, score from SC where Cno='002') b where a.score<b.score and a.sno=b.sno;
2、查询平均成绩大于60分的同学的学号和平均成绩；
    select Sno,avg(score) from sc group by Sno having avg(score) >60;
3、查询姓“高”的老师的个数；
  select count(distinct(Teacher)) from C where Teacher like '高%';
4、查询没学过“高凯”老师课的同学的学号、姓名；
    select S.Sno,S.Sname from S where Sno not in (select distinct( SC.Sno) from SC, C where SC.Cno=C.Cno and C.Teacher='高凯');
5、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select S.Sno, S.Sname from S, SC where S.Sno=SC.Sno and SC.Cno='001'and exists ( Select * from SC as SC_2 where SC_2.Sno=SC.Sno and SC_2.Cno='002');
6、查询学过“高凯”老师所教的所有课的同学的学号、姓名；
Select Sno,Sname from S where Sno in (select Sno from SC, C where SC.Cno=C.Cno and C.Teacher='高凯' group by Sno having count(SC.Cno)=(select count(Cno) from C where Teacher='高凯'))
8、查询课程编号“002”的成绩比课程编号“001”课程高的所有同学的学号、姓名；
  Select Sno,Sname from (select S.Sno, S.Sname, score, (select score from SC SC_2
where SC_2.Sno=S.Sno and SC_2.Cno='002') score2 from S, SC where S.Sno=SC.Sno and Cno='001') S_2  where score2 >score;
9、查询所有课程成绩大于60分的同学的学号、姓名；
  select Sno,Sname from S where Sno not in (select S.Sno from S, SC where S.Sno=SC.Sno and score<60);
10、查询没有学全所有课的同学的学号、姓名；
select S.Sno, S.Sname from S,SC where S.Sno=SC.Sno group by S.Sno having count(Cno) <(select count(Cno) from C)
11、查询至少有一门课与学号为“95001”的同学所学相同的同学的学号和姓名；
select S.Sno,S.Sname from S,SC where S.Sno=SC.Sno and Cno in (select Cno from SC where Sno='95001')
12、查询至少学过学号为“95001”同学所有一门课的其他同学学号和姓名；
select中distinct
select distinct SC.Sno,Sname from S,SC where S.Sno=SC.Sno and Cno in (select Cno from SC where Sno='95001')

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。