学生表 答案

—学生表练习题
select * from t_students;
select * from t_teacher;
select * from t_course;
select * from t_sc;

1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；
select * from
(select sno,score from t_sc where cno=’c001’) t1,
(select sno,score from t_sc where cno=’c002’) t2
where t1.sno=t2.sno and t1.score>t2.score;
——-方式二：
select * from t_sc t1 ,t_sc t2
where t1.sno=t2.sno and t1.cno=’c001’ and t2.cno=’c002’
and t1.score>t2.score;
—–方式三：
select * from
(select sno,score from t_sc where cno=’c001’) t1
left join
(select sno,score from t_sc where cno=’c002’) t2
on t1.sno=t2.sno
where t1.score>t2.score;

2、查询平均成绩大于60 分的同学的学号和平均成绩；
select sno,avg(score) from t_sc
group by sno having avg(score)>60;
3、查询所有同学的学号、姓名、选课数、总成绩；
select sno,
(select t1.sname from t_students t1 where t1.sno=sc.sno) sname,
count(*) cou,sum(score) sumscore
from t_sc sc group by sno;

select t1.sno,
max(sname),
count(t2.sno),
nvl(sum(score),0) sumscore
from t_students t1
left join t_sc t2 on t1.sno=t2.sno
group by t1.sno
order by sumscore desc;

4、查询姓“刘”的老师的个数；
select count(*) from t_teacher where tname like ‘刘%’;
5、查询没学过“谌燕”老师课的同学的学号、姓名；
select sno,sname from t_students
where sno not in
(–学过谌燕教过的课程的学生学号
select distinct sno from t_sc t2 where t2.cno in(
select cno from t_course t1 where t1.tno=(
select tno from t_teacher where tname=’谌燕’)));

–学过谌燕教过的课程的学生学号
select distinct sno from t_sc t2
left join t_course t1 on t2.cno=t1.cno
left join t_teacher t on t1.tno=t.tno
where trim(t.tname) =’谌燕’;

6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；
方式一：
select t2.sno,t1.sname from t_students t1
inner join –内连接 取姓名
(select sno from t_sc where cno=’c001’
intersect –交集取学号
select sno from t_sc where cno=’c002’) t2
on t1.sno =t2.sno;
方式二：
select t1.sno,
(select sname from t_students t where t.sno=t1.sno ) sname
—select 后的子查询姓名
from
(select sno from t_sc where cno=’c001’) t1,
(select sno from t_sc where cno=’c002’) t2
where t1.sno=t2.sno;
方式三:
select stu.sno,stu.sname from t_students stu
where stu.sno in
(select sc.sno from t_sc sc
where sc.cno in (‘c001’,’c002’) –只要满足其中一个就会被显示出来
group by sno having count(*)=2); –通过分组后 数量等于2的筛选结果就是题意

7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；
数量相同 还是 其中一门的课程相同？
–1、查询学生学了谌燕教的几门课程
–2、拿学生学的课程数量和–谌燕老师教的课程的数量作比较，
–相等就是学过所有的
select s1.sno,
(select sname from t_students st where st.sno = s1.sno) sname
from (select sno, count(*) 选课数 —-1,2,3,4,9 学生编号和他们的选修课数量
from t_sc
where cno in
(select cno
from t_course –c1,2,7,10 是 老师所教的所有课程编号
where tno = (select tno from t_teacher where tname = ‘谌燕’))–t002 是教师编号
group by sno) s1,

   (select count(cno) 选课数
    from t_course
    where tno = (select tno from t_teacher where tname = '谌燕')) s2  --老师教的课程数量

where s1.选课数 = s2.选课数;—单行单列子查询，不需