数据库实验二12870

计算机科学系实验报告 （首页）

课程名称 数据库系统概论 班 级 网络工程2班

实验名称 数据库中数据的查询 指导教师 索剑

姓名 李文森 学 号 1214080613213

日 期 2014年 5月12日

一、实验目的

学会使用SQL语言进行各种类型的查询，理解各种查询的异同及相互之间的转换。

二、实验设备与环境

SQL SERVER 2000、XP系统

三、实验内容、程序清单及运行结果

实验内容：

练习单表查询、嵌套查询、连接查询及集合查询

操作步骤：

1) 打开实验1建立的数据库

2) 在查询分析器中完成以下单表查询语句

查询全体学生的姓名及其出生年份

查询年龄不在20~23岁之间的学生姓名、系别和年龄

查询既不是IS、MA，也不是CS系学生的姓名和性别

查询所有姓刘的学生的姓名、学号和性别

查询选修了3号课程的学生的学号及其成绩，结果按分数的降序排列

计算1号课程的学生平均成绩

查询选修了3门以上课程的学生学号 3) 在查询分析器中完成以下连接查询语句

查询每个学生及其选修课程的情况（使用自然连接、外连接分别完成）

查询每一门课的间接先修课

查询选修2号课程且成绩在90分以上的所有学生

查询每个学生的学号、姓名、选修的课程名及成绩

4) 在查询分析器中完成以下嵌套查询语句

查询与“刘晨”在同一个系学习的学生（使用嵌套查询、自身连接、比较运算符分别完成）

查询选修了课程名为“信息系统”的学生学号和姓名（使用嵌套查询、自然连接分别完成）

查询其他系中比信息系某一学生年龄小的学生姓名和年龄（使用ANY谓词、集函数分别完成）

查询所有选修了1号课程的学生（使用比较运算符、IN、EXISTS分别完成）

查询没有选修1号课程的学生的姓名

查询选修全部课程的学生姓名

查询至少选修了学生95002选修的全部课程的学生号码

5) 在查询分析器中完成以下集合查询语句

查询计算机科学系的学生及年龄不大于19岁的学生并按学号排序（使用单表查询、集合查询分别完成）

查询计算机科学系的学生及年龄不大于19岁的学生的差集

6) 在企业管理器可视化环境下重新完成2-5步骤的内容

程序清单及运行结果

第一：查询

7) 打开实验1建立的数据库

8) 在查询分析器中完成以下单表查询语句

查询全体学生的姓名及其出生年份

SELECT Sname,2014-Sage AS years FROM Student;

查询选修了课程的学生学号（去掉重复行）

SELECT DISTINCT Sno FROM SC;

查询年龄不在20~23岁之间的学生姓名、系别和年龄

SELECT Sname,Sdept,Sage FROM Student where Sage not between 20 and 23;

查询既不是IS、MA，也不是CS系学生的姓名和性别

SELECT Sname,Sdept FROM Student WHERE Sdept not in('IS','MA','CS');

查询所有姓刘的学生的姓名、学号和性别

SELECT Sname,Sno,Sage FROM Student WHERE Sname LIKE '刘%';

查询选修了3号课程的学生的学号及其成绩，结果按分数的降序排列

SELECT Sno,Grade FROM SC WHERE Cno='3' ORDER BY Grade DESC;

计算1号课程的学生平均成绩

SELECT AVG(Grade) FROM SC WHERE Cno='1' ;

查询选修了3门以上课程的学生学号

SELECT Sno from SC GROUP BY Sno HAVING COUNT(Cno)>3;

9) 在查询分析器中完成以下连接查询语句

查询每个学生及其选修课程的情况（使用自然连接、外连接分别完成）

SELECT Student.*,o FROM Student,SC WHERE Student.Sno=SC.Sno;

SELECT Student.*,Cno FROM Student JOIN

SC ON(Student.Sno=SC.Sno);

查询每一门课的间接先修课 SELECT o,second.Cpno from Course first,Course second WHERE

first.Cpno=o;

查询选修2号课程且成绩在90分以上的所有学生

SELECT Sname FROM Student a,SC b WHERE a.Sno=b.Sno AND Cno='2' AND

Grade>90;

查询每个学生的学号、姓名、选修的课程名及成绩

SELECT a.Sno,Sname,ame,Grade FROM Student a,SC b,Course c WHERE

a.Sno=b.Sno AND o=o ;

10) 在查询分析器中完成以下嵌套查询语句

查询与“刘晨”在同一个系学习的学生（使用嵌套查询、自身连接、比较运算符分别完成）

1.嵌套查询

SELECT * FROM Student a WHERE Sdept IN(SELECT Sdept FROM Student b WHERE

b.Sname='刘晨' );

2.自身连接

SELECT a.* FROM Student a,Student b WHERE b.Sname='刘晨' AND a.Sdept=b.Sdept ;

3.比较查询

SELECT * FROM Student a WHERE Sdept=(SELECT Sdept FROM Student b WHERE

b.Sname='刘晨' );

查询选修了课程名为“信息系统”的学生学号和姓名（使用嵌套查询、自然连接分别完成）

1.嵌套查询

SELECT Sno,Sname FROM Student WHERE Sno IN(SELECT Sno FROM SC

WHERE Cno IN(SELECT Cno FROM Course WHERE Cname='信息系统' ) ) ;

2.自然连接

SELECT a.Sno,Sname FROM Student a,SC b ,Course c WHERE a.Sno=b.Sno AND

o=o AND Cname='信息系统';

查询其他系中比信息系某一学生年龄小的学生姓名和年龄（使用ANY谓词、集函数分别完成）

1.ANY谓词

SELECT Sname,Sage FROM Student WHERE Sage

Student WHERE Sdept='IS' ) AND Sdept<>'IS';

2.聚集函数

SELECT Sname,Sage FROM Student WHERE Sage<(SELECT MAX(Sage) FROM

Student WHERE Sdept='IS' )AND Sdept<>'IS';

查询所有选修了1号课程的学生（使用比较运算符、IN、EXISTS分别完成）

1.比较运算符

2.IN

SELECT Sname FROM Student WHERE Sno IN(SELECT Sno FROM SC WHERE

Cno='1' );

3.EXISTS

SELECT Sname FROM Student WHERE EXISTS(SELECT * FROM SC WHERE

Sno=Student.Sno AND Cno='1' );

查询没有选修1号课程的学生的姓名

查询选修全部课程的学生姓名

SELECT Sname FROM Student WHERE NOT EXISTS(SELECT * FROM Course

WHERE NOT EXISTS(SELECT * FROM SC WHERE Sno=Student.Sno AND

Cno=o) );

查询至少选修了学生95002选修的全部课程的学生号码

SELECT DISTINCT Sno FROM SC a WHERE NOT EXISTS(SELECT * FROM SC b

WHERE b.Sno='95002' AND NOT EXISTS(SELECT * FROM SC c WHERE c.Sno=a.Sno

AND o=o));

11) 在查询分析器中完成以下集合查询语句

查询计算机科学系的学生及年龄不大于19岁的学生并按学号排序（使用单表查询、集合查询分别完成）

单表查询：SELECT * FROM Student WHERE Sdept='CS' OR Sage<=19 ORDER BY

Sno;