（002）初级大数据开发_基础SQL(1题-10题)

数据开发小胡

已于 2022-04-05 14:34:51 修改

阅读量2.4k

点赞数 2

分类专栏： SQL 文章标签：数据库 mysql database 数据仓库 etl工程师

于 2022-04-04 20:17:05 首次发布

本文链接：https://blog.csdn.net/qq_42209718/article/details/123950276

版权

SQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一. 准备工作

--1.学生表Student(SId,Sname,Sage,Ssex)

--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

--2.课程表Course(CId,Cname,TId)

--CId 课程编号,Cname 课程名称,TId 教师编号

--3.教师表Teacher(TId,Tname)

--TId 教师编号,Tname 教师姓名

--4.成绩表SC(SId,CId,score)

--SId 学生编号,CId 课程编号,score 分数

/*
 * 50道SQL练习题
 * author:Rabin
 * address:shanghai
 * current_date:2022-04-03
   环境:sqlserver
 * source：https://www.jianshu.com/p/476b52ee4f1b ,author：Kaidi_G
 */


--1.学生表Student(SId,Sname,Sage,Ssex)
--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');

--2.课程表Course(CId,Cname,TId)
--CId 课程编号,Cname 课程名称,TId 教师编号
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');


--3.教师表Teacher(TId,Tname)
--TId 教师编号,Tname 教师姓名
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');


--4.成绩表SC(SId,CId,score)
--SId 学生编号,CId 课程编号,score 分数
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 "课程成绩高的学生的信息及课程分数

--1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
SELECT s.*
,t.score  
FROM Student s 
join 
    --查询" 01 "课程比" 02 "课程成绩高的学生
    ( SELECT a.sid , a.score 
     FROM (SELECT * FROM SC s WHERE s.CId ='01') as a
     JOIN (SELECT * FROM SC s WHERE s.CId ='02') as b 
     on a.sid=b.sid 
     WHERE a.score >b.score 
     )  t 
on s.SId=t.sid;

-- 1.1、查询同时存在" 01 "课程和" 02 "课程的情况

-- 1.1、查询同时存在" 01 "课程和" 02 "课程的情况
SELECT *
FROM 
(SELECT * FROM SC s WHERE s.CId ='01')  a
JOIN 
(SELECT * FROM SC s WHERE s.CId ='02')  b
on a.sid =b.sid ;

--1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

--1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
SELECT *
FROM 
(select * from SC s where s.cid='01') a
LEFT JOIN 
(select * from SC s where s.cid='02') b
on a.sid =b.sid ;

--1.3 查询不存在" 01 "课程但存在" 02 "课程的情况

--1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
SELECT *
from SC s2 
WHERE s2.SId NOT  in (SELECT  s.SId  FROM SC s WHERE s.CId ='01') and s2.CId ='02';

--2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

--2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
/*分析：先把student表和sc表进行关联，题目中说每个同学的平均成绩，那么就需要对学生进行分组并计算出每个同学分组之后的平均成绩
可能有人会疑惑为什么同时对学号，学生姓名进行分组，其实是为了可以把学生名字在select中输出
因为你对sid进行分组之后每个学生单独分为一组，那么一组里面的学生sname也是一样的，所以在根据sname进行分组
并不会改变什么*/
SELECT s.SId ,s.Sname ,AVG(s2.score) 
from Student s 
join SC s2 on s.SId =s2.SId 
GROUP BY s.SId ,s.Sname 
HAVING AVG(s2.score) >60;


--法二,先对sc表分组得出每个学生的平均成绩并筛选出大于60的学生id，在和student表进行关联得出大于60的学生信息
select student.SId,sname,ss from student,(
    select SId, AVG(score) as ss from sc  
    GROUP BY SId 
    HAVING AVG(score)> 60
    )r
where student.sid = r.sid;

--3.查询在 SC 表中存在成绩的学生信息

--3.查询在 SC 表中存在成绩的学生信息
SELECT s2.*
from Student s2 
join 
(SELECT s.SId 
from SC s 
group by s.SId ) a
on s2.SId =a.sid ;

SELECT DISTINCT  s.* 
from Student s 
join SC s2 on s.SId =s2.SId ;

--4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和

--4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
--法一.先把student表和sc表进行关联然后根据sid进行分组，count（*）得出选课总数，sum(s2.score)得出总成绩
SELECT s.SId ,s.Sname ,COUNT(*),sum(s2.score) 
FROM Student s 
join SC s2 on s.SId =s2.SId 
GROUP by s.SId ,s.Sname ;

--法二
--第一步先对sc进行分组查出学生编号，选课总数，课程之和
SELECT s.SId ,COUNT(*),SUM(s.score)  
FROM  SC s 
group by s.SId ;
--第二步把上面查出来的内容作为临时表和student进行关联
SELECT s.Sname ,t.*
FROM  Student s 
join (select s2.sid ,count(*) as coursenum,sum(s2.score) as scorenum from SC s2 group by s2.SId)t
on s.SId =t.sid ;

--4.1 查有成绩的学生信息

--4.1 查有成绩的学生信息
SELECT DISTINCT  s.*
from Student s 
join SC s2 on s.SId =s2.SId;

--5.查询「李」姓老师的数量

--5.查询「李」姓老师的数量
SELECT count(*)
from Teacher t 
where t.Tname like '李%';

-- 6.查询学过「张三」老师授课的同学的信息

-- 6.查询学过「张三」老师授课的同学的信息
--第一步:先找出张三老师的id
SELECT t.TId 
from Teacher t where t.Tname ='张三';
--第二步：找到张三老师教授的课程的id
SELECT c.CId 
from Course c 
join (SELECT t.TId 
from Teacher t where t.Tname ='张三') a
on c.TId =a.tid ;
--第三步
SELECT s.*,t.TId as '老师编号' ,s2.CId as '课程id'
from Student s 
join SC s2 on s.SId =s2.SId 
join Course c on c.CId =s2.CId 
join Teacher t on t.TId =c.TId 
where t.Tname ='张三';

--7.查询没有学全所有课程的同学的信息

--7.1查询成绩表中没有学全所有课程的同学的信息

--7.查询没有学全所有课程的同学的信息
--第一步：先统计有多少门课程
SELECT COUNT(*) FROM Course c;
--第二步：对sc表进行分组分别统计学生学习的课程数小于第一步统计的门数，并输出不满足全部学习课程的学生id
SELECT s.SId 
FROM SC s 
group by s.SId 
HAVING COUNT(*)=(SELECT COUNT(*) FROM Course c);
--第三步：把第二步中找到的学生id作为临时表和student表进行关联并输出对应学生的信息
SELECT s3.*
from Student s3 
WHERE s3.SId not in(SELECT s.SId 
FROM SC s 
group by s.SId 
HAVING COUNT(*)=(SELECT COUNT(*) FROM Course c));
--7.1查询成绩表中没有学全所有课程的同学的信息
select s2.*
from Student s2 	
join (SELECT s.SId 
FROM SC s 
group by s.SId 
HAVING COUNT(*)<(SELECT COUNT(*) FROM Course c)) t
on s2.SId =t.sid ;

--8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

--8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
--第一步:获取学号为01的学生所学了哪些课程
SELECT s.CId 
from SC s 
where s.SId  ='01';
--第二步:把第一步作为子查询去匹配学生的id
SELECT DISTINCT  s2.SId 
from SC s2 where s2.CId in 
(SELECT s.CId 
from SC s 
where s.SId  ='01');
--第三步：把第二步作为子查询去关联student表
SELECT s3.*
from Student s3 where s3.SId in
(SELECT DISTINCT  s2.SId 
from SC s2 where s2.CId in 
(SELECT s.CId 
from SC s 
where s.SId  ='01')
);

--9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

--9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
--第一步：查询出sid=01学生学习的课程号
SELECT s.SId  ,COUNT(s.CId) 
from SC s  where s.SId ='01'
GROUP by s.SId ;
--第二步:找出学完3门课程的学生
SELECT s.SId ,count(*)
from SC s group by s.SId 
HAVING count(*)=(SELECT COUNT(*) FROM Course c);
--第三步:把第二步得出sid从student表中输出并且保证不是01学生
SELECT s.*
from Student s 
where s.SId in
(SELECT s.SId
from SC s group by s.SId 
HAVING count(*)=(SELECT COUNT(*) FROM Course c)) and s.SId <>'01';

--10.查询没学过"张三"老师讲授的任一门课程的学生姓名

--10.查询没学过"张三"老师讲授的任一门课程的学生姓名
--第一步：找到张三老师所教授的课程编号
SELECT c.CId --张三老师教授的课程为02数学
FROM Teacher t 
join Course c on t.TId =c.TId 
where t.Tname ='张三';
--第二步：查询出学习张三老师所教授的学生id
SELECT s.SId 
FROM SC s 
group by s.SId ,s.CId 
HAVING s.CId =(SELECT c.CId
FROM Teacher t 
join Course c on t.TId =c.TId 
where t.Tname ='张三');
--第三步：作为student表的子查询
SELECT s2.SId ,s2.Sname 
FROM  Student s2 
WHERE s2.SId not in(SELECT s.SId 
FROM SC s 
group by s.SId ,s.CId 
HAVING s.CId =(SELECT c.CId
FROM Teacher t 
join Course c on t.TId =c.TId 
where t.Tname ='张三')
);

数据开发小胡

关注

2
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
（002）初级大数据开发_基础SQL(1题-10题)

一.准备工作--1.学生表Student(SId,Sname,Sage,Ssex)--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别--2.课程表Course(CId,Cname,TId)--CId 课程编号,Cname 课程名称,TId 教师编号--3.教师表Teacher(TId,Tname)--TId 教师编号,Tname 教师姓名--4.成绩表SC(SId,CId,score)--SId 学生编号,CId 课程编号,score 分数.
复制链接

扫一扫