主要跟着知乎小番茄老师进行练习,下面是网址:SQL面试必会50题 - 知乎 (zhihu.com)
图表:
对以上4个表进行的操作:
-- 1.查找姓赵的学生
--2.查找含有云字的学生
--3.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号(重点)
--4.查询平均成绩大于60分的学生的学号和平均成绩(简单,第二道重点)
--5.查询所有成绩小于60分的学生的学号和平均成绩
-- 1.查找姓赵的学生
select * from Student;
select s_name from Student where s_name like '赵%';
--2.查找含有云字的学生
select s_name from Student where s_name like '%云%';
--3.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号(重点)
--查找的是学号s_id,将学生表与成绩表拼接,
select s_id,s_name,s_birth,s_sex from Student st inner join
(
select * from
(select s_id 'as_id',s_score 'c01' from Score where(c_id='01'))as a
inner join
(select s_id 'bs_id',s_score 'c02' from Score where(c_id='02')) as b
on a.as_id=b.bs_id
where (c01>c02)
) as c on st.s_id=c.as_id;
--4.查询平均成绩大于60分的学生的学号和平均成绩(简单,第二道重点)
select s_id,avg(s_score)as 'average' from Score group by s_id
having avg(s_score)>60;
--5.查询所有成绩小于60分的学生的学号和平均成绩
select a.s_id,avg(s_score)as 'average' from Score join (
select s_id from Student where s_id not in ( select s_id from Score
where s_score >=60))as a on Score.s_id=a.s_id group by a.s_id;
--这里没有08是因为Score里面没有08的分数。 --输出含有08的
select Student.s_id,
avg(case when Score.s_score is null then 0 else Score.s_score end)as 'average'
from Student left join Score on Student.s_id=Score.s_id
where Student.s_id not in
(select Score.s_id from Score where s_score>=60)
group by Student.s_id;
--输出详细信息
select Student.s_id,Student.s_name,Student.s_birth,Student.s_sex,
avg(case when Score.s_score is null then 0 else Score.s_score end)as 'average'
from Student
left join Score
on Student.s_id=Score.s_id
group by Student.s_id,Student.s_name,Student.s_birth,Student.s_sex
having
avg(score.s_score)is null or Student.s_id
not in
(select Score.s_id from Score where Score.s_score>=60);
--报错记录把,写成了.报错不能对varchar调用方法
--选择列表中的列无效,因为该列没有包含在聚合函数或 GROUP BY 子句中
--解决办法把要select的那几列名也加在group by之后。
第五题结果: