实验三 SELECT查询

3.1简单SELECT语句

一． 实验目的

1．观察查询结果, 体会SELECT语句实际应用；

2．要求学生能够在查询分析器中使用SELECT语句进行简单查询。 3. 熟练掌握简单表的数据查询、数据排序和数据连接查询的操作方法。 二． 实验内容及步骤

所有的查询全部用Transact-SQL语句实现 1. 简单查询操作

此部分查询包括投影、选择条件表达、数据排序、使用临时表等。 对EDUC数据库实现以下查询： ① 求计算机系应用的学生学号和姓名； ② 求选修了课程的学生学号；

③ 求选修0001 课程的学生学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同则按学号的升序排列；

④ 求选修课程0001 且成绩在80－90 之间的学生学号和成绩，并将成绩乘以系数0.75 输出；

select sno,score*0.75 from sc where cno='0001' and score between 80 and 90

⑤ 求计算机应用和数学专业的姓张的学生的信息；

select * from student where sdept='计算机学院' or sdept='数学学院' ⑥ 求缺少了成绩的学生的学号和课程号。

Select sno,cno from sc where score is null 2. 连接查询操作

对EDUC数据库实现以下查询：

① 查询每个学生的情况以及他（她）所选修的课程； SELECT Student.*, cno,score

FROM Student left JOIN

SC ON Student.Sno = SC.Sno;

select t.Sname,c.Cname from Student t

left join SC s on s.Sno=t.Sno

left join Course c on c.cno=s.cno

② 求学生的学号、姓名、选修的课程名及成绩；

select t.sno,t.Sname,c.Cname,s.score from

Student t

join SC s on s.Sno=t.Sno

join Course c on c.cno=s.cno

③ 求选修0001 课程且成绩在90 分以上的学生学号、姓名及成绩；

select t.sno,t.Sname,c.Cname,s.score from

Student t

join SC s on s.Sno=t.Sno

join Course c on c.cno=s.cno and s.cno='0001' and s.score>=90

④ 查询每一门课的间接先行课。

select f.cno,s.cpno from course f

join course s on f.cpno=s.cno

3.2 子查询

一．

实验目的

1． 掌握子查询的表示。

2． 进一步掌握SQL Server 查询分析器的使用方法，加深对SQL 语言的嵌套查询语句的理解 二．

实验内容

1．在数据库EDUC中实现一下查询： 1）求选修了高等数学的学生学号和姓名； select t.sno,t.Sname from Student t

join SC s on s.Sno=t.Sno

join Course c on c.cno=s.cno and c.cname='高等数学' 2）求0001 课程的成绩高于张三的学生学号和成绩； select sc.sno,score from

SC where cno='0001' and score>(select score from sc,student s where cno='0001' and sname='张三' and sc.sno=s.sno)

3）求其他专业中比计算机应用专业某一学生年龄小的学生信息（即求其它系中年龄小于计算机应用专业年龄最大者的学生）； select * from student

where Sage < (select max(Sage) from student where sdept = '计算

机学院')and sdept <> '计算机学院'

4）求其他系中比计算机应用专业学生年龄都小的学生信息； select * from student

where Sage < (select min(Sage) from student where sdept = '计算机学院')

and sdept <> '计算机学院';

5）求选修了0002 课程的学生姓名； SELECT sname

FROM Student JOIN

SC ON Student.Sno = SC.Sno and cno='0002'

6）求没有选修0002 课程的学生姓名； SELECT sno,sname FROM student WHERE NOT EXISTS (SELECT * FROM SC WHERE sno=student.sno AND cno='0002') 7）查询选修了全部课程的学生的姓名；

select Sname from Student s

where not exists (select c.* from Course c

where not exists (select * from SC

where Sno=S.Sno and Cno=C.Cno))

8）求至少选修了学号为“20100102”的学生所选修的全部课程的学生学号和姓名。

select Sno from SC where Cno in (select Cno from SC where Sno='20100102') group by Sno having count(*)=(select count(*) from SC where Sno='20100102')

select distinct scx.sno,sname from sc scx,student

where scx.sno =student.sno and not exists (select *

from sc scy,student

where sno ='20100102'and scy.sno =student.sno and not exists (select * from sc scz

where scx.sno =scz.sno and scy.cno =scz.cno))

with t as (

select num=count(1),a.SNO from Sc a,Sc b

where a.CNO=b.CNO and b.SNO='20100102' group by a.SNO)

select t.SNO,sname from t,student s where num>=(select num from t where SNO='20100102') and t.sno=s.sno

3.3 使用子句的SELECT语句

一． 实验目的

1. 熟练掌握数据查询中的分组、统计、计算和组合的操作方法。

2. 进一步掌握SQL Server 查询分析器的使用，加深对SQL 语言的嵌套查询语句的理解。 二． 实验内容

所有查询都全部用SQL语句实现

1. 在数据库EDUC中实现如下查询

1)求学生的总人数。

select count(*) from student

2)求选修了课程的学生人数。

select count(distinct sno) from sc 3)求课程的课程号和选修该课程的人数。

select cno,count(*) from sc group by cno

4)求选修课超过3 门课的学生学号

SELECT sno FROM sc GROUP BY sno HAVING COUNT(*)>3 2. 提高操作实验

建立“图书_读者”数据库及如下3个表，并输入实验数据，用SQL语句实现如下五个查询：

1）查找这样的图书类别：要求类别中最高的图书定价不低于全部按类别分组的图书的平均定价的2 倍。

select A.类别from 图书as A group by A.类别having max(A.定价)>=ALL(select 2*avg(B.定价) from 图书as B group by B.类别) 2）求机械工业出版社出版的各类图书的平均定价，分别用GROUP BY 和GROUP BY ALL 表示。

select distinct 类别, avg(定价) '平均定价' from 图书where 出版社='机械工业出版社' group by 类别

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

SELECT 书号,书名,定价FROM 图书WHERE 类别='计算机' COMPUTE SUM(定价),COUNT(书号)

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

SELECT 书号,书名,定价FROM 图书WHERE 类别='计算机' ORDER BY 出版社COMPUTE COUNT(书号),SUM(定价) BY 出版社

COMPUTE COUNT(书号),SUM(定价)

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

select 书号,书名from 图书where 类别='计算机'and 出版社='机械工业出版社'

表结构如下： 图书：

列名 书号 类别 出版社 作者 书名 定价 读者： 列名 编号 姓名 单位 性别 电话 借阅： 列名 char char char char char 类型 类型 10 8 50 2 15 长度 长度 是 是 是 是否可为空 是否可为空 类型 char char char char char money 长度 10 12 50 20 50 8 是否可为空 是 是 是 串号 书号 读者编号 借阅日期 要求： char char char datetime 10 10 10 8 ①图书表：书号为主码 ②读者表：编号为主码；

性别只能是“男”或“女”的CHECK 约束

③借阅表：串号为主码；

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

实验数据： 图书：

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

insert into 图书values('1001','计算机','机械工业出版社','王民','数据结构',80)

insert into 图书values('1002','计算机','机械工业出版社','张建平','计算机应用',20)

insert into 图书values('1003','计算机','电子工业出版社','王敏','数据库技术',15)

insert into 图书values('1004','计算机','电子工业出版社','谭浩强','C 语言',25)

insert into 图书values('1005','英语','中国人民大学出版社','张锦芯','应用文写作',25)

insert into 图书values('1006','管理','高等教育出版社','Robison','管理学',15)

insert into 图书values('1007','管理','机械工业出版社','Fayol','工业管理',70)

insert into 图书values('1008','数学','机械工业出版社','李平','线性代数',50) insert into 图书values('1009','管理','机械工业出版社','Durark','公司的概念',14)

insert into 图书values('1010','数学','机械工业出版社','徐新国','统计学',15)

insert into 读者values('1001','丁一','数学院','男','81234567') insert into 读者values('1002','赵二','经济学院','男','82234567') insert into 读者values('1003','张三','管理学院','女','83234567') insert into 读者values('1004','李四','文学院','男','84234567') insert into 读者values('1005','王五','历史文化学院','女','85234567')

insert into 读者values('1006','孙六','物理学院','男','86234567') insert into 读者values('1007','周七','生命科学院','女','87234567') insert into 读者values('1008','徐八','化学院','男','88234567') insert into 读者values('1009','宋九','信息学院','女','89234567') insert into 读者values('1010','刘十','计算机学院','女','80234567')

insert into 借阅values('01','1001','1003',2000-1-1) insert into 借阅values('02','1002','1005',2002-3-5) insert into 借阅values('03','1003','1008',1998-6-18)

insert into 借阅values('04','1004','1003',1997-12-8) insert into 借阅values('05','1005','1010',2001-5-4) insert into 借阅values('06','1006','1001',2005-7-25) insert into 借阅values('07','1007','1010',997-11-3) insert into 借阅values('08','1008','1009',2004-2-1) insert into 借阅values('09','1009','1004',1996-9-1) insert into 借阅values('10','1010','1008',2000-6-4)