SQL经典题目（更新ing）

写在前面：

        本人目前在秋招ing，想通过在CSDN写文章督促自己每天学一点qwq。

        本科写SQL用的SQL Server Management Studio， 研究生用的DBeaver。之前没太用过MySQL，所以代码的书写风格可能会有所不同。本文使用SQL Server Management Studio进行代码书写。

         PS：本人写码水平一般，如有错误，欢迎各位指正~

create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(sid varchar(10),cid varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

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

select distinct a.sid
  from (select sid, score
          from SC
         where cid = '01') as a
         left join
       (select sid, score
          from SC
         where cid = '02') as b on a.sid = b.sid
 where a.score > b.score;

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

select sid, AVG(score)
  from SC
 group by sid
having AVG(score) > 60;

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

select S.sid, S.sname, COUNT(distinct cid), SUM(score)
  from Student S
  left 
  join SC on S.sid = SC.sid
 group by S.sid, S.sname;

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

select COUNT(distinct tid)
 from Teacher
where tname like '李%';

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

select distinct S.sid, S.sname
  from Student S
 where S.sid not in (select SC.sid
                       from SC
					   left
					   join Course C on C.cid = SC.cid
					   left
					   join Teacher T on C.tid = T.tid
					  where tname = '张三');

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

select a.sid, a.sname
  from (select S1.sid, S1.sname
		  from Student S1
		  left 
		  join SC on S1.sid = SC.sid
		 where cid = '01') as a,
	   (select S2.sid, S2.sname
		  from Student S2
		  left 
		  join SC on S2.sid = SC.sid
		 where cid = '02') as b
 where a.sid = b.sid;

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

select S.sid, S.sname
  from Student S
  left
  join SC on S.sid = SC.sid
  left
  join Course C on SC.cid = C.cid
  left 
  join Teacher T on C.tid = T.tid
 where T.tname = '张三';

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

select a.sid, a.sname
  from (select S1.sid, S1.sname, SC1.score
		  from Student S1
		  left
		  join SC SC1 on S1.sid = SC1.sid
		 where cid = '01') as a,
		 (select S2.sid, S2.sname, SC2.score
		  from Student S2
		  left
		  join SC SC2 on S2.sid = SC2.sid
		 where cid = '02') as b
 where a.sid = b.sid and a.score < b.score;

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

select S.sid, S.sname, MAX(score)
  from Student S
  left
  join SC on S.sid = SC.sid
 group by S.sid, S.sname
 having MAX(score) < 60;

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

select S.sid, sname
  from Student S
  left
  join SC on S.sid = SC.sid
 group by S.sid, sname
having COUNT(cid) < (select COUNT(cid)
                       from Course);

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

select distinct S.sid, sname
  from Student S
  left 
  join SC on S.sid = SC.sid
 where cid in (select cid
                 from SC
                where sid = '01');

12. 查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名；

select distinct S.sid, sname
  from Student S
  left 
  join SC on S.sid = SC.sid
 where cid in (select cid
                 from SC
                where sid = '01')
 group by S.sid, sname
having COUNT(cid) = (select COUNT(cid)
                       from SC
                      where sid = '01');

13. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩；

select S.sid, S.sname, AVG(score)
  from Student S,
      (select cid, sid, score
                   from SC
                where score < 60) as a
 where S.sid = a.sid
 group by S.sid, S.sname
having COUNT(distinct cid) >= 2;

14. 检索"01"课程分数小于60，按分数降序排列的学生信息；

select * 
  from Student S
  left
  join SC on S.sid = SC.sid
 where cid = '01' and score < 60
 order by score desc;

15. 按平均成绩从高到低显示所有学生的平均成绩；

select sid, AVG(score)
  from SC
 group by sid
 order by AVG(score);