【SQL】经典50题 [ 1 ]：Q1-Q10
- 准备：表的建立
- Q1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
- Q1.1 查询同时存在" 01 "课程和" 02 "课程的情况
- Q1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
- Q1.3 查询选择了02课程但没有01课程的情况
- Q2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
- Q3. 查询在 SC 表存在成绩的学生信息
- Q4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
- Q4.1 查有成绩的学生信息
- Q5. 查询「李」姓老师的数量
- Q6. 查询学过「张三」老师授课的同学的信息
- Q7. 查询没有学全所有课程的同学的信息
- Q8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
- Q9. 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
- Q10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
准备：表的建立
环境/系统/编译器：MACOS+Mysql+Sequel Pro
- 删除表的语句（重复运行创建表会报错，置于最前）
drop table Student;
drop table SC;
drop table Course;
drop TABLE Teacher;
- 创建表并插入数据
create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
insert into Student values('01' , 'zhaolei' , '1990-01-01' , 'male');
insert into Student values('02' , 'qiandian' , '1990-12-21' , 'male');
insert into Student values('03' , 'sunfeng' , '1990-05-20' , 'male');
insert into Student values('04' , 'liyun' , '1990-08-06' , 'male');
insert into Student values('05' , 'zhoumei' , '1991-12-01' , 'female');
insert into Student values('06' , 'wulan' , '1992-03-01' , 'female');
insert into Student values('07' , 'zhengzhu' , '1989-07-01' , 'female');
insert into Student values('08' , 'wangju' , '1990-01-20' , 'female');
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , 'writing' , '02');
insert into Course values('02' , 'math' , '01');
insert into Course values('03' , 'english' , '03');
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , 'zhangsan');
insert into Teacher values('02' , 'lisi');
insert into Teacher values('03' , 'wangwu');
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);
Q1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
1. 代码
SELECT * FROM Student RIGHT JOIN
(SELECT TABLE1.sid, score1,score2 -- 选择表1的key，然后使用where
FROM
(SELECT SC.sid ,score score1 FROM SC WHERE SC.cid='01') AS TABLE1, -- 此处有逗号
(SELECT SC.sid ,score score2 FROM SC WHERE SC.cid='02') AS TABLE2
WHERE TABLE1.sid=TABLE2.sid AND TABLE1.score1>TABLE2.score2
) TABELALL
ON Student.sid=TABELALL.sid
2. 运行结果
3. 问题
问题1: 运行优先级请参考下图
问题2: 选择表1的key，然后使用where
Q1.1 查询同时存在" 01 “课程和” 02 "课程的情况
1. 代码
SELECT * FROM Student
RIGHT JOIN
(SELECT TABLE1.sid,score1,score2 FROM
(SELECT SC.sid ,score score1 FROM SC WHERE SC.cid=01) AS TABLE1 -- 此处无逗号
INNER JOIN
(SELECT SC.sid ,score score2 FROM SC WHERE SC.cid=02) AS TABLE2
ON TABLE1.sid=TABLE2.sid) TABLE01
ON Student.sid=TABLE01.sid;
或者使用where语句
SELECT * FROM Student RIGHT JOIN
(SELECT TABLE1.sid, score1,score2 -- 选择表1的key，然后使用where
FROM
(SELECT SC.sid ,score score1 FROM SC WHERE SC.cid='01') AS TABLE1, -- 此处有逗号
(SELECT SC.sid ,score score2 FROM SC WHERE SC.cid='02') AS TABLE2
WHERE TABLE1.sid=TABLE2.sid ) TABELALL
ON Student.sid=TABELALL.sid
2. 运行结果
3. 问题
问题1: MYSQL没有FULL JOIN，尽量避免
问题2: where连接表，和join相比，会将null过滤掉
Q1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
1. 代码
SELECT * FROM Student RIGHT JOIN
(SELECT TABLE1.sid,score1,score2 FROM
(SELECT SC.sid ,score score1 FROM SC WHERE SC.cid=01) AS TABLE1 -- 此处无逗号
LEFT JOIN
(SELECT SC.sid ,score score2 FROM SC WHERE SC.cid=02) AS TABLE2
ON TABLE1.sid=TABLE2.sid) TABLE01
ON Student.sid=TABLE01.sid;
2. 运行结果
3. 问题
问题1: 要使用right join保留score表中的nul信息
Q1.3 查询选择了02课程但没有01课程的情况
1. 代码
SELECT * FROM Student
RIGHT JOIN
(SELECT TABLE2.sid,score1,score2 FROM -- 保留哪个表就select这个表的key
(SELECT SC.sid ,score score2 FROM SC WHERE SC.cid=02) AS TABLE2 -- 此处无逗号
LEFT JOIN
(SELECT SC.sid ,score score1 FROM SC WHERE SC.cid=01) AS TABLE1
ON TABLE1.sid=TABLE2.sid ) TABLE02
ON Student.sid=TABLE02.sid
WHERE TABLE02.score1 IS NULL
2. 运行结果
3. 问题
问题1:可以使用where col_name is null来判断为空
Q2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
1. 代码
SELECT Student.sid,Student.sname,avg_score FROM Student
RIGHT JOIN
(SELECT SC.sid,AVG(SC.score) avg_score FROM SC
GROUP BY SC.sid
HAVING avg_score>60) avg_table -- 要用having而非where，可以用别名
ON Student.sid=avg_table.sid
2. 运行结果
3. 问题
问题1:谨记group后面和having连用；
问题2:group和聚合函数连用的时候，select的对象只能是与其相关的变量 ，如(SELECT SC.sid,AVG(SC.score) avg_score FROM SC GROUP BY SC.sid;
Q3. 查询在 SC 表存在成绩的学生信息
1. 代码
SELECT Student.* FROM Student
WHERE Student.SID IN
(SELECT SC.SID FROM SC WHERE SC.SCORE IS NOT NULL)
2. 运行结果
3. 问题
问题1:
问题2:
Q4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
1. 代码
SELECT Student.*,TOTAL_score, course_count FROM Student
LEFT JOIN
(SELECT SC.SID,SUM(SC.score) TOTAL_score,count(SC.cid) course_count From SC
GROUP BY SC.sid
) sum_count_table
ON Student.sid=sum_count_table.sid
2. 运行结果
3. 问题
问题1: GROUP BY 什么，就select 什么
Q4.1 查有成绩的学生信息
1. 代码
SELECT Student.* FROM Student
WHERE Student.sid in
(SELECT SC.SID From SC)
2. 运行结果
3. 问题
问题1: 类似Q3
Q5. 查询「李」姓老师的数量
1. 代码
SELECT 'li' as namelike,count(tname) names_count FROM Teacher -- 聚合函数也可以和where连用
WHERE Teacher.tname like "li%";
2. 运行结果
3. 问题
问题1: 通过语句 SELECT 'li' as namelike可以设置新的变量名称namelike，且全部赋值为li
问题2: 聚合count可以和where联用
Q6. 查询学过「张三」老师授课的同学的信息
1. 代码
SELECT Student.* FROM Student
WHERE Student.sid in
(
SELECT SC.sid FROM SC
WHERE SC.cid in
(
SELECT Course.cid FROM Course
WHERE Course.tid in
(SELECT Teacher.tid FROM Teacher
WHERE Teacher.tname like "%zhangsan%")
)
)
2. 运行结果
3. 问题
问题1: 层层递进，注意括号和表的联系
Q7. 查询没有学全所有课程的同学的信息
1. 代码
SELECT Student.*,course_count FROM Student
LEFT JOIN
(SELECT SC.SID,count(SC.cid) course_count From SC
GROUP BY SC.sid
) sum_count_table
ON Student.sid=sum_count_table.sid
HAVING course_count<3 OR course_count IS NULL
2. 运行结果
3. 问题
问题1: left join
Q8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
1. 代码
SELECT Student.* FROM Student
WHERE Student.sid in
(
SELECT SC.sid FROM SC
WHERE SC.cid in
(SELECT SC.cid FROM SC
WHERE SC.sid =01)
)
2. 运行结果
3. 问题
问题1: 与Q6类似
Q9. 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
1. 代码
2. 运行结果
3. 问题
问题1:
问题2:
Q10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
1. 代码
SELECT Student.* FROM Student
WHERE Student.sid not in
(
SELECT SC.sid FROM SC
WHERE SC.cid in -- 因为sc里面一个sid对应多条记录，会把sc.cid不等于02的那些记录筛选出来，所以结果会变多
(
SELECT Course.cid FROM Course
WHERE Course.tid not in -- not加在这里，会出现选了其他的课但是也可能含有张三讲的课 这一情况
(SELECT Teacher.tid FROM Teacher
WHERE Teacher.tname like "%zhangsan%") -- not加在这里，会出现选了其他的课但是也可能含有张三讲的课 这一情况
)
)
-- 张三tid=01 ，cid=02，交过的sid:[01,02,03,04,05,07]
2. 运行结果
3. 问题
问题1: not要加在最后的select 逻辑判断里面
问题2: 不同位置的not in会产生不同的查询结果
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
