数据库系统原理与设计第二版实验一至实验三.docx
- 文档编号:15226793
- 上传时间:2023-07-02
- 格式:DOCX
- 页数:27
- 大小:852.07KB
数据库系统原理与设计第二版实验一至实验三.docx
《数据库系统原理与设计第二版实验一至实验三.docx》由会员分享,可在线阅读,更多相关《数据库系统原理与设计第二版实验一至实验三.docx(27页珍藏版)》请在冰点文库上搜索。
数据库系统原理与设计第二版实验一至实验三
实验一
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,Employeeb
wherea.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.查询订购的商品至少包含了订单“2”中所订购商品的订单。
SELECT*
FROMOrderMastera
WHEREnotexists
(select*
fromOrderDetaily
whereorderNo='2'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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 系统 原理 设计 第二 实验