数据查询-宁剑-SQL实验报告.docx

实验三 数据查询
●实验指导——简单查询和连接查询

一． 实验目的
1．观察查询结果, 体会SELECT语句实际应用；
2．要求学生能够在查询分析器中使用SELECT语句进行简单查询。
3. 熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。
二． 实验准备
1. 完成实验四，成功建立了基本表。
2. 了解简单SELECT语句的用法。
3. 比较熟悉查询分析器中的SQL脚本运行环境。
三． 实验要求
1.完成简单查询和连接查询操作，并验收实验结果提交实验报告
四． 实验内容
CREATE TABLE student1
(
sno char(8) PRIMARY KEY, --学号(主键)
sname char (8) NOT NULL, --姓名
ssex char(2), --性别
sage char(2), --年龄
sdept char(6), --所在院系
)
CREATE TABLE course1
(
cno char(10) PRIMARY KEY, --课程编号（主键）
cname char(20) NOT NULL, --课程名称
cpno char(2), --先行课
credit tinyint --课程学分
)
CREATE TABLE sc
(
sno char(8), --学号(主键)
cno char(10), --课程编号（主键）
grade char(3), --成绩
primary key(sno,cno)
)
所有的查询全部用Transact-SQL语句实现
1. 简单查询操作
此部分查询包括投影、选择条件表达、数据排序、使用临时表等。
对EDUC数据库实现以下查询：
① 求计算机系的学生学号和姓名；
SELECT sno,sname
from
student1 where sdept='CS';
② 求选修了课程的学生学号；
SELECT distinct sno
from sc
where cno is not null;
③ 求选修C1 课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列；
SELECT sno,grade
from course1,sc
where o=o and cname='数学'
order by grade desc,sno asc;
④ 求选修课程C1 且成绩在80－90 之间的学生学号和成绩，并将成绩乘以系数0.75 输出；
SELECT sno,grade
from course1,sc
where o=o and cname='数学' and grade between 80 and 85;
⑤ 求计算机系和数学系的姓张的学生的信息；
SELECT *
from student1
where sdept in ('CS','IS') and sname like '张%';
⑥ 求缺少了成绩的学生的学号和课程号。
SELECT sc.sno,cno
from student1,sc
where student1.sno=sc.sno and grade is null;
⑦ 将2000以后的成绩大于90分的学生成绩存入永久成绩表；将2000年以前的成绩存入临时成绩表中。
2. 连接查询操作
对EDUC数据库实现以下查询：
① 查询每个学生的情况以及他（她）所选修的课程；
SELECT student1.*,o
from student1,sc
where student1.sno=sc.sno;
② 求学生的学号、姓名、选修的课程名及成绩；
SELECT student1.*,o
from student1,sc
where student1.sno=sc.sno;
③ 求选修C1 课程且成绩在90 分以上的学生学号、姓名及成绩；
SELECT sc.sno,student1.sname,sc.grade
from student1,sc
where student1.sno=sc.sno and grade>90;
④ 查询每一门课的间接先行课。
SELECT o,c2.cpno
from course1 c1,course1 c2
where c1.cpno=o;
●实验指导——子查询
一． 实验目的
1． 掌握子查询的表示。
2． 进一步掌握SQL Server 查询分析器的使用方法，加深对SQL 语言的嵌套查询语句的理解
二． 实验准备
1． 掌握SQL语句的基本语法，并熟悉查询分析器的工作环境。
2． 完成了实验二，创建了数据库EDUC及各个基本表。
3． 了解子查询的表示方法，熟悉IN 比较符、ANY、EXISTS操作符的用法。
三． 实验要求
1. 在实验开始之前做好准备工作。
2. 完成实验，并验收实验结果提交实验报告。
四． 实验内容
1．在数据库EDUC中实现一下查询：
1）求选修了高等数学的学生学号和姓名；
select distinct sc.sno,student1.sname
from student1,sc
where sc.sno=student1.sno;
2）求C1 课程的成绩高于张三的学生学号和成绩；
select sc.sno,sc.grade
from course1,sc
where grade>(select sc.grade
from student1,sc,course1
where student1.sno=sc.sno and o=o and sname='李勇'and cname=' 数学')
and o=o and cname='数学';
3）求其他系中比计算机系某一学生年龄小的学生信息（即求其它系中年龄小于计算机系年龄最大者的学生）；
select student1.*
from student1
where sage < any (select sage
from student1
where sdept='CS')
and sdept not in('CS');
4）求其他系中比计算机系学生年龄都小的学生信息；
select student1.*
from student1
where sage < all (select sage
from student1
where sdept='CS')
and sdept not in('CS');
5）求选修了C2 课程的学生姓名；
select student1.sname
from student1,sc,course1
where student1.sno=sc.sno and o=o and cname='数据库';

select sname
from student1
where exists (select sc.*
from sc,course1
where o=o and sno=student1.sno and cname ='数据');
6）求没有选修C2 课程的学生姓名；
select sname
from student1
where not exists (select sc.*
from sc,course1
where o=o and sno=student1.sno and cname ='数据库');
7）查询选修了全部课程的学生的姓名；
select sname
from student1
where not exists (select *
from course1
where not exists
(select *
from sc
where sno=student1.sno and cno=o));
8）求至少选修了学号为“S2”的学生所选修的全部课程的学生学号和姓名。
select sno,sname
from student1
where not exists (select *
from sc x
where x.sno='200215122' and
not exists
(select *
from sc y
where sno=student1.sno and o=o));
2．提高操作实验
建立“工程-零件”数据库及如下4个表，并输入实验数据，用SQL语句实现如下三个查询：
1) 求供应项目j4红色零件的供应商号及名称

2) 求没有使用上海供应商供应的零件的项目号

3) 至少使用了供应商S5所供应全部零件的项目号。

表结构如下：
供应商（S）：
列名
类型
长度
是否可为空
供应商代码
Char
5

姓名
Char
50

所在城市
Char
20

联系电话
Char
20

工程（J）：
列名
类型
长度
是否可为空
工程代码
Char
5

工程名
Char
50

负责人
Char
10

预算
Char
8

零件（P）：
列名
类型
长度
是否可为空
零件代码
Char
5

零件名
Char
50

规格
Char
10

产地
Char
20

颜色
Char
10

供应零件（SPJ）：
列名
类型
长度
是否可为空
供应商代码
Char
5

工程代码
Char
5

零件代码
Char
5

数量
Int
4

要求：
① 应商表：供应商代码为主码

② 程表：工程代码为主码

③ 件表：零件代码为主码

④ 应零件表：供应商代码、工程代码、零件代码为联合主码；

供应商代码为外码，参照表、列是供应商表的供应商代码列；
工程代码为外码，参照表、列是工程表的工程代码列；
零件代码为外码，参照表、列是零件表的零件代码列
实验数据如下：
供应商：
供应商代码
姓名
所在城市
联系电话
S1
北京供应商
北京
0108888888
S2
天津供应商
天津
0228888888
S3
重庆供应商
重庆
0238888888
S4
上海供应商1
上海
0218888888
S5
广州供应商
广州
0208888888
S6
上海供应商2
上海
0216666666
工程：
工程代码
工程名
负责人
预算
J1
工程1
丁一
200000
J2
工程2
赵二
60000
J3
工程3
张三
70000
J4
工程4
李四
80000
J5
工程5
王五
150000
零件：
零件代码
零件名
规格
产地
颜色
P1
螺丝
中
济南
红色
P2
主板
集成
深圳
绿色
P3
显卡
独立
香港
蓝色
P4
声卡
集成
天津
红色
P5
网卡
100M
上海
黑色
P6
鼠标
无线
上海
黑色
供应零件：
供应商代码
工程代码
零件代码
数量
S1
J2
P4
50
S1
J3
P5
100
S2
J2
P6
500
S4
J1
P3
150
S4
J5
P1
200
S5
J4
P6
100
S6
J4
P2
90

●实验指导——使用子句的SELECE语句

一． 实验目的
1. 熟练掌握数据查询中的分组、统计、计算和组合的操作方法。
2. 进一步掌握SQL Server 查询分析器的使用，加深对SQL 语言的嵌套查询语句的理解。
二． 实验准备
1. 了解SELECT语句的GROUP BY和ORDER BY子句的作用。
2. 了解统计函数和分组统计函数的使用方法。
3. 熟悉查询分析器的运行环境。
三． 实验要求
1. 在实验之前做好准备。
2. 完成实验，并验收实验结果提交实验报告。
四． 实验内容
所有查询都全部用SQL语句实现
1. 在数据库EDUC中实现如下查询
1) 求学生的总人数。
SELECT count(sno) 学生人数
from student1
2) 求选修了课程的学生人数。
SELECT count(distinct sno) 选课学生人数
from sc
3) 求课程的课程号和选修该课程的人数。
SELECT cno,count(sno) 学生人数
from sc
group by cno
4)求选修课超过3 门课的学生学号
SELECT sno
from sc
group by sno
having count(*)>3
2. 提高操作实验
建立“图书_读者”数据库及如下3个表，并输入实验数据，用SQL语句实现如下五个查询：
1）查找这样的图书类别：要求类别中最高的图书定价不低于全部按类别分组的图书的平均定价的2 倍。

2）求机械工业出版社出版的各类图书的平均定价，分别用GROUP BY 和GROUP BY ALL
表示。

3）列出计算机类图书的书号、名称及价格，最后求出册数和总价格。

4）列出计算机类图书的书号、名称及价格，并求出各出版社这类书的总价格，最后求出全部册书和总价格。

5）查询计算机类和机械工业出版社出版的图书。

表结构如下：
图书：
列名
类型
长度
是否可为空
书号
char
10

类别
char
12
是
出版社
char
50

作者
char
20
是
书名
char
50

定价
money
8
是
读者：
列名
类型
长度
是否可为空
编号
char
10

姓名
char
8

单位
char
50
是
性别
char
2
是
电话
char
15
是
借阅：
列名
类型
长度
是否可为空
串号
char
10

书号
char
10

读者编号
char
10

借阅日期
datetime
8

要求：
① 书表：书号为主码

② 者表：编号为主码；
性别只能是“男”或“女”的CHECK 约束

③借阅表：串号为主码；
书号为外码，参照表、列是图书表的书号列；
读者编号为外码，参照表、列是读者表的编号列；
书号和读者编号的联合UNIQUE 约束

实验数据：
图书：
书号
类别
出版社
作者
书名
定价
1001
计算机
机械工业出版社
王民
数据结构
80
1002
计算机
机械工业出版社
张建平
计算机应用
20
1003
计算机
电子工业出版社
王敏
数据库技术
15
1004
计算机
电子工业出版社
谭浩强
C 语言
25
1005
英语
中国人民大学出版社
张锦芯
应用文写作
25
1006
管理
高等教育出版社
Robison
管理学
15
1007
管理
机械工业出版社
Fayol
工业管理
70
1008
数学
机械工业出版社
李平
线性代数
50
1009
管理
机械工业出版社
Durark
公司的概念
14
1010
数学
机械工业出版社
徐新国
统计学
15
读者：
编号
姓名
单位
性别
电话
1001
丁一
数学院
男
81234567
1002
赵二
经济学院
男
82234567
1003
张三
管理学院
女
83234567
1004
李四
文学院
男
84234567
1005
王五
历史文化学院
女
85234567
1006
孙六
物理学院
男
86234567
1007
周七
生命科学院
女
87234567
1008
徐八
化学院
男
88234567
1009
宋九
信息学院
女
89234567
1010
刘十
计算机学院
女
80234567
借阅：
串号
书号
读者编号
借阅日期
01
1001
1003
2000-1-1
02
1002
1005
2002-3-5
03
1003
1008
1998-6-18
04
1004
1003
1997-12-8
05
1005
1010
2001-5-4
06
1006
1001
2005-7-25
07
1007
1010
997-11-3
08
1008
1009
2004-2-1
09
1009
1004
1996-9-1
10
1010
1008
2000-6-4

23 / 23