【DataBase】SQL45 Training 45题训练

2023-11-10 11:05:04

视频地址：

https://www.bilibili.com/video/BV1pp4y1Q7Yv

创建案例库：

------------创建数据库---------------
create database data charset=utf8;

------------ 建表语句-----------------
# 学生表 Student：
create table Student(
SId varchar(10) ,
Sname varchar(10),
Sage datetime,
Ssex varchar(10));

# 教师表 Teacher
create table Teacher(
TId varchar(10),
Tname varchar(10)); 

# 科目表 Course
create table Course(
CId varchar(10),
Cname nvarchar(10),
TId varchar(10)); 

# 成绩表 SC
create table SC(
SId varchar(10),
CId varchar(10),
score decimal(18,1)); 

------------ 插入数据语句-----------------

# 学生表 Student：
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(‘09‘ , ‘张三‘ , ‘2017-12-20‘ , ‘女‘);
insert into Student values(‘10‘ , ‘李四‘ , ‘2017-12-25‘ , ‘女‘);
insert into Student values(‘11‘ , ‘李四‘ , ‘2017-12-30‘ , ‘女‘);
insert into Student values(‘12‘ , ‘赵六‘ , ‘2017-01-01‘ , ‘女‘);
insert into Student values(‘13‘ , ‘孙七‘ , ‘2018-01-01‘ , ‘女‘);

# 科目表 Course
insert into Course values(‘01‘ , ‘语文‘ , ‘02‘); 
insert into Course values(‘02‘ , ‘数学‘ , ‘01‘); 
insert into Course values(‘03‘ , ‘英语‘ , ‘03‘); 

# 教师表 Teacher
insert into Teacher values(‘01‘ , ‘张三‘);
insert into Teacher values(‘02‘ , ‘李四‘); 
insert into Teacher values(‘03‘ , ‘王五‘); 

# 成绩表 SC
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成绩高的学生信息和课程分数

-- 一、教程解法 【0.018s】
SELECT 
    a.sid,
    a.sname,
    a.sage,
    a.ssex,
    b.score as ‘课程01‘,
    c.score as ‘课程02‘
FROM
    student AS a
    INNER JOIN sc AS b ON a.sid = b.sid
    INNER JOIN sc AS c ON a.sid = c.sid AND b.cid = 01 AND c.cid = 02
WHERE 
    b.score > c.score
-- 二、我的解法 【0.019s】 
SELECT
    D.sid,
    D.sname,
    D.sage,
    D.ssex,
    E.`课程01分数`,
    E.`课程02分数`
FROM 
    student AS D,
    (
    SELECT
        A.sid,
        A.score AS ‘课程01分数‘,
        B.score AS ‘课程02分数‘
    FROM 
        sc AS A,
        sc AS B 
    WHERE 
        A.sid = B.sid
        AND A.cid = 01
        AND B.cid = 02
        AND A.score > B.score
    ) AS E
WHERE
    D.sid = E.sid

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

用时【0.017s】

SELECT 
    * 
FROM 
    sc as a, 
    sc as b 
WHERE 
    1 = 1
    AND a.sid = b.sid 
    AND a.cid = 01 
    AND b.cid = 02

1.2、要求查询存在01, 可能存在02课程的情况（不存在显示为NULL）

-- SQL解法1 【0.017s】
SELECT 
    *
FROM 
    (SELECT * FROM sc WHERE cid = 01) AS a
    LEFT JOIN sc AS b
    ON a.sid = b.sid
    AND b.cid = 02

-- SQL解法2 【0.018s】
SELECT 
    *
FROM 
    sc AS a
    LEFT JOIN sc AS b
    ON a.sid = b.sid
    AND b.cid = 02
WHERE 
    1 = 1
    AND a.cid = 01

1.3、要求查询不存在01,存在02课程的情况’

-- 解法1 【0.017s】会有多余记录
SELECT 
    *
FROM 
    (SELECT * FROM sc WHERE sid NOT IN (SELECT sid FROM sc WHERE cid = 01)) AS a
    INNER JOIN sc AS b
    ON a.sid = b.sid
    AND b.cid = 02
-- 解法2 【0.017s】
SELECT
    *
FROM 
    sc as a
WHERE 
    1 = 1
    AND sid not in (SELECT sid FROM sc where cid = 01)
    AND cid = 02

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

耗时【0.018s】

SELECT
    a.sid,
    a.sname,
    b.avg_score
FROM 
    student as a,
    (SELECT sid,AVG(score) as avg_score FROM sc GROUP BY sid HAVING avg_score > 60) as b
WHERE
    1 = 1
    AND a.sid = b.sid

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

-- 我的解法 【0.016s】
SELECT DISTINCT b.* FROM sc as a, student as b WHERE a.sid = b.sid

-- 教程解法1 能够查出结果，但是SQL语法是有问题的，这是错误的示范
SELECT
    b.*
FROM
    sc AS a LEFT JOIN student AS b ON a.sid = b.sid
GROUP BY b.sid
-- 
> 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘devbase.b.Sname‘ 
which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by
--

-- 教程解法2 GROUP BY 允许对指定的列查询 【0.016s】
SELECT
    b.*
FROM
    (SELECT sid FROM sc GROUP BY sid) AS a
    LEFT JOIN student AS b
    ON a.sid = b.sid

4、查询所有学生的编号，姓名，选课数，所有课程的总成绩。没选课程则成绩为NULL

-- 首先在成绩表这里查询出选课数，和总成绩
SELECT sid,COUNT(cid) AS course_count, SUM(score) total_score FROM sc GROUP BY sid

-- 解法1 【0.018s】
SELECT 
    a.sid,
    a.sname,
    b.course_count,
    b.total_score
FROM
    student AS a 
    LEFT JOIN (SELECT sid,COUNT(cid) AS course_count, SUM(score) AS total_score FROM sc GROUP BY sid) AS b
    ON a.sid = b.sid;
    
-- 解法2 教程可查出，实际操作不支持此语法
SELECT
    a.sid,
    a.sname,
    COUNT(b.cid) AS ‘选课数‘,
    SUM(b.score) AS ‘总成绩‘
FROM
    student AS a
    LEFT JOIN sc AS b
    ON a.sid = b.sid
GROUP BY a.sid
-- 
> 1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘devbase.a.Sname‘ 
which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by
--

5、查询姓【李】的老师数量

-- 我的解法【0.017s】
SELECT count(tname) FROM teacher WHERE tname LIKE ‘李%‘
-- 教程解法【0.016s】
SELECT count(1) FROM teacher WHERE tname LIKE ‘李%‘

6、查询学过【张三】老师课程的学生信息

-- 我的解法，使用子查询作为逐个条件【0.017s】

-- 用教师表的名字找tid
SELECT tid FROM teacher WHERE tname = ‘张三‘
-- 用tid 给课程表找cid
SELECT cid FROM course WHERE tid = 01
-- 用cid 给成绩表找sid
SELECT sid FROM sc WHERE cid = 02

-- 最后用成绩表查出的sid作为in条件给学生表查询
SELECT * FROM student WHERE sid IN (SELECT sid 
FROM sc 
WHERE cid = (
    SELECT cid 
    FROM course 
    WHERE tid = (
        SELECT tid 
        FROM teacher 
        WHERE tname = ‘张三‘
    )
))

-- 教程解法 耗时【0.016s】
-- 1、老师和课程的关系
SELECT A.*, B.tname FROM course A INNER JOIN teacher B ON A.tid = B.tid
-- 2、教师和成绩的关系
SELECT 
    * 
FROM 
    SC a 
    INNER JOIN (SELECT b.*,c.tname FROM course b INNER JOIN teacher c ON b.tid = c.tid) d
    ON a.cid = d.cid 
-- 3、得到学生和教师的关系
SELECT 
    * 
FROM
    student e
    INNER JOIN (
    SELECT 
        a.*,
        d.cname,
        d.tid,
        d.tname 
    FROM 
        SC a 
        INNER JOIN (SELECT b.*,c.tname FROM course b INNER JOIN teacher c ON b.tid = c.tid) d
        ON a.cid = d.cid 
    ) f
    ON e.sid = f.sid
-- 4、追加老师等于张三的条件
SELECT 
    * 
FROM
    student e
    INNER JOIN (
    SELECT 
        a.*,
        d.cname,
        d.tid,
        d.tname 
    FROM 
        SC a 
        INNER JOIN (SELECT b.*,c.tname FROM course b INNER JOIN teacher c ON b.tid = c.tid) d
        ON a.cid = d.cid 
    ) f
    ON e.sid = f.sid
WHERE f.tname = ‘张三‘

7、查询没有全选课程的学生

-- 我的解法 耗时【0.018s】
-- 首先筛选全部课程
SELECT COUNT(1) FROM course
-- 筛选SID
SELECT 
    sid, 
    count(cid) course_count
FROM 
    sc 
GROUP BY sid
HAVING course_count <> (SELECT COUNT(1) FROM course)

-- 筛选学生
SELECT A.*
FROM student A, 
(SELECT 
    sid, 
    count(cid) course_count
FROM 
    sc 
GROUP BY sid
HAVING course_count <> (SELECT COUNT(1) FROM course)) B
WHERE A.sid = B.sid

-- 教程解法1 不支持的语法
SELECT 
    *
FROM
    student a
    LEFT JOIN sc b ON a.sid = b.sid
GROUP BY a.sid
HAVING COUNT(b.sid)    <> (SELECT COUNT(cid) FROM course)
-- 教程解法2 语法也不支持
SELECT
    a.*, b.*
FROM
    sc a
    INNER JOIN student b
    ON a.sid = b.sid
GROUP BY a.sid
HAVING count(1) < (SELECT COUNT(1) FROM course)

8、查询和学号01的同学，所学的课程至少同一个的学生

-- 我的解法 解法1
SELECT
    DISTINCT a.*
FROM
    student a INNER JOIN sc b ON a.sid = b.sid
WHERE
    1 = 1
    AND a.sid <> 01
    AND b.cid IN (SELECT cid FROM sc WHERE sid = 01)
-- 解法2

--  直接筛选
SELECT 
    a.sid
FROM 
    sc a 
WHERE 
    a.cid in (SELECT cid FROM sc WHERE sid = ‘01‘) 
GROUP BY a.sid
    
-- 套student表子查询
SELECT * FROM student WHERE sid IN (SELECT 
    a.sid
FROM 
    sc a 
WHERE 
    1 = 1
    AND a.cid in (SELECT cid FROM sc WHERE sid = ‘01‘) 
GROUP BY a.sid)
    AND sid <> 01

9、查询和01同学所选课程一样的其他同学

解法没看懂。。。

SELECT  *
FROM student 
WHERE student.sid IN(
    SELECT sid 
    FROM sc 
    WHERE 
        1 = 1
        AND sid <> 01 
        AND cid IN( SELECT cid FROM sc WHERE sid=‘01‘)
        GROUP BY sid HAVING COUNT(*) = (SELECT COUNT(*) FROM sc WHERE sid=‘01‘)
)

10、查询没学习过张三老师的任意一门课程的学生姓名

-- 我的解法
-- 1 张三老师ID
SELECT tid 
FROM teacher 
WHERE tname = ‘张三‘

-- 2 张三老师所授课程ID
SELECT cid 
FROM course 
WHERE tid = (SELECT tid FROM teacher WHERE tname = ‘张三‘)

-- 3 学了该老师的课程的学生ID
SELECT sid 
FROM sc 
WHERE cid IN(SELECT cid FROM course WHERE tid = (SELECT tid FROM teacher WHERE tname = ‘张三‘)) 

-- 4、取反
SELECT sname,sid 
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 = ‘张三‘)))

-- 教程解法
-- 查出学习了张三老师课程的学生ID
SELECT
    d.sid
FROM
    sc a
    LEFT JOIN course b ON a.cid = b.cid
    LEFT JOIN teacher c ON b.tid = c.tid
    LEFT JOIN student d ON a.sid = d.sid 
WHERE
    1 = 1
    AND c.tname = ‘张三‘
-- 取反
SELECT sname,sid FROM student WHERE sid NOT IN(
    SELECT
        d.sid
    FROM
        sc a
        LEFT JOIN course b ON a.cid = b.cid
        LEFT JOIN teacher c ON b.tid = c.tid
        LEFT JOIN student d ON a.sid = d.sid 
    WHERE
        1 = 1
        AND c.tname = ‘张三‘
)

11、查询两门以上不及格的学生

-- 我的解法
-- 1 在sc表直接查出sid和平均成绩【全条件筛查】
SELECT sid, AVG(score) average_score 
FROM sc 
GROUP BY sid 
HAVING 
    1 = 1
    AND COUNT(cid) > 1 
    AND average_score < 60
-- 2 联表student查即可
SELECT
    A.sname,
    B.*
FROM
    student A,
    (
        SELECT sid, AVG(score) average_score 
        FROM sc 
        GROUP BY sid 
        HAVING 
            1 = 1
            AND COUNT(cid) > 1 
            AND average_score < 60
    ) B
WHERE
    1 = 1
    AND A.sid = B.sid

-- 教程解法 【语法不支持】
-- 1、先求出两门以上不及格的学生ID
SELECT 
    sid
    -- AVG(score) average_score
FROM
    sc
WHERE 
    1 = 1
    AND score < 60
GROUP BY sid
HAVING COUNT(1) > 1

-- 求平均值的时候筛选我们要的同学
SELECT
    a.sid,
    b.sname,
    AVG( score ) 
FROM
    sc a
    LEFT JOIN student b ON a.sid = b.sid
    INNER JOIN ( SELECT sid FROM sc WHERE score < 60 GROUP BY sid HAVING COUNT( 1 ) > 1 ) c ON a.sid = c.sid 
GROUP BY
    a.sid

12、查询课程01小于60分，且降序排序的学生信息

-- 我的解法，直接联表
SELECT 
    -- a.sid,
    -- a.score,
    b.*
FROM 
    sc a,
    student b
WHERE 
    1 = 1 
    AND a.sid = b.sid
    AND a.score < 60 
    AND a.cid = 01
ORDER BY a.score DESC

-- 教程解法，左外连
SELECT 
    -- a.sid,
    -- a.score,
    b.*
FROM 
    sc a
    LEFT JOIN student b ON a.sid = b.sid
WHERE 
    1 = 1 
    AND a.score < 60 
    AND a.cid = 01
ORDER BY a.score DESC

13、按平均成绩从高到底排序，显示所有学生所有课程成绩和平均成绩

-- 我的解法
-- sc表查询平均成绩和排序操作
SELECT
    sid,
    AVG(score) avg_score
FROM
    sc
GROUP BY
    sid
ORDER BY
    avg_score DESC
-- 因为是所有学生，肯定是学生表做主表查询
SELECT
    a.sid,
    a.sname,
    b.avg_score ‘平均分‘,
    (SELECT score FROM sc WHERE sid = a.sid AND cid = 01) ‘课程01‘,
    (SELECT score FROM sc WHERE sid = a.sid AND cid = 02) ‘课程02‘,
    (SELECT score FROM sc WHERE sid = a.sid AND cid = 03) ‘课程03‘
FROM
    student a
    LEFT JOIN (
        SELECT
        sid,
        AVG(score) avg_score
    FROM
        sc
    GROUP BY
        sid) b
    ON a.sid = b.sid
    ORDER BY
        `平均分` DESC -- 注意，这个排序要从子查询放到外面来

-- 教程解法
SELECT
    a.*,
    avg_score
FROM
    sc a
    LEFT JOIN (SELECT sid, AVG(score) as avg_score FROM sc GROUP BY sid) b
    ON a.sid = b.sid
ORDER BY
    avg_score DESC

14、查询各科成绩最高分，平均分，最低分

按照如下形式：

课程ID，课程名称，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格 >= 60

中等 70 - 80

优良 80 - 90

优秀 >= 90

还要求选修人数，按这个人数降序排序

人数相同，按课程号升序排序

-- 我的解法
SELECT 
    cid,  
    (SELECT cname FROM course WHERE cid = a.cid) ‘课程名称‘,
    MAX(score) ‘最高分‘, 
    ROUND(AVG(score), 2) ‘平均分‘, 
    MIN(score) ‘最低分‘, 
    (SELECT (SELECT COUNT(1) FROM sc WHERE cid = a.cid AND score > 59 ) / (SELECT COUNT(1) FROM sc WHERE cid = a.cid) ) ‘及格率‘,
    (SELECT (SELECT COUNT(1) FROM sc WHERE cid = a.cid AND score BETWEEN 70 AND 79 ) / (SELECT COUNT(1) FROM sc WHERE cid = a.cid) ) ‘中等率‘,
    (SELECT (SELECT COUNT(1) FROM sc WHERE cid = a.cid AND score BETWEEN 80 AND 89 ) / (SELECT COUNT(1) FROM sc WHERE cid = a.cid) ) ‘优良率‘,
    (SELECT (SELECT COUNT(1) FROM sc WHERE cid = a.cid AND score > 89 ) / (SELECT COUNT(1) FROM sc WHERE cid = a.cid) ) ‘优秀率‘,
    (SELECT COUNT(1) FROM sc WHERE cid = a.cid) ‘选修人数‘
FROM 
    sc a
GROUP BY 
    cid 
ORDER BY
    `选修人数` DESC, cid ASC

-- 教程解法
SELECT 
    cid,  
    -- (SELECT cname FROM course WHERE cid = a.cid) ‘课程名称‘,
    MAX(score) ‘最高分‘, 
    AVG(score) ‘平均分‘, 
    MIN(score) ‘最低分‘, 
    SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END) / COUNT(1)  AS ‘及格率‘,
    SUM(CASE WHEN score BETWEEN 70 AND 79 THEN 1 ELSE 0 END) / COUNT(1)  AS ‘中等率‘,
    SUM(CASE WHEN score BETWEEN 80 AND 89 THEN 1 ELSE 0 END) / COUNT(1)  AS ‘优良率‘,
    SUM(CASE WHEN score > 89 THEN 1 ELSE 0 END) / COUNT(1)  AS ‘优秀率‘,
    COUNT(1) ‘选修人数‘
FROM 
    sc a
GROUP BY 
    cid 
ORDER BY
    `选修人数` DESC, cid ASC

15、按各科成绩进行排序，显示排名, 成绩重复继续排序

-- 教程解法
SELECT
    sid,
    cid,
    score,
    (@rank:= @rank + 1) rank-- 每查询到一行就赋值给这个记录进行叠加
FROM
    sc, (SELECT @rank:=0) t -- 利用子查询做一张虚拟表，和sc表进行一个笛卡尔积
ORDER BY
    score DESC

16、查询学生的总成绩，进行排名

-- 教程解法
SELECT 
    a.*,
    @rank:= if(@sco=scos, ‘‘, @rank + 1) rank,
    -- @sco:= scos
FROM
    (SELECT sid, SUM(score) scos FROM sc GROUP BY sid ORDER BY scos DESC) a,
    (SELECT @sco:= NULL, @rank:= 0) b

17、统计各科成绩各分数段人数：

课程编号，课程名称

[100 - 85)

[85 - 70)

[70 - 60)

[60 - 0)

和所占百分比

SELECT
    cid,
    CONCAT( SUM( CASE WHEN score BETWEEN 0 AND 60 THEN 1 ELSE 0 END ) / COUNT( 1 ) * 100, ‘%‘ ) ‘(0 - 60]‘,
    CONCAT( SUM( CASE WHEN score BETWEEN 61 AND 70 THEN 1 ELSE 0 END ) / COUNT( 1 ) * 100, ‘%‘ ) ‘(60 - 70]‘,
    CONCAT( SUM( CASE WHEN score BETWEEN 71 AND 85 THEN 1 ELSE 0 END ) / COUNT( 1 ) * 100, ‘%‘ ) ‘(70 - 85]‘,
    CONCAT( SUM( CASE WHEN score BETWEEN 86 AND 100 THEN 1 ELSE 0 END ) / COUNT( 1 ) * 100, ‘%‘ ) ‘(85 - 100]‘ 
FROM
    sc 
GROUP BY
    cid

补充其他字段：

SELECT
    cid, 
    (SELECT cname FROM course WHERE cid = sc.cid) ‘课程‘,
    CONCAT( SUM( CASE WHEN score BETWEEN 0 AND 60 THEN 1 ELSE 0 END ) / COUNT( 1 ) * 100, ‘%‘ ) ‘(0 - 60]‘,
    SUM( CASE WHEN score BETWEEN 0 AND 60 THEN 1 ELSE 0 END ) ‘P1‘,
    CONCAT( SUM( CASE WHEN score BETWEEN 61 AND 70 THEN 1 ELSE 0 END ) / COUNT( 1 ) * 100, ‘%‘ ) ‘(60 - 70]‘,
    SUM( CASE WHEN score BETWEEN 61 AND 70 THEN 1 ELSE 0 END ) ‘P2‘,
    CONCAT( SUM( CASE WHEN score BETWEEN 71 AND 85 THEN 1 ELSE 0 END ) / COUNT( 1 ) * 100, ‘%‘ ) ‘(70 - 85]‘,
    SUM( CASE WHEN score BETWEEN 71 AND 85 THEN 1 ELSE 0 END ) ‘P3‘,
    CONCAT( SUM( CASE WHEN score BETWEEN 86 AND 100 THEN 1 ELSE 0 END ) / COUNT( 1 ) * 100, ‘%‘ ) ‘(85 - 100]‘, 
    SUM( CASE WHEN score BETWEEN 86 AND 100 THEN 1 ELSE 0 END ) ‘P4‘
FROM
    sc 
GROUP BY
    cid

18、查询各科前三名记录

SELECT
    * 
FROM
    sc a 
WHERE
    ( SELECT count( 1 ) FROM sc b WHERE a.cid = b.cid AND a.score <= b.score ) < 4 
ORDER BY
    a.cid,
    a.score DESC;

19、查询每门课程被选修的学生人数

SELECT
    cid,
    COUNT(1) ‘选修人数‘
FROM
    sc
GROUP BY cid

20、查询只是选修两门课程的学生学号和姓名

-- 我的解法
SELECT
    sid,
    (SELECT sname FROM student WHERE sid = sc.sid) ‘name‘
FROM
    sc
GROUP BY sid
HAVING COUNT(cid) = 2

-- 教程解法
SELECT
    a.sid,
    b.sname,
    count(1) ‘count‘
FROM
    sc a
    LEFT JOIN student b ON a.sid = b.sid
    GROUP BY a.sid, b.sname
    HAVING COUNT(a.cid) = 2

21、查询男女人数

SELECT ssex,COUNT(1) ‘count‘ FROM student GROUP BY ssex

22、查询名字中含有【风】的学生信息

SELECT * FROM student WHERE sname LIKE "%风%"

23、查询同名同性学生并且统计人数

-- 我的解法
SELECT
    a.sname,
    a.ssex,
    COUNT(1) ‘count‘
FROM
    student a,
    student b
WHERE
    1 = 1
    AND a.sid <> b.sid
    AND a.sname = b.sname
    AND a.ssex = b.ssex
GROUP BY a.sname, a.ssex

-- 教程解法
SELECT
    a.sname,
    a.ssex,
    COUNT(1) ‘count‘
FROM
    student a
    INNER JOIN student b ON a.sid <> b.sid
    AND a.sname = b.sname
    AND a.ssex = b.ssex
GROUP BY a.sname, a.ssex

24、查询1990年出生的学生名单

SELECT
    *
FROM
    student
WHERE
    YEAR(sage) = 1990

25、查询每门课程的平均成绩，按平均成绩降序排列，如果相同，按编号升序排列

SELECT
    cid,
    AVG(score) avg_score
FROM
    sc
GROUP BY cid
ORDER BY avg_score DESC, cid ASC

26、平均成绩大于等于85的所有学生学号，姓名，平均成绩

-- 我的解法
SELECT
    sid,
    (SELECT sname FROM student WHERE sid = sc.sid) ‘name‘,
    AVG(score) avg_score
FROM
    sc
GROUP BY sid
HAVING avg_score > 84


-- 教程解法
SELECT
    a.sid,
    b.sname,
    a.avg_score
FROM
    (SELECT sid, AVG(score) avg_score FROM sc GROUP BY sid HAVING avg_score >= 85) a
    LEFT JOIN student b ON a.sid = b.sid

27、查询课程为数学，且分数低于60的学生姓名和分数

-- 我的解法
-- 1、筛查数学课程ID
SELECT cid FROM course WHERE cname = ‘数学‘
-- 2 列查询 + 子查询 完成
SELECT
    (SELECT sname FROM student WHERE sid = sc.sid) ‘name‘,
    score
FROM sc 
WHERE 
    1 = 1
    AND cid = (SELECT cid FROM course WHERE cname = ‘数学‘) 
    AND score < 60

-- 教程解法
SELECT
    a.sid,
    b.sname,
    a.cid,
    a.score
FROM
    sc a
    LEFT JOIN student b ON a.sid = b.sid
WHERE 
    1 = 1
    AND cid = (SELECT cid FROM course WHERE cname = ‘数学‘)
    AND score < 60

28、查询所有学生课程及分数情况【没有则NULL】

SELECT
    a.sid,
    a.sname,
    b.cid,
    b.score
FROM
    student a
    LEFT JOIN sc b ON a.sid = b.sid

29、查询任意一门课程成绩在70分以上的学生名称，课程名称，分数

-- 我的解法
SELECT 
    (SELECT sname FROM student WHERE sid = sc.sid) ‘name‘,
    (SELECT cname FROM course WHERE cid = sc.cid) ‘course‘,
    score
FROM
    sc
WHERE score > 69

-- 教程解法
SELECT
    a.score,
    s.sname,
    c.cname
FROM
    sc a
    LEFT JOIN course c ON a.cid = c.cid
    LEFT JOIN student s ON a.sid = s.sid
WHERE a.score > 69

30、查询存在不及格成绩的课程

-- 我的解法
SELECT
    DISTINCT cid
FROM
    sc
WHERE
    score < 60
-- 教程解法
SELECT
    cid, score
FROM
    sc
WHERE
    score < 60

31、课程01成绩80分以上的学生ID和姓名

-- 我的解法
-- 1、成绩表筛查SID
SELECT 
    sid
FROM 
    sc
WHERE
    1 = 1
    AND cid = 01 
    AND score > 79
-- 2、联表
SELECT
    a.sid,
    b.sname,
    a.score,
    a.cid
FROM
    sc a
    INNER JOIN student b ON a.sid = b.sid
WHERE
    1 = 1
    AND a.cid = 01
    AND a.score > 79

32、每门课程的学生人数

SELECT
    cid,
    COUNT( 1 ) 
FROM
    sc 
GROUP BY
    cid

33、在成绩不重复的情况下，查询张三老师所授课程的学生中，成绩最高的学生信息和成绩

-- 教程解法
SELECT 
    *
FROM
    sc a
    LEFT JOIN student b ON a.sid = b.sid
    LEFT JOIN course c ON a.cid = c.cid
    LEFT JOIN teacher d ON c.tid = d.tid
WHERE
    1 = 1
    AND d.tname = ‘张三‘
ORDER BY a.score DESC
LIMIT 1

34、在成绩重复的情况下，查询张三老师所授课程的学生中，成绩最高的学生信息和成绩

-- 我的解法

-- 1、张三老师
SELECT tid FROM teacher WHERE tname = ‘张三‘
-- 2、所受课程【可能不止一门】
SELECT cid FROM course WHERE tid = (SELECT tid FROM teacher WHERE tname = ‘张三‘)
-- 3、学习的学生
SELECT * FROM sc WHERE cid IN (SELECT cid FROM course WHERE tid = (SELECT tid FROM teacher WHERE tname = ‘张三‘))
-- 4、取最高成绩和ID
SELECT * FROM sc WHERE cid IN (SELECT cid FROM course WHERE tid = (SELECT tid FROM teacher WHERE tname = ‘张三‘)) ORDER BY score DESC LIMIT 1 
-- 5、连表 
SELECT
    a.*,
    b.cid,
    (SELECT cname FROM course WHERE cid = b.cid),
    b.score
FROM
    student a,
    (SELECT * FROM sc WHERE cid IN (SELECT cid FROM course WHERE tid = (SELECT tid FROM teacher WHERE tname = ‘张三‘)) ORDER BY score DESC LIMIT 1 ) b
WHERE
    1 = 1
    AND a.sid = b.sid
    
-- 教程解法
SELECT
    s.*
FROM
(SELECT
    a.*,
    CASE 
        WHEN @score=score THEN @rank
        WHEN @score:=score THEN @rank:=@rank+1 
    END rn
FROM
(SELECT
    a.sid,
    a.score,
    b.sname,
    c.cid,
    d.tname
FROM
    sc a
    LEFT JOIN student b ON a.sid = b.sid
    LEFT JOIN course c ON a.cid = c.cid
    LEFT JOIN teacher d ON c.tid = d.tid
WHERE
    1 = 1
    AND d.tname = ‘张三‘) a,
(SELECT @score:=NULL, @rank:=0) t ) s
WHERE rn = 1

35、查询不同课程相同成绩的学生编号，课程编号，学生成绩

-- 我的解法
SELECT
    DISTINCT a.sid,
    a.cid,
    a.score
FROM
    sc a,
    sc b
WHERE
    1 = 1
    AND a.sid = b.sid
    AND a.score = b.score
    AND a.cid <> b.cid

-- 教程解法
SELECT
    a.sid,
    a.cid
FROM
    sc a
    INNER JOIN sc b ON a.sid = b.sid
WHERE
    a.score = b.score
    AND a.cid <> b.cid
GROUP BY a.sid, a.cid

36、查询每门课程考试最好的前两名

-- 参考解法 https://blog.csdn.net/weixin_28847323/article/details/113088935
SELECT
    * 
FROM
    sc a 
WHERE
    ( SELECT count( 1 ) FROM sc b WHERE a.cid = b.cid AND a.score <= b.score ) < 3 
ORDER BY
    a.cid,
    a.score DESC;

-- 教程解法
SELECT
    sid,
    cid,
    score,
    rank
FROM
    (SELECT 
        sc.*,
        @rank:=if(@c_cid=cid, if(@sco=score,@rank, @rank + 1), 1) rank,
        @sco:=score,
        @c_cid:=cid
    FROM
        sc,
        (SELECT @sco:=NULL, @rank:=0, @c_cid:=NULL) b
ORDER BY cid, score DESC) a
WHERE a.rank < 3

37、查询每门课程的选修人数【超过五人统计】

SELECT
    cid,
    COUNT(1)
FROM
    sc
GROUP BY
    cid
HAVING
    COUNT(1) > 5

38、查询至少选修两门课程的学生ID　　

-- 我的解法
SELECT 
    sid
FROM
    sc
GROUP BY
    sid
HAVING
    COUNT(cid) > 1
    
-- 教程解法
SELECT 
    sid,
    COUNT(1) ‘选课数‘
FROM
    sc
GROUP BY
    sid
HAVING
    COUNT(1) >= 2

39、查询了选修了全部课程的学生信息

-- 我的解法
-- 1、全部课程
SELECT COUNT(1) FROM course
-- 2、全修了课程的sid
SELECT
    sid
FROM
    sc
GROUP BY
    sid
HAVING
    COUNT(cid) = (SELECT COUNT(1) FROM course)
-- 3、做IN条件查询学生表
SELECT * FROM student WHERE sid IN(
SELECT
    sid
FROM
    sc
GROUP BY
    sid
HAVING
    COUNT(cid) = (SELECT COUNT(1) FROM course))
    
-- 教程解法1 [语法不支持]
SELECT
    b.*,
    a.sid
FROM
    sc a
    INNER JOIN student b ON a.sid = b.sid
GROUP BY
    a.sid
HAVING
    COUNT(1) = (SELECT COUNT(1) FROM course)

-- 教程解法2 [笛卡尔积]
SELECT a.* FROM student a 
WHERE (SELECT COUNT(1) FROM sc b WHERE a.sid = b.sid) = (SELECT COUNT(1) FROM COURSE);

40、查询各个学生年龄，只按年份来算

SELECT
    *,
    YEAR(NOW()) - YEAR(sage) age
FROM
    student

41、按照出生年月计算年龄，当前日月 < 出生年月的日月年龄减一

SELECT
    *,
    TIMESTAMPDIFF(YEAR,sage,NOW()) age
FROM
    student

42、查询本周过生日的学生

SELECT *, WEEK(sage), WEEK(NOW()) FROM student 
WHERE WEEK(sage) = WEEK( NOW() )

43、查询下周过生日的学生

SELECT *, WEEK(sage), WEEK(NOW()) FROM student 
WHERE WEEK(sage) = WEEK( NOW() ) + 1

44、查询本月过生日的学生

SELECT *, MONTH(sage), MONTH(NOW()) FROM student 
WHERE MONTH(sage) = MONTH( NOW() ) + 1

45、查询下个月过生日的学生

SELECT *, MONTH(sage), MONTH(NOW()) FROM student 
WHERE MONTH(sage) = MONTH( NOW() ) + 1

【DataBase】SQL45 Training 45题训练

码农公寓

1、课程01比课程02成绩高的学生信息和课程分数

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

1.2、要求查询存在01, 可能存在02课程的情况（不存在显示为NULL）

1.3、要求查询不存在01,存在02课程的情况’

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

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

4、查询所有学生的编号，姓名，选课数，所有课程的总成绩。没选课程则成绩为NULL

5、查询姓【李】的老师数量

6、查询学过【张三】老师课程的学生信息

7、查询没有全选课程的学生

8、查询和学号01的同学，所学的课程至少同一个的学生

9、查询和01同学所选课程一样的其他同学

10、查询没学习过张三老师的任意一门课程的学生姓名

11、查询两门以上不及格的学生

12、查询课程01小于60分，且降序排序的学生信息

13、按平均成绩从高到底排序，显示所有学生所有课程成绩和平均成绩

14、查询各科成绩最高分，平均分，最低分

15、按各科成绩进行排序，显示排名, 成绩重复继续排序

16、查询学生的总成绩，进行排名

17、统计各科成绩各分数段人数：

18、查询各科前三名记录

19、查询每门课程被选修的学生人数

20、查询只是选修两门课程的学生学号和姓名

21、查询男女人数

22、查询名字中含有【风】的学生信息

23、查询同名同性学生并且统计人数

24、查询1990年出生的学生名单

25、查询每门课程的平均成绩，按平均成绩降序排列，如果相同，按编号升序排列

26、平均成绩大于等于85的所有学生学号，姓名，平均成绩

27、查询课程为数学，且分数低于60的学生姓名和分数

28、查询所有学生课程及分数情况【没有则NULL】

29、查询任意一门课程成绩在70分以上的学生名称，课程名称，分数

30、查询存在不及格成绩的课程

31、课程01成绩80分以上的学生ID和姓名

32、每门课程的学生人数

33、在成绩不重复的情况下，查询张三老师所授课程的学生中，成绩最高的学生信息和成绩

34、在成绩重复的情况下，查询张三老师所授课程的学生中，成绩最高的学生信息和成绩

35、查询不同课程相同成绩的学生编号，课程编号，学生成绩

36、查询每门课程考试最好的前两名

37、查询每门课程的选修人数【超过五人统计】

38、查询至少选修两门课程的学生ID

39、查询了选修了全部课程的学生信息

40、查询各个学生年龄，只按年份来算

41、按照出生年月计算年龄，当前日月 < 出生年月的日月 年龄减一

42、查询本周过生日的学生

43、查询下周过生日的学生

44、查询本月过生日的学生

45、查询下个月过生日的学生

相关文章

38、查询至少选修两门课程的学生ID　　

41、按照出生年月计算年龄，当前日月 < 出生年月的日月年龄减一