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

實(shí)驗(yàn)一1-1.查詢(xún)員工的姓名、職務(wù)和薪水select employeeName,headShip,salaryfrom employee圖1-12. 查詢(xún)名字中含有“有限”的客戶(hù)姓名和所在地select CustomerName,addressfrom Customerwhere CustomerName like %有限%圖1-23. 查詢(xún)出姓“張”并且姓名的最后一個(gè)字為“梅”的員工。select *from employeewhere employeeName like 張%梅圖1-34. 查詢(xún)住址中含有上?；蚰喜呐畣T工，并顯示其姓名、所屬部門(mén)、職稱(chēng)、住址，其中性別用“男”和“女”顯示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. 查詢(xún)出職務(wù)為“職員”或職務(wù)為“科長(zhǎng)”的女員工的信息select *from employeewhere (headship=職員 or headship=科長(zhǎng)) and sex=F圖1-56. 選取編號(hào)不在“C20050001”和“C20050004”的客戶(hù)編號(hào)、客戶(hù)名稱(chēng)、客戶(hù)地址。Select *from Customerwhere CustomerNo not in ( C20050001 ,C20050004)圖1-6圖1-67. 在訂單明細(xì)表Ordermaster中挑出銷(xiāo)售金額大于等于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. 計(jì)算一共銷(xiāo)售了幾種商品SELECT COUNT(DISTINCT productno)as 種類(lèi)from orderDeta圖1-910.計(jì)算orderDetail表中每種商品的銷(xiāo)售數(shù)量、平均價(jià)格和總銷(xiāo)售量金額，并且依據(jù)銷(xiāo)售金額由大到小輸出。 SELECT productno 商品種類(lèi),count(*)quantity,avg (price)平均價(jià)格,sum(quantity*price)金額from orderDetailgroup by productnoorder by 金額desc圖1-1011. 按客戶(hù)編號(hào)統(tǒng)計(jì)每個(gè)客戶(hù)2008年2月的訂單總金額。select customerno,ordersumfrom ordermasterwhere year(orderDate)=2008 and month(orderDate)=2圖1-1112.統(tǒng)計(jì)至少銷(xiāo)售了10件以上的商品編號(hào)和銷(xiāo)售數(shù)量。select productno 商品編號(hào),quantity 商品數(shù)目from orderdetailwhere quantity=10圖1-1213. 統(tǒng)計(jì)在業(yè)務(wù)科工作且在1973年或1967年出生的員工人數(shù)和平均工資select count(*) 人數(shù),avg(salary) 平均工資 from Employee where department=業(yè)務(wù)科 and (year(birthday)=1973 or year(birthday)=1967)圖1-13實(shí)驗(yàn)二1. 找出同一天進(jìn)入公司工作的員工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. 查找與“陳詩(shī)杰”在同一個(gè)單位工作的員工姓名、性別、部門(mén)和職務(wù)select a.employeeName,a.sex,a.department,a.headShip from Employee a,Employee b where a.department=b.department and b.employeeName=陳詩(shī)杰圖2-23. 在employee表中查詢(xún)薪水超過(guò)員工平均薪水的員工信息 select * from Employee a where a.salary(select avg(b.salary) from Employee b) 圖2-34. 查找有銷(xiāo)售記錄的客戶(hù)編號(hào)名稱(chēng)和訂單總額 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.查詢(xún)沒(méi)有訂購(gòu)商品的客戶(hù)編號(hào)和客戶(hù)名稱(chēng) SELECT a.customerNo,customerNameFROM Customer aWHERE a.customerNo NOT IN (SELECT customerNo FROM OrderMaster )圖2-56.使用子查詢(xún)查找32M DRAM的銷(xiāo)售情況要求顯示相應(yīng)的銷(xiāo)售員的姓名、性別、銷(xiāo)售日期銷(xiāo)售數(shù)量和金額其中性別用“男”和“女”表示 select employeeName,case sex when M then 男 when F then 女 end as sex, b.orderDate,c.quantity 銷(xiāo)售數(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.查詢(xún)OrderMaster表中訂單金額最高的訂單號(hào)及訂單金額 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.在訂單主表中查詢(xún)訂單金額大于“E2005002業(yè)務(wù)員在2008-1-9這天所接的任一張訂單的金額”的所有訂單信息。 select * from OrderMaster where orderSumany(select orderSum from OrderMaster where salerNo=E2005002 and orderDate=2008-1-9) 圖2-89.查詢(xún)單價(jià)高于400元的商品編號(hào)商品名稱(chēng)訂貨數(shù)量和訂貨單價(jià)。 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.分別使用左外連接、右外連接、完整外部連接查詢(xún)單價(jià)高于400元的商品編號(hào)、商品名稱(chēng)、訂貨數(shù)量和訂貨單價(jià)并分析比較檢索的結(jié)果。 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.使用左外連接查找每個(gè)客戶(hù)的客戶(hù)編號(hào)、名稱(chēng)、訂貨日期、訂單金額、其中訂貨日期不顯示時(shí)間日期格式為yyyy-mm-dd按客戶(hù)編號(hào)排序同一客戶(hù)再按訂單金額降序排序輸出。 select a.customerno 客戶(hù)編號(hào),customername 客戶(hù)名稱(chēng),convert(char(10),orderdate,120)銷(xiāo)售日期,ordersum 銷(xiāo)售金額from ordermaster a left outer join customer b on (a.customerno=b.customerno) order by a.customerno,ordersum desc 12.查找每個(gè)員工的銷(xiāo)售記錄要求顯示銷(xiāo)售員的編號(hào)、姓名、性別、商品名稱(chēng)、數(shù)量、單價(jià)、金額和銷(xiāo)售日期其中性別使用“男”和“女”表示日期使用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.查詢(xún)16M DRAM的銷(xiāo)售情況要求顯示相應(yīng)的銷(xiāo)售員的姓名、性別、銷(xiāo)售日期、銷(xiāo)售數(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è)務(wù)員所接且訂單金額超過(guò)2000的訂單號(hào)及訂單金額。 select b.orderNo,b.orderSum from Employee a,OrderMaster b where a.employeeNo=b.salerNo and sex=M and b.orderSum2000 圖2-1415.查詢(xún)每種商品的總銷(xiāo)售數(shù)量及總銷(xiāo)售金額要求顯示出商品編號(hào)、商品名稱(chēng)、總數(shù)量及總金額，并按商品號(hào)從小到大排列。select ductno 商品編號(hào),productname 商品稱(chēng),sum(quantity)總銷(xiāo)售數(shù)量,sum(quantity*price) 總銷(xiāo)售金額from product a,orderdetail b where ductno=ductno group by ductno,productname order by ductno圖2-15實(shí)驗(yàn)三1. 在訂單明細(xì)表中查詢(xún)訂單金額最高的訂單。select top 1 orderNo,sum(quantity*price) orderSumfrom OrderDetailgroup by orderNoorder by orderSum desc圖3-13. 查找銷(xiāo)售總額少于5000元的銷(xiāo)售員編號(hào)、姓名和銷(xiāo)售額。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. 查詢(xún)訂單中所訂購(gòu)的商品數(shù)量沒(méi)有超過(guò)個(gè)的客戶(hù)編號(hào)和客戶(hù)名稱(chēng)。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. 求每位客戶(hù)訂購(gòu)的每種商品的總數(shù)量及平均單價(jià)，并按客戶(hù)號(hào)、商品號(hào)從小到大排列。 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. 查詢(xún)訂購(gòu)的商品至少包含了訂單“200803010001”中所訂購(gòu)商品的訂單。 SELECT *FROM OrderMaster aWHERE not exists (select * from OrderDetail y where orderNo=200803010001 and not exists