SQL语句强化练习50题

sunny龙龙哥

于 2019-10-08 17:14:31 发布

阅读量320

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/qq_42337368/article/details/102396614

版权

SQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

SQL语句强化练习50题

这些题目是我在网上看到的，自己做一下，写一些自己的理解，感觉挺有意思的，正在更新，欢迎大家提供材料
数据表介绍

–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 分数

学生表 Student

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' , '女');

科目表 Course

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');

教师表 Teacher

create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

成绩表 SC

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 "课程成绩高的学生的信息及课程分数
方法一：


-- 分别查出 课程为 01,02 的 学生的学号 课程id 和成绩

SELECT * FROM sc WHERE CId='01'; 
SELECT * FROM sc WHERE CId='02'; 

-- 然后  条件为 SId相等的情况下 课程编号为01的大于02 的他们的成绩

SELECT  t1.SId,class1,class2 FROM 
(SELECT SId, score AS class1 FROM sc WHERE CId='01') AS t1 ,
(SELECT SId, score AS class2 FROM sc WHERE CId='02') AS t2
 WHERE t1.SId=t2.SId AND t1.class1>t2.class2;
 
 -- 通过现有的学生id从学生表中找到学生所有信息,通过join on的方式，将临时表与student表连接起来。
 
 SELECT * FROM student JOIN (
 SELECT  t1.SId,class1,class2 FROM 
(SELECT SId, score AS class1 FROM sc WHERE CId='01') AS t1 ,
(SELECT SId, score AS class2 FROM sc WHERE CId='02') AS t2
 WHERE t1.SId=t2.SId AND t1.class1>t2.class2)t 
 ON t.SId=student.`SId`;

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

-- 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
--  通过学生id将课程分组  ,然后  对每个分组求取分数的平均，加上判断条件平均分数大于60.
SELECT SId,AVG(score) FROM sc GROUP BY SId HAVING AVG(score)>60 ;
-- 通过学生表和上表的共同键 SId进行查询，join是将两张拥有相同字段的表合成一张表
SELECT * FROM student JOIN (SELECT SId,AVG(score)AS ss FROM sc GROUP BY SId HAVING AVG(score)>60)r ON r.SId=student.`SId`;
-- 也可以联合查询，通过SId进行判断
SELECT student.`SId`,student.`Sname`,r.ss FROM student ,(SELECT SId,AVG(score)AS ss FROM sc GROUP BY SId HAVING AVG(score)>60)r WHERE r.SId=student.SId;s

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

方法一
select DISTINCT student.*
from student,sc
where student.SId=sc.SId
方法二
-- 查询在 SC 表存在成绩的学生信息
-- 将课程成绩表通过学生的id进行分组 查询学生的id就得到了有成绩的学生id
SELECT  SId FROM sc GROUP BY SId 
-- 将上表和学生信息表进行联合查询或者是join
SELECT st.SId,st.SName FROM student AS st,(SELECT  SId FROM sc GROUP BY SId )t WHERE st.SId=t.SId;
-- 或
SELECT * FROM student JOIN (SELECT  SId FROM sc GROUP BY SId )t ON student.`SId`=t.SId;

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

-- 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
-- 将sc表通过学生id进行分组同时计算他们的选课数量以及总成绩
SELECT SId,SUM(score),COUNT(SId) FROM sc GROUP BY SId
-- 通过和学生信息表联合查询，查不到没有成绩的学生
SELECT  DISTINCT student.`SId`,student.`Sname`,t.su,t.cou FROM student,
(SELECT SId,SUM(score)AS su,COUNT(SId) AS cou FROM sc GROUP BY SId)t WHERE t.SId=student.`SId`

-- 现在我们来复习一下几种连接
-- JOIN: 如果表中有至少一个匹配，则返回行
-- LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
-- RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
-- FULL JOIN: 只要其中一个表中存在匹配，就返回行

-- 使用join  不会返回没有匹配的
SELECT  DISTINCT student.`SId`,student.`Sname`,su,cou FROM student JOIN
(SELECT SId,SUM(score)AS su,COUNT(SId) AS cou FROM sc GROUP BY SId)t ON t.SId=student.`SId`

-- 使用left join ， 即使右表中没有匹配，也从左表返回所有的行 
SELECT DISTINCT s.SId, s.Sname,r.coursenumber,r.scoresum
FROM (
    student AS s
    LEFT JOIN 
    (SELECT 
        sc.SId, SUM(sc.score) AS scoresum, COUNT(sc.CId) AS coursenumber
        FROM sc 
        GROUP BY sc.SId
    )r 
   ON s.SId = r.SId
);


-- RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
SELECT DISTINCT s.SId, s.Sname,r.coursenumber,r.scoresum
FROM (
    student AS s
    RIGHT JOIN 
    (SELECT 
        sc.SId, SUM(sc.score) AS scoresum, COUNT(sc.CId) AS coursenumber
        FROM sc 
        GROUP BY sc.SId
    )r 
   ON s.SId = r.SId
);

查有成绩的学生信息

-- 查询有成绩的学生信息
-- 将sc先根据学生的id进行分组
SELECT sc.`SId` FROM sc GROUP BY sid
-- 用学生信息表联合sc表查询，或者是用join
SELECT student.* FROM student,(SELECT sc.`SId` FROM sc GROUP BY sid)t WHERE student.`SId`=t.sid;

6.查询「李」姓老师的数量
复习一波：
SQL COUNT() 语法
SQL COUNT(column_name) 语法
COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）：

SELECT COUNT(column_name) FROM table_name
SQL COUNT() 语法
COUNT() 函数返回表中的记录数：

SELECT COUNT(*) FROM table_name
SQL COUNT(DISTINCT column_name) 语法
COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目：

SELECT COUNT(DISTINCT column_name) FROM table_name

-- 查询「李」姓老师的数量
SELECT COUNT(*) FROM teacher WHERE Tname LIKE '李%'

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

复习一波：
in 和 exists的区别: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了，另外IN时不对NULL进行处理。

in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。

not in 和not exists
　　　　如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

-- 查询学过「张三」老师授课的同学的信息
-- 查询 张三老师的id
SELECT TId FROM teacher WHERE Tname='张三'
-- 查询张三老师上的课程的id
SELECT CId FROM course WHERE TId IN (SELECT TId FROM teacher WHERE Tname='张三')
-- 查询 学过张三老师课程的同学id
SELECT DISTINCT SId FROM sc WHERE CId IN (SELECT CId FROM course WHERE TId IN (SELECT TId FROM teacher WHERE Tname='张三'))
-- 联合查询或者是join都可以
SELECT student.*FROM student,(SELECT DISTINCT SId FROM sc WHERE CId IN 
(SELECT CId FROM course WHERE TId IN (SELECT TId FROM teacher WHERE Tname='张三'))) t
WHERE student.`SId`=t.sid

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


-- 查询课程总数
SELECT COUNT(*)FROM course;
-- 查询选了所有课的学生，选课课数等于总课数
SELECT SId FROM sc GROUP BY SId 
HAVING COUNT(*)=(SELECT COUNT(*)FROM course)
-- 没有选全部课的学生没有在这个里面上结果集中  用not in解决
SELECT * FROM student WHERE SId NOT IN(
SELECT SId FROM sc GROUP BY SId 
HAVING COUNT(*)=(SELECT COUNT(*)FROM course))

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

-- 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
-- 查询出  学号为01 同学所学的课程编号结果集
SELECT CId FROM sc WHERE SId='01'
-- 查询出成绩表中CId出现在以上结果集中的记录，就达成这些课程是01同学学过的，那么只要这些同学学过这些课就达成条件
SELECT * FROM sc WHERE CId IN (SELECT CId FROM sc WHERE SId='01')
-- 将以上结果集通过SId分组出来，就找到了符合条件的学生SId，
SELECT SId FROM (
SELECT * FROM sc WHERE CId IN (
SELECT CId FROM sc WHERE SId='01'))t 
GROUP BY SId

-- 联合学生信息表或用join都可以
SELECT student.* FROM student,(SELECT SId FROM (
SELECT * FROM sc WHERE CId IN (
SELECT CId FROM sc WHERE SId='01'))t 
GROUP BY SId)r
WHERE r.SId=student.`SId`

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

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

-- 查询01同学的课程总数
SELECT COUNT(*) FROM sc WHERE SId='01'
-- 查询01同学的课程id集合
SELECT CId FROM sc WHERE SId='01'
-- 用表sc表在01同学课程id结果集合中 并且分组 求每位同学在这个范围内的课程总数

SELECT SId ,COUNT(*) AS c01 FROM (SELECT *FROM sc WHERE CId IN (SELECT CId FROM sc WHERE SId='01'))t GROUP BY SId 
-- 用表sc表求每位同学的课程总数
SELECT SId,COUNT(*) AS c02 FROM sc GROUP BY SId

-- 如果与01同学课程总数相同的同学，在01同学课程集合里的课程总数也与01同学课程总数相同，那么达成条件

SELECT t1.SId FROM (SELECT SId ,COUNT(*) AS c01 FROM 
(SELECT *FROM sc WHERE CId IN (SELECT CId FROM sc WHERE SId='01'))t GROUP BY SId )t1,
(SELECT SId,COUNT(*) AS c02 FROM sc GROUP BY SId)t2,
(SELECT COUNT(*) AS c03 FROM sc WHERE SId='01')t3 
WHERE c01=c03 AND c02 =c03 AND t1.SId=t2.SId  ORDER BY t1.SId

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

-- 查询没学过"张三"老师讲授的任一门课程的学生姓名
-- 查询张三老师的id
SELECT TId FROM teacher WHERE Tname='张三'
-- 查询张三老师所教的课程CId
   SELECT CId FROM course WHERE TId=(SELECT TId FROM teacher WHERE Tname='张三');
-- 通过sc表查询到学习了张三老师课程的学生ID
SELECT SId FROM sc WHERE CId IN (SELECT CId FROM course WHERE TId=(SELECT TId FROM teacher WHERE Tname='张三'))
-- 查询不在以上结果集中的学生信息
SELECT Sname FROM student WHERE SId NOT IN 
(SELECT SId FROM sc WHERE CId IN 
(SELECT CId FROM course WHERE TId=(SELECT TId FROM teacher WHERE Tname='张三')))

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

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

-- 在sc中查询出不及格记录的学生id，
SELECT * FROM sc WHERE score<60;  
-- 并根据SId分组求数量，通过having查询不及格数量大于等于2的学生ID
SELECT t.SId FROM (SELECT * FROM sc WHERE score<60)t GROUP BY SId HAVING COUNT(*)>=2
-- 查询平均成绩
SELECT SId,AVG(score)FROM sc GROUP BY SId

-- 查询学生信息,平均成绩，两门及其以上不及格学生学号
SELECT student.`SId`,student.`Sname`,r2.sav 
FROM student ,(SELECT t.SId FROM (SELECT * FROM sc WHERE score<60)t GROUP BY SId HAVING COUNT(*)>=2)r1,
(SELECT SId,AVG(score) AS sav FROM sc GROUP BY SId)r2 
WHERE student.`SId` =r1.SId AND r1.SId=r2.SId

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

-- 检索" 01 "课程分数小于 60，按分数降序排列的学生信息(DESC（逆序，默认ASC升序）。)
-- 在sc中查出01课程成绩小于60记录
SELECT * FROM sc WHERE sc.`score`<60 AND sc.`CId`='01'

-- student联合以上结果集 或者join以上结果集通过SId，在通过成绩order by dese  排序

SELECT student.*,t.score FROM student ,(SELECT * FROM sc WHERE sc.`score`<60 AND sc.`CId`='01')t WHERE student.`SId`=t.SId ORDER BY t.score DESC

14.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

15.查询各科成绩最高分、最低分和平均分：

16.以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

17.及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

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

19.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
20. 按各科成绩进行排序，并显示排名， Score 重复时合并名次

21.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
22. 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

23.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

24.查询各科成绩前三名的记录

25.查询每门课程被选修的学生数

26.查询出只选修两门课程的学生学号和姓名

27.查询男生、女生人数

28.查询名字中含有「风」字的学生信息

29.查询同名同性学生名单，并统计同名人数

30.查询 1990 年出生的学生名单

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

32.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

33.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

34.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

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

36.查询不及格的课程

37.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

38.求每门课程的学生人数

39.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

40.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

42.查询每门功成绩最好的前两名

43.统计每门课程的学生选修人数（超过 5 人的课程才统计）。

44.检索至少选修两门课程的学生学号

45.查询选修了全部课程的学生信息

46.查询各学生的年龄，只按年份来算

47.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

48.查询本周过生日的学生

49.查询下周过生日的学生

50.查询本月过生日的学生

51.查询下月过生日的学生

sunny龙龙哥

关注

0
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
SQL语句强化练习50题

SQL语句强化练习50题这些题目是我在网上看到的，自己做一下，写一些自己的理解，感觉挺有意思的，正在更新，欢迎大家提供材料数据表介绍–1.学生表Student(SId,Sname,Sage,Ssex)–SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别–2.课程表Course(CId,Cname,TId)–CId 课程编号,Cname 课程名称,TId ...
复制链接

扫一扫

专栏目录