[大数据]多表查询练习—25道经典题目

表数据

-- 学生表
CREATE TABLE `student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
-- 课程表
CREATE TABLE `course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
-- 教师表
CREATE TABLE `teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
-- 成绩表
CREATE TABLE `score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);

插入数据

-- 插入学生表测试数据
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' , '女');
-- 课程表测试数据
INSERT INTO Course VALUES('01' , '语文' , '02');
INSERT INTO Course VALUES('02' , '数学' , '01');
INSERT INTO Course VALUES('03' , '英语' , '03');

-- 教师表测试数据
INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');

-- 成绩表测试数据
INSERT INTO Score VALUES('01' , '01' , 80);
INSERT INTO Score VALUES('01' , '02' , 90);
INSERT INTO Score VALUES('01' , '03' , 99);
INSERT INTO Score VALUES('02' , '01' , 70);
INSERT INTO Score VALUES('02' , '02' , 60);
INSERT INTO Score VALUES('02' , '03' , 80);
INSERT INTO Score VALUES('03' , '01' , 80);
INSERT INTO Score VALUES('03' , '02' , 80);
INSERT INTO Score VALUES('03' , '03' , 80);
INSERT INTO Score VALUES('04' , '01' , 50);
INSERT INTO Score VALUES('04' , '02' , 30);
INSERT INTO Score VALUES('04' , '03' , 20);
INSERT INTO Score VALUES('05' , '01' , 76);
INSERT INTO Score VALUES('05' , '02' , 87);
INSERT INTO Score VALUES('06' , '01' , 31);
INSERT INTO Score VALUES('06' , '03' , 34);
INSERT INTO Score VALUES('07' , '02' , 89);
INSERT INTO Score VALUES('07' , '03' , 98);

题目：?1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT st.`s_name`,sc.`s_score`FROM  student st
LEFT  JOIN `score`  sc ON sc.`s_id`=st.`s_id`
AND sc.`c_id`=01
LEFT JOIN `score` sc1 ON sc1.`s_id`=st.`s_id`
AND sc1.`c_id`=02
WHERE sc.`c_id`>sc1.`c_id`;

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

SELECT st.`s_name`,sc.`s_score`FROM student st 
LEFT JOIN score sc ON sc.`s_id`=st.`s_id` AND
sc.`c_id`=01
LEFT JOIN score sc1 ON sc1.`s_id`=st.`s_id` AND
sc1.`c_id`=02 
WHERE sc.`c_id`<sc1.`c_id`;

?3、查询"李"姓老师的数量 （简单）

SELECT COUNT(*)FROM  `teacher` WHERE`t_name` LIKE "李%";

?4、查询学过"张三"老师授课的同学的信息?

SELECT *FROM `student` st 
INNER JOIN `score` sc ON sc.`s_id`=st.`s_id`
INNER JOIN `course` co ON co.`c_id`=sc.`c_id`
INNER JOIN `teacher`te ON te.`t_id`=co.`t_id`
WHERE
te.`t_name` LIKE '张三';

5、查询没学过"张三"老师授课的同学的信息?

SELECT *FROM `student` st1 WHERE st1.`s_id` NOT IN(
SELECT st.`s_id` FROM `student`st 
LEFT JOIN `score` sc ON sc.`s_id`=st.`s_id`
LEFT JOIN  `course` co ON co.`c_id`=sc.`c_id`
LEFT JOIN `teacher`te ON  te.`t_id`=co.`c_id`
WHERE te.`t_name`="张三");

?6、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT s_id,s_name FROM student
WHERE s_id IN ( 
SELECT a.s_id FROM
(SELECT * FROM score WHERE c_id='01') AS a
INNER JOIN 
(SELECT * FROM score WHERE c_id='02') AS b
ON a.s_id=b.s_id
)

7，查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

SELECT *FROM `student` st 
INNER JOIN `score`sc ON sc.`s_id`=st.`s_id`
INNER JOIN `course` co ON co.`c_id`=sc.`c_id`AND co.`c_id`=01
WHERE st.`s_id` NOT IN(
SELECT st.`s_id`FROM `student` st 
INNER JOIN `score`sc ON sc.`s_id`=st.`s_id`
INNER JOIN `course` co ON co.`c_id`=sc.`c_id`AND co.`c_id`=02);

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

SELECT
st.*
FROM student st
LEFT JOIN score sc ON st.s_id=sc.s_id
GROUP BY st.s_id HAVING COUNT(sc.s_score)<3

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

SELECT DISTINCT st.*FROM `student`st 
LEFT JOIN `score` sc ON st.s_id=sc.s_id
WHERE sc.c_id IN(SELECT sc.c_id FROM `student` st
 LEFT JOIN `score` sc ON st.`s_id`
WHERE st.`s_id`='01') AND st.s_id<>01;

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

SELECT st.s_name 
FROM student st 
WHERE st.s_id 
NOT IN (
SELECT sc.s_id
FROM score sc 
INNER JOIN course c ON c.c_id=sc.c_id
INNER JOIN teacher t ON t.t_id=c.t_id AND t.t_name="张三"
);

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

SELECT *FROM `student` ss
LEFT JOIN `score` cc ON  ss.`s_id`=cc.`s_id`
WHERE cc.`c_id`=1 AND     cc.`s_score`<60
GROUP BY cc.`s_score`DESC

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

SELECT s_id,MAX(CASE WHEN c_id='01' THEN s_score ELSE NULL END)"语文",MAX(CASE WHEN c_id='01' THEN s_score ELSE NULL END)"数学",MAX(CASE WHEN c_id='01' THEN s_score ELSE NULL END)"英语",AVG(s_score)"平均成绩"
FROM score
GROUP BY s_id
ORDER BY AVG(s_score) DESC

13、查询不同老师所教不同课程平均分从高到低显示?

SELECT 
t.t_id,
t.t_name,
c.c_name,
AVG(sc.s_score) 

FROM teacher t 
LEFT JOIN course c ON c.t_id=t.t_id 
LEFT JOIN score sc ON sc.c_id =c.c_id
GROUP BY t.t_id
ORDER BY AVG(sc.s_score) DESC

14,查询每门课程被选修的学生数

SELECT c.`c_name`,COUNT(*)AS 个数 FROM `course`c
INNER JOIN `score` s ON s.`c_id`=c.`c_id`
GROUP BY c.`c_id`;

15、查询出只有两门课程的全部学生的学号和姓名

SELECT st.s_id,st.s_name FROM student st 
LEFT JOIN score sc ON sc.s_id=st.s_id
INNER JOIN course c ON c.c_id=sc.c_id 
GROUP BY st.s_id HAVING COUNT(1)=2

16、查询男生、女生人数

SELECT COUNT(1)  FROM `student` GROUP BY `s_sex`;

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

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

18,查询1990年出生的学生名单

SELECT st*FROM `student` st 
WHERE LIKE st.`s_birth`="1900%";

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

SELECT sc.`s_score`,s.`s_id`,s.`s_name` FROM `student` s 
INNER JOIN `score` sc ON sc.s_id=s.`s_id`
INNER JOIN `course` c ON c.`c_id`=sc.c_id
GROUP BY sc.`s_score` HAVING AVG(sc.`s_score`)>85

20,查询课程名称为"数学"，且分数低于60的学生姓名和分数

SELECT st.`s_name`,s.`s_score`FROM `course` c
 LEFT JOIN `score`s ON s.`c_id`=c.`c_id` 
 LEFT JOIN `student`st ON st.`s_id`=s.`s_id`
 WHERE c.`c_name`="数学" AND s.`s_score`<60;

21，查询所有学生的课程及分数情况

SELECT s.`s_name`,co.`c_name`,sc.`s_score`FROM `student` s 
LEFT JOIN `score` sc ON sc.`s_id`=s.`s_id`
LEFT JOIN `course` co ON co.`c_id`=sc.`c_id` ;

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

SELECT st.`s_name`,c.`c_name`,s.`s_score`
FROM `student` st 
LEFT JOIN `score` s ON s.`s_id`=st.`s_id`
LEFT JOIN  `course` c ON c.`c_id`=s.`c_id` 
WHERE s.`s_score`>70 

23，检索至少选修两门课程的学生学号?

SELECT st.`s_id`FROM `score` s1 
LEFT  JOIN `course` c1 ON s1.`c_id`=c1.`c_id`
LEFT JOIN `student` st ON st.`s_id`=s1.`s_id`
WHERE c1.`c_id`=2

24，统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，

SELECT sc.c_id,COUNT(1)FROM score sc 
GROUP BY sc.c_id 
HAVING  COUNT(1)>5
ORDER BY COUNT(1) DESC

25,求每门课程的学生人数

SELECT COUNT(1)FROM `course` co
INNER JOIN `score` sc ON sc.`c_id`=co.`c_id`
GROUP BY co.`c_id`;

答案可参考，还有更多更简单可实现步骤，互相不雷同