原创力文档- 12
- 0
- 约 5页
- 2016-10-11 发布于广东
- 举报
50SQL语句目及答案
/*
Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
*/
--1、查询“001”课程比“002”课程成绩高的所有学生的学号；
select s1.s#
from SC s1,SC s2
where s1.s#=s2.s# and s1.c#=001 and s2.c#=002 and s1.scores2.score;
--2、查询平均成绩大于60分的同学的学号和平均成绩；
select s.s#,avg(s.score)
from SC s
group by s.s#
having avg(s.score)60;
--3、查询所有同学的学号、姓名 、选课数、总成绩；
select d.s#,max(d.sname),count(distinct s.c#),sum(s.score)
from Student d,SC s
where d.s#=s.s#
group by d.s#;
--4、查询姓“李”的老师的个数；
select count(0)
from Teacher t
where t.tname like 李%;
--5、查询没学过“叶平”老师课的同学的学号、姓名；
select d.s#,d.sname
from Student d
where not exists (
select distinct(s.s#)
from SC s,Teacher t,Course c
where t.t#=c.t# and s.c#=c.c# and t.tname=叶平 and d.s#=s.s#);
--6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select d.s#, d.sname
from Student d, SC s
where s.s# = d.s# and s.c# = 001 and exists
(select s1.s# from SC s1 where s1.s# = s.s# and s1.c# = 002);
--7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
select s.s#,max(d.sname)
from SC s,Student d,Course c,Teacher t
where s.s#=d.s# and s.c#=c.c# and c.t#=t.t# and t.tname=叶平
group by s.s#
having count(distinct s.c#) = (
select count(0) from Course c,Teacher t where c.t#=t.t# and t.tname=叶平);
--8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
select d.s#,d.sname
from SC s1,SC s2,Student d
where s1.s#=s2.s# and s1.s#=d.s# and s1.c#=002and s2.c#=001 and s1.scores2.score;
--9、查询所有课程成绩小于60分的同学的学号、姓名；
select s.s#,max(d.sname)
from Student d,SC s
where d.s#=s.s# and s.score60
group by s.s#
having count(0) = (select count(0) from SC s1 where s.s#=s1.s#);
--10、查询没有学全所有课的同学的学号、姓名；
select s.s#,max(d.sname)
from Student d,SC s
where d.s#=s.s#
group by s.s#
having count(distinct s.c#) (select count(0) from Course);
--11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
select distinct s.s#,d.sname
from Student d,SC s
where d.s#=s.s# and s.s#1001 and exists(
select s1.c# from SC s1 where s1.s#=1001 and s.c#=s1.c# );
--12、查询至少学过学
文档评论（0）