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

1、数据库系统原理与设计第二版实验一至实验三实验一11。查询员工的姓名、职务和薪水select employeeName，headShip，salaryfrom employee图112. 查询名字中含有“有限的客户姓名和所在地select CustomerName,addressfrom Customerwhere CustomerName like 有限图123. 查询出姓“张”并且姓名的最后一个字为“梅”的员工。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 图145。 查询出职务为“职员”或职务为“科长的女员工的信息select from employeewhere (headship=职员 or headship=科长） and sex

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

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

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

6、etailwhere quantity=10图1-1213。 统计在业务科工作且在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 Employee a，Employee b where a.empl

7、oyeeNo！=b。employeeNo and a。hireDate=b。hireDate图212.查找与“陈诗杰”在同一个单位工作的员工姓名、性别、部门和职务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 a。salary(select avg(

8、b。salary） from Employee b) 图234.查找有销售记录的客户编号名称和订单总额 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,b。orderNo 图245.查询没有订购商品的客户编号和客户名称 SEL

9、ECT a.customerNo,customerNameFROM Customer aWHERE a。customerNo NOT IN （SELECT customerNo FROM OrderMaster )图2-56。使用子查询查找32M DRAM的销售情况要求显示相应的销售员的姓名、性别、销售日期销售数量和金额其中性别用“男”和“女”表示 select employeeName,case sex when M then 男 when F then 女 end as sex， b。orderDate,c。quantity 销售数量,c。quantityc.price 金额from Em

10、ployee 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） 图267.查询OrderMaster表中订单金额最高的订单号及订单金额 select orderNo，sum(quantityprice) orderSu

11、m 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-78.在订单主表中查询订单金额大于“E2005002业务员在200819这天所接的任一张订单的金额”的所有订单信息。 select from OrderMaster where orderSumany（select orderSum from Orde

12、rMaster where salerNo=E2005002 and orderDate=2008-19) 图289。查询单价高于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.productName,b.price 图2-910.分别使用左外连接、右外连接、完整外部连

13、接查询单价高于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。productNo,a。productName,sum（b.quantity）订货数量,b.pric

14、e 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.productPrice400 and b.productNo=a。productNo group

15、 by a。productNo，a。productName,b.price图2-1011.使用左外连接查找每个客户的客户编号、名称、订货日期、订单金额、其中订货日期不显示时间日期格式为yyyy-mm-dd按客户编号排序同一客户再按订单金额降序排序输出。 select a。customerno 客户编号,customername 客户名称，convert（char（10），orderdate，120)销售日期，ordersum 销售金额from ordermaster a left outer join customer b on （a。customerno=b.customerno) orde

16、r 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 金额，orderDate=convert（char（10）,orderDate,120） from Employee

17、a,Product b,OrderMaster c,OrderDetail d where a。employeeNo=c.salerNo and b.productNo=d.productNo and c。orderNo=d.orderNo 图21213。查询16M DRAM的销售情况要求显示相应的销售员的姓名、性别、销售日期、销售数量和金额、其中性别用“男“女表示。 select a.employeeName，case sex whenFthen女 whenMthen男 end as sex,b.orderDate,c.quantity,c。price*c.quantity 金额 from

18、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 图21314.找出公司男业务员所接且订单金额超过2000的订单号及订单金额. select b。orderNo，b。orderSum from Employee a，OrderMaster b where a。employeeNo=b。salerNo and sex=M and b.o

19、rderSum2000 图21415.查询每种商品的总销售数量及总销售金额要求显示出商品编号、商品名称、总数量及总金额，并按商品号从小到大排列.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.productno图2-15实验三1.在订单明细表中查询订单金额最高的订单.sel

20、ect 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.salerNo and b。orderNo=c。orderNogroup by

21、 a.employeeNo，a.employeeNamehaving sum(quantityprice）5000图3-35.查询订单中所订购的商品数量没有超过个的客户编号和客户名称。SELECT a。CustomerNo,CustomerNameFROM Customer aWHERE a.CustomerNo IN ( SELECT CustomerNo FROM OrderMaster b，OrderDetail c WHERE b。orderNo=c.orderNo GROUP BY CustomerNo HAVING sum（quantity)10)图3-57. 查找至少销售了3种商

22、品的客户编号、客户名称、商品编号、商品名称、数量和金额。SELECT a。CustomerNo,CustomerName,b.ProductNo， ProductName，quantity，sum(quantityprice) sumFROM Customer a,Product b，OrderMaster c，OrderDetail dWHERE a。CustomerNo=c.CustomerNo and c。orderNo=d.orderNo and b.ProductNo=d.ProductNo and EXISTS ( SELECT CustomerNo FROM OrderMaste

23、r 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，quantityORDER BY a.CustomerNo,sum DESC图379. 求每位客户订购的每种商品的总数量及平均单价，并按客户号、商品号从小到大排列。 SELECT customerNo，productNo，

24、sum（quantity) quantitys, (sum（quantity*price)/sum（quantity） avgpriceFROM OrderMaster a，OrderDetail bWHERE a.orderNo=b.orderNoGROUP BY customerNo,productNoORDER BY customerNo,productNo图3-911。 查询订购的商品至少包含了订单“200803010001”中所订购商品的订单. SELECT *FROM OrderMaster aWHERE not exists （select from OrderDetail y

25、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，orderDate， orderSumFROM Employee a，OrderMaster b,OrderDetail cWHERE b.orderNo=c。orderNo and a。employeeNo=

26、b.salerNoGROUP BY salerNo,b.orderNo,orderDate,orderSumORDER BY orderSum DESC图31315.查询既订购了“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