sql server实验报告

1.查询员工的姓名，职务和薪水

select employeeName 姓名, headShip 职务, salary 薪水

from Employee

2.查询名字中含有“有限”的客户名称和所在地

select customerName 客户名称,address所在地from Customer

where customerName like'%有限%'

3.查询出姓“张”并且姓名最后一个字为梅的员工

select*

from employee

where 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 employee

where address like'%上海%'or address like'%南昌%'and sex ='F'

5.查询出职务为职员或职务为科长的女员工的信息

select*

from Employee

where sex ='F'and headShip in('职员','科长')

6.选取编号不在c20050001-c20050004之间的客户编号，客户名称，客户地址

select customerNo 客户编号, customerName 客户名称, address客户地址

from customer

where customerNo not between'C20050001'and

'C20050004'

7.在表orderMaster中挑选出销售金额大于等于5000元的订单

update OrderMaster set orderSum = sum2

from OrderMaster a,(

select orderNo,sum(quantity*price) sum2

from OrderDetail

group by orderNo) b

where a.orderNo = b.orderNo

select orderSum

from OrderMaster

where orderSum>5000

order by orderSum

8.在订单主表中选择订单金额最高的前百分之10的订单数据

select top 10 percent*

from OrderMaster

order by orderSum desc

9.计算一共销售了几种商品

select count(distinct ProductNo)销售的产品种类数from OrderDetail

10.计算orderdetail中的每种商品的销售数量，平均销售单价和总销售金额，并且依据销售金额由大到小排序

select quantity 数量,avg(price)平均单价,

sum(quantity*price)总金额

from OrderDetail

group by quantity

order by sum(quantity*price)desc

11．按客户编号统计每个客户2008年2月的订单总金额select a.customerNo 客户编号,sum(quantity*price)总金额

from customer a, OrderDetail b, OrderMaster c

where a.customerNo = c.customerNo and b.orderNo =

c.orderNo and year(orderDate)='2008'and

month(orderDate)= 2

group by a.customerNo

12.统计至少销售了10以上的商品和销售数量

select a.productNo 商品编号, productName 商品名称, sum(quantity)

from product a, OrderDetail b

where a.productNo = b.productNo

group by a.productNo, productName

having sum(quantity)>=10

13.统计在业务科工作且在1973年或1967年出生的员工人数和平均工资

select count(employeeNo)人数,avg(salary)平均工资from employee

where year(birthday)in('1973','1967')and department ='业务科'

SQL Server 实验报告（二）

指导老师：韦向远

学生姓名：彭之群

学生学号：3130835238

1.查询同一天进入公司的员工

select distinct a.employeeNo, a.employeeName,

a.hireDate

from employee a, employee b

where a.employeeNo != b.employeeNo and a.hireDate = b.hireDate