数据库系统原理与设计(第二版)实验一至实验三.pdf

《数据库系统原理与设计(第二版)实验一至实验三.pdf》由会员分享，可在线阅读，更多相关《数据库系统原理与设计(第二版)实验一至实验三.pdf（22页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、实验一 11。查询员工的姓名、职务和薪水 select employeeName，headShip，salary from employee 图 11 2.查询名字中含有“有限的客户姓名和所在地 select CustomerName,address from Customer where CustomerName like 有限 图 12 3.查询出姓“张”并且姓名的最后一个字为“梅”的员工。select from employee where employeeName like 张梅 图 1-3 4。查询住址中含有上海或南昌的女员工，并显示其姓名、所属部门、职称、住址，其中性别用“男”和“

2、女”显示 SELECT employeeName，department,address,isnull（convert（char（10），birthday,120），不详)出生日期，case sex when Mthen 男 when Fthen女 end as 性别 from employee where（address like%上海%or address like 南昌）and sex=F 图 14 5。查询出职务为“职员”或职务为“科长的女员工的信息 select from employee where(headship=职员 or headship=科长）and sex=F 图 15

3、6.选取编号不在“C20050001”和“C20050004的客户编号、客户名称、客户地址。Select *from Customer where CustomerNo not in（C20050001,C20050004）图 16 图 16 7.在订单明细表 Ordermaster 中挑出销售金额大于等于 5000 元的订单。update ordermaster set ordersum=sum2 from ordermaster a,（select orderno，sum（quantityprice)sum2 from orderdetail group by orderno）b wher

4、e 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 图 19 10。计算orderDetail表中每种商品的销售数量、平均价格和总销售量金额，并且依据销售金额由大到小输出。SELECT productno

5、 商品种类，count(*）quantity，avg（price）平均价格,sum(quantityprice)金额 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 图 111 12.统计至少销售了 10 件以上的商品编号和销售数量.select productno 商品编号，qua

6、ntity 商品数目 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)图 113 实验二 1.找出同一天进入公司工作的员工 select distinct a。employeeNo，a。employeeName，a.hireDate from Em

7、ployee a，Employee b where a.employeeNo！=b。employeeNo and a。hireDate=b。hireDate 图 21 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*fro

8、m Employee a where a。salary(select avg(b。salary）from Employee b)图 23 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

9、,b。orderNo 图 24 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,

10、c。quantity 销售数量,c。quantityc.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）图26 7.查询 OrderMaster 表中订单金额最高的订单号及

11、订单金额 select orderNo，sum(quantityprice)orderSum from OrderDetail group by orderNo having sum(quantity*price)=(select max（orderSum)from(select orderNo,sum(quantityprice）orderSum from OrderDetail group by orderNo)b）图 2-7 8.在订单主表中查询订单金额大于“E2005002 业务员在 200819 这天所接的任一张订单的金额”的所有订单信息。select from OrderMaste

12、r where orderSumany（select orderSum from OrderMaster where salerNo=E2005002 and orderDate=2008-19)图 28 9。查询单价高于 400 元的商品编号商品名称订货数量和订货单价.select a.productNo，a。productName,sum（b。quantity)订货数量，b.price from Product a，OrderDetail b where a.productPrice400 and b。productNo=a。productNo group by a。productNo，a

13、.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.productPrice400 and b.productNo=a.productNo group by a。productNo,a.productName，b。price select a。

14、productNo,a。productName,sum（b.quantity）订货数量,b.price from Product a right outer join OrderDetail b on a。productPrice400 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

15、.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 ou

16、ter 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 whenFthen女 whenMthen男 End sex,b.productName,d.quantity，d.price,d。quantityd。price 金额，or

17、derDate=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 图 212 13。查询 16M DRAM 的销售情况要求显示相应的销售员的姓名、性别、销售日期、销售数量和金额、其中性别用“男“女表示。select a.employeeName，case sex whenFthen女 whenMthen男 end a

18、s 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 图 213 14.找出公司男业务员所接且订单金额超过 2000 的订单号及订单金额.select b。orderNo，b。orderSum from Employee a，O

19、rderMaster b where a。employeeNo=b。salerNo and sex=M and b.orderSum2000 图 214 15.查询每种商品的总销售数量及总销售金额要求显示出商品编号、商品名称、总数量及总金额，并按商品号从小到大排列.select a.productno 商品编号,productname 商品称,sum（quantity)总销售数量，sum(quantity*price)总销售金额 from product a，orderdetail b where a。productno=b。productno group by a.productno，pro

20、ductname 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，Or

21、derMaster c where a。employeeNo=c.salerNo and b。orderNo=c。orderNo group by a.employeeNo，a.employeeName having sum(quantityprice）5000 图 3-3 5.查询订单中所订购的商品数量没有超过个的客户编号和客户名称。SELECT a。CustomerNo,CustomerName FROM Customer a WHERE a.CustomerNo IN(SELECT CustomerNo FROM OrderMaster b，OrderDetail c WHERE b。o

22、rderNo=c.orderNo GROUP BY CustomerNo HAVING sum（quantity)10)图 3-5 7.查找至少销售了 3 种商品的客户编号、客户名称、商品编号、商品名称、数量和金额。SELECT a。CustomerNo,CustomerName,b.ProductNo，ProductName，quantity，sum(quantityprice)sum FROM Customer a,Product b，OrderMaster c，OrderDetail d WHERE a。CustomerNo=c.CustomerNo and c。orderNo=d.or

23、derNo 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

24、 DESC 图 37 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

25、”中所订购商品的订单.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

26、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 图 313 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 寸显示器）图 315