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

1、4. 2對(duì)于教學(xué)數(shù)據(jù)庫的三個(gè)基本表S ( SNO,SNAME,AGE,SEX,SDEP) T SC(SNO,CNO,GRAD) EC ( CNO,CNAME, CDEPT,TNAM) E試用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)。參考 答案：SELECT CNO, CNAMEFROMCWHERE TNAME二

2、，LILT;SELECT SNO, SNAME FROM SWHERE AGE> 23 AND SE 冷'M'SELECT SNAME (聯(lián)接查詢方式)FROM S, SC, CWHERE S.SNO=SC.SNO AND SC.CNO=C.CNO AND SEX=' F' ANDTNAME=' LIU '或：SELECT SNAME (嵌套查詢方式)FROM S WHERE SE 冷'F'AND SNO IN(SELECT SNO FROM SC WHERE CNO IN (SELECT CNO FROM C WHERE

3、TNAME' = LIU ');或：SELECT SNAME (存在量詞方式)FROM S WHERE SEW' F'AND EXISTS (SELECT * FROM SC WHERESC.SNO=S.SNOAND EXISTS(SELECT * FROM CWHERE C.CNO=SC.CNOANDTNAME=' LIU ');SELECT CNOFROMCWHERE NOT EXISTS(SELECT *FROM S,SCWHERE S.SNO=SC.SNO AND SC.CNO=C.CNOANDSNAME=' WANG')

4、;或：SELECT CNOFROMCWHERE CNO NOTIN(SELECT SC.CNOFROM S,SCWHERE S.SNO=SC.SNOAND SNAME=' WANG，);或：SELECT CNO FROMCWHERE 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.CNOoY.CNO;或：SELECT SNO

5、FROM SCGROUP BY SNO HAVING COUNT(CNO)>=2;SELECT CNO,CNAMEFROMCWHERE NOT EXISTS(SELECT *FROMSWHERE NOT EXISTS(SELECT *FORM SCWHERE SC.SNO=S.SNO AND SC.CNO=C.CNO);SELECT DISTINCT SNOFROM SC AS XWHERE NOT EXISTS(SELECT *FORMCWHERE TNAME'= LIU 'AND NOT EXISTS(SELECT *FROM SC AS YWHERE Y.SNO=X

6、.SNO AND Y.CNO=C.CNO);4. 3設(shè)有兩個(gè)基本表R (A, B, C)和S(D, E, F),試用SQL查詢語句表達(dá)下列關(guān)系代數(shù)表達(dá) 式：SELECT * FROM R WHERE B=17;SELECT * FROM R,S;SELECT A,F FROM R,S WHERE C=D;4、4設(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.

7、 (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.B, S.CFROM R,SWHERE R.B=S.B4.6試用SQL查詢語句表達(dá)下列對(duì)教學(xué)數(shù)據(jù)庫中三個(gè)基本表SSCC的查詢：1、統(tǒng)計(jì)有學(xué)生選修的課程門數(shù)。2、求選修C4課程的學(xué)生的平均年齡。3、求LIU老師所授課程的每門課程的學(xué)生平均成績。4、統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)(超過10人的課程才統(tǒng)計(jì))。要求輸出課程號(hào)和選修人數(shù),查詢結(jié)果按人數(shù)降序排列,若人數(shù)相同,

8、按課程號(hào)升序排列。5、檢索學(xué)號(hào)比WANG同學(xué)大，而年齡比他小的學(xué)生姓名。6、檢索姓名以WANG打頭的所有學(xué)生的姓名和年齡。7、在SC中檢索成績?yōu)榭罩档膶W(xué)生學(xué)號(hào)和課程號(hào)。8、求年齡大于女同學(xué)平均年齡的男學(xué)生姓名和年齡。9、求年齡大于所有女同學(xué)年齡的男學(xué)生姓名和年齡。參考答案：SELECT COUNT ( DISTINCT CNO)FROM SC；SELECT AVG( AGE)FROM S, SCWHERE S.SNO=SC.SNO AND CNO'=C4'SELECT SC.CNO, CNAME, AVG(GRADE)FROM SC,CWHERE SC.CNO=C.CNO AN

9、D TNAME'= LIU 'GROUP BY SC.CNO;SELECT CNO,COUNT(SNO)FROM SCGROUP BY CNOHAVING COUNT(*)>10ORDER BY 2 DESC, 1;SELECT X.SNAMEFROM S AS X,S AS YWHERE Y.SNAME' = WANG'AND X.SNO>Y.SNO AND X.AGEvY.AGE;或：SELECT SNAMEFROM SWHERE SNO>(SELECT SNOFROMSWHERE SNAME=' WANG')AND AGE

10、<(SELECT AGEFROMSWHERE SNAME=' WANG');(此處要求學(xué)生姓名不重復(fù))SELECT SNAME, AGE FROM SWHERE SNAME LIKE 'WANG%'SELECT SNO, CNOFROM SCWHERE GRADE IS NULL；SELECT SNAME , AGEFROM SWHERE SE 冷'M 'AND AGEX SELECT AVG (AGE)FROM SWHERE SE 冷'F)SELECT SNAME, AGEFROMSWHERE SE 冷'M 'AN

11、D AGE> ALL ( SELECT AGE FROM SWHERE SE 冷'F'或：把上述紅色部分改為：AGEX SELECT MAX( AGE)4. 7試用SQL更新語句表達(dá)對(duì)教學(xué)數(shù)據(jù)庫中三個(gè)基本表SSCC的各個(gè)更新操作：1、往基本表S中插入一個(gè)學(xué)生元組('S9'," WU', 18) o2、在基本表S中檢索每一門課程成績都大于等于80分的學(xué)生學(xué)號(hào)、姓名和性別，并3、在基本表SC中刪除尚無成績的選課元組。4、把WANG同學(xué)的學(xué)習(xí)選課和成績?nèi)縿h去。5、把選修MATHS課不及格的成績?nèi)臑榭罩怠?、把低于總平均成績的女同學(xué)成績提高5

12、%o7、在基本表SC中修改C4課程的成績，若成績小于等于75分時(shí)提高5 %,若成績大于75分時(shí)提高4% (用兩個(gè)UPDATE語句實(shí)現(xiàn))。參考答案：INSERT INTO S ( SNO, SNAME, AGE) 把檢索到的值送往另一個(gè)已存在的基本表STUDENT (SNO, SNAME, SEX)VALUES( 'S9', 4WU', 18)；INSERT INTO STUDENT SELECT SNO, SNAME, SEXFROM SWHERE 80<= ALL ( SELECT GRADE FROM SCWHERE SNO= S.SNO;DELETE FRO

13、M SCWHERE GRADE IS NULL;DELETE FROM SCWHERE SNO INC SELECT SNOFROM S WHERE SNAME=' WANG'UPDATE SCSET GRADE= NULLWHERE GRADE < 60ANDCNOINC SELECT CNOFROM C WHERE CNAME=' MATHS );UPDATE SCSET GRADE= GRADE* 1.05WHERE SNO INC SELECT SNOFROMSWHERE SEW' F'ANDGRADE<( SELECT AVG (G

14、RADE)FROM SC);UPDATE SCSET GRADE= GRADE* 1.04WHERE CNO=，C4* AND GRADE> 75;UPDATE SCSET GRADE= GRADE* 1.05WHERE CNO=' C4" AND GRADE<= 75;注：這兩個(gè)語句順序不能顛倒。4. 8假設(shè)某“倉庫管理”關(guān)系模型有下列五個(gè)關(guān)系模式： 零件 PART( PNO, PNAME, COLOR, WEIGHT) 項(xiàng)目 PROJEC(TJNO, JNAME, DATE)供應(yīng)商 SUPPLIER(SNO, SNAME, SADDR)供應(yīng) P_P( JNO,

15、 PNO, TOTAL)采購 P_S(PNO, SNO, QUANTITY)試用SQL DDL語句定義上述五個(gè)基本表，并說明主鍵和外鍵。 試將PROJECT P_P、PART三個(gè)基本表的自然聯(lián)接定義為一個(gè)視圖VIEW1,PART,P_S SUPPLIERS個(gè)基本表的自然聯(lián)接定義為一個(gè)視圖VIEW2。試在上述兩個(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 FLO

16、AT(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 CHAR(12) NOT NULL,SADDR VARCHAR(30),PRIMARY KEY(SNO);CREATE TABLE P_P(JNOCHAR(6),PNOCHAR(6),TOTALINTEGER,PRIMARY KEY(JNO,PNO)FOREIGN KEY(JNO) REFERENCES

17、PROJECT(JNO),FOREIGN KEY(PNO) REFERENCESPART(PNO);CREATE TABLE P_S(PNOCHAR(6),SNOCHAR(6),QUANTITY INTEGER,PRIMARY KEY(PNO.SNO)FOREIGN KEY(PNO) REFERENCESPART(PNO), FOREIGN KEY(SNO)REFERENCESSUPPLIER(SNO); CREATE VIEW VIEW1AS SELECT A.JNO,JNAME,DATE,C.PNO,PNAME,COLOR, WEIGHT.TOTALFROM PROJECT A, P_P

18、B, PARTCWHERE A.JNO=B.JNO AND B.PNO=C.PNO;CREATE VIEW VIEW2AS SELECT A.PNO, PNAME,COLOR,WEIGHT,C.SNO,SNAME, SADDR,QUANTITYFROM PART A, P_S B,SUPPLIER C WHERE A.PNO=B.PNO AND B.SNO=C.SNO;i. SELECT PNO,PNAME FROM VIEW2WHERE SADDR LIKE '上海 %'ii. SELECT SNO,SNAMEFROM VIEW1.VIEW2WHERE VIEW1.PNO=VIEW2.PNO AND JNO=' J4'4. 9對(duì)于教學(xué)數(shù)據(jù)庫中基本表SC已建立下列視圖：CREATE VIEW S_GRADE( S, C_NUM, AVG_GRADE AS SELECT SN, OCOUNT(CNO), AVG (GRADE) FROM SCGROUP BY SNO；試判斷下列查詢和更新是否允許執(zhí)行。若允許,寫出轉(zhuǎn)換到基本 表 SELECT * FROM S_GRADESC上的相應(yīng)操作: SELECT SNO, C_NUM RROM S_GRA