SQL語言習題及答案(好)ppt課件

1、1（第一大題）（第一大題）職工職工 E（ename，e#，bdate，addr，salary，se#，d#）部門部門 D（dname，d#，部門負責人的工號，部門負責人的工號mgre#）P（pname，p#，所在城市，所在城市city，主管部門編號，主管部門編號d#）工作工作 W（職工工號（職工工號e#，項目編號，項目編號p#，工作時間，工作時間hours）職工家屬職工家屬Depend（e#，家屬的姓名，家屬的姓名name，家屬的性別，家屬的性別sex）2（第一大題）（第一大題）1.檢索部門檢索部門Research的所有職工的姓名和家庭地址的所有職工的姓名和家庭地址 關系代數關系代數 sele

2、ct ename, addr from E, D where D.d# = E.d# and dname = Research 3（第一大題）（第一大題）2.檢索位于檢索位于Stafford的每個項目的編號、主管部門的編號及其部門負責人的姓的每個項目的編號、主管部門的編號及其部門負責人的姓名和家庭地址名和家庭地址 關系代數關系代數Select P.p#, P.d#, ename, addrfrom P, D, Ewhere P.d# = D.d# and D.mgre# = E.e# and city= Stafford4（第一大題）（第一大題） 3.檢索參與了檢索參與了5號部門的所有項目的職

3、工的姓名號部門的所有項目的職工的姓名 關系代數關系代數select enamefrom EWhere not exist ( Select * from PWhere P.d# = 5 and p# not in ( Select p# from WWhere W.e# = E.e# ) )5（第一大題）（第一大題） 4.檢索職工檢索職工Smith所參與的或者主管部門為該職工所在部門的項目的編號所參與的或者主管部門為該職工所在部門的項目的編號 關系代數關系代數( select pFrom E, Wwhere ename = Smith and E.e# = W.e# ) UNION( sele

4、ct p#from E, Pwhere ename = Smith and E.d# = P.d# )6（第一大題）（第一大題） 5.檢索擁有兩個或兩個以上家屬的職工的姓名檢索擁有兩個或兩個以上家屬的職工的姓名 關系代數關系代數(兩種寫法）兩種寫法） 答案答案1： Select enamefrom E, Depend D1, Depend D2Where E.e# = D1.e# and D1.e# = D2.e# and D1.name D2.name 答案答案2： select enamefrom E, DependWhere E.e# = Depend.e#group by E.e#,

5、E.enamehaving Count(*) = 2 7（第一大題）（第一大題） 6.檢索至少有一個家屬且具有管理員身份的職工的姓名檢索至少有一個家屬且具有管理員身份的職工的姓名 關系代數關系代數 答案答案1： select E1.enamefrom E E1, E E2, Depend D1where E1.e# = D1.e# and E1.e# = E2.se# 答案答案2： select E.enamefrom E , Depend where E.e# = Depend.e# and E.e# in (select E.se# from E) 8（第一大題）（第一大題） 7.檢索不帶

6、家屬的職工的姓名檢索不帶家屬的職工的姓名 關系代數關系代數 答案答案select ename from Ewhere e# NOT IN ( select e# from Depend )9（第一大題）（第一大題） 8.檢索工資收入最高的職工的姓名檢索工資收入最高的職工的姓名 關系代數關系代數Select enamefrom Ewhere salary IN (select max(salary) From E )10（第二大題）（第二大題） 顧客顧客Customers (編號編號cid, 姓名姓名cname, 城市城市city, 銷售折扣銷售折扣discnt) 經銷商經銷商Agents (編

7、號編號aid, 名稱名稱aname, 城市城市city, 傭金百分比傭金百分比percent) 商品商品Products (編號編號pid, 商品名商品名pname, 城市城市city, 庫存數量庫存數量quantity, 單價單價price) 訂單訂單Orders (編號編號ordno, 訂購月份訂購月份month, 顧客編號顧客編號cid, 經銷商編號經銷商編號aid, 商商品編號品編號pid, 訂購數量訂購數量qty, 購買金額購買金額dollars)請用請用SQL語言來表示下述數據操作要求。語言來表示下述數據操作要求。11（第二大題）（第二大題） 關系代數關系代數Select aid,

8、 pid, sum(qty)from Ordersgroup by aid, pid1.1.檢索每個經銷商銷售每一種產品的總數量檢索每個經銷商銷售每一種產品的總數量(結果列有：經銷商的編號，產結果列有：經銷商的編號，產品的編號，銷售總數量品的編號，銷售總數量)12 關系代數關系代數Select T.cidFrom ( select cid, pid, avg(qty) as q_avgfrom Ordersgroup by cid, pid ) TGroup by T.cidHaving min(T.q_avg) = 3002.2.檢索符合下述要求的客戶的編號：在該客戶訂購過的所有商品中，每一

9、種檢索符合下述要求的客戶的編號：在該客戶訂購過的所有商品中，每一種商品的每筆訂單的訂購數量均達到或超過商品的每筆訂單的訂購數量均達到或超過30013 關系代數關系代數Select aidfrom agentswhere aid not in (select aidfrom Customers C, Orders Owhere C.cid = O.cid and C.city = Duluth)3.3.檢索沒有為居住在檢索沒有為居住在Duluth的任何客戶訂購過任何商品的經銷商的編號的任何客戶訂購過任何商品的經銷商的編號14 關系代數關系代數Select O1.aid from Orders O

10、1, Customers C1Where O1.cid = C1.cid and not exist (select * from Customers C2where C2.cid not in (select O2.cid from Orders O2where O2.aid = O1.aid and O2.pid = O1.pid)4.4.檢索為所有客戶訂購過同一種商品的經銷商的編號檢索為所有客戶訂購過同一種商品的經銷商的編號15 關系代數關系代數 答案答案Select O1.cidfrom Orders O1where O1.cid not in ( select O2.cid from

11、 Orders O2where O2.aid a03 and O2.aid a05 )5.5.檢索僅通過檢索僅通過a03和和a05兩個經銷商訂購過商品的客戶編號兩個經銷商訂購過商品的客戶編號16 關系代數關系代數Select pidfrom Products Pwhere not exist (select * from Customers Cwhere C.city = Dallas and C.cid not in ( select O.cid from Orders Owhere O.pid = P.pid ) )6.6.檢索居住在檢索居住在Dallas的所有客戶都訂購過的商品編號的所有

12、客戶都訂購過的商品編號17 關系代數關系代數Select aidfrom Agentswhere percent IN ( select max(A1.percent) from Agents A1 )7.7.檢索享有最高傭金百分比的經銷商的編號檢索享有最高傭金百分比的經銷商的編號18 關系代數關系代數 答案答案Select O1.cid, sum(O1.dollars)From Orders O1Where O1.cid not in ( select O2.cidfrom Orders O2where O2.aid a04 )Group by O1.cid8.8.檢索僅僅通過檢索僅僅通過a

13、04號經銷商訂購過商品的客戶編號，并給出每個客戶的訂號經銷商訂購過商品的客戶編號，并給出每個客戶的訂購總金額購總金額19 關系代數關系代數 答案答案Select aid, percentFrom Agents AWhere not exist ( select * from Customers Cwhere C.city = Duluth and C.cid not in ( select O.cid from Orders Owhere O.aid = A.aid)Order by percent desc9.9.檢索為居住在檢索為居住在Duluth的所有客戶訂購過商品的經銷商的編號及其傭金百分的所有客戶訂購過商品的經銷商的編號及其傭金百分比，并按照傭金百分比的降序輸出查詢結果比，并按照傭金百分比的降序輸出查詢結果20 關系代數關