數(shù)據(jù)庫測(cè)試答案answer

1、數(shù)據(jù)庫過程測(cè)試1、 以下圖的數(shù)據(jù)庫為例，用SQL完成以下檢索： （3分/ 題）倉庫 職工 訂購(gòu)單供應(yīng)商1)檢索在北京的供應(yīng)商的名稱。SELECT * FROM 供應(yīng)商 WHERE 地址=北京2)檢索發(fā)給供應(yīng)商S6的訂購(gòu)單號(hào)。SELECT 訂購(gòu)單號(hào) FROM 訂購(gòu)單 WHERE 供應(yīng)商號(hào)=S63)檢索出職工E6發(fā)給供應(yīng)商S6的訂購(gòu)單信息。SELECT * FROM 訂購(gòu)單 WHERE 供應(yīng)商號(hào)=S6 AND 職工號(hào)=E64)檢索出向供應(yīng)商S3發(fā)過訂購(gòu)單的職工的職工號(hào)和倉庫號(hào)。SELECT 職工號(hào),倉庫號(hào) FROM 職工 WHERE 職工號(hào) IN( SELECT 職工號(hào) FROM 訂購(gòu)單 WHER

2、E 供應(yīng)商號(hào)=S3 )5)檢索出目前與S3供應(yīng)商沒有聯(lián)系的職工信息。SELECT * FROM 職工 WHERE 職工號(hào) NOT IN( SELECT 職工號(hào) FROM 訂購(gòu)單 WHERE 供應(yīng)商號(hào)=S3 )6)檢索出目前沒有任何訂購(gòu)單的供應(yīng)商信息。SELECT * FROM 供應(yīng)商 WHERE NOT EXISTS( SELECT * FROM 訂購(gòu)單 WHERE 供應(yīng)商號(hào)=供應(yīng)商.供應(yīng)商號(hào) )7)檢索出和職工E1、E3都有聯(lián)系的北京的供應(yīng)商信息。SELECT * FROM 供應(yīng)商 WHERE 供應(yīng)商號(hào) IN( SELECT 供應(yīng)商號(hào) FROM 訂購(gòu)單 WHERE 職工號(hào)=E1 )AND 供

3、應(yīng)商號(hào) IN( SELECT 供應(yīng)商號(hào) FROM 訂購(gòu)單 WHERE 職工號(hào)=E3 )8)檢索出目前和華通電子公司有業(yè)務(wù)聯(lián)系的每個(gè)職工的工資。SELECT 職工號(hào),工資 FROM 職工 WHERE 職工號(hào) IN(SELECT 職工號(hào) FROM 訂購(gòu)單 WHERE 供應(yīng)商號(hào) IN(SELECT 供應(yīng)商號(hào) FROM 供應(yīng)商 WHERE 供應(yīng)商名=華通電子公司)9)檢索出與工資在1220元以下的職工沒有聯(lián)系的供應(yīng)商的名稱。SELECT 供應(yīng)商名 FROM 供應(yīng)商 WHERE 供應(yīng)商號(hào) IN(SELECT 供應(yīng)商號(hào) FROM 訂購(gòu)單 WHERE 職工號(hào) NOT IN(SELECT 職工號(hào) FROM 職

4、工 WHERE 工資 < 1220)10)檢索出向S4供應(yīng)商發(fā)出訂購(gòu)單的倉庫所在的城市。SELECT 城市 FROM 倉庫 WHERE 倉庫號(hào) IN(SELECT 倉庫號(hào) FROM 職工 WHERE 職工號(hào) IN(SELECT 職工號(hào) FROM 訂購(gòu)單 WHERE 供應(yīng)商號(hào)=S4)11)檢索出在上海工作并且向S6供應(yīng)商發(fā)出了訂購(gòu)單的職工號(hào)。SELECT 職工號(hào) FROM 職工 WHERE 倉庫號(hào) IN(SELECT 倉庫號(hào) FROM 倉庫 WHERE 城市=上海) AND 職工號(hào) IN(SELECT 職工號(hào) FROM 訂購(gòu)單 WHERE 供應(yīng)商號(hào)=S6)12)檢索出在廣州工作并且只向S6

5、供應(yīng)商發(fā)出了訂購(gòu)單的職工號(hào)。SELECT 職工號(hào) FROM 職工 WHERE 倉庫號(hào) IN(SELECT 倉庫號(hào) FROM 倉庫 WHERE 城市=廣州) AND 職工號(hào) IN(SELECT 職工號(hào) FROM 訂購(gòu)單 WHERE 供應(yīng)商號(hào)=S6) AND 職工號(hào) NOT IN(SELECT 職工號(hào) FROM 訂購(gòu)單 WHERE 供應(yīng)商號(hào)!=S6)13)檢索出由工資多于1230元的職工向北京的供應(yīng)商發(fā)出的訂購(gòu)單號(hào)。SELECT 訂購(gòu)單號(hào) FROM 訂購(gòu)單 WHERE 職工號(hào) IN(SELECT 職工號(hào) FROM 職工 WHERE 工資>1230) AND 供應(yīng)商號(hào) IN(SELECT 供應(yīng)

6、商號(hào) FROM 供應(yīng)商 WHERE 地址=北京)14)檢索出倉庫的個(gè)數(shù)。SELECT COUNT(*) FROM 倉庫15)檢索出有最大面積的倉庫信息。SELECT * FROM 倉庫 WHERE 面積=(SELECT MAX(面積) FROM 倉庫)16)檢索出所有倉庫的平均面積。SELECT AVG(面積) FROM 倉庫17)檢索出向S4供應(yīng)商發(fā)出訂購(gòu)單的那些倉庫的平均面積。SELECT AVG(面積) FROM 倉庫 WHERE 倉庫號(hào) IN(SELECT 倉庫號(hào) FROM 職工 WHERE 職工號(hào) IN(SELECT 職工號(hào) FROM 訂購(gòu)單 WHERE 供應(yīng)商號(hào)=S4)18)檢索出

7、每個(gè)倉庫中工資多于1220元的職工個(gè)數(shù)。SELECT 倉庫號(hào),COUNT(*) FROM 職工 WHERE 工資>1220 GROUP BY 倉庫號(hào)19)檢索出和面積最小的倉庫有聯(lián)系的供應(yīng)商的個(gè)數(shù)。SELECT COUNT(*) FROM 供應(yīng)商 WHERE 供應(yīng)商號(hào) IN(SELECT 供應(yīng)商號(hào) FROM 訂購(gòu)單 WHERE 職工號(hào) IN(SELECT 職工號(hào) FROM 職工 WHERE 倉庫號(hào) IN(SELECT 倉庫號(hào) FROM 倉庫 WHERE 面積=(SELECT MIN(面積) FROM 倉庫)20)檢索出工資低于本倉庫平均工資的職工信息。SELECT * FROM 職工 o

8、ut WHERE 工資<(SELECT AVG(工資) FROM 職工 inne WHERE 倉庫號(hào)=out.倉庫號(hào))2. 以上圖的數(shù)據(jù)庫為例，用SQL語句完成以下更新操作，并討論數(shù)據(jù)完整性約束對(duì)這些操作的影響：（4分/ 題）1) 插入一個(gè)新的訂購(gòu)單元組，其中職工號(hào)為E6，訂購(gòu)單號(hào)為OR99，其他列為空。INSERT INTO 訂購(gòu)單(職工號(hào), 訂購(gòu)單號(hào)) VALUES(E6, OR99)2) 刪除目前沒有任何訂購(gòu)單的供應(yīng)商。DELETE FROM 供應(yīng)商 WHERE 供應(yīng)商號(hào) NOT IN(SELECT 供應(yīng)商號(hào) FROM 訂購(gòu)單 WHERE 供應(yīng)商號(hào) IS NOT NULL)3) 刪

9、除由在上海倉庫工作的職工發(fā)出的所有訂購(gòu)單。DELETE FROM 訂購(gòu)單 WHERE 職工號(hào) IN(SELECT 職工號(hào) FROM 職工 WHERE 倉庫號(hào) IN(SELECT 倉庫號(hào) FROM 倉庫 WHERE 城市=上海)4) 北京的所有倉庫增加100m2的面積。UPDATE 倉庫 SET 面積=面積+100 WHERE 城市=北京5) 給低于所有職工平均工資的職工提高5%的工資。UPDATE 職工 SET 工資=工資*1.05 WHERE 工資<(SELECT AVG(工資) FROM 職工)3. 在第1題的數(shù)據(jù)庫中建立一個(gè)存儲(chǔ)過程，查詢工資大于某個(gè)值且在某個(gè)城市工作的職工信息；并

10、以工資大于8000元且在北京工作的職工為例執(zhí)行該存儲(chǔ)過程。（10分）CREATE PROC getemp(salary int,city char(8)ASSELECT * FROM 職工 WHERE 工資>salary and倉庫號(hào) IN(SELECT 倉庫號(hào) FROM 倉庫 WHERE 城市=city)EXECUTE getemp salary =8000, city=北京4. 在第1題的數(shù)據(jù)庫中建立一個(gè)觸發(fā)器，當(dāng)刪除倉庫記錄時(shí)同時(shí)刪除該倉庫的職工信息。（5分）CREATE TRIGGER wdel_triON 倉庫 FOR DELETEASDELETE 職工 WHERE 倉庫號(hào) IN(select 倉庫號(hào) from DELETED);5. 在第1題的數(shù)據(jù)庫中建立一個(gè)觸發(fā)器，當(dāng)插入一個(gè)職工記錄時(shí)，檢查該職工所在的倉庫是否存在，若不存在則撤消所做的插入操作。（5分）CREATE TR