Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
(1)查询“001”课程比“002”课程成绩高的所有学生的学号;
1 select a.S# from 2 (select S#,Score from SC where C#='001') a, 3 (select S#,Score from SC where C#='002') b 4 where a.S#=b.S# and a.Score>b.Score
(2) 查询平均成绩大于60分的同学的学号和平均成绩;
1 select S#,AVG(Score) as AvgScore 2 from SC 3 group by S# 4 having AVG(Score)>60
(3)查询所有同学的学号、姓名、选课数、总成绩;
1 select s.S#,s.Sname,COUNT(sc.C#) as CourseCount,SUM(sc.Score) as ScoreSum 2 from Student s left outer join SC sc 3 on s.S# = sc.S# 4 group by s.S#,s.Sname 5 order by s.S#
(4)查询姓“李”的老师的个数;
1 select COUNT(distinct Tname) as count 2 from Teacher 3 where Tname like '李%'
(5)查询没学过“叶平”老师课的同学的学号、姓名;
1 select s.S#,s.Sname 2 from Student s 3 where s.S# not in 4 ( 5 select distinct(sc.S#) from SC sc,Course c,Teacher t 6 where sc.C#=c.C# and c.T#=t.T# and t.Tname='叶平' 7 )
(6)查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
1 --解法一:求交集 2 select s.S#,s.Sname 3 from Student s,SC sc 4 where s.S#=sc.S# and sc.C#='001' 5 intersect 6 select s.S#,s.Sname 7 from Student s,SC sc 8 where s.S#=sc.S# and sc.C#='002' 9 --解法二:使用exists 10 select s.S#,s.Sname 11 from Student s,SC sc 12 where s.S#=sc.S# and sc.C#='001' and exists 13 ( 14 select * from SC sc2 where sc.S#=sc2.S# and sc2.C#='002' 15 )
(7)查询学过“叶平”老师所教的所有课的同学的学号、姓名;
1 select s.S#,s.Sname 2 from Student s 3 where s.S# in 4 ( 5 select sc.S# 6 from SC sc,Course c,Teacher t 7 where c.C#=sc.C# and c.T#=t.T# and t.Tname='叶平' 8 group by sc.S# 9 having COUNT(sc.C#)= 10 ( 11 select COUNT(c1.C#) 12 from Course c1,Teacher t1 13 where c1.T#=t1.T# and t1.Tname='叶平' 14 ) 15 )
(8)查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
1 select s.S#,s.Sname 2 from Student s, 3 (select sc1.S#,sc1.Score from SC sc1 where sc1.C#='002') a, 4 (select sc2.S#,sc2.Score from SC sc2 where sc2.C#='001') b 5 where s.S#=a.S# and s.S#=b.S# and a.S#=b.S# and a.Score(9)查询有课程成绩小于60分的同学的学号、姓名;
1 select s.S#,s.Sname 2 from Student s 3 where s.S# in 4 ( 5 select distinct(sc.S#) from SC sc 6 where s.S#=sc.S# and sc.Score<60 7 )
(10)查询没有学全所有课的同学的学号、姓名;
1 select s.S#,s.Sname 2 from Student s 3 where s.S# not in 4 ( 5 select sc.S# from SC sc 6 group by sc.S# 7 having COUNT(distinct sc.C#)= 8 ( 9 select COUNT(distinct c.C#) from Course c 10 ) 11 )
(11)查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名;
1 select distinct(s.S#),s.Sname 2 from Student s,SC sc 3 where s.S#=sc.S# and sc.C# in 4 ( 5 select distinct(sc2.C#) from SC sc2 6 where sc2.S#='001' 7 ) 8 order by s.S# asc
(12)查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名;
select s.S#,s.Sname from Student s where s.S#!='002' and s.S# in ( select distinct(S#) from SC where C# in (select C# from SC where S#='002') group by S# having COUNT(distinct C#)= ( select COUNT(distinct C#) from SC where S#='002' ) )
(13)查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;
1 select sc.C# as '课程ID',MAX(Score) as '最高分',MIN(Score) as '最低分' 2 from SC sc 3 group by sc.C#