【DataBase】SQL50 Training 50题训练

原文地址：

https://blog.csdn.net/xiushuiguande/article/details/79476964

 

实验数据

CREATE DATABASE IF NOT EXISTS SQL50;
USE SQL50;


CREATE TABLE Student(sid VARCHAR(10),sname VARCHAR(10),sage DATETIME,ssex NVARCHAR(10));
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('08' , '王菊' , '1990-01-20' , '女');


CREATE TABLE Course(cid VARCHAR(10),cname VARCHAR(10),tid VARCHAR(10));
INSERT INTO Course VALUES('01' , '语文' , '02');
INSERT INTO Course VALUES('02' , '数学' , '01');
INSERT INTO Course VALUES('03' , '英语' , '03');


CREATE TABLE Teacher(tid VARCHAR(10),tname VARCHAR(10));
INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');


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

 

4张表的结构关系

Course课程表：

　　课程ID、课程名、教师ID

 

SC成绩表：

　　学生ID、课程ID、成绩

 

Student学生表：

　　学生ID、学生姓名、学生年龄、学生性别、

 

Teacher老师表：

　　教师ID、教师名

 

1、查询“01”课程比“02”课程成绩高的所有学生的学号

先查询 01课程的成绩表 & 02课程的成绩表

SELECT * FROM `sc` WHERE `cid` = 1;
SELECT * FROM `sc` WHERE `cid` = 2;

使用连接查询，附加 01表的成绩 > 02表的成绩

SELECT 
    sc1.`sid`
FROM
    (SELECT `sid`,`cid`,`score` FROM `sc` WHERE `cid` = 1) AS sc1,
    (SELECT `sid`,`cid`,`score` FROM `sc` WHERE `cid` = 2) AS sc2
where
    sc1.`sid` = sc2.`sid`
    and
    sc1.`score` > sc2.`score`;

 

2、查询平均成绩大于60分的同学的学号和平均成绩

信息都在成绩表里面，要每个学生的全部成绩的平均成绩，

使用AVG聚合函数，然后对学生ID进行分组GROUP BY

查出上面的结果之后再对平均分进行 > 60的筛选

但是为什么SQL查不出结果？？？

SELECT 
    `sid`,AVG(`score`) AS 'avg_sc'
FROM 
    `sc`
GROUP BY 
    `sid`
HAVING    
    'avg_sc' > 60;

 

3、查询所有同学的学号、姓名、选课数、总成绩

因为在成绩表里面，可以使用分组+计数+求和，求出成绩表内的学号，选课数和总成绩

然后再和学生表进行一个左连接，学生表作为主表，上面的结果表作为从表，因为还存在学生表没有选课的情况

成绩表的筛选

SELECT 
    `sid`,COUNT(`cid`) AS '选课数',SUM(`score`) as '总成绩'
FROM
    `sc`
GROUP BY 
    `sid`;

左连接学生表

SELECT 
    S1.`sid`,S1.`sname`,S2.选课数,S2.总成绩
FROM
    `student` AS S1
    LEFT JOIN (
    SELECT  `sid`,COUNT(`cid`) AS '选课数',SUM(`score`) AS '总成绩'
    FROM `sc` 
　　 GROUP BY  `sid`) AS S2
ON
    S1.`sid` = S2.`sid`;

已采用内连接：

SELECT 
    S1.`sid`,S1.`sname`,S2.选课数,S2.总成绩
FROM
    `student` AS S1
    INNER JOIN (
    SELECT  `sid`,COUNT(`cid`) AS '选课数',SUM(`score`) AS '总成绩'
    FROM `sc` 
　　 GROUP BY  `sid`) AS S2
ON
    S1.`sid` = S2.`sid`;

 

4、查询姓“李”的老师的个数

只在李老师表内查询，使用计数函数

SELECT COUNT(*)
FROM `teacher`
WHERE `tname` LIKE '李%';

 

5、查询没学过“张三”老师课的同学的学号、姓名；

先筛选张三老师教授的课程ID，可以看到是01课程

SELECT `tid`
FROM `teacher`
WHERE `tname` = '张三';

 

通过上面的线索，查找课程表，其对应的课程ID是02

select `cid` -- ,`cname`,`tid`
from `course`
where `tid` = (SELECT `tid` FROM `teacher` WHERE `tname` = '张三');

 

在成绩表查询没学02课程太难排查了，所以反过来，我们查学了02的学生ID

-- 我们查询学了该课程的学生ID
SELECT DISTINCT `sid`
FROM `sc`
WHERE `cid` = (SELECT `cid` FROM `course` WHERE `tid` = (SELECT `tid` FROM `teacher` WHERE `tname` = '张三'))

 

再用上面这张表连接学生表查询【左连接】

SELECT 
    S1.`sid`,S1.`sname`
FROM
    `student` AS S1
    LEFT JOIN (SELECT DISTINCT `sid`
    FROM `sc`
    WHERE `cid` = (SELECT `cid` FROM `course` WHERE `tid` = (SELECT `tid` FROM `teacher` WHERE `tname` = '张三')))
    AS S2
ON
    S1.`sid` = S2.`sid`
WHERE
    S1.`sid` NOT IN(S2.`sid`);

 

6、查询学过“```”并且也学过编号“```”课程的同学的学号、姓名；

select s.sid,sname
 from student s 
 join (select a.sid 
 from (select sid from sc where cid = "01") a 
 join (select sid from sc where cid = "02") b
 on a.sid = b.sid) temp on temp.sid = s.sid;

 

7、查询学过“张三”老师所教的所有课的同学的学号、姓名；

查询张三老师的教师编号：

SELECT `tid` FROM `Teacher` WHERE `tname` = '张三'

然后根据编号查询所授课程：

SELECT `cid`
FROM `Course`
WHERE `tid` = (SELECT `tid` FROM `Teacher` WHERE `tname` = '张三');

然后得到对应的所学课程的学生编号

SELECT `sid` 
FROM `SC`
WHERE `cid` IN(
    SELECT `cid` 
    FROM `Course` 
    WHERE `tid` = (
        SELECT `tid` 
        FROM `Teacher` 
        WHERE `tname` = '张三'
        )
    );

最后再联合学生表：

SELECT `sid`,`sname`
FROM `Student`
WHERE `sid` IN (
    select `sid` 
    from `SC`
    where `cid` IN(
        SELECT `cid` 
        FROM `Course` 
        WHERE `tid` = (
            SELECT `tid` 
            FROM `Teacher` 
            WHERE `tname` = '张三'
        )
    )
);

8、查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；

select a.sid,s.sname
from (select sid,score from sc where cid = "01") a
join (select sid,score from sc where cid = "02") b on a.sid = b.sid
left join student s on a.sid = s.sid
where a.score < b.score;

 

9、查询所有课程成绩小于60分的同学的学号、姓名；

也就是说只要其中一个课程成绩大于60就不算在内

所以就可以先查询大于60分，再取反就行了

 

大于60分的学生ID：

SELECT `sid` FROM `SC` WHERE `score` > 60

然后取反得到：

SELECT `sid`,`sname`
FROM `Student`
WHERE `sid` NOT IN(SELECT `sid` FROM `SC` WHERE `score` > 60)

10、查询没有学全所有课的同学的学号、姓名；

首先是需要知道全部课程个数

然后这个查询的意思是，计数学生学习课程的个数，如果小于总课数即没学全

但是需要跟学生表关联，他这里的联动真的难理解。。。

SELECT 
    `sid`,`sname` 
FROM 
    `Student`
WHERE 
    (SELECT COUNT(*) FROM `SC` WHERE `Student`.`sid` = `SC`.`sid`) < (SELECT COUNT(*) FROM `Course`)

11、查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；

select sid,sname
from student 
where sid in (

 select DISTINCT sid 
from sc
join ((select cid from sc where sid = "01"))temp  on  temp.cid = sc.cid
where sc.sid <> "01"
)

 

24、查询学生平均成绩及其名次

SELECT 
    a.sid,
    a.avg_score,
    SUM(CASE WHEN a.avg_score<b.avg_score THEN 1 ELSE 0 END)+1 scc
FROM 
    (SELECT sid,AVG(score) avg_score FROM SC GROUP BY sid) a INNER JOIN 
    (SELECT sid,AVG(score) avg_score FROM SC GROUP BY sid) b
GROUP BY 
    a.sid,
    a.avg_score
ORDER BY 
    scc

25、查询各科成绩前三名的记录

SELECT * FROM
(
 SELECT s.*,a.cid,count(DISTINCT b.score) + 1 AS 名次
FROM sc a 
LEFT JOIN sc b ON b.cid = a.cid and a.score < b.score
LEFT JOIN student s ON s.sid = a.cid
group by a.cid,a.sid
order by a.cid , 名次
) resultTab
where 名次 BETWEEN 1 AND 3

 

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

SELECT 
    `cid`,COUNT(`sid`) '人数'
FROM
    `sc`
GROUP BY
    `cid`

27、查询出只选修了一门课程的全部学生的学号和姓名

# 查询选课数只有1的学生编号
SELECT 
    `sid`,COUNT(`cid`) '选课数'
FROM 
    `sc`
GROUP BY
    `sid`
HAVING 
    `选课数` = 1
# 然后关联学生表进行查询
SELECT 
    `student`.`sname`,`ss`.`sid`
FROM
    `student`,
    (SELECT `sid`,COUNT(`cid`) '选课数'
    FROM `sc`
    GROUP BY `sid`
    HAVING `选课数` = 1) `ss`
WHERE
    `student`.`sid` IN (`ss`.`sid`)

28、查询男生、女生人数

SELECT `ssex`,COUNT(1) '人数'
FROM `student`
GROUP BY `ssex`

29、查询名字中含有"风"字的学生信息

SELECT *
FROM `student`
WHERE `sname` LIKE '%风%';

使用正则表达式方式实现：

select *
from student
where sname REGEXP "风";

另外：

regexp "^风"
regexp "风$"

 

30、查询同名同姓学生名单，并统计同名人数

SELECT 
    `sname`,COUNT(1) AS '同名人数'
FROM
    `student`
GROUP BY
    `sname`
HAVING 
    `同名人数` > 1

30、查询同名同性学生名单，并统计同名男生人数

SELECT 
    `sname`,`ssex`, COUNT(1) AS num 
FROM 
    Student
GROUP BY 
    `sname`,`ssex`
HAVING 
    COUNT(1) > 1 AND `ssex`='男'

31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)

SELECT * FROM `student` WHERE YEAR(`sage`) = 1990 ;

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

# 对课程编号分组，求平均分，然后分数升序排列，对课程编号降序排列
SELECT 
    `cid`,AVG(`score`)
FROM
    `sc`
GROUP BY 
    `cid`
ORDER BY
    `cid` DESC;

37、查询不及格的课程，并按课程号从大到小排列

# 在成绩表中的查询    
SELECT * FROM `sc` WHERE `score` < 60 ORDER BY `cid` DESC;
# 关联课程表
SELECT 
    `sc`.*, `course`.`cname`
FROM
    `sc`,`course`
WHERE
    `sc`.`cid` = `course`.`cid`
    AND `sc`.`score` < 60 ORDER BY `sc`.`cid` DESC

38、查询课程编号为"01"且课程成绩在60分以上的学生的学号和姓名；

# 1、现在成绩表查询课程编号为1的
SELECT * FROM `sc` WHERE `cid` = 01;
# 2、再添加分数要求大于60的
SELECT * FROM `sc` WHERE `cid` = 01 AND `score` > 60;
# 3、最后联合学生表获取名字
SELECT 
    `sc`.*,`student`.`sname`  
FROM 
    `sc`,`student`
WHERE 
    `sc`.`sid` = `student`.`sid`
    AND `sc`.`cid` = 01 
    AND `score` > 60

 

40、查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩

# 查询老师编号
SELECT `tid` FROM `teacher` WHERE `tname` = '张三';
# 通过编号查询所授课程编号
SELECT `cid` FROM `course` WHERE `tid` = (SELECT `tid` FROM `teacher` WHERE `tname` = '张三');
# 查询到成绩表学生编号和成绩
SELECT `sid`,`score`
FROM `sc`
WHERE`cid` = (SELECT `cid` FROM `course` WHERE `tid` = (SELECT `tid` FROM `teacher` WHERE `tname` = '张三'))
ORDER BY `score` DESC 
LIMIT 1
# 关联学生表
SELECT 
    `student`.`sname`,`ss`.`score`
FROM 
    `student`,
    (SELECT `sid`,`score`
    FROM `sc`
    WHERE`cid` = (SELECT `cid` FROM `course` WHERE `tid` = (SELECT `tid` FROM `teacher` WHERE `tname` = '张三'))
    ORDER BY `score` DESC 
    LIMIT 1) `ss`
WHERE
    `student`.`sid` = `ss`.`sid`

42、查询每门功课成绩最好的前两名学生

 

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

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

# 查询成绩表，按照课程字段分组，计数整个表的记录
# 要求课程大于5记录的倒序排序，并返回人数记录
SELECT cid,COUNT(1) COUNT
FROM SC GROUP BY cid HAVING COUNT(1)>=5 ORDER BY COUNT DESC,cid ;

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

# 查询成绩表，并计数课程字段
# 对学号字段分组，之后计数的课程字段大于1的记录返回
SELECT `sid`,COUNT(`cid`) AS C
FROM `sc`
GROUP BY `sid`
HAVING C > 1;

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

# 1 先查询课程表的总课程数量
SELECT COUNT(1) FROM Course
# 2 在成绩表中，查询选课累计等于总课程数的学生ID
SELECT sid ,COUNT(cid) count_c FROM SC 
GROUP BY sid 
HAVING count_c=(SELECT COUNT(1) FROM Course);
# 3 查询学生表得到信息
SELECT `student`.*
from 
    `student`,
    (SELECT sid ,COUNT(cid) count_c FROM SC 
    GROUP BY sid 
    HAVING count_c=(SELECT COUNT(1) FROM Course)) as t
WHERE
    `student`.`sid` in (t.`sid`)

46、查询各学生的年龄 

# 用当前日期求得年份值，再减去字段日期的年份值即可
SELECT Student.*,YEAR(CURDATE())-YEAR(Student.sage) AS '年龄' FROM Student;

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

SELECT * FROM Student WHERE WEEKOFYEAR(NOW())-WEEKOFYEAR(sage)=0;

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

# 使用周按年换算函数，然后和月一样，下一周就取-1
SELECT * FROM Student WHERE WEEKOFYEAR(NOW())-WEEKOFYEAR(sage)=-1;

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

# 先求出现在的时间的月，再和字段月相减得为0的记录即可
SELECT * FROM Student WHERE MONTH(NOW())-MONTH(sage)=0;

50、查询下月过生日的学生

# 本月值，减去字段月的值，相差一个月
# 因为本月大于字段月相减求得的差一定是大于0的
# 所以要查出下一个月过生日的人应该为负数，例如下一个月就-1，下3个月就-3
SELECT * FROM Student WHERE MONTH(NOW())-MONTH(sage)=-1;