下图分别是练习中的教师表,学生表,成绩表,科目表
![](https://img-blog.csdnimg.cn/b3aa4dfe0e104ee38b287cf7c3dbd906.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5bCP5LiR55qE6Iie5Y-w,size_11,color_FFFFFF,t_70,g_se,x_16)
![](https://img-blog.csdnimg.cn/7cff1b85c09447f8b3685c50cab91228.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5bCP5LiR55qE6Iie5Y-w,size_19,color_FFFFFF,t_70,g_se,x_16)
![](https://img-blog.csdnimg.cn/c9d87057f2d24d34bde5a00e8c193138.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5bCP5LiR55qE6Iie5Y-w,size_14,color_FFFFFF,t_70,g_se,x_16)
![](https://img-blog.csdnimg.cn/2114707c849440be91cd11175c41fc93.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5bCP5LiR55qE6Iie5Y-w,size_15,color_FFFFFF,t_70,g_se,x_16)
练习如下
1、查询“c001”课程比“c002”课程成绩高的所有学生的学号;
方法一:使用自连接
select c.sno 学号
from sc c
inner join sc t on c.sno=t.sno and c.cno='c001' and t.cno='c002'
where c.score>t.score
方法二:使用相关自查询
select s.sno from sc s
where s.sno in (
select c.sno from sc c
where s.score>c.score and s.cno='c001' and t.cno='c002'
)
2、查询平均成绩大于60 分的同学的学号和平均成绩
select c.sno,avg(score) 平均成绩
from sc c
group by c.sno
having avg(score)>60
3、查询所有同学的学号、姓名、选课数、总成绩;
select t.sno 学号,t.sname 姓名,count(s.cno) 选课数,sum(s.score) 总成绩
from student t
left join sc s on t.sno=s.sno
group by t.sno,t.sname
4、查询姓“刘”的老师的个数;
select count(tname) 数量
from teacher t
where t.tname like '刘%'
5、查询没学过“谌燕”老师课的同学的学号、姓名;
方法一:
5.1查询出老师的编号
select t.tno from teacher t
where t.tname='谌燕'
5.2查询出老师的所有课程
select s.cno
from course s
inner join teacher t on t.tno=s.tno
where t.tname='谌燕'
5.3查询出学过“谌燕”老师课的同学的学号
select c.sno
from sc c
where c.cno in (
select s.cno
from course s
inner join teacher t on t.tno=s.tno
where t.tname='谌燕'
)
group by c.sno
5.4查询出没学过“谌燕”老师课的同学的学号、姓名
select d.sno,d.sname
from student d
where d.sno not in (
select c.sno
from sc c
where c.cno in (
select s.cno
from course s
inner join teacher t on t.tno=s.tno
where t.tname='谌燕'
)
)
方法二:
select d.sno,d.sname
from student d
where d.sno not in (
select distinct s.sno from student s
left join sc c on c.sno=s.sno
left join course o on c.cno=o.cno
left join teacher t on t.tno = o.tno
where t.tname = '谌燕'
)
6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名;
方法一:通过子查询+自连接
---.1查询学过“c001”并且也学过编号“c002”课程的同学的学号
select c.sno
from sc c
inner join sc t on c.sno=t.sno
where (c.cno='c001' and t.cno='c002') or (c.cno='c002' and t.cno='c001')
group by c.sno
---.2、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名;
select d.sno,d.sname
from student d
where d.sno in (select c.sno
from sc c
inner join sc t on c.sno=t.sno
where (c.cno='c001' and t.cno='c002') or (c.cno='c002' and t.cno='c001')
group by c.sno)
方法二:通过相关子查询
select t.sno,t