(001)初级大数据开发_基础SQL

/*

* 复杂sql练习

* 2022-04-01

*author：Rabin

*address:shanghai

*题目来源：https://zhuanlan.zhihu.com/p/38354000,作者：猴子数据分析

*

*学生表：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 ='孟扎扎'

)

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 216,324评论 6赞 498
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,356评论 3赞 392
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 162,328评论 0赞 353
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,147评论 1赞 292
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,160评论 6赞 388
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,115评论 1赞 296
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,025评论 3赞 417
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,867评论 0赞 274
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,307评论 1赞 310
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,528评论 2赞 332
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,688评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,409评论 5赞 343
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 41,001评论 3赞 325
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,657评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,811评论 1赞 268
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,685评论 2赞 368
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,573评论 2赞 353

推荐阅读更多精彩内容