《数据库系统原理与设计实验教程》实验一.docx
《《数据库系统原理与设计实验教程》实验一.docx》由会员分享,可在线阅读,更多相关《《数据库系统原理与设计实验教程》实验一.docx(13页珍藏版)》请在冰豆网上搜索。
《数据库系统原理与设计实验教程》实验一
(1)查询所有业务部门的员工姓名、职称、薪水。
SELECTemployeeName,headShip,salary
FROMEmployee
WHEREdepartment='业务科'
(2)查询名字中含有“有限”的客户姓名和所在地。
SELECTcustomerName,address
FROMCustomer
WHEREcustomerNameLIKE'%有限%'
(3)查询出姓“王”并且姓名的最后一个字为“成”的员工。
SELECT*
FROMEmployee
WHEREemployeeNameLIKE'王%成'
(4)查询住址中含有上海或者南昌的女员工,并显示其姓名、所属部门、职称、住址,其中性别用“男”和“女”显示。
SELECTemployeeName,
CASEsexWHEN'M'THEN'男'
WHEN'F'THEN'女'
ENDASsex,department,headShip,address
FROM(SELECT*FROMEmployeeWHEREsex='F')ASa
WHEREaddressLIKE'%上海%'ORaddressLIKE'%南昌%'
(5)在表OrderMaster中挑出销售金额大于等于5000元的订单。
SELECTorderNo,customerNo,salerNo,orderDate,orderSum,invoiceNo
FROMOrderMaster
WHEREorderSum>=5000
(6)选取订单金额最高的前10%的订单数据。
SELECTTOP10PERCENT*
FROMOrderMaster
ORDERBYorderSumDESC
(7)查询出职务为“职员”或职务为“科长”的女员工的信息。
SELECT*
FROM(SELECT*FROMEmployeeWHEREsex='F')ASa
WHEREheadShip='职员'ORheadShip='科长'
(8)查找订单金额高于4000的所有客户编号。
SELECTcustomerNo
FROMOrderMaster
WHEREorderSum>4000
(9)选取编号界于C20050001~C20050004的客户编号、客户名称、客户地址。
SELECTcustomerNo,customerName,address
FROMCustomer
WHEREcustomerNoBETWEEN'C20050001'AND'C20050004'
(10)找出同一天进入公司服务的员工。
SELECTa.employeeNo,a.employeeName,a.hireDate,a.department,a.headShip,b.employeeNo,b.employeeName,b.hireDate,b.department,b.headShip
FROMemployeea,employeeb
WHEREa.employeeNo!
=b.employeeNoANDa.employeeName>b.employeeNameAND(a.hireDate=b.hireDate)
ORDERBYa.hireDate
(11)在订单主表中查询订单金额大于“E2005002业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。
SELECT*
FROMOrderMaster
WHEREorderSum>ANY
(SELECTorderSum
FROMOrderMaster
WHEREsalerNo='E2005002'ANDorderDate='2008-1-9')
(12)查询既订购了“52倍速光驱”商品,又订购了“17寸显示器”商品的客户编号、订单编号和订单金额。
SELECTcustomerNo,orderNo,orderSum
FROMOrderMaster
WHEREcustomerNoIN(SELECTcustomerNo
FROMOrderMastera,OrderDetailb,Productc
WHEREa.orderNo=b.orderNoANDb.productNo=c.productNoANDproductName='17寸显示器')
AND
customerNoIN(SELECTcustomerNo
FROMOrderMastera,OrderDetailb,Productc
WHEREa.orderNo=b.orderNoANDb.productNo=c.productNoANDproductName='52倍速光驱')
(13)查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务。
SELECTb.employeeName,b.sex,b.department,b.headShip
FROMEmployeea,Employeeb
WHEREa.department=b.departmentANDa.employeeName='陈诗杰'
(14)查询每种商品的商品编号、商品名称、订货数量和订货单价。
SELECTa.productNo商品编号,a.productName商品名称,sum(quantity)订货数量,sum(quantity*price)/sum(quantity)订货单价
FROMProducta,OrderDetailb
WHEREa.productNo=b.productNo
GROUPBYa.productNo,a.productName
(15)查询单价高于400元的商品编号、商品名称、订货数量和订货单价。
SELECTa.productNo,productName,quantity,price
FROMOrderDetaila,Productb
WHEREa.productNo=b.productNoANDprice>400
(16)分别使用左外连接、右外连接、完整外连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价,并分析比较检索的结果。
左外连接:
SELECTa.productNo,productName,quantity,price
FROMProductASaLEFTJOINOrderDetailASb
ON(A.productNo=b.productNo)ANDprice>400
右外连接:
SELECTa.productNo,productName,quantity,price
FROMProductASaRIGHTJOINOrderDetailASb
ON(A.productNo=b.productNo)ANDprice>400
完整外部连接:
SELECTa.productNo,productName,quantity,price
FROMProductASaFULLJOINOrderDetailASb
ON(A.productNo=b.productNo)ANDprice>400
(17)查找每个员工的销售记录,要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期,其中性别使用“男”和“女”表示,日期使用yyyy-mm-dd格式显示。
SELECTemployeeNo编号,employeeName姓名,性别=
CASEa.sex
WHEN'M'THEN'男'
WHEN'F'THEN'女'
END,
productName商品名称,quantity数量,price单价,quantity*priceAS金额,
销售日期=(CONVERT(char(10),b.orderDate,120))
FROMEmployeea,OrderMasterb,OrderDetailc,Productd
WHEREd.productNo=c.productNoANDa.employeeNo=b.salerNoANDb.orderNo=c.orderNo
ORDERBYa.employeeNo
(18)查找在2008年3月中有销售记录的客户编号、名称和订单总额。
SELECTa.CustomerNo,a.CustomerName,sum(quantity*price)orderSum
FROMCustomera,OrderMasterb,orderdetailc
WHEREa.CustomerNo=b.CustomerNoandb.orderNo=c.orderNoandYEAR(orderDate)=2008ANDMONTH(orderDate)=3
GROUPBYa.CustomerNo,a.CustomerName
(19)使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额,其中订货日期不要显示时间,日期格式为yyyy-mm-dd,按客户编号排序,同一客户再按订单金额降序排序输出。
SELECTa.customerNo,customerName,
orderDate=CONVERT(char(10),orderDate,120),orderSum
FROMCustomeraLEFTJOINOrderMasterb
ON(a.customerNo=b.customerNo)
ORDERBYa.customerNo,orderSum
(20)查找16MDRAM的销售情况,要求显示相应的销售员的姓名、性别,销售日期、销售数量和金额,其中性别用“男”、“女”表示。
SELECTemployeeName,CASEsex
WHEN'M'THEN'男'
WHEN'F'THEN'女'
ENDsex,
orderDate,quantity,quantity*pricetotal
FROMEmployeea,OrderMasterb,OrderDetailc,Productd
WHEREa.employeeNo=b.salerNoANDb.orderNo=c.orderNoAND
c.productNo=d.productNoANDproductName='16MDRAM'
(21)查找每个人的销售记录,要求显示销售员的编号、姓名、性别、商品名称、数量、单价、金额和销售日期。
SELECTsalerNo编号,employeeName姓名,sex性别,productName商品名称,quantity数量,price单价,quantity*priceAS金额,orderDate销售日期
FROMEmployeea,OrderMasterb,OrderDetailc,Productd
WHEREd.productNo=c.productNoANDa.employeeNo=b.salerNoANDb.orderNo=c.orderNo
(22)查询客户姓名为“客户丙”所购货物的“客户名称”、“订单金额”、“订货日期”和“电话号码”。
SELECTa.customerName,orderSum,orderDate,telephone
FROMCustomera,OrderMasterb
WHEREa.customerNo=b.customerNoANDa.customerName='客户丙'
(23)找出公司男业务员所接且订单金额超过2000元的订单号及订单金额。
SELECTorderNo,orderSum
FROMEmployeea,OrderMasterb
WHEREa.employeeNo=b.salerNoANDsex='M'ANDorderSum>2000
(24)查询来自上海市的客户的姓名、电话、订单号及订单金额。
SELECTcustomerName,telephone,orderNo,orderSum
FROMCustomera,OrderMasterb
WHEREa.customerNo=b.customerNoANDaddress='上海市'