数据库系统原理与设计实验教程实验1

数据库系统原理与设计实验教程实验1

实验一简单查询

在订单数据库中，完成如下的查询：

(1) 查询所有业务部门的员工姓名、职称、薪水。

select employeeName,headShip,salary

from employee

where department='业务科'

(2) 查询名字中含有“有限”的客户姓名和所在地。

select CustomerName,address

from Customer

where CustomerName like '%有限%'

(3) 查询出姓“王”并且姓名的最后一个字

为“成”的员工。

select *

from employee

where employeeName like '王%成'

没有结果

(4) 查询住址中含有上海或南昌的女员工，

并显示其姓名、所属部门、职称、住址，其

中性别用“男”和“女”显示。

select

employeeName,department,headship,address,

sex= Case sex

when 'M'then '男'

when 'F'then '女'

end

from employee

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

(5) 在订单明细表OrderDetail中挑出销售金额大于等于10000元的订单。

select orderNo

from OrderDetail

group by orderNo

having sum(quantity*price)>=10000

(6) 选取订单金额最高的前10%的订单数据。

SELECT TOP 10 PERCENT orderNo

FROM Orderdetail

GROUP BY orderNo

ORDER BY sum(quantity*price) DESC

(7) 查询出职务为“职员”或职务为“科长”的女员工的信息。

select *

from employee

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

(8) 查找定单金额高于8000的所有客户编号。

1）查询总金额高于8000元的客户编号

select CustomerNo

from OrderMaster a,Orderdetail b

where a.orderNo=b.orderNo

group by CustomerNo

having sum(quantity*price)>8000

2）查找定单金额高于8000的所有客户编号

select CustomerNo

from OrderMaster

where orderNo in ( select orderNo

from OrderDetail

group by orderNo

having

sum(quantity*price)>8000 )

(9) 选取编号界于“C20050001”和

“C20050004”的客户编号、客户名称、客户地址。

select CustomerNo,CustomerName,address

from Customer

where CustomerNo between 'C20050001' and 'C20050004'

(11) 找出同一天进入公司服务的员工。

Select

a.employeeNo,a.employeeName,

b.employee

No,b.employeeName

from Employee a,Employee as b

where a.employeeNo!=b.employeeNo and

a.employeeName>

b.e

mployeeName

and (a.hireDate=b.hireDate)

(12) 在订单主表中查询订单金额大于“E2005002业务员在2008-1-9这天所接的任一

张订单的金额”的所有订单信息。

1）首先计算订单主表的订单金额

update OrderMaster set orderSum=totalSum

from OrderMaster a,(select orderNo,sum(quantity*price) totalSum

from OrderDetail

group by orderNo) b

where a.orderNO=b.orderNo

2）

SELECT *

FROM OrderMaster

WHERE orderSum>any

(SELECT orderSum

FROM OrderMaster

WHERE salerNo='E2005002' AND orderDate='20080109' )

(13) 查询既订购了“52倍速光驱”商品，又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。

Select customerNo,orderNo,orderSum

from OrderMaster

where customerNo in

(select customerNo

from OrderMaster a,OrderDetail b,Product c

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

productName='52倍速光驱') and customerNo in(select customerNo

from OrderMaster a,OrderDetail b,Product c

where

a.orderNo=

b.orderNo and

b.productNo=

c.productNo and

productName='17寸显示器')

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

Select

b.employeeName,b.sex,b.department,b.headS hip

from Employee a,Employee b

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

(15) 查询每种商品的商品编号、商品名称、订货数量和订货单价。

SELECT

a.productNo,productName,quantity,price

FROM Product a, OrderDetail b

WHERE a.productNo=b.productNo

ORDER BY productName,price

(16) 查询单价高于400元的商品编号、商品名称、订货数量和订货单价。

SELECT

a.productNo,productName,quantity,price

FROM Product a, OrderDetail b

WHERE a.productNo=b.productNo AND price>400

ORDER BY productName

(17) 分别使用左外连接、右外连接、完整外

部连接查询单价高于400元的商品编号、商

品名称、订货数量和订货单价，并分析比较

检索的结果。

左外连接命令：

SELECT

a.productNo,productName,quantity,price

FROM Product a LEFT OUTER JOIN OrderDetail b

ON a.productNo=b.productNo WHERE price>400

, b.quantity,b.price

From OrderDetail As b left JOIN Product As a

ON (a.productNo=b.productNo)

and price>400

Order by a.productNo

右外连接命令：

SELECT

a.productNo,productName,quantity,price

FROM Product a RIGHT OUTER JOIN OrderDetail b

ON a.productNo=b.productNo WHERE price>400

Select a.productNo , a.productName , b.quantity,b.price

From OrderDetail As b RIGHT JOIN Product As a

ON (a.productNo=b.productNo)

and price>400

Order by a.productNo

全连接命令：

SELECT

a.productNo,productName,quantity,price

FROM Product a FULL OUTER JOIN

OrderDetail b

ON a.productNo=b.productNo WHERE price>400

Select a.productNo , a.productName , b.quantity,b.price

From OrderDetail As b full JOIN Product As a

ON (a.productNo=b.productNo)

and price>400

Order by a.productNo

(18)分别使用左外连接、右外连接、完整外部连接查询客户编号、客户名称、订货金额和订

单编号，并分析比较检索的结果。

SELECT

a.customerNo,customerName,orderSum,orderN o

FROM Customer a left OUTER JOIN OrderMaster b

ON a.customerNo=b.customerNo

SELECT

a.customerNo,customerName,orderSum,orderN o

FROM Customer a right OUTER JOIN OrderMaster b

ON a.customerNo=b.customerNo

SELECT

a.customerNo,customerName,orderSum,orderN o

FROM Customer a full OUTER JOIN OrderMaster b

ON a.customerNo=b.customerNo

从上述结果可知：若表a和表b做外连接，且b表是外码表，则a和b表左外连接可能会出现空值，但是右连接一定不会出现空值，全外连接与左外连接一样的结果。

(18) 查找每个员工的销售记录，要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期，其中性别使用“男”和“女”表示，日期使用“yyyy-mm-dd”格式显示。

SELECT salerNo,employeeName,

CASE sex WHEN 'M'THEN '男'

WHEN 'F'THEN '女'

END 性别,

productName,quantity,price,orderSum,

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

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

WHERE a.employeeNo=b.salerNo AND b.orderNo=c.orderNo

AND c.productNo=d.productNo ORDER BY salerNo

(19) 查找在2008年3月中有销售记录的客户编号、名称和订单总额。

SELECT

a.customerNo,customerName,orderSum

FROM OrderMaster a,Customer b

WHERE year(orderDate)=2008 AND month(orderDate)=3

AND a.customerNo=b.customerNo

(20) 使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额，其中订货日期不要显示时间，日期格式为“yyyy-mm-dd”，按客户编号排序，同一客户再按订单金额降序排序输出。

SELECT

a.customerNo,customerName,convert(char(10), orderDate,120) orderDate,orderSum

FROM Customer a LEFT OUTER JOIN OrderMaster b ON

a.customerNo=

b.customerNo

ORDER BY a.customerNo,orderSum desc