1.查询员工的姓名，职务和薪水select employeeName 姓名, headShip 职务, salary 薪水from Employee2.查询名字中含有“有限”的客户名称和所在地select customerName 客户名称,address所在地from Customerwhere customerName like'%有限%'3.查询出姓“张”并且姓名最后一个字为梅的员工select*from employeewhere employeeName like'张_梅'4.查询住址中含有上海或者南昌的女员工，并显示其姓名，所属部门，职务，住址，出生日期和性别，如果出生日期为空，则显示为不详，否则按格式‘yyyy-mm-dd’显示，性别用男和女显示select employeeName 姓名, department 部门, headship 职务,address住址,isnull(convert(char(10), birthday, 120),'不详')出生日期, case sex when'M'then'男'when'F'then'女'end性别from employeewhere address like'%上海%'or address like'%南昌%'and sex ='F'5.查询出职务为职员或职务为科长的女员工的信息select*from Employeewhere sex ='F'and headShip in('职员','科长')6.选取编号不在c20050001-c20050004之间的客户编号，客户名称，客户地址select customerNo 客户编号, customerName 客户名称, address客户地址from customerwhere customerNo not between'C20050001'and'C20050004'7.在表orderMaster中挑选出销售金额大于等于5000元的订单update OrderMaster set orderSum = sum2from OrderMaster a,(select orderNo,sum(quantity*price) sum2from OrderDetailgroup by orderNo) bwhere a.orderNo = b.orderNoselect orderSumfrom OrderMasterwhere orderSum>5000order by orderSum8.在订单主表中选择订单金额最高的前百分之10的订单数据select top 10 percent*from OrderMasterorder by orderSum desc9.计算一共销售了几种商品select count(distinct ProductNo)销售的产品种类数from OrderDetail10.计算orderdetail中的每种商品的销售数量，平均销售单价和总销售金额，并且依据销售金额由大到小排序select quantity 数量,avg(price)平均单价,sum(quantity*price)总金额from OrderDetailgroup by quantityorder by sum(quantity*price)desc11．按客户编号统计每个客户2008年2月的订单总金额select a.customerNo 客户编号,sum(quantity*price)总金额from customer a, OrderDetail b, OrderMaster cwhere a.customerNo = c.customerNo and b.orderNo =c.orderNo and year(orderDate)='2008'andmonth(orderDate)= 2group by a.customerNo12.统计至少销售了10以上的商品和销售数量select a.productNo 商品编号, productName 商品名称, sum(quantity)from product a, OrderDetail bwhere a.productNo = b.productNogroup by a.productNo, productNamehaving sum(quantity)>=1013.统计在业务科工作且在1973年或1967年出生的员工人数和平均工资select count(employeeNo)人数,avg(salary)平均工资from employeewhere year(birthday)in('1973','1967')and department ='业务科'SQL Server 实验报告（二）指导老师：韦向远学生姓名：彭之群学生学号：31308352381.查询同一天进入公司的员工select distinct a.employeeNo, a.employeeName,a.hireDatefrom employee a, employee bwhere a.employeeNo != b.employeeNo and a.hireDate = b.hireDate2.查询与“陈诗杰”在同一个工作单位的员工姓名，性别，部门和职务select a.employeeName 员工姓名,case a.sex when 'M' then '男'when 'F' then '女'end 性别, a.department 部门, a.headship 职务from employee a, employee bwhere a.department = b.department andb.employeeName = '陈诗杰'3.在employee表中查询薪水超过员工平均薪水的员工信息select *from Employeewhere salary>(select avg(salary)from Employee)4.查找有销售记录的客户编号、名称和订单总额select a.CustomerNo, CustomerName,sum(quantity*price)from Customer a, OrderDetail b, OrderMaster c where a.customerNo = c.customerNo and b.orderNo = c.orderNogroup by a.CustomerNo, CustomerName5.查询没有订购商品的客户编号和客户名称select CustomerNo, CustomerNamefrom Customerwhere customerNo not in(select customerNofrom OrderMaster)6.使用子查询查找32M DRAM 的销售情况，要求显示相应销售人员的姓名、性别、销售日期、销售数量、金额，其中性别用男和女表示end, orderDate, quantity,sum(quantity*price)from employee a, OrderDetail b, OrderMaster cwhere a.employeeNo = c.salerNo and c.orderNo =b.orderNo and productNo =(select productNofrom Productwhere productName ='32M DRAM')group by employeeName, sex, orderDate, quantity7.查询orderMaster表中订单金额最高的订单号及订单金额select orderNo, orderSumfrom OrderMasterwhere orderSum =(select max(orderSum)from OrderMaster)8.在订单主表中查询订单金额大于“E2005002业务员在2008-1-9这天所接的任一订单的金额”的所有订单信息select*from OrderMasterwhere orderSum>any(select orderSumfrom OrderMasterwhere orderDate ='2008-1-9'and salerNo ='E2005002')9.查询单价高于400元的商品编号，商品名称，订货数量和订货单价select a.ProductNo, ProductName, quantity, pricefrom Product a, OrderDetail bwhere a.productNo = b.productNo and price>40010.分别使用左外连接，右外连接，完整外部链接查询单价高于400元的商品编号，商品名称，订货数量和订货单价，并分析比较检索的结果左连接查询：select a.ProductNo, ProductName, quantity, pricefrom Product a left outer join OrderDetail bon a.productNo = b.productNo and price>400右连接查询：select a.ProductNo, ProductName, quantity, price from Product a right outer join OrderDetail bon a.productNo = b.productNo and price>400完整外部连接：select a.ProductNo, ProductName, quantity, price from Product a full outer join OrderDetail bon a.productNo = b.productNo and price>40011.使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额，其中订货日期不要显示时间，日期格式为“yyyy-mm-dd，按客户编号排序，同一客户再按订单金额降序排序输出select a.CustomerNo, CustomerName,isnull(convert(char(10),orderDate,120),'不详'), orderSum from Customer a left outer join OrderMaster bon a.customerNo = b.customerNoorder by a.customerNo, orderSum desc12. 查找每个员工的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期，其中性别使用“男”和“女”表示，日期使用“yyyy-mm-dd”格式显示。