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 = '刘阳');

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

select a1.sno, a1.sname
  from (select t1.*, t2.cno
          from student t1
          join sc t2
            on t1.sno = t2.sno
         where t2.cno = 'c001') a1
  join (select t1.*, t2.cno
          from student t1
          join sc t2
            on t1.sno = t2.sno
         where t2.cno = 'c002') a2
    on a1.sno = a2.sno

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

select t1.sno, t1.sname
  from student t1
  join sc t2
    on t1.sno = t2.sno
  join course t3
    on t2.cno = t3.cno
  join teacher t4
    on t3.tno = t4.tno
 where t4.tname = '涛哥'
 group by t1.sno, t1.sname;

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

select t1.sno, t1.sname
  from student t1
  join sc t2
    on t1.sno = t2.sno
  join sc t3
    on t1.sno = t3.sno
   and t2.cno = 'c002'
   and t3.cno = 'c001'
 where t2.score < t3.score;

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

select t1.sno,t1.sname
  from student t1
  join sc t2
    on t1.sno = t2.sno
 where t1.sno not in (select sno
                     from (select t1.*, t2.score
                             from student t1
                             join sc t2
                               on t1.sno = t2.sno)
                    where score >= 60);

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

select sno, sname
  from (select t1.sno, t1.sname, count(1) js
          from student t1
          left join sc t2
            on t1.sno = t2.sno
          left join course t3
            on t2.cno = t3.cno
         group by t1.sno, t1.sname)
 where js < (select count(1) from course);

11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名；

select sno, sname
   from student
  where sno in (select sno
                  from sc
                 where cno in (select cno
                                 from (select t1.sno, t2.cno
                                         from student t1
                                         join sc t2
                                           on t1.sno = t2.sno)
                                where sno = 's001')
                   and sno != 's001');

12、检索“c004”课程分数小于60，按分数降序排列的同学学号

select t1.*,t2.cno,t2.score from student t1 join sc t2 on t1.sno=t2.sno where cno='c004' order by t1.sno  ;

13、查询和“s002”号的同学学习的课程完全相同的其他同学学号和姓名；

select sno, sname
  from student
 where sno in
       (select t1.sno
          from (select * from sc where sno in(
          select sno
                  from sc
                 group by sno
                having count(1) = (select count(1) from sc where sno = 's002'))) t1
          join sc t2
            on t2.sno = 's002' and t1.cno=t2.cno
           and t1.sno != 's002'
         group by t1.sno
        having count(1) = (select count(1) from sc where sno = 's002'));

14、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

select cno 课程ID,
       (select max(score) from sc where cno = t.cno) 最高分,
       (select min(score) from sc where cno = t.cno) 最低分
  from sc t
 group by cno;

15、按各科平均成绩从低到高和及格率的百分数从高到低顺序

及格率：大于60分的人数/总人数（选课总人数）

select t1.*, t2.bl
  from (select cno, avg(score) pj from sc group by cno ) t1
  join (select cno, bl
          from (select cno,
                       (select count(1)
                          from sc
                         where cno = t.cno
                           and score > 60) / count(1) over(partition by cno) bl
                  from sc t)
         group by cno, bl) t2
    on t1.cno = t2.cno order by t1.pj,t2.bl;

16、查询不同老师所教不同课程平均分从高到低显示

select t1.tname, t2.cno, avg(score)
  from teacher t1
  join course t2
    on t1.tno = t2.tno
  join sc t3
    on t2.cno = t3.cno
 group by t1.tname, t2.cno
 order by avg(score) desc;

17、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

select cno,
       cname,
       count(case
               when score <= 100 and score > 85 then
                1
               else
                null
             end) as "[100-85]",
       count(case
               when score <= 85 and score > 70 then
                1
               else
                null
             end) as "[85-70]",
       count(case
               when score <= 70 and score >= 60 then
                1
               else
                null
             end) as"[70-60]",
       count(case
               when score < 60 then
                1
               else
                null
             end) as"[ <60]"
  from (select t1.*, t2.cno, t2.score, t3.cname, t3.tno
          from student t1
          join sc t2
            on t1.sno = t2.sno
          join course t3
            on t2.cno = t3.cno)
 group by cno, cname;

18、查询各科成绩前三名的记录:(不考虑成绩并列情况)

select t1.*, t2.sname
  from (select *
          from (select cno,
                       sno,
                       rank() over(partition by cno order by score) pm
                  from sc)
         where pm <= 3) t1
  join student t2
    on t1.sno = t2.sno
 order by cno, pm;

19、查询每门课程被选修的学生数

select cno,(select count(1) from sc where cno = t.cno) from sc t group by cno;

20、查询出只选修了一门课程的全部学生的学号和姓名

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

21、查询男生、女生人数

select ssex,(select count(1) from student where ssex=t.ssex) from student t group by ssex;

22、查询姓“张”的学生名单

select sname from student where sname like '张%';

23、查询同名同性学生名单，并统计同名人数

select  t1.sname,count(1) from student t1 join student t2 on t1.sname=t2.sname and t1.sno!=t2.sno and t1.ssex=t2.ssex group by t1.sname;

24、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

select cno,avg(score) s from sc group by cno order by s,cno desc;

25、查询平均成绩大于80的所有学生的学号、姓名和平均成绩

select t1.sno,t1.sname,avg(score) from student t1 join sc t2 on t1.sno=t2.sno  group by t1.sno,t1.sname having avg(score)>80;

26、查询课程名称为“J2SE”，且分数低于60 的学生姓名和分数

select t1.sname,t2.score from student t1 join sc t2 on t1.sno=t2.sno join course t3 on t2.cno=t3.cno where t3.cname='J2SE' and score<60;

27、查询所有学生的选课情况；

select sname, wm_concat(cname) from (select * from student t1 join sc t2 on t1.sno=t2.sno join course t3 on t2.cno=t3.cno)  group by sname;

28、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数

select  t1.sname,t3.cname,t2.score 
from student t1 join sc t2 on t1.sno=t2.sno join course t3 on t2.cno=t3.cno 
where score > 70;

29、查询不及格的课程，并按课程号从大到小排列

select * from sc where score<60 order by cno;

30、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名

select * from student t1 join sc t2 on t1.sno=t2.sno where t2.cno='c0001'and t2.score>80;

31、求选了课程的学生人数

select count(1) from student t1 left join sc t2 on t1.sno=t2.sno where t2.cno is null group by t1.sno;

32、查询选修“涛哥”老师所授课程的学生中，成绩最高的学生姓名及其成绩

select cno, sname, score
  from (select t1.*,
               t2.cno,
               t2.score,
               dense_rank() over(partition by t2.cno order by t2.score desc) pm
          from student t1
          join sc t2
            on t1.sno = t2.sno
          join course t3
            on t2.cno = t3.cno
          join teacher t4
            on t3.tno = t4.tno
         where t4.tname = '涛哥'
         order by t2.cno, t2.score)
 where pm = 1;

33、查询各个课程及相应的选修人数

select cno,count(1) from sc group by cno;

34、查询不同课程成绩相同的学生的学号、课程号、学生成绩

select sname, cno, score
  from (select t1.*, t2.cno, t2.score
          from student t1
          left join sc t2
            on t1.sno = t2.sno)
 where (cno, score) in (select t2.cno, t2.score
                          from student t1
                          left join sc t2
                            on t1.sno = t2.sno
                         group by t2.cno, t2.score
                        having count(1) > 1);

35、查询每门功课成绩最好的前两名

select cno, sname, score
  from (select t1.*,
               t2.cno,
               t2.score,
               dense_rank() over(partition by t2.cno order by t2.score desc) pm
          from student t1
          join sc t2
            on t1.sno = t2.sno
          join course t3
            on t2.cno = t3.cno
         order by t2.cno, t2.score)
 where pm < = 2
 order by cno, score desc;

36、统计每门课程的学生选修人数（超过10 人的课程才统计）。

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select t2.cno, count(1) from student t1 join sc t2 on t1.sno=t2.sno group by t2.cno having count(1)>10 order by count(1),t2.cno;

37、检索至少选修两门课程的学生学号

select t1.sno, count(1) from student t1 join sc t2 on t1.sno=t2.sno group by t1.sno having count(1)>=2;

38、查询全部学生都选修的课程的课程号和课程名

select t4.cno, t5.cname
  from (select cno
          from (select cno, count(1) numb
                  from student t1
                  join sc t2
                    on t1.sno = t2.sno
                 group by cno)
         where numb = (select count(1) from student)) t4
  join course t5
    on t4.cno = t5.cno;

39、查询两门以上不及格课程的同学的学号及其平均成绩

select sno, avg(score)
  from (select t1.*, t2.cno, t2.score
          from student t1
          join sc t2
            on t1.sno = t2.sno
         where score < 60)
 group by sno
having count(1) > 2;