sql server实验报告.docxVIP

查询员工的姓名，职务和薪水select employeeName 姓名, headShip 职务, salary 薪水from Employee查询名字中含有“有限”的客户名称和所在地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 whenMthen男whenFthen女end性别from employeewhereaddresslike%上海%oraddresslike%南昌%and sex =F5.查询出职务为职员或职务为科长的女员工的信息select*from Employeewhere sex =Fand headShip in(职员,科长)6.选取编号不在间的客户编号，客户名称，客户地址select customerNo 客户编号, customerName 客户名称,address客户地址from customerwhere customerNo notbetweenndC200500047.在表orderMaster中挑选出销售金额大于等于5000元的订单update OrderMaster set orderSum = sum2from OrderMaster a,(select orderNo,sum(quantity*price) sum2from OrderDetailgroupby orderNo) bwhere a.orderNo = b.orderNoselect orderSumfrom OrderMasterwhere orderSum5000orderby orderSum8.在订单主表中选择订单金额最高的前百分之10的订单数据selecttop 10 percent*from OrderMasterorderby orderSum desc9.计算一共销售了几种商品selectcount(distinct ProductNo)销售的产品种类数from OrderDetail10.计算orderdetail中的每种商品的销售数量，平均销售单价和总销售金额，并且依据销售金额由大到小排序select quantity 数量,avg(price)平均单价,sum(quantity*price)总金额from OrderDetailgroupby quantityorderbysum(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 andyear(orderDate)=2008andmonth(orderDate)= 2groupby a.customerNo12.统计至少销售了10以上的商品和销售数量select a.productNo 商品编号, productName 商品名称,sum(quantity)from product a, OrderDetail bwhere a.productNo = b.productNogroupby a.productNo, productNamehavingsum(quantity)=1013.统计在业务科工作且在1973年或1967年出生的员工人数和平均工资selectcount(employeeNo)人数,avg(salary)平均工资from employeewhereyear(birthday)in(1973,1967)and department =业务科SQL Server 实验报告（二）指导老师：韦向远学生姓名：彭之群学生学号：3130835238查询同一天进入公司的员工select distinc