SQL部分练习

记录一下做过的一些题
已知如下4张表：
学生表：stuents01(s,name,age,sex) #s为学号，name为姓名，age为年龄，sex为性别
课程表：course01(c,name,teacher) #c为课程号，name为课程名称，teacher为教师编号
成绩表：score01(s,c,score) #s为学号，c为课程号，score为分数
教师表：teacher(teacher,name) # teacher为教师编号，name为教师姓名

每张表赋值

INSERT INTO `stuents01` VALUES('1','始皇帝','39','male');
INSERT INTO `stuents01` VALUES('2','汉高祖','54','male');
INSERT INTO `stuents01` VALUES('3','吕太后','46','female');
INSERT INTO `stuents01` VALUES('4','汉武帝','16','male');
INSERT INTO `stuents01` VALUES('5','汉光武帝','30','male');
INSERT INTO `stuents01` VALUES('6','阴丽华','28','female');
INSERT INTO `stuents01` VALUES('7','魏武帝','58','male');
INSERT INTO `stuents01` VALUES('8','隋文帝','41','male');
INSERT INTO `stuents01` VALUES('9','唐太宗','27','male');
INSERT INTO `stuents01` VALUES('10','宋武帝','57','male');

INSERT INTO `course01` VALUES('1001','english','TS01');
INSERT INTO `course01` VALUES('1002','math','TS09');
INSERT INTO `course01` VALUES('1003','art','TS07');
INSERT INTO `course01` VALUES('1004','dance','TS08');
INSERT INTO `course01` VALUES('1005','physic','TS03');
INSERT INTO `course01` VALUES('1006','chemistry','TS02');
INSERT INTO `course01` VALUES('1007','paint','TS06');
INSERT INTO `course01` VALUES('1008','panio','TS05');
INSERT INTO `course01` VALUES('1009','computer','TS04');

INSERT INTO `teacher01` VALUES('TS01','秦庄襄王');
INSERT INTO `teacher01` VALUES('TS02','张良');
INSERT INTO `teacher01` VALUES('TS03','汉文帝');
INSERT INTO `teacher01` VALUES('TS04','汉景帝');
INSERT INTO `teacher01` VALUES('TS05','王莽');
INSERT INTO `teacher01` VALUES('TS06','史万岁');
INSERT INTO `teacher01` VALUES('TS07','韩信');
INSERT INTO `teacher01` VALUES('TS08','张居正');
INSERT INTO `teacher01` VALUES('TS09','项羽');

INSERT INTO score01 VALUES('1','1001','100');
INSERT INTO score01 VALUES('1','1002','98');
INSERT INTO score01 VALUES('1','1003','80');
INSERT INTO score01 VALUES('1','1004','100');
INSERT INTO score01 VALUES('1','1005','99');
INSERT INTO score01 VALUES('1','1006','82');
INSERT INTO score01 VALUES('1','1007','90');
INSERT INTO score01 VALUES('1','1008','91');
INSERT INTO score01 VALUES('1','1009','95');
INSERT INTO score01 VALUES('2','1001','100');
INSERT INTO score01 VALUES('2','1002','98');
INSERT INTO score01 VALUES('2','1003','80');
INSERT INTO score01 VALUES('2','1004','99');
INSERT INTO score01 VALUES('2','1005','78');
INSERT INTO score01 VALUES('2','1006','87');
INSERT INTO score01 VALUES('2','1007','50');
INSERT INTO score01 VALUES('2','1008','80');
INSERT INTO score01 VALUES('2','1009','79');
INSERT INTO score01 VALUES('3','1001','68');
INSERT INTO score01 VALUES('3','1002','80');
INSERT INTO score01 VALUES('3','1003','89');
INSERT INTO score01 VALUES('3','1004','80');
INSERT INTO score01 VALUES('3','1005','99');
INSERT INTO score01 VALUES('3','1006','82');
INSERT INTO score01 VALUES('3','1007','90');
INSERT INTO score01 VALUES('3','1008','91');
INSERT INTO score01 VALUES('3','1009','95');
INSERT INTO score01 VALUES('4','1001','88');
INSERT INTO score01 VALUES('4','1002','96');
INSERT INTO score01 VALUES('4','1003','80');
INSERT INTO score01 VALUES('4','1004','100');
INSERT INTO score01 VALUES('4','1005','94');
INSERT INTO score01 VALUES('4','1006','80');
INSERT INTO score01 VALUES('4','1007','66');
INSERT INTO score01 VALUES('4','1008','56');
INSERT INTO score01 VALUES('4','1009','98');
INSERT INTO score01 VALUES('5','1001','100');
INSERT INTO score01 VALUES('5','1002','98');
INSERT INTO score01 VALUES('5','1003','80');
INSERT INTO score01 VALUES('5','1004','10');
INSERT INTO score01 VALUES('5','1005','99');
INSERT INTO score01 VALUES('5','1006','82');
INSERT INTO score01 VALUES('5','1007','90');
INSERT INTO score01 VALUES('5','1008','98');
INSERT INTO score01 VALUES('5','1009','85');
INSERT INTO score01 VALUES('6','1001','100');
INSERT INTO score01 VALUES('6','1002','98');
INSERT INTO score01 VALUES('6','1003','80');
INSERT INTO score01 VALUES('6','1004','100');
INSERT INTO score01 VALUES('6','1005','99');
INSERT INTO score01 VALUES('6','1006','82');
INSERT INTO score01 VALUES('6','1007','90');
INSERT INTO score01 VALUES('6','1008','91');
INSERT INTO score01 VALUES('6','1009','95');
INSERT INTO score01 VALUES('7','1001','100');
INSERT INTO score01 VALUES('7','1002','98');
INSERT INTO score01 VALUES('7','1003','80');
INSERT INTO score01 VALUES('7','1004','100');
INSERT INTO score01 VALUES('7','1005','99');
INSERT INTO score01 VALUES('7','1006','82');
INSERT INTO score01 VALUES('7','1007','90');
INSERT INTO score01 VALUES('7','1008','99');
INSERT INTO score01 VALUES('7','1009','95');
INSERT INTO score01 VALUES('8','1001','10');
INSERT INTO score01 VALUES('8','1002','98');
INSERT INTO score01 VALUES('8','1003','88');
INSERT INTO score01 VALUES('8','1004','10');
INSERT INTO score01 VALUES('8','1005','49');
INSERT INTO score01 VALUES('8','1006','82');
INSERT INTO score01 VALUES('8','1007','96');
INSERT INTO score01 VALUES('8','1008','91');
INSERT INTO score01 VALUES('8','1009','99');
INSERT INTO score01 VALUES('9','1001','100');
INSERT INTO score01 VALUES('9','1002','98');
INSERT INTO score01 VALUES('9','1003','88');
INSERT INTO score01 VALUES('9','1004','80');
INSERT INTO score01 VALUES('9','1005','99');
INSERT INTO score01 VALUES('9','1006','84');
INSERT INTO score01 VALUES('9','1007','56');
INSERT INTO score01 VALUES('9','1008','54');
INSERT INTO score01 VALUES('9','1009','95');
INSERT INTO score01 VALUES('10','1001','100');
INSERT INTO score01 VALUES('10','1002','98');
INSERT INTO score01 VALUES('10','1003','80');
INSERT INTO score01 VALUES('10','1004','100');
INSERT INTO score01 VALUES('10','1005','99');
INSERT INTO score01 VALUES('10','1006','82');
INSERT INTO score01 VALUES('10','1007','90');
INSERT INTO score01 VALUES('10','1008','91');
INSERT INTO score01 VALUES('10','1009','95');

#查询课程1001比1002分数高的学号

SELECT x.`s` FROM score01 x,score01 y
WHERE x.`c`='1001' and y.`c`='1002' AND x.`s`=y.`s` AND x.score > y.score;

#查询平均成绩大于90分的学生的学号和平均成绩

SELECT `s`,avg(`score`) from score01  GROUP BY `s` HAVING avg(`score`)>90;

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

SELECT stuents01.`s`,`name`,COUNT(c),SUM(score) 
FROM stuents01 INNER join score01 on stuents01.s=score01.s GROUP BY `s`;

#查询姓“汉”的老师的个数

SELECT COUNT(`name`) FROM teacher01 WHERE `name` LIKE '汉%';

#查询没学过“张良”老师课的学生的学号、姓名

DELETE from score01 WHERE s='7' AND c='1006';

SELECT `s`,`name` FROM stuents01 WHERE s not in 
(SELECT `s` FROM score01 WHERE c in 
(SELECT c from course01 WHERE `teacher` in 
(SELECT `teacher` FROM teacher01 WHERE `name`='张良')));

#查学过“张良”所有课的学生的学号、姓名
这题有两种方法

INSERT INTO course01 VALUES('1010','quanmao','TS02');
INSERT INTO score01 VALUES('2','1010','100');
INSERT INTO score01 VALUES('4','1010','99');
#第一种方法
SELECT `s`,`name` FROM stuents01 WHERE 
NOT EXISTS
/*
要满足的条件是：
1:查张良的course _c(两个)
2:张良的两个c（看做一个整体）不能包含在学生对应的查出来的成绩表中的课程c
*/
(SELECT `c` FROM course01 WHERE `teacher` in (SELECT `teacher` FROM teacher01 WHERE `name`='张良') 
AND course01.c not in
(SELECT `c` FROM score01 WHERE score01.`s`=stuents01.`s`));

#第二种方法
SELECT `s`,`name` FROM stuents01 WHERE `s` in
(SELECT `s` FROM(
SELECT `s`,COUNT(`c`) as num FROM score01 WHERE `c` in 
(SELECT `c` FROM course01 WHERE `teacher` in 
(SELECT `teacher` FROM teacher01 WHERE `name`='张良')) GROUP BY `s`
HAVING num = (SELECT COUNT(`c`) FROM course01 WHERE `teacher`  in 
(SELECT `teacher` FROM teacher01 WHERE `name`='张良'))) as t );

#查询学过编号为“1001”的课程并且也学过编号为“1010”的课程的学生的学号、姓名

SELECT 	`s`,`name` FROM stuents01 WHERE `s` in
(SELECT x.`s` FROM score01 x,score01 y WHERE x.`c`='1010' AND y.c='1001' AND x.s=y.s);

#查询所有课程成绩小于90分的学生的学号、姓名，也是用了两种方法

#第一种
SELECT b.`s`,a.`name` FROM stuents01 a 
INNER JOIN score01 b ON a.s=b.s 
GROUP BY b.`s` 
HAVING MAX(score)<90;
#第二种
SELECT `s`,`name` FROM stuents01 WHERE `s` in(
(SELECT `s` FROM score01 GROUP BY `s` having sum(`score`<90)=COUNT(`c`)));

#查询没有学全所有课的学生的学号、姓名

SELECT `s`,`name` FROM stuents01 WHERE `s` in(
SELECT `s` FROM score01 GROUP BY `s` HAVING COUNT(`c`) <
(SELECT COUNT(DISTINCT `c`) FROM course01));