SQL关于表结构的练习题和题解

表结构

---

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#