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

《数据查询与操作》实验

一、实验目的与要求

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

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

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

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

二、实验平台

1、操作系统：Windows XP或Windows2003

2、数据库管理系统：SQL Server2005

三、实验内容

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。

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%的员工信息。

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 top1EmpID 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,https://www.360docs.net/doc/af2800498.html,ame,c.Credit,c.CouresHour from TeacherInfo a,Teach b,CourseInfo c

where a.Tno=b.Tno and https://www.360docs.net/doc/af2800498.html,o=https://www.360docs.net/doc/af2800498.html,o 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成绩<60and学号in(

select学号from学生信息

where班级编号=

(select班级编号

from学生信息

where姓名='陈霞')

)

GO

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

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

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教务管理系统

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 TOP5*

FROM ProductInfo

GO

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

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 between200and400

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语句执行结果的前两行记录。

五、实验总结

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