SQL【复习】经典MySQL练习50题之1-11

问题

试问：SELECT * FROM employees; 和 SELECT * FROM employees WHERE commission_pct LIKE “%%” AND last_name LIKE “%%”;结果是否一样？并说明原因

答：

结果不一样 ，因为表中可能存在NULL值，如果表中没有NULL值，则结果是一样的，若and改为or，则结果一样。
#--------------------------------
SELECT *
FROM employees
WHERE commission_pct LIKE '%%’ or last_name like “%%”;

---

经典MySQL练习50题

表名和字段

学生表
Student(s_id、s_name、s_birth、s_sex)：学生编号、学生姓名、出生年月、学生性别。

课程表
Course(c_id、c_name、t_id)：课程编号、课程名称、教师编号。

教师表
Teacher(t_id、t_name)：教师编号、教师姓名。

成绩表
Score(s_id、c_id、s_score)：学生编号、课程编号、分数。

第一题

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数（重点）

-- 解答一

SELECT s.*
FROM `student` AS s,`score` AS sc1,`score` AS sc2
WHERE s.`s_id`=sc1.`s_id` AND s.`s_id`=sc2.`s_id`
AND sc1.`c_id`='01' AND sc2.`c_id`='02'
AND sc1.`s_score`>sc2.`s_score`;

-- 解答二
SELECT s.`s_id`,s.`s_name`,a.`c_id`
FROM (SELECT * FROM `score` AS sc1 WHERE sc1.`c_id`='01') AS a
INNER JOIN 
(SELECT * FROM `score` AS sc2 WHERE sc2.`c_id`='02') AS b ON a.`s_id`=b.`s_id`
INNER JOIN `student` AS s
ON s.`s_id`=a.`s_id`
WHERE a.`s_score`>b.`s_score`;

第二题

2、查询平均成绩大于60分的学生的学号和平均成绩（重点）

SELECT `s_id` AS "学号",AVG(`s_score`) AS "平均成绩"
FROM `score` AS sc
GROUP BY sc.`s_id`
HAVING AVG(sc.`s_score`)>60;

第三题

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

SELECT s.`s_id` AS 'ID',s.`s_name` AS '姓名',COUNT(sc.`s_score`) AS '选课数',SUM(sc.`s_score`) AS '总分'
FROM `student` AS s
LEFT  JOIN `score` AS sc ON s.`s_id`=sc.`s_id`
GROUP BY sc.`s_id`;

第四题

4、查询"李"姓老师的数量

SELECT COUNT(*)
FROM `teacher` AS t
WHERE t.`t_name`LIKE '李%';

第五题

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

-- 解法一
SELECT s.*
FROM `student` AS s,`score` AS sc,`course` AS c,`teacher` AS t
WHERE s.`s_id`=sc.`s_id` AND sc.`c_id`=c.`c_id` AND c.`t_id`=t.`t_id`
AND t.`t_name`='张三';


-- 解法二
SELECT  s.*
	FROM  Student AS s , Score AS  sc 
	WHERE s.s_id = sc.s_id 
	AND  sc.c_id IN  (SELECT  DISTINCT(c.c_id)
		FROM  Course AS c,Teacher AS t 
		WHERE c.t_id = t.t_id 
		AND  t.t_name = "张三")

第六题

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

-- 解法一
SELECT *
FROM `student` AS s
WHERE s.`s_id` NOT IN(
	SELECT sc.`s_id`
	FROM `student` AS s
	LEFT JOIN `score` AS sc
	ON s.`s_id`=sc.`s_id`
	WHERE sc.`c_id` IN(
		SELECT c.`c_id`
		FROM `course` AS c
		INNER JOIN `teacher` AS t
		WHERE c.`t_id`=t.`t_id` AND t.`t_name`='张三'
	));

第七题

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

-- 解法一
SELECT s.*
FROM 
(SELECT * FROM `score` AS sc1 WHERE sc1.`c_id`='01') AS a
INNER JOIN 
(SELECT * FROM `score` AS sc1 WHERE sc1.`c_id`='02') AS b
ON a.`s_id`=b.`s_id`
INNER JOIN `student` AS s ON s.`s_id`=a.`s_id`;


-- 解法二
SELECT  s.* 
FROM Student AS s, Score AS sc1 ,Score AS sc2
WHERE s.s_id = sc1.s_id 
AND  s.s_id = sc2.s_id  
AND  sc1.c_id = "01"
AND  sc2.c_id = "02"

第八题

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

SELECT * 
FROM  Student 
WHERE s_id IN (SELECT s_id FROM  Score WHERE c_id = "01") 
AND  s_id NOT IN (SELECT s_id FROM  Score WHERE c_id = "02");

第九题

9、查询没有所有课程的同学的信息

SELECT s.*
FROM `student` AS s
LEFT JOIN `score` AS sc
ON s.`s_id`=sc.`s_id`
GROUP BY sc.`s_id`
HAVING COUNT(sc.`c_id`)<>
(SELECT COUNT(DISTINCT c.`c_id`) FROM `course` AS c);

第十题

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

SELECT *
FROM `student` AS s
WHERE s.`s_id` IN(
SELECT DISTINCT sc.`s_id`
FROM `score` AS sc
WHERE sc.`c_id` IN (
SELECT sc1.`c_id`
FROM `score` AS sc1
WHERE sc1.`s_id`='01') AND sc.`s_id`<>'01'
)

第十一题

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

SELECT *
FROM `student` AS S
WHERE S.`s_id` IN(
SELECT SC.`s_id`
FROM `score` AS sc
GROUP BY SC.`s_id`
HAVING GROUP_CONCAT(SC.`c_id`) IN(SELECT GROUP_CONCAT(sc.`c_id`)
FROM `score` AS sc
WHERE sc.`s_id`='01') AND S.`s_id`<>'01'
);