作者：数据开发小胡同学
链接：https://www.zhihu.com/question/486712700/answer/2424239325
来源：知乎
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
/*
* 复杂sql练习
* 2022-04-01
*author：Rabin
*address:shanghai
*题目来源：https://zhuanlan.zhihu.com/p/38354000,作者：猴子数据分析
使用环境:sqlserver
*
*学生表：student(学号,学生姓名,出生年月,性别)
成绩表：score(学号,课程号,成绩)
课程表：course(课程号,课程名称,教师号)
教师表：teacher(教师号,教师姓名)
*/
-- 一、创建数据库和表
-- 1、学生表
CREATE table student (
sid int,--学号
sname varchar(20),--姓名
sbirth varchar(20), --出生日期
sgender varchar(20)--性别
);
INSERT into student values (0001,'猴子','1989-01-01','男'),(0002,'猴子','1990-12-21','女'),(0003,'马云','1991-12-21','男'),(0004,'王思聪','1990-05-20','男');
SELECT * from student ;
-- 2.创建学生成绩表
create table score (
sid int ,--学号
scoure int,--课程号
sscore int, --成绩
primary key (sid,scoure)
);
INSERT into score values (0001,0001,80),(0001,0002,90),(0001,0003,99),(0002,0002,60),(0002,0003,80),(0003,0001,80),(0003,0002,80),(0003,0003,80);
SELECT * from score ;
-- 创建课程表
create table course (
scoure int ,--课程号
cname varchar(20),--课程名称
cteacher int
);
INSERT into course values (001,'语文',0002),(0002,'数学',0001),(0003,'英语',0003);
SELECT * from course ;
--创建教师表
create table teacher(
cteacher int,--教师号
tname varchar (20)
);
INSERT into teacher values (0001,'孟扎扎'),(0002,'马化腾'),(0003,null),(0004,'');
SELECT * from teacher ;
-- 1、查询姓“猴”的学生名单
SELECT * from student s where s.sname like '猴%';
-- 1.1查询姓名中最后一个字是猴的学生名单
SELECT * from student s where s.sname like '%猴';
-- 1.2查询姓名中带猴的学生名单
SELECT * from student s where s.sname like '%猴%';
-- 1.3、查询姓“孟”老师的个数
SELECT count(*) from student s where s.sname like '孟%';
--2、查询课程编号为“0002”的总成绩
SELECT sum(s.sscore) from course c join score s on c.scoure =s.scoure where c.scoure =002 ;
-- 2.1、查询选了课程的学生人数
SELECT count( DISCONNECT s.sid) from score s;
-- 3、查询各科创建最高和最低分数
SELECT scoure , max(sscore),min(sscore) from score group by scoure ;
-- 3.1、查询每门课程被选修学生数
SELECT * from course ;
SELECT s.scoure,count(*) from score s join course c on s.scoure =c.scoure group by s.scoure ;
-- 3.2、查询男生，女生人数
SELECT sgender , count(*) from student group by sgender ;
-- 4、查询平均成绩大于70分学生的学号和平均成绩
SELECT s.sid ,AVG(s.sscore) from score s group by s.sid HAVING AVG(s.sscore)>70 ;
-- 4.1、查询至少选修两门课程的学生学好
SELECT s.sid,count(s.scoure)from score s group by s.sid HAVING count(s.scoure)>=2;
-- 4.2、查询同名同性学生名单并统计同名人数
SELECT count(*),s2.sname FROM student s2 group by s2.sname HAVING count(*)>1;
-- 4.3、查询不及格的课程并按课程号从大到小排列
SELECT s.scoure,s.sscore from score s where s.sscore <60 ORDER BY s.scoure
-- 4.4、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
SELECT s2.scoure,AVG(s2.sscore) from score s2 group by s2.scoure ORDER by avg(s2.sscore),s2.scoure DESC ;
SELECT * from score s ;
-- 4.5、检索课程编号为“0004”且分数小于60的学生学号，结果按按分数降序排列
SELECT s.sid ,s.sscore from score s where s.sid =004 and s.sscore <60 ORDER BY s.sscore DESC ;
SELECT * from score s ;
SELECT * from course c ;
--4.6、统计每门课程的学生选修人数(超过2人的课程才统计)
--要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序
SELECT s.scoure ,count(s.sid) FROM score s group by s.scoure HAVING count(s.sid)>2 ORDER BY count(s.sid) desc,s.scoure ;
-- 4.7、查询两门以上不及格课程的同学的学号及其平均成绩
SELECT s.sid , AVG(s.sscore)
FROM score s
WHERE s.sscore<60
group by s.sid
HAVING COUNT(s.scoure)>2;
-- 5、查询学生的总成绩并进行排序
SELECT s.sid , sum(s.sscore)
FROM score s
group by s.sid
ORDER by sum(s.sscore);
-- 5.1、查询平均成绩大于60分的学生学号和平均成绩
SELECT s.sid,AVG(s.sscore)
from score s
group by s.sid
HAVING AVG(s.sscore)>60
ORDER BY AVG(s.sscore) ;
--复杂查询
-- 6、查询所有课程成绩小于80分学生的学号、姓名
--法一
SELECT s.sid,s2.sname
from score s
join student s2
on s.sid =s2.sid
WHERE s.sscore <80;
--法二
SELECT s2.sid ,s2.sname
FROM student s2
where s2.sid in
(SELECT s.sid
from score s
where s.sscore<80);
-- 6.1、查询没有学全所有课的学生的学号、姓名
SELECT s3.sid ,s3.sname from student s3 where s3.sid in (
-- 2.把学生id作为筛选条件葱student表中找到
SELECT s.sid -- 1.先把没有学全的学生id找到
from score s
group by s.sid
HAVING count(*)<(SELECT count(*) from course c));
--6.2、查询出只选修了两门课程的全部学生的学号和姓名
--法一
SELECT s2.sid ,s2.sname
FROM student s2
where s2.sid in
(SELECT s.sid
from score s
group by s.sid
HAVING count(s.sid)=2)
/*
查找1990年出生的学生名单
学生表中出生日期列的类型是datetime
*/
SELECT * from student s where s.sbirth LIKE '%1990%' ;
--按照学生id对score进行排序并输出前三条记录
SELECT top 3 * FROM score order by sid;
-- 查询各学生的年龄（精确到月份）
SELECT SUBSTRING(s.sbirth,6,7)from student s
-- 找出本月过生日的学生
select *
from student
where month(SUBSTRING(s.sbirth,6,10) )= month(current_date);
-- 7.查询所有学生的学号、姓名、选课数、总成绩
SELECT s.sid,count(s2.scoure),SUM(s2.sscore)
from student s left join score s2
on s.sid =s2.sid
group by s.sid
select a.sid,count(b.scoure),sum(b.sscore)
from student as a left join score as b
on a.sid = b.sid
group by a.sid;
-- 7.1、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT s2.sid , s2.sname
from student s2 where s2.sid in-- 1
(SELECT s.sid
from score s
group by s.sid
HAVING AVG(s.sscore)>85)
select a.sid ,max(a.sname) ,avg(b.sscore)
from student as a left join score as b
on a.sid = b.sid
group by a.sid
having avg(b.sscore)>85;
-- 7.2、查询学生的选课情况：学号，姓名，课程号，课程名称
SELECT s.sid ,s.sname ,c.scoure ,c.cname
from student s
left join score s2 on s.sid =s2.sid
LEFT join course c on s2.scoure =c.scoure;
-- 7.3、查询出每门课程的及格人数和不及格人数（***）
SELECT
s.scoure
,sum(case when s.sscore<60 then 1 else 0 end ) as '不及格人数'
,sum(case when s.sscore>=60 then 1 else 0 end ) as '及格人数'
from score s
GROUP by s.scoure
-- 7.4、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称
SELECT s2.scoure ,c.cname
,sum(case when s2.sscore<60 then 1 else 0 end ) as '不及格人数'
,sum(case when s2.sscore between 60 and 70 then 1 ELSE 0 END) as '60-70分人数'
,sum(case when s2.sscore BETWEEN 70 and 80 then 1 ELSE 0 END) as '70-80分人数'
,sum(case when s2.sscore BETWEEN 85 and 100 then 1 ELSE 0 END) as '85-100分人数'
from score s2
left join course c on s2.scoure =c.scoure
group by s2.scoure ,c.cname ;
-- 7.5、查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名
SELECT a.sid ,s2.sname,a.sscore
from student s2 join
(SELECT *
from score s
where s.scoure =3 and s.sscore >80) a
on s2.sid =a.sid
SELECT s.sid ,s.sname,s2.sscore
from student s inner join score s2
on s.sid =s2.sid
where s2.scoure =3 and s2.sscore >80
-- 8.sql面试题：行列如何互换？（****）
select s.sid ,'课程号0001','课程号0002','课程号0003'
from score s ;
select s.sid
,MAX((case s.scoure when 1 then s.sscore else 0 end )) as '课程号0001'-- 当s.scoure =1时输出s.sscore 否则就为0
,max((case s.scoure when 2 then s.sscore else 0 end)) as '课程号0002'
,max((case s.scoure when 3 then s.sscore else 0 end))as '课程号0003'
from score s
group by s.sid ;
--9.多表连接
--9.1、检索0001课程分数小于90，按照分数降序排列的学生信息
SELECT s3.*,a.scoure ,a.sscore
from student s3 right join
(SELECT *
from score s2
where s2.sscore <90 and s2.scoure =1
) a
on s3.sid =a.sid
ORDER BY a.sscore DESC
SELECT * from score s ;
SELECT s.*,s2.sscore ,s2.scoure
from student s inner join score s2
on s.sid =s2.sid
where s2.sscore <90 and s2.scoure =1
ORDER BY s2.sscore DESC ;
-- 9.2、查询不同老师所教授不同课程平均分从高到低显示
SELECT t.cteacher,avg(s.sscore)
from score s
join course c on s.scoure =c.scoure
join teacher t on t.cteacher =c.cteacher
GROUP by t.cteacher
ORDER BY AVG(s.sscore) DESC
SELECT * from teacher t ;
-- 9.3 查询课程名称为"数学"，且分数低于60的学生姓名和分数
SELECT s2.sname ,s.sscore,s.sid
from course c
inner join score s on c.scoure =s.scoure
INNER join student s2 on s.sid =s2.sid
where c.cname ='数学' and s.sscore <90
-- 9.4、查询任何一门课程成绩在70分以上的姓名、课程名称和分数（与上题类似）
SELECT s3.sname ,c2.cname ,s4.sscore
from student s3
join score s4 on s3.sid =s4.sid
join course c2 on c2.scoure =s4.scoure
WHERE s4.sscore >70
-- 9.5、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
--法1⃣
SELECT s.sid ,avg(s2.sscore)
from student s
join score s2 on s.sid =s2.sid
WHERE s2.sscore <90 --先整体过滤出不及格(先初步筛选后分组，再对分组进一步筛选)
group by s.sid --再根据学生id进行分组
HAVING count(s.sid)>=2;--每个学生里面的记录大于等于2就说明
--法二
SELECT s.sid ,avg(s2.sscore)
from student s
join score s2 on s.sid =s2.sid
group by s.sid --（先分组，后对每一组进行筛选）
HAVING sum(case when s2.sscore<90 then 1 ELSE 0 end)>=2;
-- 9.6、查询学生的总成绩并进行排名
SELECT s.sid ,AVG(s.sscore)
from score s
group by s.sid
ORDER by sum(s.sscore)
--9.7、查询平均成绩大于60分学生的学号和平均成绩
SELECT s.sid ,avg(s.sscore)
FROM score s
group by s.sid
HAVING AVG(s.sscore)>60
-- 10.1、查询所有课程成绩小于60分学生的学号、姓名
--法一
SELECT s.sid ,s2.sname ,s.sscore
FROM score s
join student s2 on s.sid =s2.sid
WHERE s.sscore <90;
--法二
SELECT s2.sid ,s2.sname
FROM student s2
where s2.sid IN
(SELECT s.sid
from score s
where s.sscore <90);
-- 10.2、查询没有学全所有课的学生的学号、姓名
SELECT s.sid ,s2.sname
from score s
join student s2 on s.sid =s2.sid
GROUP BY s.sid,s2.sname
HAVING count(*)<(SELECT count(*) from course c );
-- 10.3、查询出只选修了两门课程的全部学生的学号和姓名
SELECT s.sid ,s2.sname
from score s
join student s2 on s.sid =s2.sid
GROUP BY s.sid,s2.sname
HAVING count(*)=2;
/*
* CURRENT_DATE :2022-04-03
* current_time:11:05:23
* current_timestamp:2022-04-03 05:23
*
* */
SELECT YEAR ('2022-04-03') --2022
SELECT MONTH ('2022-04-03') --4
SELECT day ('2022-04-03') --3
select * from score s
--11.1、查询所有学生的学号、姓名、选课数、总成绩
select s.sid ,s.sname ,count(*),sum(s2.sscore)
FROM student s
join score s2 on s.sid =s2.sid
GROUP BY s.sid ,s.sname;
--11.2、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT s.sid ,s.sname ,AVG(s2.sscore)
from student s
join score s2 on s.sid =s2.sid
GROUP BY s.sid,s.sname
HAVING AVG(s2.sscore)>85 ;
--11.3、查询学生的选课情况：学号，姓名，课程号，课程名称
SELECT s.sid ,s.sname ,s2.scoure ,c.cname
FROM student s
join score s2 on s.sid =s2.sid
JOIN course c on s2.scoure =c.scoure ;
--GROUP by s.sid ,s.sname ,s2.scoure ,c.cname
--11.4、查询出每门课程的及格人数和不及格人数
SELECT s.scoure as '课程号'
,SUM(CASE when s.sscore<60 then 1 else 0 end) as '不及格人数'
,SUM(case when s.sscore>60 then 1 else 0 end) as '及格人数'
from score s
group by s.scoure ;
--11.5、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称
--考察case when,between 小 and 大
SELECT s.scoure ,c.cname
,sum(case when s.sscore between 85 and 100 then 1 else 0 END ) as '100-85'
,sum(case when s.sscore BETWEEN 70 and 85 then 1 else 0 END ) as '85-70'
,sum(CASE WHEN s.sscore BETWEEN 60 and 70 then 1 else 0 END ) as '70-60'
,sum(case when s.sscore<60 then 1 else 0 END ) as '小于60'
from score s
join course c on s.scoure =c.scoure
GROUP by s.scoure ,c.cname ;
--11.6、查询课程编号为3且课程成绩在80分以上的学生的学号和姓名
SELECT * from score s2
select s3.sid ,s3.sname
FROM score s
join student s3 on s.sid =s3.sid
where s.scoure =3 and s.sscore >80;
--11.7、对score进行行转列
SELECT * FROM score s2
--第一步：
SELECT s.sid ,'课程表001','课程表002','课程表003'
FROM score s ;
--第二步：
SELECT s.sid --每一条记录都会走下面的3个case when
--例如sid=1,source=1,sscore=80,
--第一个case when 判断scoure是否为1，是的话就输出80，否则就输出0
--第二个case when 判断scoure是否为2，不是话就输出0
--第三个case when 判断scoure 是否为3，不是的话就输出0
,case s.scoure when 1 then s.sscore else 0 end as '课程表001'
,case s.scoure when 2 then s.sscore else 0 end as '课程表002'
,case s.scoure when 3 then s.sscore else 0 end as '课程表003'
FROM score s ;
--第三步：
SELECT s.sid
,sum(case s.scoure when 1 then s.sscore else 0 end) as '课程表001'
,sum(case s.scoure when 2 then s.sscore else 0 end) as '课程表002'
,sum(case s.scoure when 3 then s.sscore else 0 end) as '课程表003'
FROM score s
group by s.sid ;
-- 12.1、-检索"0001"课程分数小于90，按分数降序排列的学生信息
SELECT s2.*,s.*
from score s
join student s2 on s.sid =s2.sid
where s.scoure =1 and s.sscore <90
ORDER by s.sscore DESC ;
--12.2、-查询不同老师所教不同课程平均分从高到低显示
SELECT t.tname ,t.cteacher,AVG(s.sscore)
FROM score s
join course c on s.scoure =c.scoure
join teacher t on t.cteacher =c.cteacher
GROUP BY t.tname,t.cteacher
ORDER BY AVG(s.sscore) DESC;
--12.3、查询课程名称为"数学"，且分数低于90的学生姓名和分数
SELECT s.sid,s.sname,s2.sscore
FROM student s
join score s2 on s.sid =s2.sid
join course c on s2.scoure =c.scoure
where c.cname ='数学' and s2.sscore <90;
--12.4、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
SELECT s.sname ,c.cname ,s2.sscore
FROM student s
join score s2 on s.sid =s2.sid
join course c on s2.scoure =c.scoure
where s2.sscore >70;
--12.5、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT s.sid ,s.sname ,avg(s2.sscore)
FROM student s
join score s2 on s.sid =s2.sid
where s2.sscore <90 --先过滤
group by s.sid ,s.sname
HAVING count(*)>=2; --后对分组数据就行筛选
-- 12.6、查询课程编号为“0001”的课程比“0002”的课程成绩等于或者低的所有学生的学号，成绩，和课程号
--思路：把0001和0002课程的学生分别找出来然后对两个临时表进行join然后进行筛选
--先把0001和0002课程的学生分别找出来
SELECT a.*
FROM
(select * from score s where s.scoure=1) as a
JOIN
(select * from score s where s.scoure=2) as b
on a.sid =b.sid
where a.sscore <=b.sscore ;
--12.7、查询学过“孟扎扎”老师所教的所有课的同学的学号、姓名
SELECT s.sid,s.sname
from student s
join score s2 on s.sid =s2.sid
join course c on c.scoure =s2.scoure
join teacher t on t.cteacher =c.cteacher
where t.tname ='孟扎扎'
-- 12.8、查询没学过"孟扎扎"老师讲授的任一门课程的学生姓名
SELECT s3.sname
FROM student s3
where s3.sid not in
(
SELECT s.sid
from student s
join score s2 on s.sid =s2.sid
join course c on c.scoure =s2.scoure
join teacher t on t.cteacher =c.cteacher
where t.tname ='孟扎扎'
)
