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

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

1、-数据库系统原理与设计(第二版)实验一至实验三-第 8 页实验一1-1.查询员工的姓名、职务和薪水select employeeName,headShip,salaryfrom employee图1-12. 查询名字中含有“有限”的客户姓名和所在地select CustomerName,addressfrom Customerwhere CustomerName like %有限%图1-23. 查询出姓“张”并且姓名的最后一个字为“梅”的员工。select *from employeewhere employeeName like 张%梅图1-34. 查询住址中含有上海或南昌的女员工，并显示其

2、姓名、所属部门、职称、住址，其中性别用“男”和“女”显示SELECT employeeName,department,address,isnull (convert(char(10),birthday,120),不详)出生日期,case sex when Mthen 男 when Fthen女end as 性别from employeewhere （address like %上海%or address like %南昌%）and sex=F图1-45. 查询出职务为“职员”或职务为“科长”的女员工的信息select *from employeewhere (headship=职员 or he

3、adship=科长) and sex=F图1-56. 选取编号不在“C20050001”和“C20050004”的客户编号、客户名称、客户地址。Select *from Customerwhere CustomerNo not in ( C20050001 ,C20050004)图1-6图1-67. 在订单明细表Ordermaster中挑出销售金额大于等于5000元的订单。update ordermaster set ordersum=sum2from ordermaster a,(select orderno,sum(quantity*price)sum2from orderdetailgr

4、oup by orderno)bwhere a.orderno=b.ordernoSelect *From ordermasterWhere ordersum=5000图1-78. 选取订单金额最高的前10%的订单数据SELECT TOP 10 PERCENT * from orderdetail order by price DESC图1-89. 计算一共销售了几种商品SELECT COUNT(DISTINCT productno)as 种类from orderDeta图1-910.计算orderDetail表中每种商品的销售数量、平均价格和总销售量金额，并且依据销售金额由大到小输出。 SE

5、LECT productno 商品种类,count(*)quantity,avg (price)平均价格,sum(quantity*price)金额from orderDetailgroup by productnoorder by 金额desc图1-1011. 按客户编号统计每个客户2008年2月的订单总金额。select customerno,ordersumfrom ordermasterwhere year(orderDate)=2008 and month(orderDate)=2图1-1112.统计至少销售了10件以上的商品编号和销售数量。select productno 商品编号

6、,quantity 商品数目from orderdetailwhere quantity=10图1-1213. 统计在业务科工作且在1973年或1967年出生的员工人数和平均工资select count(*) 人数,avg(salary) 平均工资 from Employee where department=业务科 and (year(birthday)=1973 or year(birthday)=1967)图1-13实验二1. 找出同一天进入公司工作的员工select distinct from Employee a,Employee b where != and =图2-12. 查找与

7、“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务select a.employeeName,a.sex,a.department,a.headShip from Employee a,Employee b where a.department=b.department and b.employeeName=陈诗杰图2-23. 在employee表中查询薪水超过员工平均薪水的员工信息 select * from Employee a where (select avg() from Employee b) 图2-34. 查找有销售记录的客户编号名称和订单总额 select a.custom

8、erNo,a.customerName,b.orderNo,sum(quantity*price) orderSum from Customer a,OrderMaster b,OrderDetail c where = and = group by 图2-45.查询没有订购商品的客户编号和客户名称 SELECT a.customerNo,customerNameFROM Customer aWHERE a.customerNo NOT IN (SELECT customerNo FROM OrderMaster )图2-56.使用子查询查找32M DRAM的销售情况要求显示相应的销售员的姓名

9、、性别、销售日期销售数量和金额其中性别用“男”和“女”表示 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

10、d,OrderDetail e,Product f where d.orderNo=e.orderNo and productName=32M DRAM) 图2-67.查询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 Ord

11、erDetail group by orderNo)b) 图2-78.在订单主表中查询订单金额大于“E2005002业务员在2008-1-9这天所接的任一张订单的金额”的所有订单信息。 select * from OrderMaster where orderSumany(select orderSum from OrderMaster where salerNo=E2005002 and orderDate=2008-1-9) 图2-89.查询单价高于400元的商品编号商品名称订货数量和订货单价。 select a.productNo,a.productName,sum()订货数量, fro

12、m Product a,OrderDetail b where 400 and = group by a.productNo,a.productName,b.price 图2-910.分别使用左外连接、右外连接、完整外部连接查询单价高于400元的商品编号、商品名称、订货数量和订货单价并分析比较检索的结果。 select a.productNo,a.productName,sum()订货数量, from Product a left outer join OrderDetail b on 400 and = group by select a.productNo,a.productName,su

13、m()订货数量, from Product a right outer join OrderDetail b on 400 and group by e select a.productNo,a.productName,sum()订货数量, from Product a full outer join OrderDetail b on 400 and = group by 图2-1011.使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额、其中订货日期不显示时间日期格式为yyyy-mm-dd按客户编号排序同一客户再按订单金额降序排序输出。 select a.customerno 客户

14、编号,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.employeeNam

15、e,case sex whenFthen女 whenMthen男 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-1213.查询16M DRAM的销售情况要求显示

16、相应的销售员的姓名、性别、销售日期、销售数量和金额、其中性别用“男”“女”表示。 select a.employeeName,case sex whenFthen女 whenMthen男 end as * 金额 from Employee a,OrderMaster b,OrderDetail c,Product d where = and = and = and =16M DRAM 图2-1314.找出公司男业务员所接且订单金额超过2000的订单号及订单金额。 select b.orderNo,b.orderSum from Employee a,OrderMaster b where a.

17、employeeNo=b.salerNo and sex=M and b.orderSum2000 图2-1415.查询每种商品的总销售数量及总销售金额要求显示出商品编号、商品名称、总数量及总金额，并按商品号从小到大排列。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.pr

18、oductno图2-15实验三1. 在订单明细表中查询订单金额最高的订单。select top 1 orderNo,sum(quantity*price) orderSumfrom OrderDetailgroup by orderNoorder by orderSum desc图3-13. 查找销售总额少于5000元的销售员编号、姓名和销售额。select a.employeeNo,a.employeeName,sum(quantity*price) sunmoneyfrom Employee a,OrderDetail b,OrderMaster cwhere a.employeeNo=c

19、.salerNo and b.orderNo=c.orderNogroup by a.employeeNo,a.employeeNamehaving sum(quantity*price)5000图3-35. 查询订单中所订购的商品数量没有超过个的客户编号和客户名称。SELECT ,CustomerNameFROM Customer aWHERE IN (SELECT CustomerNoFROM OrderMaster b,OrderDetail cWHERE = GROUP BY CustomerNoHAVING sum(quantity)=3)GROUP BY , ProductName

20、,quantityORDER BY a.CustomerNo,sum DESC图3-79. 求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。 SELECT customerNo,productNo,sum(quantity) quantitys, (sum(quantity*price)/sum(quantity) avgpriceFROM OrderMaster a,OrderDetail bWHERE =GROUP BY customerNo,productNoORDER BY customerNo,productNo图3-911. 查询订购的商品至少包含了订单

21、“200803010001”中所订购商品的订单。 SELECT *FROM OrderMaster aWHERE 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-1113. 查询销售金额最高的销售员编号、订单编号、订单日期和订单金额。SELECT top 1 salerNo,b.orderNo,orderDat

22、e, orderSumFROM Employee a,OrderMaster b,OrderDetail cWHERE = and =GROUP BY salerNo,b.orderNo,orderDate,orderSumORDER BY orderSum DESC图3-1315.查询既订购了“52倍速光驱”商品，又订购了“17寸显示器”商品的客户编号、订单和订单金额。Select customerNo,orderNo,orderSum from ordermaster where customerno in(select customernofrom ordermaster a,orderdetail b,product cwhere a.orderno=b.orderno and b.productno=c.productno and productname=52倍速光驱) and customerno in (select customernofrom ordermaster a,orderdetail b,product cwhere a.orderno=b.orderno and b.productno=c.productno and productname=17寸显示器)图3-15