数据库系统原理与设计第二版实验一至实验三.docx
《数据库系统原理与设计第二版实验一至实验三.docx》由会员分享，可在线阅读，更多相关《数据库系统原理与设计第二版实验一至实验三.docx（26页珍藏版）》请在冰豆网上搜索。
数据库系统原理与设计第二版实验一至实验三
实验一
1—1。
查询员工的姓名、职务和薪水
selectemployeeName，headShip，salary
fromemployee
图1—1
2.查询名字中含有“有限"的客户姓名和所在地
selectCustomerName,address
fromCustomer
whereCustomerNamelike'％有限％'
图1—2
3.查询出姓“张”并且姓名的最后一个字为“梅”的员工。
select＊
fromemployee
whereemployeeNamelike’张％梅'
图1-3
4。
查询住址中含有上海或南昌的女员工，并显示其姓名、所属部门、职称、住址，其中性别用“男”和“女”显示
SELECTemployeeName，department,address,
isnull（convert（char（10），birthday,120），'不详’）出生日期，
casesexwhen'M'then'男'
when'F'then'女’
endas性别
fromemployee
where（addresslike'%上海%'oraddresslike’％南昌％'）andsex=’F’
图1—4
5。
查询出职务为“职员”或职务为“科长"的女员工的信息
select＊
fromemployee
where（headship=’职员'orheadship='科长'）andsex='F'
图1—5
6.选取编号不在“C20050001”和“C20050004"的客户编号、客户名称、客户地址。
Select*
fromCustomer
whereCustomerNonotin（'C20050001’,’C20050004’）
图1—6
图1—6
7.在订单明细表Ordermaster中挑出销售金额大于等于5000元的订单。
updateordermastersetordersum=sum2
fromordermastera,（selectorderno，sum（quantity＊price）sum2
fromorderdetail
groupbyorderno）b
wherea.orderno=b.orderno
Select*
Fromordermaster
Whereordersum〉=’5000'
图1-7
8。
选取订单金额最高的前10％的订单数据
SELECTTOP10PERCENT*
fromorderdetailorderbypriceDESC
图1-8
9。
计算一共销售了几种商品
SELECTCOUNT（DISTINCTproductno）as种类
fromorderDeta
图1—9
10。
计算orderDetail表中每种商品的销售数量、平均价格和总销售量金额，并且依据销售金额由大到小输出。
SELECTproductno商品种类，count（*）quantity，avg（price）平均价格,sum（quantity＊price）金额
fromorderDetail
groupbyproductno
orderby金额desc
图1-10
11.按客户编号统计每个客户2008年2月的订单总金额。
selectcustomerno，ordersum
fromordermaster
whereyear（orderDate）=2008andmonth（orderDate）=2
图1—11
12.统计至少销售了10件以上的商品编号和销售数量.
selectproductno商品编号，quantity商品数目
fromorderdetail
wherequantity〉=10
图1-12
13。
统计在业务科工作且在1973年或1967年出生的员工人数和平均工资
selectcount（*）人数,avg（salary）平均工资
fromEmployee
wheredepartment='业务科'and（year（birthday）=1973oryear（birthday）=1967）
图1—13
实验二
1.找出同一天进入公司工作的员工
selectdistincta。
employeeNo，a。
employeeName，a.hireDate
fromEmployeea，Employeebwherea.employeeNo！
=b。
employeeNoanda。
hireDate=b。
hireDate
图2—1
2.查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务
selecta.employeeName，a。
sex,a。
department，a。
headShip
fromEmployeea，Employeeb
wherea。
department=b。
departmentandb。
employeeName=’陈诗杰'
图2-2
3.在employee表中查询薪水超过员工平均薪水的员工信息
select*
fromEmployeea
wherea。
salary〉（selectavg（b。
salary）fromEmployeeb）
图2—3
4.查找有销售记录的客户编号名称和订单总额
selecta.customerNo，a。
customerName,b。
orderNo,sum（quantity*price）orderSum
fromCustomera，OrderMasterb,OrderDetailc
wherea.customerNo=b.customerNoandb。
orderNo=c。
orderNo
groupbya.customerNo,a.customerName,b。
orderNo
图2—4
5.查询没有订购商品的客户编号和客户名称SELECTa.customerNo,customerName
FROMCustomera
WHEREa。
customerNoNOTIN（SELECTcustomerNoFROMOrderMaster）
图2-5
6。
使用子查询查找32MDRAM的销售情况要求显示相应的销售员的姓名、性别、销售日期销售数量和金额其中性别用“男”和“女”表示
selectemployeeName,casesex
when’M'then’男’
when'F’then'女’
endassex，b。
orderDate,c。
quantity销售数量,c。
quantity＊c.price金额
fromEmployeea,OrderMasterb,OrderDetailc
wherea。
employeeNo=b。
salerNoandb。
orderNo=c.orderNoandc。
productNoin（selectf.productNo
fromOrderMasterd，OrderDetaile，Productf
whered。
orderNo=e。
orderNoandproductName=’32MDRAM'）
图2—6
7.查询OrderMaster表中订单金额最高的订单号及订单金额
selectorderNo，sum（quantity＊price）orderSum
fromOrderDetail
groupbyorderNo
havingsum（quantity*price）=（selectmax（orderSum）
from（selectorderNo,sum（quantity＊price）orderSum
fromOrderDetail
groupbyorderNo）b）
图2-7
8.在订单主表中查询订单金额大于“E2005002业务员在2008—1—9这天所接的任一张订单的金额”的所有订单信息。
select＊
fromOrderMaster
whereorderSum>any（selectorderSumfromOrderMasterwheresalerNo='E2005002’andorderDate=’2008-1—9’）
图2—8
9。
查询单价高于400元的商品编号商品名称订货数量和订货单价.
selecta.productNo，a。
productName,sum（b。
quantity）订货数量，b.price
fromProducta，OrderDetailb
wherea.productPrice〉400andb。
productNo=a。
productNo
groupbya。
productNo，a.productName,b.price
图2-9
10.分别使用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价并分析比较检索的结果。
selecta。
productNo,a.productName，sum（b.quantity）订货数量，b。
price
fromProductaleftouterjoinOrderDetailbona.productPrice>400andb.productNo=a.productNo
groupbya。
productNo,a.productName，b。
price
selecta。
productNo,a。
productName,sum（b.quantity）订货数量,b.price
fromProductarightouterjoinOrderDetailbona。
productPrice〉400and
b。
productNo=a。
productNo
groupbya.productNo，a。
productName,b。
price
selecta。
productNo，a.productName,sum（b。
quantity）订货数量,b.price
fromProductafullouterjoinOrderDetailbona.productPrice〉400andb.productNo=a。
productNo
groupbya。
productNo，a。
productName,b.price
图2-10
11.使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额、其中订货日期不显示时间日期格式为yyyy-mm-dd按客户编号排序同一客户再按订单金额降序排序输出。
selecta。
customerno客户编号,customername客户名称，convert（char（10），orderdate，120）销售日期，ordersum销售金额
fromordermasteraleftouterjoincustomerbon（a。
customerno=b.customerno）
orderbya。
customerno,ordersumdesc
12.查找每个员工的销售记录要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期其中性别使用“男”和“女”表示日期使用yyyy-mm-dd格式显示。
selecta.employeeNo，a。
employeeName,casesex
when’F’then’女'
when'M’then’男'
Endsex,b.productName,d.quantity，d.price,d。
quantity＊d。
price金额，orderDate=convert（char（10）,orderDate,120）
fromEmployeea,Productb,OrderMasterc,OrderDetaild
wherea。
employeeNo=c.salerNoandb.productNo=d.productNoandc。
orderNo=d.orderNo
图2—12
13。
查询16MDRAM的销售情况要求显示相应的销售员的姓名、性别、销售日期、销售数量和金额、其中性别用“男"“女"表示。
selecta.employeeName，casesexwhen'F’then'女’
when’M’then’男'
endassex,b.orderDate,c.quantity,c。
price*c.quantity金额
fromEmployeea,OrderMasterb，OrderDetailc，Productd
wherea.employeeNo=b.salerNoandb.orderNo=c.orderNoandc。
productNo=d.productNoand
d。
productName=’16MDRAM’
图2—13
14.找出公司男业务员所接且订单金额超过2000的订单号及订单金额.
selectb。
orderNo，b。
orderSum
fromEmployeea，OrderMasterb
wherea。
employeeNo=b。
salerNoandsex=’M’andb.orderSum〉2000
图2—14
15.查询每种商品的总销售数量及总销售金额要求显示出商品编号、商品名称、总数量及总金额，并按商品号从小到大排列.
selecta.productno商品编号,productname商品称,sum（quantity）总销售数量，sum（quantity*price）总销售金额
fromproducta，orderdetailb
wherea。
productno=b。
productno
groupbya.productno，productname
orderbya.productno
图2-15
实验三
1.在订单明细表中查询订单金额最高的订单.
selecttop1orderNo,sum（quantity*price）orderSum
fromOrderDetail
groupbyorderNo
orderbyorderSumdesc
图3-1
3。
查找销售总额少于5000元的销售员编号、姓名和销售额。
selecta。
employeeNo,a。
employeeName，sum（quantity*price）sunmoney
fromEmployeea，OrderDetailb，OrderMasterc
wherea。
employeeNo=c.salerNoandb。
orderNo=c。
orderNo
groupbya.employeeNo，a.employeeName
havingsum（quantity＊price）<5000
图3-3
5.查询订单中所订购的商品数量没有超过个的客户编号和客户名称。
SELECTa。
CustomerNo,CustomerName
FROMCustomera
WHEREa.CustomerNoIN（
SELECTCustomerNo
FROMOrderMasterb，OrderDetailc
WHEREb。
orderNo=c.orderNo
GROUPBYCustomerNo
HAVINGsum（quantity）〈10）
图3-5
7.查找至少销售了3种商品的客户编号、客户名称、商品编号、商品名称、数量和金额。
SELECTa。
CustomerNo,CustomerName,b.ProductNo，
ProductName，quantity，sum（quantity＊price）sum
FROMCustomera,Productb，OrderMasterc，OrderDetaild
WHEREa。
CustomerNo=c.CustomerNoandc。
orderNo=d.orderNo
andb.ProductNo=d.ProductNoand
EXISTS（
SELECTCustomerNo
FROMOrderMastere,OrderDetailf
WHEREe.orderNo=f.orderNoanda。
customerNo=e.customerNo
GROUPBYCustomerNo
HAVINGcount（distinctProductNo）〉=3）
GROUPBYa。
CustomerNo,CustomerName,b.ProductNo,
ProductName，quantity
ORDERBYa.CustomerNo,sumDESC
图3—7
9.求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。
SELECTcustomerNo，productNo，sum（quantity）quantitys,
（sum（quantity*price）/sum（quantity））avgprice
FROMOrderMastera，OrderDetailb
WHEREa.orderNo=b.orderNo
GROUPBYcustomerNo,productNo
ORDERBYcustomerNo,productNo
图3-9
11。
查询订购的商品至少包含了订单“200803010001”中所订购商品的订单.
SELECT*
FROMOrderMastera
WHEREnotexists
（select＊
fromOrderDetaily
whereorderNo=’200803010001’andnotexists
（select*
fromOrderDetailz
wherey。
productNo=z。
productNoand
a。
orderNo=z.orderNo））
图3-11
13。
查询销售金额最高的销售员编号、订单编号、订单日期和订单金额。
SELECTtop1salerNo,b。
orderNo，orderDate，orderSum
FROMEmployeea，OrderMasterb,OrderDetailc
WHEREb.orderNo=c。
orderNoanda。
employeeNo=b.salerNo
GROUPBYsalerNo,b.orderNo,orderDate,orderSum
ORDERBYorderSumDESC
图3—13
15.查询既订购了“52倍速光驱”商品，又订购了“17寸显示器”商品的客户编号、订单和订单金额。
SelectcustomerNo,orderNo,orderSum
fromordermaster
wherecustomernoin（
selectcustomerno
fromordermastera,orderdetailb，productc
wherea。
orderno=b.ordernoandb。
productno=c。
productnoandproductname=’52倍速光驱’）andcustomernoin（
selectcustomerno
fromordermastera，orderdetailb，productc
wherea。
orderno=b。
ordernoandb。
productno=c.productnoandproductname=’17寸显示器'）
图3—15
升级会员 