SQL server 复杂查询

1. 实验目的

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

2.实验内容

1. 连接查询
2. 嵌套查询
3. 谓词查询

3.实验环境

1. Windows
2. SQL Server

实验步骤及结果

1. 创建一个数据库，文件名为“教学”
2. 打开“教学”数据库

Student 表

S#	Sname	Age	Ssex
S1	WANG	15	男
S2	LI	17	女
S3	LU	19	女
S4	ZHAO	13	女
S5	YANG	20	男

Course表

C#	Cname	T#
C1	Maths	T1
C2	DB	T2
C3	English	T3
C4	Computer	T2
C5	Chinese	T2

SC表

S#	C#	Score
S1	C1	50
S2	C1	70
S3	C1	80
S4	C1	75
S5	C1	87
S1	C2
S2	C2
S4	C2	85
S1	C3
S5	C4	60
S4	C4	45

Title表

T#	Tname	Title
T1	吴恩达	教师
T2	刘晓庆	教授
T3	张龙	副教授

注明：表格和下面的代码可能不会一一对应，可能需要增加，删除，更改表中的数据

3. 输入如下数据：

1、检索年龄小于17的女学生的学号和年龄

    select S#,sname
    	from Student
    	where age<17 and Ssex='女'
    go

2、检索男学生所学课程的课程号和成绩

    select distinct Student.s#,score
    	from Student,sc
    	where Ssex='男'
    go

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

    select distinct title.T#,Tname
    	from course,title,Student,sc
    	where course.t#=title.t# and 
    		Student.s#=sc.s# and course.C#=sc.c# 
    		and Student.Ssex='男'
    go

4、检索至少选修两门课的学生学号

    select distinct a.s#
    	from sc a,sc b
    	where a.s#=b.s# and a.c#<>b.c#
    go

5、检索至少有学号s2和s4学生选修的课程的课程号(两种方法解决）

    (1).
    select distinct a.c#
    	from sc a,sc b
    	where a.s#='s2' and b.s#='s4' and a.c#=b.c#
    go

    (2).
    select distinct C#
    	from sc
    	where c# 
    	in(
    		select c#
    		from sc
    		where s#='s2' )
    		and s#='s4'
    go

6、检索wang同学不学的课程的课程号

    select distinct C#
    	from c
    	where C# not in (		
    		select distinct c#
    			from sc
    			where s# in(
    			select s# 
    				from s
    				where sname='wang'))
	go

7、统计有学生选修的课程门数。

    select count(distinct course.c#) 选课人数
    	from course,Student,sc
    	where Student.s#=sc.s# and sc.c#=course.c#
    go

8、求选修C4课程的女学生的平均年龄。

select avg(AGE) 平均年龄
from Student, SC
where Student.S#=SC.S# and SC.C#='C4' 
and Ssex='女'

9、求LIU老师所授课程的每门课程的学生平均成绩。

select course.c#,avg(score) 平均成绩
	from sc,title,course
	where title.t#=course.t# and course.c#=sc.c# and tname='刘晓庆'
	group by course.c#
go

10、统计每门课程的学生选修人数（超过1人的课程才统计）。要求输出课程号和选修人数， 查询结果按人数降序排列，若人数相同，按课程号升序排列。

select c#,count (s#) 人数
	from sc
	group by c#
	having COUNT(*)>1
	order by 2 desc ,1

        order by 2 desc ,1中的“2”和“1”代表SC表中的第二列和第一列，如果写成C#,S#,编译器会报错。

11、检索学号比WANG同学大，而年龄比他小的学生姓名

select sname
	from Student
	where s#>all(select s#
		from Student
		where sname='wang')
		and age<all(select age
			from Student
			where sname='wang')
go

12、在SC中检索成绩为空值的学生学号和课程号。

select s#,c#
	from sc
	where score is null
go

13、检索姓名以L打头的所有学生的姓名和年龄。

select sname,age
	from Student
	where sname like 'l%'
go

14、 求年龄大于女同学平均年龄的男学生姓名和年龄。

select sname,age
	from Student
	where  Ssex='男'and age >(
	select avg (age)
		from Student
		where Ssex='女')
go

        我现在也是一名大三的学生，接触SQL Server的时间并不是很长，里面的代码难免会出错误，如果是引用数据错误，请读者们自己修改一下自己的代码，如果是我的语法和引用出错误，请大家给我在评论区留言，我看到并验证成功后我会改正自己的代码，写这个的目的也是为了同行的朋友们有一个借鉴和参考。