數(shù)據(jù)庫原理第五章課后答案數(shù)據(jù)庫原理第五章課后答案數(shù)據(jù)庫原理第五章課后答案數(shù)據(jù)庫原理第五章課后答案編制僅供參考審核批準生效日期地址：電話：傳真:郵編:對于教學(xué)數(shù)據(jù)庫的三個基本表

學(xué)生S(S#,SNAME,AGE,SEX)

學(xué)習(xí)SC(S#,,GRADE)

課程C(C#,CNAME,TEACHER)

試用SQL的查詢語句表達下列查詢：

（1）檢索LIU老師所授課程的課程號和課程名。

SELECTC#，CNAME

FROMC

WHERETEACHER=‘LIU’

（2）檢索年齡大于23歲的男學(xué)生的學(xué)號和姓名。

SELECTS#，SNAME

FROMS

WHERE(AGE>23)AND(SEX=‘M’)

（3）檢索至少選修LIU老師所授課程中一門課程的女學(xué)生姓名。

SELECTSNAME

FROMS

WHERESEX=‘F’ANDS#IN

(SELECTS#

FROMSC

WHEREC#IN

(SELECTC#

FROMC

WHERETEACHER=‘LIU’)

NOTICE：有多種寫法，比如聯(lián)接查詢寫法：

SELECTSNAME

FROMS,SC,C

WHERESEX=‘F’AND#=#AND#=#ANDTEACHER='LIU'但上一種寫法更好一些。

（4）檢索WANG同學(xué)不學(xué)的課程的課程號。

SELECTC#

FROMC

WHEREC#NOTIN

(SELECTC#

FROMSC

WHERES#IN

(SELECTS#

FROMS

WHERESNAME='WANG'))

（5）檢索至少選修兩門課程的學(xué)生學(xué)號。

SELECTDISTINCT

FROMSCX,SCY

WHERE=AND<>

Notice:對表SC進行自連接，X，Y是SC的兩個別名。

（6）檢索全部學(xué)生都選修的課程的課程號與課程名。SELECTC#,CNAMEFROMCWHERENOTEXISTS(SELECT*FROMSWHERES#NOTIN(SELECT*FROMSCWHERE#=#))要從語義上分解：（1）選擇課程的課程號與課程名，不存在不選這門課的同學(xué)。

其中，“不選這門課的同學(xué)”可以表示為：SELECT*FROMSWHERES#NOTIN（SELECT*FROMSCWHERE#=#)或者SELECT*FROMSWHERENOTEXISTS（SELECT*FROMSCWHERE#=#AND#=#)

（7）檢索選修課程包含LIU老師所授課的學(xué)生學(xué)號。

SELECTDISTINCTS#

FROMSC

WHEREC#IN

(SELECTC#

FROMC

WHERETEACHER='LIU'))

（8）統(tǒng)計有學(xué)生選修的課程門數(shù)。

SELECTCOUNT(DISTINCTC#)FROMSC

（9）求選修C4課程的學(xué)生的平均年齡。

SELECTAVG(AGE)

FROMS

WHERES#IN

(SELECTS#

FROMSC

WHEREC#='C4')

或者，

SELECTAVG(AGE)

FROMS，SC

WHERE#=#ANDC#='004'

（10）求LIU老師所授課程的每門課程的學(xué)生平均成績。

SELECTCNAME,AVG(GRADE)

FROMSC,C

WHERE#=#ANDTEACHER='LIU'

GROUPBYC#

（11）統(tǒng)計每門課程的學(xué)生選修人數(shù)（超過10人的課程才統(tǒng)計）。要求輸出課程號和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號升序排列。

SELECTDISTINCTC#,COUNT(S#)

FROMSC

GROUPBYC#

HAVINGCOUNT(S#)>10

ORDERBY2DESC,C#ASC

（12）檢索學(xué)號比WANG同學(xué)大，而年齡比他小的學(xué)生姓名。

SELECT

FROMSASX,SASY

WHERE='WANG'AND#>#AND<

（13）檢索姓名以WANG打頭的所有學(xué)生的姓名和年齡。

SELECTSNAME,AGE

FROMS

WHERESNAMELIKE'WANG%'

（14）在SC中檢索成績?yōu)榭罩档膶W(xué)生學(xué)號和課程號。

SELECTS#,C#

FROMSC

WHEREGRADEISNULL

（15）求年齡大于女同學(xué)平均年齡的男學(xué)生姓名和年齡。

SELECTSNAME,AGE

FROMSASX

WHERE='男'AND>(SELECTAVG(AGE)FROMSASYWHERE='女')

（16）求年齡大于所有女同學(xué)年齡的男學(xué)生姓名和年齡。

SELECTSNAME,AGE

FROMSASX

WHERE='男'AND>ALL(SELECTAGEFROMSASYWHERE='女')

試用SQL更新語句表達對教學(xué)數(shù)據(jù)庫中三個基本表S、SC、C的各個更新操作：

（1）往基本表S中插入一個學(xué)生元組（‘S9’，‘WU’，18）。

INSERTINTOS(S#,SNAME,AGE)VALUES('59','WU',18)

（2）在基本表S中檢索每一門課程成績都大于等于80分的學(xué)生學(xué)號、姓名和性別，并把檢索到的值送往另一個已存在的基本表STUDENT（S＃，SANME，SEX）。

INSERTINTOSTUDENT(S#,SNAME,SEX)

SELECTS#,SNAME,SEX

FROMSWHERENOTEXISTS

(SELECT*FROMSCWHERE

GRADE<80AND#=#)

（3）在基本表SC中刪除尚無成績的選課元組。

DELETEFROMSC

WHEREGRADEISNULL

（4）把WANG同學(xué)的學(xué)習(xí)選課和成績?nèi)縿h去。

DELETEFROMSC

WHERES#IN

(SELECTS#

FROMS

WHERESNAME='WANG')

（5）把選修MATHS課不及格的成績?nèi)臑榭罩怠?/p>

UPDATESC

SETGRADE=NULL

WHEREGRADE<60ANDC#IN

(SELECTC#

FROMC

WHERECNAME='MATHS')

（6）把低于總平均成績的女同學(xué)成績提高5%。

UPDATESC

SETGRADE=GRADE*

WHEREGRADE<(SELECTAVG(GRADE)FROMSC)ANDS#IN(SELECTS#FROMSWHERESEX='F')

（7）在基本表SC中修改C4課程的成績，若成績小于等于75分時提高5%，若成績大于75分時提高4%（用兩個UPDATE語句實現(xiàn)）。

UPDATESC

SETGRADE=GRADE*

WHEREC#='C4'ANDGRADE<=75

UPDATESC

SETGRADE=GRADE*

WHEREC#='C4'ANDGRADE>75

在第1章例中提到“倉庫管理”關(guān)系模型有五個關(guān)系模式：

零件PART（P＃，PNAME，COLOR，WEIGHT）

項目PROJECT（J＃，JNAME，DATE）

供應(yīng)商SUPPLIER（S＃，SNAME，SADDR）

供應(yīng)P＿P（J＃，P＃，TOTOAL）

采購P＿S（P＃，S＃，QUANTITY）

（1）試用SQLDDL語句定義上述五個基本表，并說明主鍵和外鍵。

CREATETABLEPART

(P#CHAR(4)NOTNULL,PNAMECHAR(12)NOTNULL,

COLORCHAR(10),WEIGHTREAL,

PRIMARYKEY(P#))

CREATETABLEPROJECT

(J#CHAR(4)NOTNULL,JNAMECHAR(12)NOTNULL,

DATEDATE,

PRIMARYKEY(J#))

CREATETABLESUPLIER

(S#CHAR(4)NOTNULL,SNAMECHAR(12),SADDRVARCHAR(20),

PRIMARYKEY(S#))

CREATETABLEP_P

(J#CHAR(4),P#CHAR(4),TOTALINTEGER,

PRIMARYKEY(J#,P#),

FOREIGNKEY(J#)REFERENCEPROJECT(J#),

FOREIGNKEY(P#)REFERENCEPART(P#))

CREATETABLEP_S

(P#CHAR(4),S#CHAR(4),QUANTITYINTEGER,

PRIMARYKEY(P#,S#),

FOREIGNKEY(P#)REFERENCEPART(P#),

FOREIGNKEY(S#)REFERENCESUPLIER(S#))

（2）試將PROGECT、P＿P、PART三個基本表的自然聯(lián)接定義為一個視圖VIEW1，PART、P＿S、SUPPLIER三個基本表的自然聯(lián)接定義為一個視圖VIEW2。

CREATEVIEWVIEW1(J#,JNAME,DATE,P#,PNAME,COLOR,WEIGHT,TOTAL)

ASSELECT#,JNAME,DATE,#,PNAME,COLOR,WEIGHT,TOTAL

FROMPROJECT,PART,P_P

WHERE#=#AND#=#

CREATEVIEWVIEW2(P#,PNAME,COLOR,WEIGHT,S#,SNAME,SADDR,QUANTITY)

ASSELECT#,PNAME,COLOR,WEIGHT,#,SNAME,SADDR,QUANTITY

FROMPART,P_S,SUPPLIER

WHERE#=#AND#=#

（3）試在上述兩個視圖的基礎(chǔ)上進行數(shù)據(jù)查詢：1）檢索上海的供應(yīng)商所供應(yīng)的零件的編號和名字。SELECTP#,PNAMEFROMVIEW2WHERESADDR='SHANGHAI'2）檢索項目J4所用零件的供應(yīng)商編號和名字。SELECTS#,SNAMEFROMVIEW2WHEREP#IN(SELECTP#FROMVIEW1WHEREJ#='J4')對于教學(xué)數(shù)據(jù)庫中基本表SC，已建立下列視圖：

CREATEVIEWS＿GRADE（S＃，C＿NUM，AVG＿GRADE）

ASSELECTS＃，COUNT(C＃)，AVG（GRADE）

FROMSC

GROUPBYS＃

試判斷下列查詢和更新是否允許執(zhí)行。若允許，寫出轉(zhuǎn)換到基本表SC上的相應(yīng)操作。

（1）SELECT*