数据库笔试题(考察基本SQL能力)

注意：尽量不要用“in”及“=”，用关联查询。

表：--1.学生表

Student(S#,Sname,Sage,Ssex) --S# 学生编号,Sname 学生姓名, Sage 出生年月,Ssex 学生性别

--2.课程表

Course(C#,Cname,T#) -- C# 课程编号,Cname 课程名称,T# 教师编号

--3.教师表

Teacher(T#,Tname) --T# 教师编号,Tname 教师姓名

--4.成绩表

SC(S#,C#,score) -- S# 学生编号,C# 课程编号,score 分数

题目：

1、查询“01”课程比“02”课程成绩高的学生的信息及课程分数？

Select s.S#, s.Sname, s.Sage, s.Ssex, middle.score01, middle.score02 from Student s, ( Select

a.S#, a.score as score01,

b.scor e as score02 from SC a, SC b where a.S#=b.S# and a.C#=’01’ and

b.C#=’02’ and a.score > b.score) middle where s.S# = middle.S#;

2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩？

Select s.S#, s.Sname, middle.avgScore from Student s,

(Select S#, avg(score) as avgScore from SC group by S# having avgScore>=60) middle where s.S# = middle.S#;

3、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩？

Select s.S#, s.Sname, middle.Cs, middle.Ss from Student s,

(Select S#, count(C#) as Cs, sum(score) as Ss from SC group by S# ) middle

Where s.S# = middle.S#;

4、查询学过“张三”老师授课的同学的信息？

Select s.S#, s.Sname, s.Sage, s.Ssex from Student s, Course c, Teacher t, SC sc

Where t.Tname = “张三”and c.T# = t.T#

And c.C# = sc.C# and sc.S# = s.S#;

5、查询学过编号为“01”并且也学过编号为“02”的课程的同学的信息？

Select s.* from Student s, SC sc01, SC sc02

Where sc01.C#=’01’ and sc02.C#=’02’

And s.S#=sc01.S# and s.S#=sc02.S#;

6、查询没有学全所有课程的同学的信息？

Select count(distinct C#) from SC; -- 记录为total

Select s.* from Student s,

(Select S#, count(distinct C#) as cs from SC group by S# having cs< total) middle

Where s.S# = middle.S#;

7、查询没有学过“张三”老师讲授的任一门课程的学生姓名？

Select s.Sname from Student s,

((select distinct S# from SC) minus (Select distinct S# from SC sc where sc.C#, Course c, Teacher t where t.Tname=’张三’ and t.T# = c.T# and c.C# = sc.C#)) middle

where s.S# = middle.S#;

8、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩？

Select s.S#, s.Sname, middle.avgScore from Student s,

(Select sc.S#, count(*) as counts , avg(score) as avgScore from SC sc where score <60 group by sc.S# having counts>1) middle

Where s.S# = middle.S#;

9、统计各科成绩各分数段的人数：课程编号，课程名称，[100-85],[85-70],[70-60],[0-60]？分数段分别查询，示例是100-85分数段的

Select c.C#, ame, middle.counts as counts from Course c,

(Select C#, count(*) as counts from SC sc where sc.score>=85 group by C#) middle

Where c.C# = middle.C#;