sql-50练习题6-10

sql练习题6-10题

前言
数据库表结构介绍
- 学生表
- 课程表
- 成绩表
- 教师表
0-6 查询"李"姓老师的数量
0-7 查询学过"李四"老师授课的同学的信息
0-8 查询没学过"李四"老师授课的同学的信息
0-9 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
0-10 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

前言

sql真的非常灵活，一个题目可能有很多很多种解法，我记录的只是我自己的一个解题思路，如果大家有更好的不同解法欢迎在评论区一起探讨
ps：有些题可能解法会重复，比如求大于，我们下一个题可能是求小于，大家如果第一遍没有做出来，看了我写的之后有了思路，可以试一下反面的解法。

数据库表结构介绍

学生表

在这里插入图片描述

课程表

在这里插入图片描述

成绩表

在这里插入图片描述

教师表

在这里插入图片描述

0-6 查询"李"姓老师的数量

首先读题，关键字老师，数量，确定好我们需要的东西
表：teacher
函数count（）

SELECTcount(t.t_name) count_li
FROMteacher t
WHEREt.t_name LIKE '李%';

结果如下：
在这里插入图片描述

0-7 查询学过"李四"老师授课的同学的信息

首先分析一下，我们需要通过name去teacher表中查询到李四老师的t_id，通过t_id再去课程表中查到c_id，有了c_id就可以去成绩表中查到s_id，然后再去student里面查学生信息
表：teacher，course，score，student

SELECT
b.*
from teacher t,course c,score a,student b
where t.t_name='李四'
AND   c.t_id=t.t_id
AND   a.c_id=c.c_id
AND   b.s_id=a.s_id;

结果如下：
在这里插入图片描述

0-8 查询没学过"李四"老师授课的同学的信息

上个题，其实我们是通过找到score表中的学过李四老师课程的学生的id，然后在student表中利用id找出对应符合要求的学生，这个题其实相反，我们把剩余的id取出来其实就是答案

SELECTb.*
FROMstudent b
WHEREb.s_id NOT IN (SELECTa.s_idFROMteacher t,course c,score aWHEREt.t_name = '李四'AND c.t_id = t.t_idAND a.c_id = c.c_id);

结果如下：
在这里插入图片描述

0-9 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

分析一下，感觉题目就很绕，读着很烦，白话说就是：查询学过语文课也学过数学课的同学的信息
表：score ，student
自连接，分别查询语文课，数学课

select 
c.*
from score a,score b,student c
where a.c_id='1'
and b.c_id='2'
and a.s_id=b.s_id
and c.s_id=b.s_id

结果如下：
在这里插入图片描述

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

查询学过语文课但是没有学过数学课的同学的信息
将表的长数据，转换为宽数据
通过下面的方式，得到结果可以直观的看出来有01课程成绩但是没有02课程成绩

select 
a.s_id,
max(CASE when a.c_id='1' then a.s_score else null END) sco1 ,
max(CASE when a.c_id='2' then a.s_score else null END) sco2
from score a
GROUP BY a.s_id;

结果如下：
在这里插入图片描述

再用内连接将两张表连起来

select 
s.*
from 
(select
a.s_id,
max(CASE when a.c_id='1' then a.s_score else null END) sco1 ,
max(CASE when a.c_id='2' then a.s_score else null END) sco2
from score a
GROUP BY a.s_id) t,student s
where t.s_id=s.s_id
and t.sco1 is not NULL
and t.sco2 is   null;