数据库系统原理与设计(第二版)实验一至实验三

实验一

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

select employeeName,headShip,salary

from employee

图1-1 2.查询名字中含有“有限”的客户姓名和所在地

select CustomerName,address

from Customer

where CustomerName like '%有限%'

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

select *

from employee

where employeeName like '张%梅'

图1-3

4. 查询住址中含有上海或南昌的女员工，并显示其姓名、所属部门、职称、住址，其中性别用“男”和“女”显示

SELECT employeeName,department,address,

isnull (convert(char(10),birthday,120),'不详')出生日期,

case sex when 'M'then '男'

when 'F'then'女'

end as 性别

from employee

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

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

select *

from employee

where (headship='职员' or headship='科长') and sex='F'

图1-5

6. 选取编号不在“C20050001”和“C20050004”的客户编号、客户名称、客户地址。

Select *

from Customer

where CustomerNo not in ( 'C20050001' ,'C20050004')

图1-6

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 *

From ordermaster

Where ordersum>=’5000’

图1-7

8. 选取订单金额最高的前10%的订单数据

SELECT TOP 10 PERCENT *

from orderdetail order by price DESC

图1-8

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

SELECT COUNT(DISTINCT productno)as 种类

from orderDeta

图1-9

10.计算orderDetail表中每种商品的销售数量、平均价格和总销售量金额，并且依据销售金额由大到小输出。

SELECT productno 商品种类,count(*)quantity,avg (price)平均价格,sum(quantity*price)金额

from orderDetail

group by productno

order by 金额desc

图1-10

11.按客户编号统计每个客户2008年2月的订单总金额。

select customerno,ordersum

from ordermaster

where year(orderDate)=2008 and month(orderDate)=2

图1-11

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

select productno 商品编号,quantity 商品数目

from orderdetail

where quantity>=10

图1-12

13. 统计在业务科工作且在1973年或1967年出生的员工人数和平均工资select count(*) 人数,avg(salary) 平均工资

from Employee

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

图1-13

实验二

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-1

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

select a.employeeName,a.sex,a.department,a.headShip

from Employee a,Employee b

where a.department=b.department and b.employeeName='陈诗杰'

图2-2

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

select *

from Employee a

where a.salary>(select avg(b.salary) from Employee b)

图2-3

4.

select a.customerNo,a.customerName,b.orderNo,sum(quantity*price) orderSum from Customer a,OrderMaster b,OrderDetail c

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

group by a.customerNo,a.customerName,b.orderNo

图2-4

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

SELECT a.customerNo,customerName

FROM Customer a

WHERE a.customerNo NOT IN(SELECT customerNo FROM OrderMaster )

图2-5

6.使用子查询查找32M DRAM

select employeeName,case sex

when'M'then'男'

when'F'then'女'

end as sex, b.orderDate,c.quantity 销售数量,c.quantity*c.price 金额from Employee a,OrderMaster b,OrderDetail c

where a.employeeNo=b.salerNo and b.orderNo=c.orderNo and c.productNo in(select f.productNo

from OrderMaster d,OrderDetail e,Product f

where d.orderNo=e.orderNo and productName='32M DRAM')

图2-6

7.查询OrderMaster表中订单金额最高的订单号及订单金额select orderNo,sum(quantity*price) orderSum

from OrderDetail

group by orderNo

having sum(quantity*price)=(select max(orderSum)

from (select orderNo,sum(quantity*price) orderSum

from OrderDetail

group by orderNo)b)

图2-7

8.在订单主表中查询订单金额大于“E2005002业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。

select *

from OrderMaster

where orderSum>any(select orderSum from OrderMaster where salerNo='E2005002' and orderDate='2008-1-9')

图2-8

9.查询单价高于400

select a.productNo,a.productName,sum(b.quantity)订货数量,b.price

from Product a,OrderDetail b

where a.productPrice>400 and b.productNo=a.productNo

group by a.productNo,a.productName,b.price

图2-9

10.分别使用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价并分析比较检索的结果。

select a.productNo,a.productName,sum(b.quantity)订货数量,b.price

from Product a left outer join OrderDetail b on a.productPrice>400 and

b.productNo=a.productNo

group by a.productNo,a.productName,b.price

select a.productNo,a.productName,sum(b.quantity)订货数量,b.price

from Product a right outer join OrderDetail b on a.productPrice>400 and

b.productNo=a.productNo

group by a.productNo,a.productName,b.price

select a.productNo,a.productName,sum(b.quantity)订货数量,b.price

from Product a full outer join OrderDetail b on a.productPrice>400 and

b.productNo=a.productNo

group by a.productNo,a.productName,b.price

图2-10

11.使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额、其中订货日期不显示时间日期格式为yyyy-mm-dd

select a.customerno 客户编号,customername 客户名

称,convert(char(10),orderdate,120)销售日期,ordersum 销售金额

from ordermaster a left outer join customer b on

(a.customerno=b.customerno)

order by a.customerno,ordersum desc

12.

yyyy-mm-dd格式显示。

select a.employeeNo,a.employeeName,case sex

when'F'then'女'

when'M'then'男'

End sex,b.productName,d.quantity,d.price,d.quantity*d.price 金

额,orderDate=convert(char(10),orderDate,120)

from Employee a,Product b,OrderMaster c,OrderDetail d

where a.employeeNo=c.salerNo and b.productNo=d.productNo and

c.orderNo=

d.orderNo

图2-12

13.查询16M DRAM的销售情况要求显示相应的销售员的姓名、性别、销售日期、销售数量和金额、

select a.employeeName,case sex when'F'then'女'

when'M'then'男'

end as sex,b.orderDate,c.quantity,c.price*c.quantity 金额

from Employee a,OrderMaster b,OrderDetail c,Product d

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

c.productNo=

d.productNo and

d.productName='16M DRAM'

图2-13

14.找出公司男业务员所接且订单金额超过2000的订单号及订单金额。

select b.orderNo,b.orderSum

from Employee a,OrderMaster b

where a.employeeNo=b.salerNo and sex='M'and b.orderSum>2000

图2-14

15.

总金额，并按商品号从小到大排列。

select a.productno 商品编号,productname 商品称,sum(quantity)总销售数量,sum(quantity*price)总销售金额

from product a,orderdetail b

where a.productno=b.productno

group by a.productno,productname order by a.productno

图2-15

实验三

1.在订单明细表中查询订单金额最高的订单。

select top 1 orderNo,sum(quantity*price) orderSum

from OrderDetail

group by orderNo

order by orderSum desc

图3-1

3. 查找销售总额少于5000元的销售员编号、姓名和销售额。

select a.employeeNo,a.employeeName,sum(quantity*price) sunmoney from Employee a,OrderDetail b,OrderMaster c

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

group by a.employeeNo,a.employeeName

having sum(quantity*price)<5000

图3-3

5.查询订单中所订购的商品数量没有超过个的客户编号和客户名称。

SELECT a.CustomerNo,CustomerName

FROM Customer a

WHERE a.CustomerNo IN (

SELECT CustomerNo

FROM OrderMaster b,OrderDetail c

WHERE b.orderNo=c.orderNo

GROUP BY CustomerNo

HAVING sum(quantity)<10)

图3-5

7. 查找至少销售了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额。

SELECT a.CustomerNo,CustomerName,b.ProductNo,

ProductName,quantity,sum(quantity*price) sum

FROM Customer a,Product b,OrderMaster c,OrderDetail d

WHERE a.CustomerNo=c.CustomerNo and c.orderNo=d.orderNo

and b.ProductNo=d.ProductNo and

EXISTS (

SELECT CustomerNo

FROM OrderMaster e,OrderDetail f

WHERE e.orderNo=f.orderNo and a.customerNo=e.customerNo

GROUP BY CustomerNo

HAVING count(distinct ProductNo)>=3)

GROUP BY a.CustomerNo,CustomerName,b.ProductNo,

ProductName,quantity

ORDER BY a.CustomerNo,sum DESC

图3-7

9. 求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。

SELECT customerNo,productNo,sum(quantity) quantitys,

(sum(quantity*price)/sum(quantity)) avgprice

FROM OrderMaster a,OrderDetail b

WHERE a.orderNo=b.orderNo

GROUP BY customerNo,productNo

ORDER BY customerNo,productNo

图3-9