实验三复杂查询

实验三复杂查询

实验三复杂查询

一、实验目的

通过本次实验使学生掌握数据库中表数据的各种查询操作。

二、实验内容

1、连接查询

2、嵌套查询

3、谓词查询

三、实验环境

1、Windows XP

2、SQL Server 2000

四、实验步骤

1、创建一个数据库，文件名为“教学”

2、还原“教学”数据库

3、根据教材课后题书写查询语句，

调试，运行，得到正确执行结果。

P150 5.3，5.4

5.3（1）检索年龄小于17岁的女学生的学号和姓名，

select s#,sname

from s

where age<17 and sex='女'

s4 zhao

（2）检索男学生所学课程的课程号和成绩，

select c#,score

from sc,s

where s.s#=sc.s# and sex='男'

c1 50

c1 87

c2 NULL

c3 NULL

c4 60

（3）检索男学生所学课程的任课老师的工号和姓名，

select t#,tname

from t

where t# in

( select t#

from c

where c# in

( select c#

from sc

where s# in

( select s#

from s

where sex='男'

)))

t1 wu

t2 liu

t3 zhang

（4）检索至少选修两门课程的学生学号，

select s#

from sc

group by s#

having count(*)>=2

s1

s2

s4

（5）检索至少有学号为s2和s4学生选修的课程的课程号，select c#

from c

where c# in

( select c#

from sc

where s#='s2'

)and c# in

( select c#

from sc

where s#='s4'

)

c1

c2

（6）检索wang同学不学的课程的课程号，

select distinct c#

from sc

where c# not in

( select c#

from sc

where s# in

( select s#

from s

where sname='wang'

)

)select distinct c#

from sc

where c# not in

( select c#

from sc

where s# in

( select s#

from s

where sname='wang'

)

)

空

（7）检索全部学生都选修的课程的课程号和课程名，（不存在不选这门课的同学）

select c#,cname

from c

where not exists

( select *

from s

where not exists

( select *

from sc

where s#=s.s# and c#=c.c#

)

)

c1 maths

（8）检索选修课程包含liu老师所授全部课程的学生学号。（不存在这样的课程y，教师LIU讲授了y，而学生x没有选）

select s#

from s

where not exists

from c

where t# in

( select t#

from t

where tname='liu'

)and not exists

( select *

from sc

where sc.c#=c.c# and sc.s#=s.s#

)

)

s1

5.4 试用sql查询语句表达下列对5.3题的教学数据库中四个基本表t,c,s,sc的查询：（1）统计有学生选修的课程门数，

select count(distinct c#)

from sc

4

（2）求选修c4课程的女学生的平均年龄，

select avg(age)

from s

where sex='女'and s# in

( select s#

from sc

where c# in

( select c#

from c

where c#='c4'

)

)

NULL

（3）求liu老师所授课程的每门课程的平均成绩，

select c#,avg(score)

from sc

where c# in

( select c#

where t# in

( select t#

from t

where tname='liu'

)

)

group by c#

c2 85

c4 60

（4）统计每门课程的学生选课人数（超过10人的课程才统计）。

要求显示课程号和人数，查询结果按人数降序排列，若人数相同，按课程号生序排列。select c#,count(s#)

from sc

group by c#

having count(*)>=10

order by 2

desc,c#

无结果

（5）检索学号比wang同学大，而年龄比他小的学生姓名，

select sname

from s

where s#>

( select s#

from s

where sname='wang'

)

and

age<( select age

from s

where sname='wang'

)

zhao

（6）在表sc中检索成绩为空值的学生学号和课程号，

select s#,c#

from sc

where score is null

s1 c2

s2 c2

s1 c3

（7）检索姓名以L打头的所有学生的姓名和年龄，select sname,age

from s

where sname like 'l%'

li 17

lu 19

（8）求年龄大于女同学平均年龄的男学生姓名和年龄，select sname,age

from s

where age>

( select avg(age)

from s

where sex='女'

)

and sex='男'

yang 20

（9）求年龄大于所有女同学年龄的男学生姓名和年龄。select sname,age

from s

where age>all

( select age

from s

where sex='女'

)

and sex='男'

yang 20