SQL学习日记-Day1

SQL学习日记

今天重温了网传的#SQL面试45题# （的前几道题）
但是是我之前自己做过、整理在word文档、基于自己当时的水平以难易程度重新排序的，所以跟网上流传的任何一份应该都不一样。
代码也是我自己写的，跟大神们的版本可能也略有出入（有些难题是我参考大神写法的！网络一线牵，谢谢大家集思广益教会我这个0基础小白）
仅供参考，欢迎交流。

1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
   select a.s_id, s.s_name, a.score course1 , b.score course2
   from (score a join score b using(s_id)) left join student s using (s_id)
   where a.c_id=01 and b.c_id=02 and a.score > b.score

1.1 查询同时存在01和02课程的情况
select *
from score a join score b using (s_id)
where a.c_id=01 and b.c_id=02
或
SELECT *
FROM (SELECT * FROM Score where c_id=01) a
JOIN (SELECT * FROM Score where c_id=02) b ON a.s_id=b.s_id;
或
SELECT * FROM score a INNER JOIN score b on a.S_id=b.S_id
WHERE a.C_id=01 and b.C_id=02

1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
SELECT * FROM score a LEFT JOIN score b
ON a.S_id=b.S_id and b.C_id=02
where a.C_id=01

1.3 查询选择了02课程但没有01课程的情况
SELECT * FROM
(SELECT * FROM score where s_id not in(SELECT s_id FROM score where c_id=01)) a
INNER JOIN score b
on a.s_id=b.s_id and b.c_id=02 可以参考用inner join的方法，但此处代码不太好
或select a.* from score a
where a.s_id not in (select s_id from score where c_id=01) and a.c_id=02

9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
SELECT * FROM score
where s_id not in (
SELECT s_id FROM score
where c_id not in(SELECT c_id FROM score where s_id=01))
and s_id !=01
GROUP BY s_id
HAVING count(1) =(SELECT count(1) FROM score where s_id=01)

10.查询没学过"张三"老师讲授的任一门课程的学生姓名
select distinct s_name
from score left join student using(s_id)
where score.s_id not in(
select s_id
from score
where c_id=(
select c_id
from course
where t_id=(
select t_id
from teacher
where t_name=“张三”)))

13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select *
from score a left join (select b.s_id, avg(score) jun
from score b
group by b.s_id) c using(s_id)
order by jun desc

14.查询各科成绩最高分、最低分和平均分,以如下形式显示：
课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序
排列
SELECT a.c_name, c.*
FROM course a left join
(SELECT b.c_id ,count(1)as renshu,max(score), min(score), avg(score),
concat(sum(case when score>=90 and score<100 THEN 1 else 0 end)/count(1)*100,’%’) as ‘优秀率’,
concat(sum(case when score>80 and score <90 THEN 1 else 0 end)/count(1)*100,’%’) as ‘优良率’,
concat(sum(case when score>70 and score <80 THEN 1 else 0 end)/count(1)*100,’%’) as ‘中等率’,
concat(sum(case when score>=60 and score>0 THEN 1 else 0 end)/count(1)*100,’%’) as ‘及格率’
FROM score b GROUP BY b.c_id) c using (c_id)
ORDER BY c.renshu desc,a.c_id

保留了这只可爱的小猴几