《SQLServer数据库应用与维护》-实验5-SQL数据管理-答案

温州职业技术学院实验报告实验课程SQLServer数据库应用与维护实验项目实验五：SQLServer数据管理实验地点明德楼607指导教师杜益虹班级信息1601学生姓名张楠学号16002080138教师评分日期2017-10-17

实验五SQLServer数据管理一、实验目的掌握用SQL对数据库进行简单查询和多表数据查询；进一步理解连接查询和嵌套查询的实际意义；进一步熟悉数据更新语句的使用。二、实验内容第一题【插入表数据】对产品销售数据库company增加如下数据：1、员工：insert员工values('E0001','王华','男','采购部','1976-10-13','1951-08-01',8000,'0218120440','上海市')insert员工values('E0002','陈强','男','财务部','1986-09-15','1963-06-09',4800,'0255344441','南京市')insert员工values('E0003','周梅','女','销售部','1996-03-01','1970-03-28',3200,'0218128079','上海市')insert员工values('E0004','陈建','男','办公室','1987-04-15','1967-09-01',4500,'0224507863','天津市')insert员工values('E0005','林光','男','销售部','1995-10-13','1973-08-17',3000,'0218344560','上海市')insert员工values('E0006','李珠','女','办公室','1988-01-01','1961-07-12',6000,'0106750321','北京市')insert员工values('E0007','李敬','男','人事部','1980-09-15','1958-05-13',8000,'0207180787','广州市')insert员工values('E0008','王成','男','信息中心','1993-02-15','1969-04-15',4500,'0106543475','北京市')insert员工values('E0009','陈华','男','销售部','1993-02-15','1966-07-01',4300,'0224506541','天津市')insert员工values('E0010','刘小刚','男','销售部','1994-11-01','1968-08-01',4000,'0218128727','上海市')insert员工values('E0011','李珊','女','财务部','1990-03-20','1967-04-25',3800,'0218344787','上海市')insert员工values('E0012','李蓉','女','人事部','1994-11-01','1970-11-18',3000,'0811545412','重庆市')insert员工values('E0013','蔡文','男','制造部','1984-08-15','1960-07-21',5000,'0218120636','上海市')insert员工values('E0014','张山','男','制造部','1993-12-15','1968-09-23',3500,'0218344472','上海市')insert员工values('E0015','吴雄','男','信息中心','1989-10-01','1965-04-18',5000,'0277758521','武汉市')insert员工values('E0016','方美','女','采购部','1992-05-20','1966-06-23',4000,'0218507470','上海市')insert员工values('E0017','刘兴','男','制造部','1984-05-01','1960-05-23',6000,'0218506110','上海市')insert员工values('E0018','王华','男','信息中心','1985-09-15','1959-03-24',6000,'0218128091','上海市')insert员工values('E0019','陈旺','男','销售部','1992-08-01','1964-05-12',4000,'0224120477','天津市')insert员工values('E0020','林正','男','设计部','1974-10-01','1953-05-04',10000,'0218120564','上海市')2、客户表insert客户values('C0001','蓝海股份有限公司','上海市','电话甲','100')insert客户values('C0002','绿色原野股份有限公司','天津市','电话乙','400')insert客户values('C0003','华兴股份有限公司','北京市','电话丙','800')insert客户values('C0004','金色阳光股份有限公司','上海市','电话丁','104')insert客户values('C0005','北京保利股份有限公司','北京市','电话戊','803')insert客户values('C0006','上海华为科技有限公司','上海市','电话己','103')insert客户values('C0007','上海东方有限公司','上海市','电话庚','102')3、销售主表insert销售主表values(10001,'C0002','E0013',22700.00,'1996-11-10','I000000002')insert销售主表values(10002,'C0003','E0014',13960.00,'1996-10-15','I000000003')insert销售主表values(10003,'C0003','E0014',33000.00,'1996-11-10','I000000004')insert销售主表values(10004,'C0001','E0008',60000.00,'1996-11-10','I000000001')insert销售主表values(10005,'C0007','E0008',20000.00,'1996-11-10','I000000008')4、产品名称表insert产品名称表values('P0001','内存',2600)insert产品名称表values('P0002','显示器',6500)insert产品名称表values('P0003','硬盘',5300)insert产品名称表values('P0004','光驱',1600)insert产品名称表values('P0005','键盘',500)insert产品名称表values('P0006','显卡',1000)insert产品名称表values('P0007','网卡',800)insert产品名称表values('P0008','CPU',4800)insert产品名称表values('P0009','打印机',1200)insert产品名称表values('P0010','刻录机',2000)insert产品名称表values('P0011','电子字典',600)insert产品名称表values('P0012','路由器',400)insert产品名称表values('P0013','主板',1000)5、销货明细表insert销售明细表values(10001,'P0001',5,2500.00,'2016-10-22')insert销售明细表values(10001,'P0003',3,6500.00,'2016-10-22')insert销售明细表values(10001,'P0002',2,5300.00,'2016-10-22')insert销售明细表values(10001,'P0004',2,1600.00,'2016-10-22')insert销售明细表values(10002,'P0001',3,2600.00,'2016-11-10')insert销售明细表values(10002,'P0003',1,5300.00,'2016-11-10')insert销售明细表values(10002,'P0008',2,4800.00,'2016-11-10')insert销售明细表values(10003,'P0001',4,2700.00,'2016-10-15')insert销售明细表values(10003,'P0004',2,1580.00,'2016-10-15')insert销售明细表values(10004,'P0008',4,2700.00,'2016-10-18')insert销售明细表values(10004,'P0004',2,1580.00,'2016-10-18')insert销售明细表values(10005,'P0010',4,2700.00,'2016-10-19')insert销售明细表values(10006,'P0007',2,1580.00,'2016-10-05')insert销售明细表values(10007,'P0006',4,2700.00,'2016-10-25')insert销售明细表values(10007,'P0013',2,1580.00,'2016-10-25')第二题【单表查询】请用SQL语句完成下面的题目查询所有住址中含有“京”的男员工的姓名、电话、住址。select员工姓名,电话,住址from员工where住址like'%京%'and性别='男'查询销售部每个员工姓名和在本公司的工龄。.select员工姓名,datediff(year,雇佣日,CONVERT(varchar(100),GETDATE(),20))as工龄from员工where所属部门='销售部'查询所有在销售部或人事部的所有员工的信息。select*from员工where所属部门='销售部'or所属部门='人事部'查询薪水在2000—3000之间的销售部员工的信息。select*from员工where薪水between2000and3000统计各部门薪水在4000以上的员工的人数。select所属部门,count(*)as 人数from员工where薪水>4000groupby所属部门统计平均薪水大于5000的部门及平均薪水，并按薪水从高到低排序。select所属部门,AVG(薪水)as平均薪水from员工groupby所属部门havingAVG(薪水)>5000对员工表选择姓名、部门，只返回结果集的前5行。selecttop5员工姓名,所属部门from员工8、查询所有不姓“张”，不姓“李”，也不姓“王”的员工信息select*from员工where员工姓名like'[^张李王]%'第三题【连接查询】请用SQL语句完成下面的题目查询各销售订单的信息，包括客户号、客户名称、订单编号、订单金额、订货日期Select客户.客户,客户.客户名称,销售主表.订单编号,销售主表.订单金额,销售主表.订货日期from客户innerjoin销售主表on客户.客户=销售主表.客户号 查询销售硬盘的客户名称selectdistinct客户名称from客户innerjoin产品名称表on产品名称='硬盘'innerjoin销售明细表on销售明细表.产品编号=产品名称表.产品编号innerjoin销售主表on销售主表.订单编号=销售明细表.订单编号where客户.客户=销售主表.客户号查询蓝海股份有限公司的销售明细，包括订单编号、客户名称，业务员姓名、产品名称、销售数量、单价、发票号码selectdistinct销售主表.订单编号,客户名称,员工姓名,产品名称,销售数量,单价,发票号码from销售主表leftouterjoin客户on客户.客户=销售主表.客户号leftouterjoin员工on员工.员工编号=销售主表.业务员编号leftouterjoin销售明细表on销售明细表.订单编号=销售主表.订单编号leftouterjoin产品名称表on销售明细表.产品编号=产品名称表.产品名称where客户名称='蓝海股份有限公司'查询所有客户的销售信息，包括客户编号、客户名称、订单编号、订单金额、订货日期select销售主表.客户号,客户名称,订单编号,订单金额,订货日期from销售主表rightouterjoin客户on客户.客户=销售主表.客户号第四题【嵌套查询】请用SQL语句完成下面的题目查询销售硬盘的客户名称select客户名称from客户where客户in( select客户号 from销售主表 where订单编号in( select订单编号 from销售明细表 where产品编号=( select产品编号 from产品名称表 where产品名称='硬盘' ) ))查询没有销售硬盘的客户名称select客户名称from客户where客户notin( select客户号 from销售主表 where订单编号in( select订单编号 from销售明细表 where产品编号=( select产品编号 from产品名称表 where产品名称='硬盘' ) ))哪个部门的员工人数最多？请找出该部门的部门名称。selecttop1所属部门from员工groupby所属部门orderbycount(员工编号)desc查询销售部薪水最高的员工的姓名和薪水。selecttop1员工姓名,薪水from员工where所属部门='销售部'orderby薪水desc第五题【数据更新】请用SQL语句完成下面的题目给销售部每个员工增加薪水10%update员工set薪水=