2022年度電大數(shù)據(jù)庫(kù)系統(tǒng)及應(yīng)用形考冊(cè)習(xí)題與參考答案

1、第5章習(xí)題和參照答案一單選題1當(dāng)關(guān)系R和S進(jìn)行連接操作時(shí)，如果R中元組不滿(mǎn)足連接條件，在連接成果中也會(huì)將這些記錄保存下來(lái)操作是（A）。A左外連接B右外連接C內(nèi)連接D自連接2下列聚合函數(shù)中，不忽視空值是（D）。ASUM(列名) BMAX(列名)CAVG(列名)DCOUNT(*)3下列查詢(xún)語(yǔ)句中，錯(cuò)誤是（B）。ASELECT Sno, COUNT(*) FROM SC GROUP BY SnoBSELECT Sno FROM SC GROUP BY Sno WHERE COUNT(*) > 3CSELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) &

2、gt; 3DSELECT Sno FROM SC GROUP BY Sno4現(xiàn)要運(yùn)用Student表查詢(xún)年齡最小學(xué)生姓名和年齡。下列實(shí)現(xiàn)此功能查詢(xún)語(yǔ)句中，對(duì)旳是（D）。ASELECT Sname, MIN(Sage) FROM StudentBSELECT Sname, Sage FROM Student WHERE Sage = MIN(Sage)CSELECT TOP 1 Sname, Sage FROM Student DSELECT TOP 1 Sname, Sage FROM Student ORDER BY Sage5設(shè)SC表中記錄成績(jī)列為：Grade，類(lèi)型為int。若在查詢(xún)成績(jī)時(shí)

3、，盼望將成績(jī)按優(yōu)、良、中、及格和不及格形式顯示，對(duì)旳Case函數(shù)是（C）。ACase GradeWhen 90100THEN'優(yōu)'When 8089THEN'良'When 7079THEN'中'When 6069THEN'及格'Else'不及格'EndBCase When Gradebetween 90 and 100THENGrade = '優(yōu)'When Gradebetween 80 and 89THENGrade = '良'When Gradebetween 70 and 79

4、THENGrade = '中'When Gradebetween 60 and 69THENGrade = '及格'ElseGrade = '不及格'EndCCase When Gradebetween 90 and 100THEN'優(yōu)'When Gradebetween 80 and 89THEN'良'When Gradebetween 70 and 79THEN'中'When Gradebetween 60 and 69THEN'及格'Else'不及格'EndDC

5、ase GradeWhen 90100THEN Grade = '優(yōu)'When 8089THEN Grade = '良'When 7079THEN Grade = '中'When 6069THEN Grade = '及格'ElseGrade = '不及格'End6下列SQL語(yǔ)句中，用于修改表數(shù)據(jù)語(yǔ)句是（C）。AALTER BSELECTCUPDATEDINSERT7設(shè)有Teachers表，該表定義如下：CREATE TABLE Teachers(Tno CHAR(8) PRIMARY KEY,Tname VARCH

6、AR(10) NOT NULL,Age TINYINT CHECK(Age BETWEEN 25 AND 65) )下列插入語(yǔ)句中，不能正旳確施是（D）。AINSERT INTO Teachers VALUES('T100','張宏',NULL)BINSERT INTO Teachers(Tno,Tname,Age) VALUES('T100','張宏',30)CINSERT INTO Teachers(Tno,Tname) VALUES('T100','張宏')DINSERT INTO Teach

7、ers VALUES('T100','張宏')8設(shè)數(shù)據(jù)庫(kù)中已有教材中Student(Sno,Sname,Ssex,Sage,Sdept)、Course(Cno,Cname,Credit,Semster)和SC(Sno,Cno,Grade)表?，F(xiàn)要查詢(xún)學(xué)生選第2學(xué)期開(kāi)設(shè)課程狀況，只需列出學(xué)號(hào)、姓名、所在系和所選課程號(hào)。該查詢(xún)涉及到表是（D）。A僅Student表B僅Student和SC表C僅Student和Course表DStudent、SC和Course表9下列刪除計(jì)算機(jī)系學(xué)生（在student表中）修課記錄（在SC表中）語(yǔ)句，對(duì)旳是（B）。ADELETEFRO

8、M SC JOIN Student b ON S.Sno = b.SnoWHERE Sdept = '計(jì)算機(jī)系'BDELETE FROM SC FROM SC JOIN Student b ON SC.Sno = b.SnoWHERE Sdept = '計(jì)算機(jī)系'CDELETE FROM StudentWHERE Sdept = '計(jì)算機(jī)系'DDELETE FROM SC WHERE Sdept = '計(jì)算機(jī)系'二鑒定題1SELECT * FROM Student WHERE Sname LIKE'張_'語(yǔ)句中&#

9、39;張_' 用于查找姓張學(xué)生。（）2SELECT * FROM Student WHERE Sname LIKE'張李劉%' 語(yǔ)句可以查找姓張、李、劉學(xué)生。（ ）3SELECT COUNT(DISTINCT Sno) FROM SC 語(yǔ)句用于記錄學(xué)生總?cè)藬?shù)。（）4HAVING子句用于對(duì)分組后記錄成果再進(jìn)行過(guò)濾，它用于組而不是對(duì)單個(gè)記錄。（ ）5SELECT * FROM Student INNER JOINSCON Student.Sno = SC.Sno語(yǔ)句可將Student和 SC連接起來(lái)。（）三簡(jiǎn)答題1在聚合函數(shù)中，哪個(gè)函數(shù)在記錄時(shí)不考慮NULL。答：COUNT

10、(*)2在LIKE運(yùn)算符中“%”作用是什么？答：匹配0個(gè)或多種字符。3WHERE Age BETWEEN 20 AND 30子句，查找Age范疇是多少？答：Age不小于等于20并且不不小于等于304WHERE Sdept NOT IN ('CS', 'IS', 'MA')，查找數(shù)據(jù)是什么？答：查找'CS', 'IS', 'MA'三個(gè)系之外其他系。5自連接和一般內(nèi)連接核心辨別是什么？答：自連接中進(jìn)行連接操作表在物理上是一張表，而一般內(nèi)連接進(jìn)行連接表在物理上是兩張表。6外連接和內(nèi)連接核心辨別是什么？答：進(jìn)行外連接兩個(gè)表中，可以有一張表不滿(mǎn)足連接條件，而進(jìn)行內(nèi)連接兩個(gè)表必需所有滿(mǎn)足連接條件。7有關(guān)子查詢(xún)和嵌套子查詢(xún)?cè)趯?shí)行方面核心辨別是什么？答：有關(guān)子查詢(xún)實(shí)行過(guò)程是先外后內(nèi)，而嵌套子查詢(xún)實(shí)行過(guò)程是先內(nèi)后外。并且有關(guān)子查詢(xún)中必需有和外層查詢(xún)關(guān)聯(lián)，而嵌套子查詢(xún)中，內(nèi)、外層查詢(xún)之間沒(méi)有關(guān)