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

《数据查询与操作》实验

一、实验目的与要求

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

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

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

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

二、实验平台

1、操作系统：Windows XP或Windows 2003

2、数据库管理系统：SQL Server 2005

三、实验内容

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

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

USE人事管理系统

GO

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

values('河北','10001','小龙女','100508 '，null)

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所在部门编号=10005

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 top 1 EmpID as商场编号,sum(SellAmout)as销售数量

from EmporiumSell

group by EmpID 这里不能写成group by 商场编号

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/3211039579.html,ame,c.Credit,c.CouresHour from TeacherInfo a,Teach b,CourseInfo c

where a.Tno=b.Tno and https://www.360docs.net/doc/3211039579.html,o=https://www.360docs.net/doc/3211039579.html,o and a.Tage> (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姓名='陈霞')and

学号!=(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教务管理系统

GO

select学号,姓名,班级名称

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

where a.班级编号=b.班级编号and a.班级编号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

在数据库commoditysell中操作

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

USE commoditysell

GO

SELECT TOP 5 *

FROM ProductInfo

GO

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

USE commoditysell

GO

SELECT*

FROM ProductInfo

WHERE ProdName='丙电视机'

GO

26.从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

27.从ProductInfo表中，查询StockAmount在200和400(包含200和

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

USE commoditysell

GO

SELECT ProdName,StockAmount

FROM ProductInfo

WHERE StockAmount between 200 and 400

GO

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

分，要求补考表中仅显示学生的学号和补考课程的编号。编写SELECT INTO语句完成这操作。

USE教务管理系统

GO

select学号,课程编号

into补考表

from成绩表

where成绩<60

GO

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

并显示出这些学生的考试成绩，再按成绩进行降序排列显示，要求返回学生的学号、姓名、班级编号、课程编号和成绩信息。（多表查询）

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

或

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

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

on a.学号= b.学号

where a.班级编号not in(

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

)

order by成绩desc

GO

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

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

USE教务管理系统

GO

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

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

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

GO

31.查询空调产品在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

32.在“教务管理系统”数据库中，查询出任意一个(所有)大于平均成绩

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

USE教务管理系统

GO

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

成绩表b

on a.学号=b.学号

where成绩>any(select avg(成绩)from成绩表)

GO

33.在“教务管理系统”数据库中，对于成绩大于等于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

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

“经理”的员工编号和员工姓名信息，并为其增加新列“所在位置”，列的内容为“员工信息表”；从“部门信息”表中查询所有的部门编号和部门名称信息，并定义新增列的内容为“部门信息表”；最后将两个查询结果联合在一起。

USE人事管理系统

GO

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

where所任职位='经理'

union

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

from部门信息

GO

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

有考试及格的学生的成绩信息，包括学生的学号、姓名、性别、年级、班级编号及考试成绩，并且按照成绩进行降序排列。

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

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

询学生和对应的班级信息，要求返回的结果中包含学生的学号、姓名、性别和班级名称及年级。

USE教务管理系统

GO

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

GO

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

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

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

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

交叉查询，要求查询返回所任职位为“职员”的员工编号、员工姓名、所任职位、文化程度和部门名称，并按照员工编号降序排序列。

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

五、实验总结

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