《数据查询与操作》实验答案.docx

《数据查询与操作》实验答案.docx

《《数据查询与操作》实验答案.docx》由会员分享，可在线阅读，更多相关《《数据查询与操作》实验答案.docx（13页珍藏版）》请在冰豆网上搜索。

《数据查询与操作》实验答案

《数据查询与操作》实验

一、实验目的与要求

1、理解简单查询和复合查询的意义。

2、掌握 SELECT 语句和各子句的使用。

3、掌握多表查询和子查询的使用。

4、掌握 INSERT、UPDATE 和 DELETE 语句的使用。

二、实验平台

1、操作系统：

Windows XP 或 Windows 2003

2、数据库管理系统：

SQL Server 2005

三、实验内容

1. 在“人事管理系统”中，新增一个员工信息（员工编号‘100508’、

员工姓名‘小龙女’、所在部门编号‘10001’、籍贯‘河南’）。

USE 人事管理系统

GO

insert into 员工信息（籍贯,所在部门编号,员工姓名,员工编号）

values（'河北', '10001', '小龙女','100508 '）

GO

2. 将“人事管理系统”数据库的“员工信息”表中籍贯为“河南”并且所

在部门编号为”10001”数据插入到“新员工信息”表中。

USE 人事管理系统

GO

insert into 新员工信息（员工编号,员工姓名,所在部门编号）

select 员工编号,员工姓名,所在部门编号

from 员工信息

where 所在部门编号='10001' and 籍贯='河南'

GO

3. 在“人事管理系统”数据库“部门信息”表中，将部门的员工人数设

置为 10。

USE 人事管理系统

GO

update 部门信息 set 员工人数=10

GO

4. 在“人事管理系统”数据库“员工信息”表中，将文化程度为“大专”，

并且在“2005-05-01”到“2007-05-01”之间入职的所有员工调动到编

号为“10006”的部门去。

USE 人事管理系统

GO

update 员工信息

set 所在部门编号=10006

where 入职时间between '2005-05-01' and '2007-05-01'

and 文化程度='大专'

GO

5. 在“人事管理系统”数据库中对部门进行了重组和调整，原来编号

为 10006 的部门名称变为“市场开发部”，人数也调整为 20 人。

USE 人事管理系统

GO

update 部门信息

set 部门名称='市场开发部',员工人数=20

where 部门编号=10006

GO

6. 在“人事管理系统”数据库中，编号为“100503”的新员工升级为正

式员工，需要在“新员工信息”表中删除他的记录。

USE 人事管理系统

GO

delete from 新员工信息 where 员工编号=100503

GO

7. 在“人事管理系统”数据库中，删除“新员工信息”表中所有在编号

为“10005”部门工作的员工记录。

USE 人事管理系统

GO

delete from 新员工信息where 所在部门编号=10001

GO

8. 在“人事管理系统”数据库中，需要删除 5%的员工信息。

USE 人事管理系统

GO

Delete TOP （5） percent from 员工信息

GO

9. GO 在“教务管理系统”数据库“班级信息”表中，查询分配了班

主任的班级信息。

USE 教务管理系统

GO

select * from 班级信息

where 班主任<>''

GO

10. 在“教务管理系统”数据库“学生信息”表中，根据籍贯查询各

省学生人数，并显示省份和人数信息。

USE 教务管理系统

GO

select 籍贯,count（*） AS 学生人数from 学生信息

Group by 籍贯

GO

11. 在“教务管理系统”数据库中，根据相关表的内容，查询平均成

绩大于 70 的课程信息。

USE 教务管理系统

GO

select * from 课程信息

where 课程编号in （

select 课程编号

from 成绩表

group by 课程编号

having avg（成绩）>70）

GO

12. 在 commidtysell 数据库中，根据相关表的内容，查询销售同类产

品的商场编号（去除相同的行）。

USE commoditysell

GO

select DISTINCT EmpID

from EmporiumSell

GO

13. 在 commidtysell 数据库中，根据相关表的内容，查询买出产品数

量最多的商场编号和销售总量信息。

USE commoditysell

GO

select top 1 EmpID as 商场编号,sum（SellAmout） as 销售数量

from EmporiumSell

group by EmpID

order by 销售数量desc

GO

14. 在 TeachingManageSYS 数据库中，根据相关表的内容，统计年

龄大于平均年龄的教师的 Tname、Tsex、Tage、Trank，所教授

课程的 Cname，Credit，CouresHour。

USE TeachingManageSYS

GO

select a.Tname,a.Tsex,a.Tage,c.Cname,c.Credit,c.CouresHour from

TeacherInfo a,Teach b,CourseInfo c

where a.Tno=b.Tno and b.Cno=c.Cno and a.Tage>any（select avg（Tage）

from TeacherInfo）

GO

15. 在“人事管理系统”数据库“员工信息”表中，查询工龄大于 4

年的员工信息

USE 人事管理系统

GO

select * from 员工信息

where year（getdate（））-year（入职时间）>4

GO

16. 将学生“陈霞”所在班级的其他学生并且成绩不合格的学生成绩

删除。

USE 教务管理系统

GO

delete 成绩表

where 成绩<60 and 学号in （

select 学号from 学生信息

where 班级编号=

（select 班级编号

from 学生信息

where 姓名='陈霞'）

）

GO

17. 在“人事管理系统”数据库中，使用 TRUNCATETABLE 语句

删除“新员工信息”表中的所有记录。

USE 人事管理系统

GO

TRUNCATE TABLE 新员工信息

GO

18. 在“人事管理系统”数据库“员工信息”表中，把员工“李朋”

所任职位更改为“副经理”。

USE 人事管理系统

GO

update 员工信息

set 所任职位='副经理'

where 员工姓名='李朋'

GO

19. 查询“学生信息”表中姓名包含“红”字的学生的学号、姓名、性别、

民族、籍贯和班级编号信息。

USE 教务管理系统

GO

select 学号,姓名,性别,民族,籍贯,班级编号from 学生信息where 姓名 like '%红%'

GO

20. 查询“学生信息”表中姓氏为“徐”的学生的学号、姓名、性别、民

族、籍贯和班级编号信息。

USE 教务管理系统

GO

select 学号,姓名,性别,民族,籍贯,班级编号from 学生信息where 姓名 like '徐%'

GO

21. 从“教务管理系统”数据库中，查询人数超过 45 人所在班级中的

学生的学号、姓名和班级名称。

（使用子查询）

USE 教务管理系统

GO

select 学号,姓名,班级名称

from 学生信息a,班级信息b

where a.班级编号=b.班级编号and a.班级编号not in（select 班级编号from 班级信息

where 人数>45）

GO

22. 查询“学生信息”表中姓名不包含“红”字的学生的学号、姓名、性

别、民族、籍贯和班级编号信息。

USE 教务管理系统

GO

select 学号,姓名,性别,民族,籍贯,班级编号

from 学生信息

where 学号not in（select 学号from 学生信息where 姓名like '%红%'）

GO

23. 在“教务管理系统”数据库中，查询还未分配班主任的班级信息。

USE 教务管理系统

GO

select * from 班级信息where 班主任=''

GO

24. 在“人事管理系统”数据库“员工信息”表中，将文化程度为“大专”，

并且在“2005-05-01”到“2007-05-01”之间入职的所有员工调动到编

号为“10006”的部门去。

update 员工信息

set 所在部门编号=10006

where 入职时间between '2005-05-01' and '2007-05-01'

and 文化程度='大专'

25. 查询数据表 ProductInfo 中前 5 条记录。

USE commoditysell

GO

SELECT TOP 5 *

FROM ProductInfo

GO

26. 从 ProductInfo 表中，查询产品名称为“丙电视机”的产品信息。

USE commoditysell

GO

SELECT *

FROM ProductInfo

WHERE ProdName='丙电视机'

GO

27. 从 ProductInfo 表中，查询 FactAddr 为“四川绵阳”或“广东东

莞”，同时 StockAmount 大于 110 的产品的

ProdID、StockAmount 和 FactAddr 信息，并且给列名分别取别名

为产品编号、库存数量和厂商地址。

USE commoditysell

GO

SELECT ProdID AS 产品编号,StockAmount AS 库存数量,FactAddr AS 厂商地址

FROM ProductInfo

WHERE FactAddr in（'四川绵阳','广东东莞'） and StockAmount>110

GO

28. 从 ProductInfo 表中，查询 StockAmount 在 200 和 400（包含 200

和 400）之间的产品的 ProdName 和 StockAmount。

USE commoditysell

GO

SELECT ProdName,StockAmount

FROM ProductInfo

WHERE StockAmount between 200 and 400

GO

29. 创建一个学生补考表，表中数据来自成绩表，条件为成绩小于

60 分，要求补考表中仅显示学生的学号和补考课程的编号。

编

写 SELECT INTO 语句完成这操作。

USE 教务管理系统

GO

select 学号,课程编号

into 补考表

from 成绩表

where 成绩<60

GO

30. 在“教务管理系统”数据库中，查询出所有非计算机系的学生信息，

并显示出这些学生的考试成绩，再按成绩进行降序排列显示，要

求返回学生的学号、姓名、班级编号、课程编号和成绩信息。

（多表查询）

USE 教务管理系统

GO

select a.学号,a.姓名,a.班级编号,b.课程编号,b.成绩

from 学生信息a inner join 成绩表b

on a.学号= b.学号

where a.学号not in（

select 学号from 学生信息where 班级编号in

（select 班级编号from 课程信息where 开课系别='计算机系'）

）

order by 成绩desc

GO

31. 在“教务管理系统”数据库中，查询学生姓名为“朱文娟”所在班级

的学生信息，要求返回学生的学号、姓名、性别、班级编号、班

级名称和年级信息。

USE 教务管理系统

GO

select a.学号,a.姓名,a.性别,b.班级编号,b.班级名称,b.年级

from 学生信息a inner join 班级信息b on a.班级编号=b.班级编号

where a.班级编号=（select 班级编号from 学生信息where 姓名='朱文娟'）

GO

32. 查询空调产品在 ProductInfo 的相关信息，要求该产品在

EmporiumSell 中的销售总量超过 10 台。

USE commoditysell

GO

select * from productinfo p

where （select sum（SellAmout） from emporiumsell e where

p.ProdID=e.ProdID and p.ProdName like '%空调' group by e.ProdID）>10

GO

33. 在“教务管理系统”数据库中，查询出任意一个大于平均成绩的学

生的学号、姓名、班级编号、课程编号和成绩信息。

USE 教务管理系统

GO

select a.学号,a.姓名,a.班级编号,b.课程编号,b.成绩from 学生信息a inner join

成绩表b

on a.学号=b.学号

where 成绩>any（select avg（成绩） from 成绩表）

GO

34. 在“教务管理系统”数据库中，对于成绩大于等于 90 的数据行进

行查询，并返回考试成绩的学生的学号、姓名、性别、籍贯和所

在班级名称及年级信息。

USE 教务管理系统

GO

select a.学号,a.姓名,a.性别,a.籍贯,b.班级名称,b.年级 from 学生信息 a inner

join 班级信息 b

on a.班级编号=b.班级编号

where exists（select * from 成绩表where 学号=a.学号and 成绩>=90）

GO

35. 在“人事管理系统”数据库的“员工信息”表中，查询“所任职位”为

“经理”的员工编号和员工姓名信息，并为其增加新列“所在位置”，

列的内容为“员工信息表”；从“部门信息”表中查询所有的部门编

号和部门名称信息，并定义新增列的内容为“部门信息表”；最后

将两个查询结果联合在一起。

USE 人事管理系统

GO

select 员工编号,员工姓名,'员工信息表' as 所在位置 from 员工信息

where 所任职位='经理'

union

select 部门编号,部门名称,'部门信息表'

from 部门信息

GO

36. 在“教务管理系统”数据库的“学生信息”表和“成绩”表中，查询出

所有考试及格的学生的成绩信息，包括学生的学号、姓名、性别、

年级、班级编号及考试成绩，并且按照成绩进行降序排列。

USE 教务管理系统

GO

select S.学号,S.姓名,S.性别, S.年级,S.班级编号,J.成绩from 学生信息S inner

join 成绩表J on S.学号=J.学号

where J.成绩>=60

order by J.成绩 desc

GO

37. 从“教务管理系统”数据库中的“学生信息”和“班级信息”两表中，

查询学生和对应的班级信息，要求返回的结果中包含学生的学号、

姓名、性别和班级名称及年级。

USE 教务管理系统

GO

select S.学号,S.姓名,S.性别, C.班级名称,C.年级 from 学生信息 S inner join

班级信息 C on S.班级编号=C.班级编号

GO

38. 在“教务管理系统”数据库的“学生信息”表和“成绩”表中，查询所

有学生的考试成绩信息，包括学生学号、姓名、课程编号和成绩

信息。

USE 教务管理系统

GO

select a.学号,a.姓名,b.课程编号,b.成绩

from 学生信息a left outer join 成绩表b on a.学号= b.学号

GO

USE 教务管理系统

GO

select a.学号,a.姓名,b.课程编号,b.成绩

from 学生信息 a right outer join 成绩表 b on a.学号= b.学号

GO

USE 教务管理系统

GO

select a.班级名称,a.人数,a.班主任,b.课程名称,b.开课系别from 班级信息a full

outer join 课程信息b on a.班级编号=b.班级编号

GO

39. 在“人事管理系统”数据库中，对“员工信息”表和“部门信息”表进

行交叉查询，要求查询返回所任职位为“职员”的员工编号、员工

姓名、所任职位、文化程度和部门名称，并按照员工编号降序排

序列。

USE 人事管理系统

GO

select a.员工编号,a.员工姓名,a.所任职位,a.文化程度,b.部门名称from 员工信息a

cross join 部门信息b

where a.所在部门编号=b.部门编号

and a.所任职位='职员'

order by a.员工编号desc

GO

四、实验过程

写出相应的 T-SQL 语句，并写出 T-SQL 语句执行结果的前两行记

录。

五、实验总结

简述实验完成情况、实验过程中各自碰到的问题、各自对实验

目的掌握情况。