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

实验一

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

select employeeName,headShip,salary from employee

图1-1

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

select CustomerName,address from Customer

where CustomerName like '%有限%'

图1-2

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'

图1-4

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

11. 查询订购的商品至少包含了订单“200803010001”中所订购商品的订单。 SELECT *

FROM OrderMaster a WHERE not exists (select *

from OrderDetail y

where orderNo='200803010001' and not exists (select *

from OrderDetail z

where y.productNo=z.productNo and a.orderNo=z.orderNo))

图3-11

13. 查询销售金额最高的销售员编号、订单编号、订单日期和订单金额。 SELECT top 1 salerNo,b.orderNo,orderDate, orderSum FROM Employee a,OrderMaster b,OrderDetail c

WHERE b.orderNo=c.orderNo and a.employeeNo=b.salerNo GROUP BY salerNo,b.orderNo,orderDate,orderSum ORDER BY orderSum DESC

图3-13

15.查询既订购了“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寸显示器')

图3-15

本文来源：https://www.bwwdw.com/article/qwix.html