实验3 数据库综合查询

实验3  数据库综合查询

一、实验目的

1. 掌握SELECT语句的基本语法和查询条件表示方法；
2. 掌握查询条件种类和表示方法；
3. 掌握连接查询的表示及使用；
4. 掌握嵌套查询的表示及使用；
5. 了解集合查询的表示及使用。

二、实验环境

已安装SQL Server 2012 Express (或SQL Server 2017 Express)的计算机。

三、实验学时

2学时

四、实验内容及步骤

1.请使用Management Studio界面方式或T-SQL语句实现进行以下操作：

新建一个数据库“S_T”，新建3张表，并向各个数据表中插入如下记录（可使用附件中的sql脚本直接插入数据）：

学生信息表（Student）

Sno	Sname	Ssex	Sage	Sdept
200515001	赵菁菁	女	23	CS
200515002	李勇	男	20	CS
200515003	张力	男	19	CS
200515004	张衡	男	18	IS
200515005	张向东	男	20	IS
200515006	张向丽	女	20	IS
200515007	王芳	女	20	CS
200515008	王民生	男	25	MA
200515009	王小民	女	18	MA
200515010	李晨	女	22	MA
200515011	张毅	男	20	WM
200515012	杨磊	女	20	EN
200515013	李晨	女	19	MA
200515014	张丰毅	男	22	CS
200515015	李蕾	女	21	EN
200515016	刘阳	男	21	CM
200515017	刘星耀	男	18	CM
200515018	李贵	男	19	EN
200515019	林自许	男	20	WM
200515020	马翔阳	男	21
200515021	刘峰	男	25	CS
200515022	牛站强	男	22
200515023	李婷婷	女	18
200515024	严丽	女	20
200515025	朱小鸥	女	30	WM

课程信息表（Course）

Cno	Cname	Cpno	Ccredit
1	数据库	5	4
2	数学		2
3	信息系统	1	4
4	操作系统	6	3
5	数据结构	7	4
6	数据处理		2
7	PASCAL语言	6	4
8	大学英语		4
9	计算机网络		4
10	人工智能		2

选课信息表（SC）

Sno	Cno	Grade
200515001	1	75
200515002	1	85
200515002	3	53
200515003	1	86
200515004	1	74
200515005	1	58
200515006	1	84
200515004	2	46
200515005	2	89
200515006	2	65
200515008	2	72
200515009	2	76
200515010	2	96
200515010	8	86
200515011	8	62
200515015	8	0
200515018	8	58
200515001	4	62
200515002	4	85
200515021	9	54
200515001	5	58
200515021	6	58
200515001	7	70
200515005	10	65
200515020	1	78
200515020	5	80
200515016	8	Null
200515017	8	Null

在以上数据基础上，请使用T-SQL语句实现进行以下操作，并给出执行结果(注意：T-SQL语句直接填写在本文档中，仅执行结果以截图形式附在T-SQL语句后面)：

1. 查询名字中第2个字为‘向’的学生姓名和学号及选修的课程号、课程名；

SELECT Sname,Student.Sno,Course.Cno,Cname

FROM Student,Course,SC

WHERE Student.Sno = SC.Sno AND SC.Cno = Course.cno AND Sname like '_阳%'

 

2.查询所选课程的平均成绩大于张力的平均成绩的学生学号、姓名及平均成绩；

SELECT Student.Sname,Student.Sno,avg(SC.Grade) Savg

FROM Student,SC

WHERE Student.Sno = SC.Sno

group by Student.Sno,Student.Sname

having avg(SC.Grade)>

(

select avg(SC.Grade)

from Student,sc

where Student.Sname = '张力' and Student.Sno = SC.Sno

))

 

3.按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和；

SELECT Student.Sno,Sname,Sdept,sum(Ccredit) 已修学分

FROM Student,SC,Course

WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Grade>=60

group by Student.Sno,Student.Sname,Sdept

 

4.查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号；

SELECT Student.Sno,Sname,SC.Cno

FROM Student,SC

WHERE Student.Sno = SC.Sno AND SC.Sno in(

    select SC.Sno

    FROM Student,SC

    where Cno in(

    select Cno

    from SC,Student

    where SC.Sno = Student.Sno and Sname = '张力'

    )

)

 

5.查找至少选修了“数据库”和“数据结构”课程的学生的基本信息；

SELECT distinct Student.*

FROM Student,SC,Course

WHERE Student.Sno = SC.Sno and SC.Cno = Course.Cno and Cname = '数据库'

        and SC.Sno in(

        select SC.Sno

        from Course,SC

        WHERE Course.Cno = SC.Cno and Cname = '数据结构'

)

 

6.查找只选修“数据库”和“数据结构”两门课程的学生的基本信息；

SELECT distinct Student.*

FROM Student,SC,Course

WHERE Student.Sno = SC.Sno and SC.Cno = Course.Cno

group by Student.Sno,Student.Sname,Student.Ssex,Student.Sdept,Student.Sage

having COUNT(Cname)=2

intersect

    SELECT distinct Student.*

FROM Student,SC,Course

WHERE Student.Sno = SC.Sno and SC.Cno = Course.Cno and Cname = '数据库'

intersect

    SELECT distinct Student.*

FROM Student,SC,Course

WHERE Student.Sno = SC.Sno and SC.Cno = Course.Cno and Cname = '数据结构'

 

7.检索所学课程包含学生‘张向东’所学课程的学生学号、姓名；

SELECT distinct Student.Sno,Student.Sname

FROM Student,SC

WHERE Student.Sno = SC.Sno and SC.Cno in

(

select SC.Cno

from Student,SC

where Student.Sno = SC.Sno and  Student.Sname = '张向东'

)

 

8.使用嵌套查询查询其它系中年龄小于IS系的某个学生的学生姓名、年龄和院系；

SELECT Student.Sname,Student.Sage,Student.Sdept

FROM Student

WHERE Student.Sdept<>'IS' and Student.Sage<any

(

    select Student.Sage

    from Student

    where Student.Sdept = 'IS'

    )

 

9.使用ANY、ALL 查询，列出其他院系中比CS系所有学生年龄小的学生；

SELECT Student.Sname,Student.Sage,Student.Sdept

FROM Student

WHERE Student.Sdept<>'CS' and Student.Sage<all

(

    select Student.Sage

    from Student

    where Student.Sdept = 'CS'

    )

 

10.使用集合查询列出CS系的学生与年龄不大于19岁的学生的交集；

SELECT Student.*

FROM Student

WHERE Student.Sdept='CS'

INTERSECT

    select Student.*

    from Student


    where Student.Sage!>19