数据库综合练习

下图分别是练习中的教师表，学生表，成绩表，科目表

 

 

 

 

练习如下

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