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

2.查询与“陈诗杰”在同一个工作单位的员工姓名，性别，部门和职务

select a.employeeName 员工姓名,

case a.sex when 'M' then '男'

when 'F' then '女'

end 性别, a.department 部门, a.headship 职务

from employee a, employee b

where a.department = b.department and

b.employeeName = '陈诗杰'

3.在employee表中查询薪水超过员工平均薪水的员工信息

select *

from Employee

where 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.orderNo

group by a.CustomerNo, CustomerName

5.查询没有订购商品的客户编号和客户名称select CustomerNo, CustomerName

from Customer

where customerNo not in(

select customerNo

from OrderMaster)

6.使用子查询查找32M DRAM 的销售情况，要求显示相应销售人员的姓名、性别、销售日期、销售数量、金额，其中性别用男和女表示

end, orderDate, quantity,sum(quantity*price)

from employee a, OrderDetail b, OrderMaster c

where a.employeeNo = c.salerNo and c.orderNo =

b.orderNo and productNo =(

select productNo

from Product

where productName ='32M DRAM')

group by employeeName, sex, orderDate, quantity