《數(shù)據(jù)庫》第4章習(xí)題答案

1、42 對(duì)于教學(xué)數(shù)據(jù)庫的三個(gè)基本表 S（SNO,SNAME,AGE,SEX,SDEPT） SC（SNO,CNO,GRADE） C（CNO,CNAME，CDEPT,TNAME） 試用SQL的查詢語句表達(dá)下列查詢： 檢索LIU老師所授課程的課程號(hào)和課程名。 檢索年齡大于23歲的男學(xué)生的學(xué)號(hào)和姓名。 檢索至少選修LIU老師所授課程中一門課程的女學(xué)生姓名。 檢索WANG同學(xué)不學(xué)的課程的課程號(hào)。 檢索至少選修兩門課程的學(xué)生學(xué)號(hào)。 檢索全部學(xué)生都選修的課程的課程號(hào)與課程名。 檢索選修課程包含LIU老師所授(全部)課程的學(xué)生學(xué)號(hào)。參考答案： SELECTCNO，CNAMEFROMCWHERETNAMELIU;

2、 SELECTSNO，SNAMEFROMSWHEREAGE23ANDSEXM; SELECTSNAME（聯(lián)接查詢方式）FROMS，SC，CWHERES.SNO=SC.SNO AND SC.CNO=C.CNOAND SEX=F AND TNAME=LIU;或：SELECT SNAME (嵌套查詢方式)FROMSWHERESEXF AND SNO IN(SELECT SNO FROM SCWHERE CNO IN (SELECT CNO FROM C WHERE TNAME=LIU);或：SELECT SNAME (存在量詞方式)FROMSWHERESEXFANDEXISTS（SELECTFROM

3、SCWHERESC.SNO=S.SNO AND EXISTS(SELECT * FROM C WHERE C.CNO=SC.CNO AND TNAME=LIU); SELECT CNOFROM CWHERE NOT EXISTS(SELECT * FROM S,SC WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO AND SNAME=WANG);或：SELECT CNOFROM C WHERE CNO NOT IN (SELECT SC.CNO FROM S,SC WHERE S.SNO=SC.SNO AND SNAME=WANG);或：SELECT CNO FROM

4、C WHERE CNO NOT IN (SELECT CNO FROM SC WHERE SNO IN (SELECT SNO FROM S WHERE SNAME=WANG); SELECT DISTINCT X.SNOFROM SC AS X,SC AS YWHERE X.SNO=Y.SNO AND X.CNOY.CNO;或：SELECT SNOFROM SCGROUP BY SNO HAVING COUNT(CNO)=2; SELECT CNO,CNAMEFROM CWHERE NOT EXISTS(SELECT * FROM SWHERE NOT EXISTS (SELECT * FO

5、RM SC WHERE SC.SNO=S.SNO AND SC.CNO=C.CNO); SELECT DISTINCT SNOFROM SC AS XWHERE NOT EXISTS(SELECT * FORM CWHERE TNAME=LIU AND NOT EXISTS(SELECT * FROM SC AS Y WHERE Y.SNO=X.SNO AND Y.CNO=C.CNO);43 設(shè)有兩個(gè)基本表R（A，B，C）和S（D，E，F(xiàn)），試用SQL查詢語句表達(dá)下列關(guān)系代數(shù)表達(dá)式： SELECT A FROM R; SELECT * FROM R WHERE B=17; SELECT * F

6、ROM R,S; SELECT A,F FROM R,S WHERE C=D; 44 設(shè)有兩個(gè)基本表R（A，B，C）和S（A，B，C），試用SQL查詢語句表達(dá)下列關(guān)系代數(shù)表達(dá)式： 1.(SELECT * FROM R) UNION (SELECT * FROM S);2.(SELECT * FROM R) INTERSECT(SELECT * FROM S); 3.(SELECT * FROM R)EXCEPT(SELECT * FROM S); 4.(SELECT A,B FROM R)NATURAL INNER JOIN(SELECT B,C FROM S);或：SELECT R.A, R

7、.B, S.C FROM R,S WHERE R.B=S.B4.6 試用 SQL查詢語句表達(dá)下列對(duì)教學(xué)數(shù)據(jù)庫中三個(gè)基本表 S、SC、C的查詢： 1、統(tǒng)計(jì)有學(xué)生選修的課程門數(shù)。2、求選修 C4課程的學(xué)生的平均年齡。 3、求 LIU老師所授課程的每門課程的學(xué)生平均成績(jī)。 4、統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)（超過10人的課程才統(tǒng)計(jì)）。要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列。5、檢索學(xué)號(hào)比WANG同學(xué)大，而年齡比他小的學(xué)生姓名。6、檢索姓名以WANG打頭的所有學(xué)生的姓名和年齡。7、在SC中檢索成績(jī)?yōu)榭罩档膶W(xué)生學(xué)號(hào)和課程號(hào)。8、求年齡大于女同學(xué)平均年齡的男學(xué)生姓名和年

8、齡。9、求年齡大于所有女同學(xué)年齡的男學(xué)生姓名和年齡。參考答案：1、 SELECTCOUNT（DISTINCTCNO）FROMSC；2、 SELECTAVG（AGE）FROMS，SCWHERES.SNO=SC.SNO AND CNO=C4;3、 SELECTSC.CNO, CNAME, AVG(GRADE)FROM SC,CWHERE SC.CNO=C.CNO AND TNAME=LIUGROUP BY SC.CNO;4、 SELECT CNO,COUNT(SNO)FROM SC GROUP BY CNOHAVING COUNT(*)10ORDER BY 2 DESC, 1;5、 SELECT

9、X.SNAMEFROM S AS X,S AS YWHERE Y.SNAME=WANGAND X.SNOY.SNO AND X.AGE(SELECT SNO FROM S WHERE SNAME=WANG)AND AGE(SELECT AGE FROM S WHERE SNAME=WANG); (此處要求學(xué)生姓名不重復(fù))6、 SELECTSNAME，AGEFROMSWHERESNAMELIKEWANG%7、 SELECTSNO，CNOFROMSCWHEREGRADEISNULL；8、 SELECTSNAME，AGEFROMSWHERESEXMANDAGE（SELECTAVG（AGE）FROMS

10、WHERESEXF）9、 SELECTSNAME，AGEFROMSWHERESEXMANDAGEALL（SELECTAGEFROMSWHERESEXF）；或：把上述紅色部分改為：AGE（SELECTMAX（AGE）47 試用SQL更新語句表達(dá)對(duì)教學(xué)數(shù)據(jù)庫中三個(gè)基本表S、SC、 C的各個(gè)更新操作： 1、往基本表S中插人一個(gè)學(xué)生元組（S9，WU，18）。 2、在基本表S中檢索每一門課程成績(jī)都大于等于80分的學(xué)生學(xué)號(hào)、姓名和性別，并把檢索到的值送往另一個(gè)已存在的基本表STUDENT（SNO，SNAME，SEX）。 3、在基本表SC中刪除尚無成績(jī)的選課元組。 4、把WANG同學(xué)的學(xué)習(xí)選課和成績(jī)?nèi)縿h去

11、。 5、把選修MATHS課不及格的成績(jī)?nèi)臑榭罩怠?6、把低于總平均成績(jī)的女同學(xué)成績(jī)提高5。 7、在基本表SC中修改 C4課程的成績(jī)，若成績(jī)小于等于75分時(shí)提高 5，若成績(jī)大于 75分時(shí)提高4（用兩個(gè)UPDATE語句實(shí)現(xiàn)）。參考答案：1、 INSERTINTOS（SNO，SNAME，AGE）VALUES（S9，WU，18）；2、 INSERTINTOSTUDENTSELECTSNO，SNAME，SEXFROMSWHERE80ALL（SELECTGRADEFROMSCWHERESNOS.SNO）；3、 DELETEFROMSCWHEREGRADE IS NULL;4、 DELETEFROMSCW

12、HERESNOIN（SELECTSNOFROMSWHERESNAMEWANG）；5、 UPDATESCSETGRADENULLWHEREGRADE60ANDCNOIN（SELECTCNOFROM CWHERECNAMEMATHS）；6、 UPDATESCSETGRADEGRADE1.05WHERESNOIN（SELECTSNOFROMSWHERESEXF）ANDGRADE（SELECTAVG（GRADE）FROMSC）；7、 UPDATESCSETGRADEGRADE1.04WHERECNOC4ANDGRADE75；UPDATESCSETGRADEGRADE1.05WHERECNOC4ANDG

13、RADE75；注：這兩個(gè)語句順序不能顛倒。 48 假設(shè)某“倉庫管理”關(guān)系模型有下列五個(gè)關(guān)系模式： 零件PART（PNO，PNAME，COLOR，WEIGHT） 項(xiàng)目PROJECT（JNO，JNAME，DATE） 供應(yīng)商SUPPLIER（SNO，SNAME，SADDR） 供應(yīng)P_P（JNO，PNO，TOTAL） 采購 P_S（PNO，SNO，QUANTITY） 試用 SQL DDL語句定義上述五個(gè)基本表，并說明主鍵和外鍵。 試將 PROJECT、P_P、PART三個(gè)基本表的自然聯(lián)接定義為一個(gè)視圖 VIEW1，PART,P_S、SUPPLIER三個(gè)基本表的自然聯(lián)接定義為一個(gè)視圖VIEW2。 試在上

14、述兩個(gè)視圖的基礎(chǔ)上進(jìn)行數(shù)據(jù)查詢： i 檢索上海的供應(yīng)商所供應(yīng)的零件的編號(hào)和名字。 ii檢索項(xiàng)目J4所用零件的供應(yīng)商編號(hào)和名字。參考答案： CREATE TABLE PART(PNO CHAR(6),PNAME CHAR(10) NOT NULL,COLOR CHAR(6),WEIGHT FLOAT(6),PRIMARY KEY(PNO);CREATE TABLE PROJECT (JNO CHAR(6), JNAME CHAR(12) NOT NULL, DATE DATE, PRIMARY KEY(JNO);CREATE TABLE SUPPLIER (SNO CHAR(8), SNAME

15、CHAR(12) NOT NULL, SADDR VARCHAR(30), PRIMARY KEY(SNO);CREATE TABLE P_P (JNO CHAR(6), PNO CHAR(6), TOTAL INTEGER, PRIMARY KEY(JNO,PNO) FOREIGN KEY(JNO) REFERENCES PROJECT(JNO), FOREIGN KEY(PNO) REFERENCES PART(PNO);CREATE TABLE P_S (PNO CHAR(6), SNO CHAR(6), QUANTITY INTEGER, PRIMARY KEY(PNO,SNO) FO

16、REIGN KEY(PNO) REFERENCES PART(PNO), FOREIGN KEY(SNO) REFERENCES SUPPLIER(SNO); CREATE VIEW VIEW1AS SELECT A.JNO,JNAME,DATE,C.PNO,PNAME,COLOR, WEIGHT,TOTAL FROM PROJECT A, P_P B, PART C WHERE A.JNO=B.JNO AND B.PNO=C.PNO; CREATE VIEW VIEW2AS SELECT A.PNO, PNAME,COLOR,WEIGHT,C.SNO,SNAME, SADDR,QUANTIT

17、Y FROM PART A, P_S B,SUPPLIER C WHERE A.PNO=B.PNO AND B.SNO=C.SNO; i. SELECT PNO,PNAMEFROM VIEW2WHERE SADDR LIKE 上海%; ii. SELECT SNO,SNAMEFROM VIEW1,VIEW2WHERE VIEW1.PNO=VIEW2.PNO AND JNO=J4;49 對(duì)于教學(xué)數(shù)據(jù)庫中基本表SC，已建立下列視圖： CREATE VIEW SGRADE（S，C_NUM，AVGGRADE） AS SELECT SNO，COUNT（CNO），AVG（GRADE） FROM SC GR

18、OUP BY SNO； 試判斷下列查詢和更新是否允許執(zhí)行。若允許，寫出轉(zhuǎn)換到基本表SC上的相應(yīng)操作： SELECT * FROM S_GRADE SELECT SNO，C_NUMRROM S_GRADE WHERE AVG_GRADE80； SELECT SNO，AVGGRADE FROM S_GRADE WHERE C_NUM （SELECT C_NUM FROM S_GRADE WHERE SNOS4）; UPDATE S_GRADE SET C_NUMC_NUM 1 WRERE SNOS4； DELETE FROM S_GRADE WHERE C_NUM4； 參考答案：允許查詢。相應(yīng)操作如下：SELECTSNO，COUNT（CNO）ASCNUM，AVG（GRADE）ASAVGGRADEFROMSCGROUPBYSNO； 允許查詢。相應(yīng)操作如下：SELECT SNO，COUNT（CNO）ASC_NUMRROM SC GROUPBYSNOHAVIN