數(shù)據(jù)庫系統(tǒng)原理與設計(第二版)實驗一至實驗三.doc

實驗一1-1.查詢員工的姓名、職務和薪水select employeeName,headShip,salaryfrom employee圖1-12. 查詢名字中含有“有限”的客戶姓名和所在地select CustomerName,addressfrom Customerwhere CustomerName like %有限%圖1-23. 查詢出姓“張”并且姓名的最后一個字為“梅”的員工。select *from employeewhere employeeName like 張%梅圖1-34. 查詢住址中含有上?；蚰喜呐畣T工，并顯示其姓名、所屬部門、職稱、住址，其中性別用“男”和“女”顯示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 headship=科長) 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 orderdetailgroup by orderno)bwhere a.orderno=b.ordernoSelect *From ordermasterWhere ordersum=5000圖1-78. 選取訂單金額最高的前10%的訂單數(shù)據(jù)SELECT TOP 10 PERCENT * from orderdetail order by price DESC圖1-89. 計算一共銷售了幾種商品SELECT COUNT(DISTINCT productno)as 種類from orderDeta圖1-910.計算orderDetail表中每種商品的銷售數(shù)量、平均價格和總銷售量金額，并且依據(jù)銷售金額由大到小輸出。 SELECT productno 商品種類,count(*)quantity,avg (price)平均價格,sum(quantity*price)金額from orderDetailgroup by productnoorder by 金額desc圖1-1011. 按客戶編號統(tǒng)計每個客戶2008年2月的訂單總金額。select customerno,ordersumfrom ordermasterwhere year(orderDate)=2008 and month(orderDate)=2圖1-1112.統(tǒng)計至少銷售了10件以上的商品編號和銷售數(shù)量。select productno 商品編號,quantity 商品數(shù)目from orderdetailwhere quantity=10圖1-1213. 統(tǒng)計在業(yè)務科工作且在1973年或1967年出生的員工人數(shù)和平均工資select count(*) 人數(shù),avg(salary) 平均工資 from Employee where department=業(yè)務科 and (year(birthday)=1973 or year(birthday)=1967)圖1-13實驗二1. 找出同一天進入公司工作的員工select distinct a.employeeNo,a.employeeName,a.hireDate from Employee a,Employee b where a.employeeNo!=b.employeeNo and a.hireDate=b.hireDate圖2-12. 查找與“陳詩杰”在同一個單位工作的員工姓名、性別、部門和職務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(b.salary) from Employee b) 圖2-34. 查找有銷售記錄的客戶編號名稱和訂單總額 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 圖2-45.查詢沒有訂購商品的客戶編號和客戶名稱 SELECT a.customerNo,customerNameFROM Customer aWHERE a.customerNo NOT IN (SELECT customerNo FROM OrderMaster )圖2-56.使用子查詢查找32M DRAM的銷售情況要求顯示相應的銷售員的姓名、性別、銷售日期銷售數(shù)量和金額其中性別用“男”和“女”表示 select employeeName,case sex when M then 男 when F then 女 end as sex, b.orderDate,c.quantity 銷售數(shù)量,c.quantity*c.price 金額from Employee a,OrderMaster b,OrderDetail c where a.employeeNo=b.salerNo and b.orderNo=c.orderNo and ductNo in(select ductNo from OrderMaster 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 OrderDetail group by orderNo)b) 圖2-78.在訂單主表中查詢訂單金額大于“E2005002業(yè)務員在2008-1-9這天所接的任一張訂單的金額”的所有訂單信息。 select * from OrderMaster where orderSumany(select orderSum from OrderMaster where salerNo=E2005002 and orderDate=2008-1-9) 圖2-89.查詢單價高于400元的商品編號商品名稱訂貨數(shù)量和訂貨單價。 select ductNo,ductName,sum(b.quantity)訂貨數(shù)量,b.price from Product a,OrderDetail b where ductPrice400 and ductNo=ductNo group by ductNo,ductName,b.price 圖2-910.分別使用左外連接、右外連接、完整外部連接查詢單價高于400元的商品編號、商品名稱、訂貨數(shù)量和訂貨單價并分析比較檢索的結果。 select ductNo,ductName,sum(b.quantity)訂貨數(shù)量,b.price from Product a left outer join OrderDetail b on ductPrice400 and ductNo=ductNo group by ductNo,ductName,b.price select ductNo,ductName,sum(b.quantity)訂貨數(shù)量,b.price from Product a right outer join OrderDetail b on ductPrice400 and ductNo=ductNo group by ductNo,ductName,b.price select ductNo,ductName,sum(b.quantity)訂貨數(shù)量,b.price from Product a full outer join OrderDetail b on ductPrice400 and ductNo=ductNo group by ductNo,ductName,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) order by a.customerno,ordersum desc 12.查找每個員工的銷售記錄要求顯示銷售員的編號、姓名、性別、商品名稱、數(shù)量、單價、金額和銷售日期其中性別使用“男”和“女”表示日期使用yyyy-mm-dd格式顯示。 select a.employeeNo,a.employeeName,case sex whenFthen女 whenMthen男 End sex,ductName,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 ductNo=ductNo and c.orderNo=d.orderNo 圖2-1213.查詢16M DRAM的銷售情況要求顯示相應的銷售員的姓名、性別、銷售日期、銷售數(shù)量和金額、其中性別用“男”“女”表示。 select a.employeeName,case sex whenFthen女 whenMthen男 end as 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 ductNo=ductNo and ductName=16M DRAM 圖2-1314.找出公司男業(yè)務員所接且訂單金額超過2000的訂單號及訂單金額。 select b.orderNo,b.orderSum from Employee a,OrderMaster b where a.employeeNo=b.salerNo and sex=M and b.orderSum2000 圖2-1415.查詢每種商品的總銷售數(shù)量及總銷售金額要求顯示出商品編號、商品名稱、總數(shù)量及總金額，并按商品號從小到大排列。select ductno 商品編號,productname 商品稱,sum(quantity)總銷售數(shù)量,sum(quantity*price) 總銷售金額from product a,orderdetail b where ductno=ductno group by ductno,productname order by ductno圖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.salerNo and b.orderNo=c.orderNogroup by a.employeeNo,a.employeeNamehaving sum(quantity*price)5000圖3-35. 查詢訂單中所訂購的商品數(shù)量沒有超過個的客戶編號和客戶名稱。SELECT a.CustomerNo,CustomerNameFROM Customer aWHERE a.CustomerNo IN (SELECT CustomerNoFROM OrderMaster b,OrderDetail cWHERE b.orderNo=c.orderNo GROUP BY CustomerNoHAVING sum(quantity)=3)GROUP BY a.CustomerNo,CustomerName,b.ProductNo, ProductName,quantityORDER BY a.CustomerNo,sum DESC圖3-79. 求每位客戶訂購的每種商品的總數(shù)量及平均單價，并按客戶號、商品號從小到大排列。 SELECT customerNo,productNo,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 where orderNo=200803010001 and not exists