補(bǔ)充作業(yè)一

設(shè)有三個關(guān)系：

S(SNO, SNAME, AGE, SEX,Sdept)

SC(SNO, CNO, GRADE)

C(CNO, CNAME, TEACHER)

試用關(guān)系代數(shù)表達(dá)式表示下列查詢：

1、查詢學(xué)號為S3學(xué)生所學(xué)課程的課程名與任課教師名。

2、查詢至少選修LIU老師所教課程中一門課的女生姓名。

3、查詢WANG同學(xué)不學(xué)的課程的課程號。

4、查詢至少選修兩門課程的學(xué)生學(xué)號。

5、查詢選修課程中包含LIU老師所教全部課程的學(xué)生學(xué)號。

補(bǔ)充作業(yè)二

三個關(guān)系同上，試用SQL語言表示下列查詢：

1、 查詢門門課程都及格的學(xué)生的學(xué)號

方法1：

提示：根據(jù)學(xué)號分組，就得到每個學(xué)生所有的課程成績，在某個學(xué)生這一組成績里，如果他所有的課程成績都大于60分則輸出該組學(xué)生的學(xué)號

Select sno frome sc group by sno having(min(grade)>=60)

2、查詢既有課程大于90分又有課程不及格的學(xué)生的學(xué)號

自身連接：

Select snofromsc where grade>90andsnoin(select snofromsc where grade<60)

3、查詢平均分不及格的課程號和平均成績

Select cno,avg(GRADE)fromsc group by cno having avg(grade)<60

查詢平均分及格的課程號和課程名

Select C.cno,CnamefromSC,C where C.cno=SC.cno group by C.cno having avg(grade)>=60

4、找出至少選修了2號學(xué)生選修過的全部課程的學(xué)生

提示:不存在這樣的課程y，學(xué)生2選修了y，而學(xué)生x沒有選。

SELECT DISTINCT Sno? FROM SCasSCX? WHERE NOT EXISTS(SELECT*FROM SCasSCY? ? ? WHERE SCY.Sno=‘2’AND NOT EXISTS(SELECT*FROM SC SCZ? ? ? ? ? ? ? ? ? ? ? ? ? WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno))

5、求各門課程去掉一個最高分和最低分后的平均分

第一步，求所有成績的平均分（去掉一個最高分和最低分）

select? avg(GRADE)fromSC? ? ? where? GRADEnotin(select? top1GRADEfromSC order? by? GRADE)andGRADEnotin(select? top1GRADEfromSC order? by? GRADE? desc)

第二步，將所有成績按各門課程的課程號CNO分組

SELECT CNO avg(GRADE)fromSC? ? ? where? GRADEnotin(select? top1GRADEfromSC order? by? GRADE)andGRADEnotin(select? top1GRADEfromSC order? by? GRADE? desc)group by CNO

1、查詢7號課程沒有考試成績的學(xué)生學(xué)號。

Select sno fromsc where cno='7'andgradeisnull

2、查詢7號課程成績在90分以上或60分以下的學(xué)生學(xué)號。

Select snofromsc where cno='7'andgradenotbetween 60and9

3、查詢課程名以“數(shù)據(jù)”兩個字開頭的所有課程的課程號和課程名。

Select cno,cnamefromc where cname like'數(shù)據(jù)%'

4、查詢每個學(xué)生所有課程的平均成績，輸出學(xué)生學(xué)號和平均成績。

Select sno,avg(grade)fromsc group by sno

5、查詢每門課程的選修人數(shù)，輸出課程號和選修人數(shù)。

Selectcno,count(*)fromsc group by cno

6、查詢選修7號課程的學(xué)生的學(xué)號、姓名、性別。

Selects.sno,sname,ssexfroms,sc where s.sno=sc.snoandcno='7'

或：

Select sno,sname,ssexfroms where snoin(Select snofromsc where cno='7')

7、查詢選修7號課程的學(xué)生的平均年齡。

Selectavg(sage)froms,sc where s.sno=sc.snoandcno='7'

或：

Select avg(sage)froms where snoin(Select snofromsc where cno='7')

8、查詢有30名以上學(xué)生選修的課程號。

Select cno fromsc group by cno having count(*)>30

9、查詢至今沒有考試不及格的學(xué)生學(xué)號。

Select distinctsnofromsc where snonotin(Select snofromsc where grade<60)

或：

Select snofromsc group by sno havingmin(grade)>=60

補(bǔ)充三

1、找出選修課程號為C2的學(xué)生學(xué)號與成績。

Select sno,gradefromsc where cno='C2'

2、找出選修課程號為C4的學(xué)生學(xué)號與姓名。

Selects.sno,snamefroms,sc where s.sno=sc.snoandcno='C4'

注意本題也可以用嵌套做

思考本題改為“找出選修課程號為C4的學(xué)生學(xué)號、姓名與成績”后還能用嵌套做嗎？

3、找出選修課程名為 Maths 的學(xué)生學(xué)號與姓名。

Selects.sno,snamefroms,sc,c? ? where? s.sno=sc.snoandc.cno=sc.cno andcname='Maths'

注意本題也可以用嵌套做

4、找出選修課程號為C2或C4的學(xué)生學(xué)號。

Select distinctsnofromsc where cnoin('C2','C4')

或：

Select distinct snofromsc where cno='C2'orcno='C4'

5、找出選修課程號為C2和C4的學(xué)生學(xué)號。

Select sno fromsc where cno='C2'andsnoin(Select snofromsc where cno='C4')

注意本題也可以用連接做

思考：

Select distinct sno from sc where cno=‘C2’ andcno='C4’正確嗎？

6、找出不學(xué)C2課程的學(xué)生姓名和年齡。

Selectsname,sagefroms where snonotin(Selectsnofromsc where cno='C2'

或：

Select sname,sagefroms wherenotexists(Select*fromsc where sno=s.snoandcno='C2')

7、找出選修了數(shù)據(jù)庫課程的所有學(xué)生姓名。(同3)

Select snamefrom s,sc,cwhere? s.sno=sc.snoand c.cno=sc.cnoandcname='數(shù)據(jù)庫'

8、找出數(shù)據(jù)庫課程不及格的女生姓名。

連接：Select snamefroms,sc,c? ? ? ? where? s.sno=sc.sno andc.cno=sc.cnoandcname='數(shù)據(jù)庫'andgrade<60andssex='女'嵌套：Select snamefroms where ssex='女'andsnoin(Select snofromsc where grade<60andcnoin(Select cnofromc where cname='數(shù)據(jù)庫'))

9、找出各門課程的平均成績，輸出課程名和平均成績。

Selectcname,avg(grade)fromsc,c? ? wherec.cno=sc.cno? group by c.cno,cname

思考本題也可以用嵌套做嗎？

10、找出各個學(xué)生的平均成績，輸出學(xué)生姓名和平均成績。

Selectsname,avg(grade)froms,sc? ? wheres.sno=sc.sno group by s.sno,sname

思考本題也可以用嵌套做嗎？

11、找出至少有30個學(xué)生選修的課程名。

Select cnamefrom c where cnoin(Selectcnofromsc group by cno having count(*)>=30)

注意本題也可以用連接做

12、找出選修了不少于3門課程的學(xué)生姓名。

Select snamefrom s where snoin(Selectsnofromsc group by sno having count(*)>=3)

注意本題也可以用連接做

13、找出各門課程的成績均不低于90分的學(xué)生姓名。

Select snamefrom s,sc where s.sno=sc.sno? ? ? ? group bys.sno,sname havingmin(grade)>=90

方法二：

Select snamefroms where snonotin(Selectsnofromsc where grade<90)

只要有一門不小于90分就會輸出該學(xué)生學(xué)號

14、找出數(shù)據(jù)庫課程成績不低于該門課程平均分的學(xué)生姓名。

Select snamefrom s,sc,c? ? where? s.sno=sc.snoandsc.cno=c.cnoandcname='數(shù)據(jù)庫'andgrade>(Selectavg(grade)fromsc,c? ? ? ? ? where sc.cno=c.cnoand cname='數(shù)據(jù)庫')

15、找出各個系科男女學(xué)生的平均年齡和人數(shù)。

Selectsdept,ssex,avg(sage),count(*)froms group by sdept,ssex

16、找出計算機(jī)系(JSJ)課程平均分最高的學(xué)生學(xué)號和姓名。

Selects.sno,snamefroms,sc where s.sno=sc.snoandsdept='JSJ'group bys.sno,sname? ? havingavg(grade)>=ALL(Selectavg(grade)froms,sc? ? ? ? ? wheres.sno=sc.snoandsdept='JSJ'group bys.sno)

17、(補(bǔ)充)查詢每門課程的及格率。

本題可以分三步做：

第1步：得到每門課的選修人數(shù)

createview? v_all(cno,cnt)asselectcno,count(*)fromsc group by cno

第2步：得到每門課及格人數(shù)

createview? v_pass(cno,cnt_pass)asselectcno,count(*)fromsc where grade>=60group by cno

第3步：每門課的及格人數(shù)/每門課選修人數(shù)

selectv_all.cno,cnt_pass*100/cntfromv_all,v_pass? ? where v_all.cno=v_pass.cno

18、查詢平均分不及格的學(xué)生的學(xué)號，姓名，平均分。

Selectsc.sno,sname,avg(grade)fromstudent,sc? ? wherestudent.sno=sc.sno? ? group bysc.sno,sname? ? havingavg(grade)<60

思考本題也可以用嵌套做嗎？

19、查詢平均分不及格的學(xué)生人數(shù)。

Select count(*)fromstudent? ? where snoin(selectsnofromsc group by sno having avg(grade)<60)

下面是一個典型的錯誤

Select count(*)fromsc group by sno havingavg(grade)<60

這是每個學(xué)生有幾門不及格的數(shù)目

補(bǔ)充四

1、查詢工資在1000到3000元之間的男性業(yè)務(wù)員的姓名和辦公室編號。

SelectYname,OnofromYWY? ? where Salarybetween1000and3000andYsex='男'

2、查詢各個辦公室的業(yè)務(wù)員人數(shù)，輸出辦公室編號和對應(yīng)的人數(shù)。

SelectOno,count(*)fromYWY group by Ono

3、查詢每個客戶在2002年5月購買的總金額，輸出客戶號和相應(yīng)的總金額。

SelectKno,sum(Fmoney)fromFP? ? where Fdatebetween'2002.5.1'and'2002.5.31'group by Kno

4、查詢2002年5月購買次數(shù)超過5次的所有客戶號，且按客戶號升序排序。

Select Kno fromFP? ? where Fdatebetween'2002.5.1'and'2002.5.31'group by Kno? ? havingcount(*)>5order by KnoASC

5、查詢各辦公室男性和女性業(yè)務(wù)員的平均工資。

SelectOno,Ysex,avg(Salary)fromYWY group by Ono,Ysex

6、查詢2002年5月曾經(jīng)在王海亮業(yè)務(wù)員手中購買過商品的客戶號、 客戶姓名和聯(lián)系電話。

SelectKno,Kname,PhonefromKH where Knoin(SelectKnofromFP? ? ? ? ? whereFdate between'2002.5.1'and'2002.5.31'andYnoin(Select YnofromYWY where Yname='王海亮'))

注意本題也可以用連接做

7、查詢所有工資比1538號業(yè)務(wù)員高的業(yè)務(wù)員的編號、姓名和工資。

SelectYno,Yname,SalaryfromYWY where Salary>(SelectSalaryfromYWY where Yno='1538')

8、查詢所有與1538號業(yè)務(wù)員在同一個辦公室的其他業(yè)務(wù)員的編號和姓名。

SelectYno,YnamefromYWY where Yno!='1538'andOnoin(SelectOnofromYWY where Yno='1538')

9、查詢銷售總金額最高的業(yè)務(wù)員的編號。

Select Yno fromFP group by Yno havingsum(Fmoney)>=ALL(Selectsum(Fmoney)fromFP group by Yno)

10、查詢所有業(yè)務(wù)員的編號、姓名、工資以及工資比他高的其他業(yè)務(wù)員的平均工資。

利用自連接

SelectY1.Yno,Y1.Yname,Y1.Salary,avg(Y2.Salary)fromYWY Y1,YWY Y2? ? where? Y1.Salary<Y2.Salary? ? group by? Y1.Yno

補(bǔ)充五

1、找出每個班級的班級代碼、學(xué)生人數(shù)、平均成績。

SelectBJDM,count(*),avg(CJ)fromSC group by BJDM

2、找出每個學(xué)生的班級代碼、學(xué)生姓名、考試科目數(shù)、總成績。

SelectBJDM,XSXM,count(*),sum(CJ)fromSC? ? group byBJDM,BNXH,XSXM

3、輸出一張表格，每位學(xué)生對應(yīng)一條記錄，包括字段：班級代碼、學(xué)生姓名、語文成績、數(shù)學(xué)成績、外語成績。

SelectSC1.BJDM,SC1.XSXM,SC1.CJ,SC2.CJ,SC3.CJfromSC SC1,SC SC2,SC SC3? ? whereSC1.BJDM=SC2.BJDMandSC1.BNXH=SC2.BNXHandSC2.BJDM=SC3.BJDMandSC2.BNXH=SC3.BNXHandSC1.KM='語文'andSC2.KM='數(shù)學(xué)'andSC3.KM='外語'

4、輸出一張表格，有成績低于60分的每位學(xué)生對應(yīng)一條記錄，包括字段：班級代碼、學(xué)生姓名、最低成績。

SelectBJDM,XSXM,min(CJ)fromSC? ? where? CJ<60group by BJDM,BNXH,XSXM? ? 或：? SelectBJDM,XSXM,min(CJ)fromSC? ? ? ? ? group byBJDM,BNXH,XSXM? ? ? ? ? havingmin(CJ)<60

5、輸出一張表格，有成績低于60分的每位學(xué)生對應(yīng)一條記錄，包括字段： 班級代碼、學(xué)生姓名、最高成績、平均成績。

SelectBJDM,XSXM,max(CJ)fromSC? ? group byBJDM,BNXH,XSXM? ? havingmin(CJ)<60

請思考下列做法是否正確：

SelectBJDM,XSXM,max(CJ),avg(CJ)fromSC? ? ? ? where? CJ<60group byBJDM,BNXH,XSXM

6、輸出一張表格，所有成績都不低于60分的每位學(xué)生對應(yīng)一條記錄，包括字段：班級代碼、學(xué)生姓名、平均成績。

SelectBJDM,XSXM,avg(CJ)fromSC? ? group by BJDM,BNXH,XSXM? ? havingmin(CJ)>=60

7、輸出一張表格，每一位學(xué)生對應(yīng)一條記錄，包括字段：班級代碼、學(xué)生姓名、去掉一個最低分后的平均成績。

SelectBJDM,XSXM,(sum(CJ)-min(CJ))/(count(*)-1)fromSC? ? group byBJDM,BNXH,XSXM

8、輸出一張表格，每門科目對應(yīng)一條記錄，包括字段： 科目、去掉一個最低分后的平均成績。

Select KM,(sum(CJ)-min(CJ))/(count(*)-1)fromSC? ? group by KM

補(bǔ)充六

1、查詢年齡在19至21歲之間的女生的學(xué)號，姓名，年齡，按年齡從大到小排列。

Selectsno,sname,sagefromstudent? ? where sagebetween19and21andssex='女'order by sagedesc

2、查詢姓名中有“明”字的學(xué)生人數(shù)。

Select count(*)fromstudent? ? where snamelike"%明%"

3、查詢1001課程沒有成績的學(xué)生的學(xué)號。

Select sno fromsc where cno='1001'andgradeisnull

4、查詢JSJ、SX、WL系的學(xué)生學(xué)號，姓名，結(jié)果按系及學(xué)號排列。

Selectsno,sname,sdeptfromstudent? ? where sdeptin('JSJ','SX','WL')order bysdept,sno

5、計算每一門課的總分、平均分，最高分、最低分。

Selectcno,sum(grade),avg(grade),max(grade),min(grade)fromsc? ? group by cno

6、查詢平均分大于90分的男學(xué)生學(xué)號及平均分。

連接：

selectsc.sno,avg(grade)fromstudent,sc? ? wherestudent.sno=sc.snoandssex=’男’? ? group by sc.sno? ? havingavg(grade)>90

嵌套：

selectsno,avg(grade)fromsc? ? where snoin(select snofromstudent where ssex='男')group by sno? ? havingavg(grade)>90

7、查詢選修課程超過2門的學(xué)生姓名。

select snamefrom student,sc? ? where student.sno=sc.sno? ? group bysc.sno,sname? ? havingcount(*)>2

本題也可以用嵌套做

8、查詢 JSJ 系的學(xué)生選修的課程號。

Select distinctcnofromstudent,sc? ? where? student.sno=sc.snoandsdept='JSJ'

本題也可以用嵌套做

9、查詢選修1002課程的學(xué)生的學(xué)生姓名(用連接和嵌套2種方法)

連接：Select snamefromstudent,sc? ? ? ? ? wherestudent.sno=sc.snoandcno='1002'嵌套：Select snamefromstudent where snoin(select snofromsc where cno='1002')

10、查詢學(xué)生姓名以及他選修課程的課程號及成績。

Selectsname,cno,gradefromstudent,sc? ? wherestudent.sno=sc.sno

思考本題也可以用嵌套做嗎？

11、查詢選修“數(shù)據(jù)庫原理”課且成績 80 以上的學(xué)生姓名(用連接和嵌套2種方法)

連接：Select snamefromstudent,sc,course? ? ? ? ? wherestudent.sno=sc.snoandsc.cno=course.cnoandcname='數(shù)據(jù)庫原理'andgrade>80嵌套：Select snamefromstudent where snoin(select snofromsc where grade>80andcnoin(select cnofromcourse where cname='數(shù)據(jù)庫原理'))

14、查詢沒有選修1002課程的學(xué)生的學(xué)生姓名。

Select snamefrom student? ? where sno notin(select snofromsc where cno='1002')或： select snamefromstudent? ? ? ? where notexists(select*fromsc where cno='1002'andsno=student.sno)

思考本題也可以用一般的連接做嗎？

15、查詢平均分最高的學(xué)生學(xué)號及平均分。

Selectsno,avg(grade)fromsc? ? group by sno? ? havingavg(grade)>=ALL(Select avg(grade)fromsc? ? ? ? ? ? ? ? ? ? ? ? ? ? ? group by sno)

16、查詢每門課程成績都高于該門課程平均分的學(xué)生學(xué)號。

可以先計算每門課程平均分

create viewc_avg(cno,avg_grade)asselectcno,avg(grade)fromsc group by cno? ? 再查詢? ? Select distinctsnofromsc? ? where sno notin(Select snofromsc,c_avg? ? ? ? ? ? ? ? ? ? ? where sc.cno=c_avg.cnoandgrade<avg_grade)===========================================SELECT DISTINCT Sno? ? FROM SC SC1? ? WHERE SC1.SnoNOT IN(SELECT SC2.Sno? ? ? ? ? ? FROM SC SC2? ? ? ? ? ? WHERE SC2.Grade<=(SELECT AVG(SC3.Grade)FROM SC SC3? ? ? ? ? ? ? ? ? WHERE SC3.Cno=SC2.Cno))

或：

SELECT DISTINCTSno? ? FROM SC SC1? ? WHERE NOTEXISTS(SELECT*FROM SC SC2? ? ? ? ? ? WHERE SC2.Sno=SC1.Sno AND SC2.Grade<=(SELECT AVG(SC3.Grade)FROM SC SC3? ? ? ? ? ? ? ? ? ? ? ? WHERE SC3.Cno=SC2.Cno))

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

SELECT SNAME　　 　FROM S　　 　WHERE SEX=‘F’ AND S# IN(SELECT S#FROM SC　　 　　WHERE C# IN(SELECT C#FROM C　　 　　　WHERE TEACHER=‘LIU’)

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

SELECT SNAME　　 　FROM S,SC,C　　 　WHERE SEX=‘F’ AND SC.S#=S.S#AND SC.C#=C.C#AND TEACHER='LIU'

但上一種寫法更好一些。

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

SELECT C#FROM C　　 　WHERE C# NOT IN(SELECT C#FROM SC　　 　　WHERE S# IN(SELECT S#FROM S　　 　　　WHERE SNAME='WANG'))

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

SELECT DISTINCT X.SNO　　 　FROM　SC X,SC Y　　 　WHERE X.SNO=Y.SNO AND X.CNO<>Y.CNO

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

（6）檢索全部學(xué)生都選修的課程的課程號與課程名。

SELECT C#,CNAMEFROM C　　 　WHERE NOT EXISTS(SELECT*FROM S　　 　　WHERE S# NOT IN(SELECT*FROM SC　　 　　　WHERE? SC.C#=C.C#))

要從語義上分解：（1）選擇課程的課程號與課程名，不存在不選這門課的同學(xué)。

其中，“不選這門課的同學(xué)”可以表示為：

SELECT*FROM? S WHERE? S# NOT IN（SELECT*FROM SC? ? ? ? WHERE? SC.C#=C.C#)

或者

SELECT*FROM? S WHERE? NOT EXISTS? （SELECT*FROM SC? ? ? ? WHERE S.S#=C.S# AND SC.C#=C.C# )

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

SELECT DISTINCT S#FROM SC　　 　　WHERE C# IN(SELECT C#FROM C　　 　　　WHERE TEACHER='LIU'))

3.3　設(shè)有兩個基本表R（A，B，C）和S（D，E，F(xiàn)），試用SQL查詢語句表達(dá)下列關(guān)系代數(shù)表達(dá)式：

（1）πA?(2)σB='17’?(3)R×S(4))πA,F(σC=D(R×S))

(1)SELECT A FROM R

(2)SELECT * FROM R WHERE B=‘17’

(3)SELECT A,B,C,D,E,F FROM R,S

(4)SELECT A,F FROM R,S WHERE R.C=S.D

3.43.4　設(shè)有兩個基本表R（A，B，C）和S（A，B，C）試用SQL查詢語句表達(dá)下列關(guān)系代數(shù)表達(dá)式：

（1）R∪S　?。?）R∩S　?。?）R－S　　（4）πA,B?πB,C(S)

(1)

SELECT A,B,C　　 　　　FROM R　　 　　　UNION　　 　　　SELECT A,B,C　　 　　　FROM S

(2)

SELECT A,B,C　　 　　　FROM R　　 　　　INTERSECT　　 　　　SELECT A,B,C　　 　　　FROM S

(3)

SELECT A,B,C　　 　　　FROM R　　 　　　WHERE NOT EXISTS(SELECT A,B,C　　 　　　　FROM S　　 　　　　WHERE R.A=S.A AND R.B=S.B AND R.C=S.C)

(4)

SELECT R.A,R.B,S.C　　 　　　FROM R,S　　 　　　WHERE R.B=S.B

3.5　試敘述SQL語言的關(guān)系代數(shù)特點(diǎn)和元組演算特點(diǎn)。

3.6　試用SQL查詢語句表達(dá)下列對教學(xué)數(shù)據(jù)庫中三個基本表S、SC、C的查詢：

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

SELECT COUNT(DISTINCT C#) FROM SC

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

SELECT AVG(AGE)FROM S　　 　WHERE S# IN(SELECT S#FROM SC　　 　　WHERE C#='C4')

或者

SELECT AVG(AGE)FROM S，SC　　 　WHERE S.S#=SC.S# AND C#='004'

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

SELECT CNAME,AVG(GRADE)FROM SC,C　　　WHERE SC.C#=C.C# ANDTEACHER='LIU'GROUP BY C#

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

SELECT DISTINCT C#,COUNT(S#)FROM SC　　 　GROUP BY C#HAVING COUNT(S#)>10ORDER BY2DESC,C# ASC

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

SELECT X.SNAME　　 　FROM S AS X,S AS Y　　 　WHERE Y.SNAME='WANG'AND X.S#>Y.S# AND X.AGE<Y.AGE

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

SELECT SNAME,AGE　　 　FROM S　　 　WHERE SNAME LIKE'WANG%'

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

SELECT S#,C#FROM SC　　 　WHERE GRADE IS NULL

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

SELECT SNAME,AGE　　 　FROM S AS X　　 　WHERE　X.SEX='男'AND X.AGE>(SELECT AVG(AGE)FROM S AS Y WHERE Y.SEX='女')

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

SELECT SNAME,AGE　　 　FROM S AS X　　 　WHERE X.SEX='男'AND X.AGE>ALL(SELECT AGE FROM S AS Y WHERE Y.SEX='女')

除法運(yùn)算

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

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

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

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

INSERT INTO STUDENT(S#,SNAME,SEX)SELECT S#,SNAME,SEXFROM S WHERE 　NOT EXISTS(SELECT*FROM SC WHERE　　　　　　 GRADE<80AND S.S#=SC.S#)

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

　 　DELETE FROM SC

　　 　　WHERE GRADE IS NULL

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

DELETE FROM SC　　 　　WHERE S# IN(SELECT S#FROM S　　 　　　WHERE SNAME='WANG')

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

UPDATE SC　　 　SET GRADE=NULL　　 　WHERE GRADE<60AND C# IN(SELECT C#FROM C　　 　　　WHERE CNAME='MATHS')

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

UPDATE SC　　 　SET GRADE=GRADE*1.05WHERE GRADE<(SELECT AVG(GRADE)FROM SC)AND S# IN (SELECT S# FROM SWHERE SEX='F')

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

UPDATE SC　　 　　SET GRADE=GRADE*1.05WHERE C#='C4' AND GRADE<=75UPDATE SC　　 　　SET GRADE=GRADE*1.04WHERE C#='C4' AND GRADE>75

3.8　“倉庫管理”關(guān)系模型有五個關(guān)系模式：

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

項(xiàng)目　PROJECT（J＃，JNAME，DATE）

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

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

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

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

CREATE TABLE PART(P# CHAR(4) NOT NULL,PNAME CHAR(12) NOT NULL,COLOR CHAR(10),WEIGHT REAL,PRIMARY KEY(P#))CREATE TABLE PROJECT(J# CHAR(4) NOT NULL,JNAME CHAR(12) NOT NULL,DATE DATE,PRIMARY KEY(J#))CREATE TABLE SUPLIER(S# CHAR(4) NOT NULL,SNAME CHAR(12),SADDR VARCHAR(20),PRIMARY KEY(S#))CREATE TABLE P_P(J# CHAR(4),P# CHAR(4),TOTAL INTEGER,PRIMARY KEY(J#,P#),FOREIGN KEY(J#) REFERENCE PROJECT(J#),FOREIGN KEY(P#) REFERENCE PART(P#))CREATE TABLE P_S(P# CHAR(4),S# CHAR(4),QUANTITY INTEGER,PRIMARY KEY(P#,S#),FOREIGN KEY(P#) REFERENCE PART(P#),FOREIGN KEY(S#) REFERENCE SUPLIER(S#))

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

CREATE VIEW VIEW1(J#,JNAME,DATE,P#,PNAME,COLOR,WEIGHT,TOTAL)AS SELECT PROJECT.J#,JNAME,DATE,PART.P#,PNAME,COLOR,WEIGHT,TOTALFROM PROJECT,PART,P_P　　 　　　　　　　WHERE PART.P#=P_P.P# AND P_P.J#=PROJECT.J#　CREATE VIEW VIEW2(P#,PNAME,COLOR,WEIGHT,S#,SNAME,SADDR,QUANTITY)AS SELECT PART.P#,PNAME,COLOR,WEIGHT,SUPPLIER.S#,SNAME,SADDR,QUANTITYFROM PART,P_S,SUPPLIER　　 　　　　　　　WHERE PART.P#=P_S.P# AND P_S.S#=SUPPLIER.S#

（3）試在上述兩個視圖的基礎(chǔ)上進(jìn)行數(shù)據(jù)查詢：

1）檢索上海的供應(yīng)商所供應(yīng)的零件的編號和名字。

SELECT P#,PNAME FROM VIEW2 WHERE SADDR='SHANGHAI'

2）檢索項(xiàng)目J4所用零件的供應(yīng)商編號和名字。

SELECT S#,SNAME FROM VIEW2 WHERE P# IN(SELECT P# FROM VIEW1 WHERE J#='J4')

3.9　對于教學(xué)數(shù)據(jù)庫中基本表SC，已建立下列視圖：

CREATEVIEWS＿GRADE（S＃，C＿NUM，AVG＿GRADE）ASSELECTS＃，COUNT(C＃)，AVG（GRADE）FROMSCGROUPBYS＃

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

（1）

SELECT*FROMS＿GRADE　　 　　　允許　　　SELECT S#,COUNT(C#),AVG(GRADE)FROM SC GROUP BY S#

（2）

SELECTS＃，C＿NUMFROMS＿GRADEWHEREAVG＿GRADE＞80允許　　　SELECT S#,COUNT(C#) FROM SC WHEREAVG(GRADE)>80

（3）

SELECTS＃，AVG＿GRADEFROMS＿GRADEWHEREC＿NUM＞(SELECTC＿NUMFROMS＿GRADEWHERES＃＝‘S4’)允許　　　SELECT S#,AVG(GRADE)FROM SC AS X　　　WHERE COUNT(X.C#)>(SELECTCOUNT(Y.C#) FROM SC AS Y WHERE Y.S#='S4')GROUP BY S#

（4）

UPDATES＿GRADESETC＿NUM＝C＿NUM＋1WHERES＃＝‘S4’　　 　不允許

（5）

DELETEFROMS＿GRADEWHEREC＿NUM＞4不允許

3.10　預(yù)處理方式對于嵌入式SQL的實(shí)現(xiàn)有什么重要意義？

預(yù)處理方式是先用預(yù)處理程序?qū)υ闯绦蜻M(jìn)行掃描，識別出SQL語句，并處理成宿主語言的函數(shù)調(diào)用形式； 然后再用宿主語言的編譯程序把源程序編譯成目標(biāo)程序。這樣，不用擴(kuò)充宿主語言的編譯程序， 就能處理SQL語句。

3.11 在宿主語言的程序中使用SQL語句有哪些規(guī)定？

在宿主語言的程序中使用SLQ語句有以下規(guī)定：

（1）在程序中要區(qū)分SQL語句與宿主語言語句

（2）允許嵌入的SQL語句引用宿主語言的程序變量（稱為共享變量），但有兩條規(guī)定：

1）引用時，這些變量前必須加“：”作為前綴標(biāo)識，以示與數(shù)據(jù)庫中變量有區(qū)別。

2）這些變量由宿主語言的程序定義，并用SQL的DECLARE語句說明。

（3）SQL的集合處理方式與宿主語言單記錄處理方式之間要協(xié)調(diào)。 需要采用游標(biāo)機(jī)制，把集合操作轉(zhuǎn)換成單記錄處理方式。

3.12SQL的集合處理方式與宿主語言單記錄處理方式之間如何協(xié)調(diào)？

由于SQL語句處理的是記錄集合，而宿主語言語句一次只能處理一個記錄， 因此需要用游標(biāo)(cousor)機(jī)制，把集合操作轉(zhuǎn)換成單記錄處理方式。

2.13 嵌入式SQL語句何時不必涉及到游標(biāo)？何時必須涉及到游標(biāo)？

(1)INSERT、DELETE、UPDATE語句，查詢結(jié)果肯定是單元組時的SELECT語句， 都可直接嵌入在主程序中使用，不必涉及到游標(biāo)。

(2)當(dāng)SELECT語句查詢結(jié)果是多個元組時，此時宿主語言程序無法使用， 一定要用游標(biāo)機(jī)制把多個元組一次一個地傳送給宿主語言處理。

（電商）數(shù)據(jù)庫原理及應(yīng)用_模擬考核試題及參考答案

一、單選 (每空1分,共10分)

1．在Access數(shù)據(jù)庫中，數(shù)據(jù)保存在（ ）對象中。

A．窗體 B．查詢 C．報表 D．表

2．如果某一字段數(shù)據(jù)型為文本型，字段大小為8，該字段中最多可輸入（ ）個漢字。

A．8 B．4 C．16 D．32

3．文本型字段最多可以存放（ ）個字符。

A．250 B．252 C．254 D．255

4．Access用戶操作界面由（ ）部分組成。

A．4 B．5 C．3 D．6

5．下列（ ）圖標(biāo)是Access中表對象的標(biāo)志。

A． B． C． D．

6．在設(shè)計Access數(shù)據(jù)表時，“索引”屬性有（ ）取值。

A．1 B．2 C．3 D．4

7．Access中包含有（ ）種數(shù)據(jù)類型。

A．9 B．10 C．7 D．8

8．在一個宏中要打開一個報表，應(yīng)該使用的操作是（ ）。

A．OpenForm B．OpenReport C．OpenTable D．OpenQuery

9．可以通過Internet進(jìn)行數(shù)據(jù)發(fā)布的對象是（ ）。

A．窗體 B．報表 C．查詢 D．?dāng)?shù)據(jù)訪問頁

10．模塊窗口由（ ）個部分組成。

A．2 B．3 C．4 D．5

二、填空 (每空1分,共20分)

1．在人工管理和文件管理階段，程序設(shè)計__依賴于 ___數(shù)據(jù)表示。

2．在文件系統(tǒng)中，存取數(shù)據(jù)的基本單位為___記錄____，在數(shù)據(jù)庫系統(tǒng)中，存取數(shù)據(jù)的基本單位為___數(shù)據(jù)項(xiàng)_____。

3．若實(shí)體A和B是多對多的聯(lián)系，實(shí)體B和C是1對1的聯(lián)系，則實(shí)體A和C是___多_____對___多_____的聯(lián)系。

4．在一個關(guān)系中不允許出現(xiàn)重復(fù)的____元組____，也不允許出現(xiàn)具有相同名字的___屬性_____。

5．?dāng)?shù)據(jù)庫系統(tǒng)中的四類用戶分別為____數(shù)據(jù)庫管理員、數(shù)據(jù)庫設(shè)計員、應(yīng)用程序員、終端用戶_____。

6．在存取數(shù)據(jù)庫的數(shù)據(jù)的過程中，使用了兩個數(shù)據(jù)緩沖區(qū)，分別為___系統(tǒng)_____緩沖區(qū)和____用戶 ____緩沖區(qū)。

7．學(xué)生關(guān)系中的班級號屬性與班級關(guān)系中的班級號主碼屬性相對應(yīng)，則____班級號____為學(xué)生關(guān)系中的___外碼___。

8．設(shè)一個關(guān)系A(chǔ)具有a1個屬性和a2個元組，關(guān)系B具有b1個屬性和b2個元組，則關(guān)系A(chǔ)′B具有___a1+b1____個屬性和____ a2′b2 ____個元組。

9．設(shè)一個學(xué)生關(guān)系為S(學(xué)生號，姓名)，課程關(guān)系為C(課程號，課程名)，選課關(guān)系為X(學(xué)生號，課程號，成績)，求出所有選課的學(xué)生信息的運(yùn)算表達(dá)式為_____?學(xué)生號(X)______與____S ____的自然連接。

10．在一個關(guān)系R中，若存在X→Y和X→Z，則存在_____ X→(Y,Z)_______，稱此為函數(shù)依賴的合并性規(guī)則。

三、填空 (每空1分,共20分)

1．若一個關(guān)系的任何非主屬性都不部分依賴依賴于任何候選碼，則稱該關(guān)系達(dá)到____第二____范式。

2．在SQL中，列級完整性約束分為__6__種情況，表級完整性約束分為__4__種情況。

在SQL中，每個視圖中的列可以來自不同的___表___，它是在原有表的基礎(chǔ)上____建立____的邏輯意義上的新關(guān)系。

在SQL的查詢語句中，group by選項(xiàng)實(shí)現(xiàn)____分組統(tǒng)計______功能，order by選項(xiàng)實(shí)現(xiàn)對結(jié)果表的____排序_____功能。

5．對于較復(fù)雜的系統(tǒng)，概念設(shè)計階段的主要任務(wù)是：首先根據(jù)系統(tǒng)的各個局部應(yīng)用畫出各自對應(yīng)的____局部ER圖______，然后再進(jìn)行綜合和整體設(shè)計，畫出_____整體ER圖_____。

6．機(jī)器實(shí)現(xiàn)階段的目標(biāo)是在計算機(jī)系統(tǒng)中得到一個滿足______設(shè)計要求、功能完善、操作方便___的數(shù)據(jù)庫應(yīng)用系統(tǒng)。

7．Access的用戶操作界面由 標(biāo)題欄、菜單欄、工作區(qū) 、工具欄、狀態(tài)欄等五個部分組成。

8．Access“表”結(jié)構(gòu)設(shè)計窗口中上半部分的“表設(shè)計器”是由 字段名稱、數(shù)據(jù)類型、說明 等三列組成。

9．Access中的窗體由 頁眉、主體 和頁腳等三個部分組成。

四、填空 (每空1分,共20分)

設(shè)一個關(guān)系為R(A,B,C,D,E)，它的最小函數(shù)依賴集為FD={A→B,A→C,(C,D)→E}，則該關(guān)系的候選碼為_____(A,D)___，候選碼函數(shù)決定E是___偽傳遞___性。

2．設(shè)一個關(guān)系為R(A,B,C,D,E)，它的最小函數(shù)依賴集為FD={A→B,A→C,(A,D)→E}，該關(guān)系只滿足___第一_____范式，若要規(guī)范化為高一級的范式，則將得到____2____個關(guān)系。

3．在實(shí)際的數(shù)據(jù)庫管理系統(tǒng)中，對數(shù)據(jù)庫的操作方式有_____命令交互、程序執(zhí)行、窗口界面______等三種。

4．在SQL中，主碼約束的關(guān)鍵字為____ primary key________，外碼約束的關(guān)鍵字為______foreignkey ______。

5．基本表屬于全局模式中的表，它是____實(shí)表____，而視圖則屬于局部模式中的表，它是____虛表 ____。

在SQL新版的查詢語句中，select選項(xiàng)實(shí)現(xiàn)投影運(yùn)算，from選項(xiàng)實(shí)現(xiàn)____連接____運(yùn)算，where選項(xiàng)實(shí)現(xiàn)____選擇___運(yùn)算。

7．?dāng)?shù)據(jù)字典是對系統(tǒng)工作流程中____數(shù)據(jù)____和____處理____的描述。

8．關(guān)系數(shù)據(jù)庫系統(tǒng)中的全局模式由若干個基本表所組成，表與表之間的聯(lián)系是通過定義的____主碼____和____外碼____實(shí)現(xiàn)的。

9．在旅店管理中，涉及到的基本表有四個，它們分別為客房表、住宿表、_旅客登記表、消費(fèi)卡表。

10．在設(shè)置或撤消數(shù)據(jù)庫密碼的過程中，密碼對于字母 大小寫 是敏感的。

五、填空 (每空1分,共10分)

1．Access的用戶操作界面由 標(biāo)題欄、菜單欄、工作區(qū) 、工具欄、狀態(tài)欄等五個部分組成。

2．Access“表”結(jié)構(gòu)設(shè)計窗口中上半部分的“表設(shè)計器”是由 字段名稱、數(shù)據(jù)類型 和說明等三列組成。

3．Access中的窗體由 頁眉、主體、頁腳 等三個部分組成。

4．在Access中模塊分為 類模塊 和 標(biāo)準(zhǔn)模塊 兩種類型。

六、根據(jù)主教材第四章所給的商品庫和教學(xué)庫，或者按照下列所給的每條SQL查詢語句寫出相應(yīng)的功能，或者按照下列所給的每種功能寫出相應(yīng)的SQL查詢語句。（每小題4分，共20分）

在名稱為商品庫的數(shù)據(jù)庫中包含有商品表1和商品表2，它們的定義分別為：

商品表1(商品代號 char(8),分類名 char(8),單價 float,數(shù)量 int)

商品表2(商品代號 char(8),產(chǎn)地 char(6),品牌 char(6),)

在名稱為教學(xué)庫的數(shù)據(jù)庫中包含有學(xué)生、課程和選課三個表，它們的定義分別為：

學(xué)生(學(xué)生號 char(7),姓名 char(6),性別 char(2),出生日期 datetime,

專業(yè) char(10),年級 int)

課程(課程號 char(4),課程名 char(10),課程學(xué)分 int

選課(學(xué)生號 char(7),課程號 char(4),成績 int)

1．select distinct 產(chǎn)地from商品表2功能：從商品庫中查詢出所有商品的不同產(chǎn)地。

2．select*from學(xué)生? ? ? ? where 學(xué)生號in(select 學(xué)生號from選課? ? ? ? ? group by 學(xué)生號 having count(*)=1)功能：從教學(xué)庫中查詢出只選修了一門課程的全部學(xué)生。3．select*from學(xué)生? ? ? ? where 學(xué)生號in(select 學(xué)生號from選課? ? ? ? ? group by 學(xué)生號 having count(*)<=2)ornotexists(select*from選課? ? ? ? ? ? ? where 學(xué)生.學(xué)生號=選課.學(xué)生號)功能： 從教學(xué)庫中查詢出最多選修了2門課程（含未選任何課程）的全部學(xué)生。

4．從商品庫中查詢出每類（即分類名相同）商品的最高單價。

select 分類名,max(單價)as最高單價from商品表1group by 分類名

5．從教學(xué)庫中查詢出至少選修了姓名為@m1學(xué)生所選課程中一門課的全部學(xué)生。

select distinct 學(xué)生.*from學(xué)生,選課? ? ? ? where 學(xué)生.學(xué)生號=選課.學(xué)生號and課程號=any(select 課程號from學(xué)生,選課? ? ? ? ? where 學(xué)生.學(xué)生號=選課.學(xué)生號and姓名=@m1)

收藏點(diǎn)贊~~養(yǎng)成好習(xí)慣~

微信搜一搜**【程序員一凡】**關(guān)注這個文縐縐的程序員，關(guān)注后回復(fù)【面試】有我準(zhǔn)備的一線大廠面試資料和簡歷模板，希望大家都能找到心儀的工作，學(xué)習(xí)是一條時而郁郁寡歡，時而開懷大笑的路，加油。如果你通過努力成功進(jìn)入到了心儀的公司，一定不要懈怠放松，職場成長和新技術(shù)學(xué)習(xí)一樣，不進(jìn)則退。如果有幸我們江湖再見！

如果對軟件測試、接口、自動化、性能測試、測試開發(fā)、面試經(jīng)驗(yàn)交流。感興趣可以1079636098，群內(nèi)會有不定期的發(fā)放免費(fèi)的資料鏈接，這些資料都是從各個技術(shù)網(wǎng)站搜集、整理出來的，如果你有好的學(xué)習(xí)資料可以私聊發(fā)我，我會注明出處之后分享給大家。