《数据查询与操作》实验
一、实验目的与要求
1、理解简单查询和复合查询的意义。 2、掌握SELECT语句和各子句的使用。 3、掌握多表查询和子查询的使用。
4、掌握INSERT、UPDATE和DELETE语句的使用。 二、实验平台
1、操作系统：Windows XP或Windows 2003 2、数据库管理系统：SQL Server 2005 三、练习 1、插入数据
1)在“人事管理系统”数据库“员工信息”表中，插入一个新员工信息（员工编号‘100508’、员工姓名‘苏娜’、所在部门编号‘10005’、 性别’女’籍贯‘河北’）该员工还没安排职位。
insert into 员工信息(员工编号,员工姓名,所在部门编号,性别,籍贯) values('100508','苏娜','10005', '女','河北')
2)在“人事管理系统”中，新增一个员工信息（员工编号‘100509’、员工姓名‘小龙女’、所在部门编号‘10001’、籍贯‘河南’）。
insert into 员工信息(员工编号,员工姓名,所在部门编号,籍贯) values('100509','小龙女','10001','河南')
3)将“人事管理系统”数据库的“员工信息”表中籍贯为“河南”并且所在部门编号为”10001”数据插入到“新员工信息”表中。 create table 新员工信息 (员工编号 int not null,
员工姓名 varchar(50) not null, 所在部门编号 int null, 入职时间 datetime null )
insert into 新员工信息(员工编号,员工姓名,所在部门编号) select 员工编号,员工姓名,所在部门编号 from 员工信息
where 所在部门编号='10001' and 籍贯='河南'
4)将“人事管理系统”数据库中技术部门的员工的简明信息 (包括：员工编号、员工姓名、部门名称、所任职位和文化程 度)保存到表“技术部人员”中。
select 员工编号,员工姓名,部门名称,所任职位,文化程度 into 技术部人员
from 部门信息b join 员工信息y on b.部门编号=y.所在部门编号 where 部门名称='技术部'
2、更新数据
1)在“人事管理系统”数据库“部门信息”表中，将部门的员工人数设置为10。
update 部门信息 set 员工人数=10
2)在“人事管理系统”数据库“员工信息”表中，将文化程度为“大专”，并且在“2005-05-01”到“2007-05-01”之间入职的所有员工调动到编号为“10006”的部门去。
update 员工信息 set 所在部门编号=10006
where 入职时间between '2005-05-01' and '2007-05-01' and 文化程度='大专'
3)在“人事管理系统”数据库中对部门进行了重组和调整，原来编号为10006的部门名称变为“市场开发部”，人数也调整为20人。
update 部门信息
set 部门名称='市场开发部',员工人数=20 where 部门编号=10006
3、删除数据
1)在“人事管理系统”数据库中，编号为“100509”的新员工升级为正式员工，需要在“新员工信息”表中删除他的记录。 delete from 新员工信息 where 员工编号=100509
2)在“人事管理系统”数据库中，删除“新员工信息”表中所有在编号为“10005”部门工作的员工记录。
delete from 新员工信息 where 所在部门编号=10005
3)在“人事管理系统”数据库中，删除“新员工信息”表中，所有的员工记录。
delete from 新员工信息
4)在“人事管理系统”数据库中，需要删除20%的员工信息。 Delete top 20 percent from 新员工信息 在“教务管理系统”中操作。 （一）添加教务信息
1、在2003年级中创建一个新班级，编号为20031340000200、班级名称为“环境与科学”、班级人数限制在60、暂定班主任为“张浩太”。编写INSERT语句完成这操作。
insert into 班级
values('20031340000200',2003,'环境与科学',60,'张浩太')
2、添加一个来自“上海”、“汉族”、姓名为“苏娜”的女生信息，并设置该生的学号为“200330000201”、班级编号为“20031340000200”。 编写INSERT语句完成这操作。
insert into 学生信息(学号,姓名,班级编号,民族,籍贯)
values('200330000201','苏娜','20031340000200','汉','上海')
3、创建一个学生补考表，表中数据来自成绩表，条件为成绩小于60分，要求补考表中仅显示学生的学号和补考课程的编号。编写SELECT INTO语句完成这操作。
select 学号,课程编号 into 补考表 from 成绩表 where 成绩<60
（二）更新教务信息
4、学生“胡少华”选择了新的专业，并调换到了编号为“20021340000104”的班级。编写UPDATE语句完成这操作。
update 学生信息
set 班级编号='20021340000104' where 姓名='胡少华'
5、调整课程“中外美术名作鉴赏”到班级“美术01”开设。
update 课程信息
set 班级编号= (select 班级编号 from 班级信息
where 班级名称='美术')
where 课程名称='中外美术名作鉴赏'
（三）删除教务信息
6、学生“胡少华”已经学完所有课程并顺利毕业，删除他的学生信息。编写DELETE语句完成这操作。
delete 成绩表 where 学号in
(select 学号from 学生信息where 姓名='胡少华')
delete 学生信息 where 姓名='胡少华'
7、将学生“陈霞”所在班级的其他学生并且成绩合格（大于60）的
学生成绩删除。编写DELETE语句完成这操作。
delete 成绩表 where 学号in (
select 学号from 学生信息where 班级编号= (select 班级编号 from 学生信息 where 姓名='陈霞') )
8、删除前面创建的学生补考表。 delete 补考表 四、实验内容
1、在“教务管理系统”数据库“班级信息”表中，查询分配了班主任的班级信息。
select * from 班级信息
where 班主任is not null
2、在“教务管理系统”数据库“学生信息”表中，根据籍贯查询各省学生人数，并显示省份和人数信息。
select 籍贯,count(*) as 人数 from 学生信息 group by 籍贯
3、在“教务管理系统”数据库中，根据相关表的内容，查询平均成绩大于70的课程信息。
select * from 课程信息 where 课程编号in (
select 课程编号 from 成绩表 group by 课程编号 having avg(成绩)>70 )
4在“人事管理系统”数据库“员工信息”表中，把政治面貌是“党员”的职工信息保存到表名为“党员信息”新表中。
select * into 党员信息 from 员工信息
where 政治面貌='党员'
5在“人事管理系统”数据库“员工信息”表中，把员工“李朋”所任职位更改为“副经理”。
update 员工信息 set 所任职位='副经理' where 员工姓名='李朋'
6、在“教务管理系统”数据库的“成绩”表中，按照学号分组，查询学生课程成绩都大于等于80的学号。
select 学号 from 成绩表 group by 学号 having min(成绩)>80
7、在“教务管理系统”数据库的 “成绩”表中，查询课程编号为’12’课程的总分。
select sum(成绩) as 总分 from 成绩表 where 课程编号='12' 8、从“教务管理系统”数据库中，查询人数超过45人所在班级中的学生的学号、姓名和班级名称。
select a.学号,a.姓名,b.班级名称 from 学生信息a,班级信息b
where a.班级编号= b.班级编号and b.人数>45
9、在“教务管理系统”数据库中，查询出所有非计算机系的学生信息，并显示出这些学生的考试成绩，再按成绩进行降序排列显示，要求返回学生的学号、姓名、班级编号、课程编号和成绩信息。（多表查询）
select a.学号,a.姓名,a.班级编号,c.课程编号,d.成绩
from 学生信息a,班级信息b,课程信息c,成绩表d
where a.班级编号= b.班级编号and b.班级编号= c.班级编号 and c.课程编号=d.课程编号
and b.班级名称not like '计算机%' order by d.成绩desc
10、在“教务管理系统”数据库中，查询出任意一个大于平均成绩的学生的学号、姓名、班级编号、课程编号和成绩信息
select a.学号,a.姓名,a.班级编号,b.课程编号,b.成绩 from 学生信息a ,成绩表b where a.学号=b.学号and
成绩>any(select avg(成绩) from 成绩表)
五、实验过程
写出相应的T-SQL语句，并写出T-SQL语句执行结果的前两行记录。 六、实验总结
简述实验完成情况、实验过程中各自碰到的问题、各自对实验目的掌握情况。
因篇幅问题不能全部显示，请点此查看更多更全内容