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

数据查询与操作实验一、实验目的与要求1、理解简单查询和复合查询的意义。2、掌握SELECT语句和各子句的使用。3、掌握多表查询和子查询的使用。4、掌握INSERT、UPDATE和DELETE语句的使用。二、实验平台1、操作系统：Windows XP或Windows 20032、数据库管理系统：SQL Server 2005三、实验内容1. 在“人事管理系统”中，新增一个员工信息（员工编号100508、员工姓名小龙女、所在部门编号10001、籍贯河南）。USE 人事管理系统GOinsert into 员工信息(籍贯,所在部门编号,员工姓名,员工编号)values(河北, 10001, 小龙女,100508 )GO2. 将“人事管理系统”数据库的“员工信息”表中籍贯为“河南”并且所在部门编号为”10001”数据插入到“新员工信息”表中。USE 人事管理系统GOinsert into 新员工信息(员工编号,员工姓名,所在部门编号)select 员工编号,员工姓名,所在部门编号from 员工信息where 所在部门编号=10001 and 籍贯=河南GO3. 在“人事管理系统”数据库“部门信息”表中，将部门的员工人数设置为10。USE 人事管理系统GOupdate 部门信息 set 员工人数=10GO4. 在“人事管理系统”数据库“员工信息”表中，将文化程度为“大专”，并且在“2005-05-01”到“2007-05-01”之间入职的所有员工调动到编号为“10006”的部门去。USE 人事管理系统GOupdate 员工信息set 所在部门编号=10006where 入职时间between 2005-05-01 and 2007-05-01 and 文化程度=大专GO5. 在“人事管理系统”数据库中对部门进行了重组和调整，原来编号为10006的部门名称变为“市场开发部”，人数也调整为20人。USE 人事管理系统GOupdate 部门信息set 部门名称=市场开发部,员工人数=20where 部门编号=10006GO6. 在“人事管理系统”数据库中，编号为“100503”的新员工升级为正式员工，需要在“新员工信息”表中删除他的记录。USE 人事管理系统GOdelete from 新员工信息 where 员工编号=100503GO7. 在“人事管理系统”数据库中，删除“新员工信息”表中所有在编号为“10005”部门工作的员工记录。USE 人事管理系统GOdelete from 新员工信息where 所在部门编号=10001GO8. 在“人事管理系统”数据库中，需要删除5%的员工信息。USE 人事管理系统GODelete TOP (5) percent from 员工信息GO9. GO在“教务管理系统”数据库“班级信息”表中，查询分配了班主任的班级信息。USE 教务管理系统GOselect * from 班级信息where 班主任GO10. 在“教务管理系统”数据库“学生信息”表中，根据籍贯查询各省学生人数，并显示省份和人数信息。USE 教务管理系统GOselect 籍贯,count(*) AS 学生人数from 学生信息Group by 籍贯GO11. 在“教务管理系统”数据库中，根据相关表的内容，查询平均成绩大于70的课程信息。USE 教务管理系统GOselect * from 课程信息where 课程编号in (select 课程编号from 成绩表group by 课程编号having avg(成绩)70)GO12. 在commidtysell数据库中，根据相关表的内容，查询销售同类产品的商场编号（去除相同的行）。USE commoditysellGOselect DISTINCT EmpIDfrom EmporiumSellGO13. 在commidtysell数据库中，根据相关表的内容，查询买出产品数量最多的商场编号和销售总量信息。USE commoditysellGOselect top 1 EmpID as 商场编号,sum(SellAmout) as 销售数量from EmporiumSellgroup by EmpIDorder by 销售数量descGO14. 在TeachingManageSYS数据库中，根据相关表的内容，统计年龄大于平均年龄的教师的Tname、Tsex、Tage、Trank，所教授课程的Cname，Credit，CouresHour。USE TeachingManageSYSGOselect a.Tname,a.Tsex,a.Tage,c.Cname,c.Credit,c.CouresHour from TeacherInfo a,Teach b,CourseInfo cwhere a.Tno=b.Tno and b.Cno=c.Cno and a.Tageany(select avg(Tage) from TeacherInfo)GO15. 在“人事管理系统”数据库“员工信息”表中，查询工龄大于4年的员工信息USE 人事管理系统GOselect * from 员工信息where year(getdate()-year(入职时间)4GO16. 将学生“陈霞”所在班级的其他学生并且成绩不合格的学生成绩删除。USE 教务管理系统GOdelete 成绩表where 成绩45)GO22. 查询“学生信息”表中姓名不包含“红”字的学生的学号、姓名、性别、民族、籍贯和班级编号信息。USE 教务管理系统GOselect 学号,姓名,性别,民族,籍贯,班级编号from 学生信息where 学号not in(select 学号from 学生信息where 姓名like %红%)GO23. 在“教务管理系统”数据库中，查询还未分配班主任的班级信息。USE 教务管理系统GOselect * from 班级信息where 班主任=GO24. 在“人事管理系统”数据库“员工信息”表中，将文化程度为“大专”，并且在“2005-05-01”到“2007-05-01”之间入职的所有员工调动到编号为“10006”的部门去。update 员工信息set 所在部门编号=10006where 入职时间between 2005-05-01 and 2007-05-01 and 文化程度=大专25. 查询数据表ProductInfo中前5条记录。USE commoditysellGOSELECT TOP 5 *FROM ProductInfoGO26. 从ProductInfo表中，查询产品名称为“丙电视机”的产品信息。USE commoditysellGOSELECT *FROM ProductInfoWHERE ProdName=丙电视机GO27. 从ProductInfo表中，查询FactAddr为“四川绵阳”或“广东东莞”，同时StockAmount大于110的产品的ProdID、StockAmount和FactAddr信息，并且给列名分别取别名为产品编号、库存数量和厂商地址。USE commoditysellGOSELECT ProdID AS 产品编号,StockAmount AS 库存数量,FactAddr AS 厂商地址FROM ProductInfoWHERE FactAddr in(四川绵阳,广东东莞) and StockAmount110GO28. 从ProductInfo表中，查询StockAmount在200和400(包含200和400)之间的产品的ProdName和StockAmount。USE commoditysellGOSELECT ProdName,StockAmountFROM ProductInfoWHERE StockAmount between 200 and 400GO29. 创建一个学生补考表，表中数据来自成绩表，条件为成绩小于60分，要求补考表中仅显示学生的学号和补考课程的编号。编写SELECT INTO语句完成这操作。USE 教务管理系统GOselect 学号,课程编号into 补考表from 成绩表where 成绩10GO33. 在“教务管理系统”数据库中，查询出任意一个大于平均成绩的学生的学号、姓名、班级编号、课程编号和成绩信息。USE 教务管理系统GOselect a.学号,a.姓名,a.班级编号,b.课程编号,b.成绩from 学生信息a inner join 成绩表bon a.学号=b.学号where 成绩any(select avg(成绩) from 成绩表)GO34. 在“教务管理系统”数据库中，对于成绩大于等于90的数据行进行查询，并返回考试成绩的学生的学号、姓名、性别、籍贯和所在班级名称及年级信息。USE 教务管理系统GOselect a.学号,a.姓名,a.性别,a.籍贯,b.班级名称,b.年级 from 学生信息 a inner join 班级信息 bon a.班级编号=b.班级编号where exists(select * from 成绩表where 学号=a.学号and 成绩=90)GO35. 在“人事管理系统”数据库的“员工信息”表中，查询“所任职位”为“经理”的员工编号和员工姓名信息，并为其增加新列“所在位置”，列的内容为“员工信息表”；从“部门信息”表中查询所有的部门编号和部门名称信息，并定义新增列的内容为“部门信息表”；最后将两个查询结果联合在一起。USE 人事管理系统GOselect 员工编号,员工姓名,员工信息表 as 所在位置 from 员工信息where 所任职位=经理unionselect 部门编号,部门名称,部门信息表 from 部门信息GO36. 在“教务管理系统”数据库的“学生信息”表和“成绩”表中，查询出所有考试及格的学生的成绩信息，包括学生的学号、姓名、性别、年级、班级编号及考试成绩，并且按照成绩进行降序排列。USE 教务管理系统GOselect S.学号,S.姓名,S.性别, S.年级,S.班级编号,J.成绩from 学生信息S inner join 成绩表J on S.学号=J.学号where J.成绩=60order by J.成绩 descGO37. 从“教务管理系统”数据库中的“学生信息”和“班级信息”两表中，查询学生和对应的班级信息，要求返回的结果中包含学生的学号、姓名、性别和班级名称及年级。USE 教务管理系统GOselect S.学号,S.姓名,S.性别, C.班级名称,C.年级 from 学生信息 S inner join 班级信息 C on S.班级编号=C.班级编号GO38. 在“教务管理系统”数据库的“学生信息”表和“成绩”表中，查询所有学生的考试成绩信息，包括学生学号、姓名、课程编号和成绩信息。USE 教务管理系统GOselect a.学号,a.姓名,b.课程编号,b.成绩from 学生信息a left outer join 成绩表b on a.学号= b.学号GOUSE 教务管理系统GOselect a.学号,a.姓名,b.课程编号,b.成绩from 学生信息 a right outer join 成绩表 b on a.学号= b.学号GOUSE 教务管理系统GOselect a.班级名称,a.人数,a.班主任,b.课程名称,b.开课系别from 班级信息a full outer join 课程信息b on a.班级编号=b.班级编号GO39. 在“人事管理系统”数据库中，对“员工信息”表和“部门信息”表进行交叉查询，要求查询返回所任职位为“职员”的