频道栏目
首页 > 资讯 > SQL Server > 正文

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

18-07-03        来源:[db:作者]  
收藏   我要投稿

表结构


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#

相关TAG标签
上一篇:关于Linux-eth0 eth0:1 和eth0.1关系、ifconfig以及虚拟IP实现的详细教程
下一篇:数据库关于索引优化和SQL语句优化的知识讲解
相关文章
图文推荐

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站