SQL基础综合练习题（39题）

https://download.csdn.net/download/ruyigongfang/89681313

可以用这个文件的建表语句在自己的pysql执行，就有该练习用的表。

https://download.csdn.net/download/ruyigongfang/89681312

该链接是只有题没有答案的文档。

所用到的表：

student（学生表）：sno（学号），sname（学生姓名），ssex（学生性别），sage（学生年龄）

course（课程表）：cno（课程号），cname（课程名称），tno（老师编号）

sc（成绩表）：sno（学号），cno（课程号），score（成绩）

teacher（老师表）：tno（老师编号），tname（老师姓名）

1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；

select  distinct t3.sno
  from student t3
  join sc t4
    on t3.sno = t4.sno
 where (select score
          from student t1
          join sc t2
            on t1.sno = t2.sno
         where cno = 'c001'
           and t1.sno = t3.sno) >
       (select score
          from student t1
          join sc t2
            on t1.sno = t2.sno
         where cno = 'c002'
           and t1.sno = t3.sno)
           
select t1.sno
  from (select score, sno from sc where cno = 'c001') t1
  join (select score, sno from sc where cno = 'c002') t2
    on t1.sno = t2.sno
   and t1.score > t2.score;

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

select* from student t1 join sc t2 on t1.sno=t2.sno where score>60;
select t1.sno,avg(score) from student t1 join sc t2 on t1.sno=t2.sno where score>60 group by t1.sno;

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

select t1.sno,t1.sname,count(1),sum(t2.score) from student t1 join sc t2 on t1.sno=t2.sno group by t1.sno,t1.sname;

4、查询姓“刘”的老师的个数；

select count(1) from teacher where tname like'刘%' ;

--5、查询没学过“刘阳”老师课的同学的学号、姓名；

select sno, sname
  from student
 where sno not in (select t2.sno
                       from sc t2
                 
                       join course t3
                         on t2.cno = t3.cno
                       join teacher t4
                         on t3.tno = t4.tno
                      where t4.tname = '刘阳');