1.設有一個學生選課資料庫,其包括學生表S、課程表C和學生選課表SC三個關系:
S(SNO, SNAME, SAGE, SSEX, SDEP);
C(CNO, CNAME);
SC(SNO, CNO, GRADE)。
其中SNO是學生号,SNAME是學生姓名,SAGE是年齡,SDEP是學生所在系,CNO是課程号,CNAME是課程名稱。
檢索所有比“劉晨”年齡大的學生姓名、年齡和性别。正确的SELECT語句是()。正确答案:A
A.
SELECT SNAME, SAGE, SSEX FROM S
WHERE SAGE > (SELECT SAGE FROM S WHERE SNAME = ‘劉晨’);
B.
SELECT SNAME, SAGE, SSEX FROM S WHERE SNAME = '劉晨';
C.
SELECT SNAME, SAGE, SSEX FROM S
WHERE SAGE> (SELECT SAGE WHERE SNAME = "劉晨");
D.
SELECT SNAME, SAGE, SSEX FROM S WHERE SAGE>'王華'.SAGE;
2.檢索選修課程“C2”的學生中成績最高的學生的學号。正确的SELECT語句是()。正确答案:D
SELECT SNO FROM SC
WHERE CNO='C2' AND GRADE >= (
SELECT GRADE FORM SC WHERE CNO='C2'
);
SELECT SNO FROM SC
WHERE CNO='C2' AND GRADE IN (
SELECT GRADE FORM SC WHERE CNO='C2'
);
SELECT SNO FROM SC
WHERE CNO='C2' AND GRADE NOT IN (
SELECT GRADE FORM SC WHERE CNO='C2'
);
SELECT SNO FROM SC
WHERE CNO='C2' AND GRADE >= ALL (
SELECT GRADE FORM SC WHERE CNO='C2'
);
3.檢索選修四門以上課程的學生總成績,并要求按總成績的降序排列出來。正确的SELECT語句是()。正确答案:B
SELECT SNO, SUM (GRADE) FROM SC GROUP BY SNO ORDER BY 2 DESC HAVING COUNT(*) >= 4;
SELECT SNO, SUM (GRADE) FROM SC GROUP BY SNO HAVING COUNT(*)>=4 ORDER BY 2 DESC;
SELECT SNO, SUM (GRADE) FROM SC HAVING COUNT(*)>=4 GROUP BY SNO ORDER BY 2 DESC;
SELECT SNO, SUM (GRADE) FROM SC ORDER BY 2 DESC GROUP BY SNO HAVING COUNT(*)>=4;
4.以下對觸發器概念了解正确的是()。 正确答案:A
觸發器經常用于加強資料的完整性限制和業務規則等,其由事件來觸發執行。
觸發器一旦被激活,則觸發動作體在觸發器激活後立即執行
在一個資料庫中,觸發器的命名必須是唯一的
觸發器類似于存儲過程,需要使用者手工啟動或程式調用
5.“查詢與‘劉晨’在同一個系學習的學生”的SQL語句為()。
** 正确答案:A、B、D
SELECT SNO, SNAME, SAGE, SSEX, SDEP FROM S
WHERE SDEP IN (SELECT SDEP
FROM S WHERE SNAME = '劉晨');
SELECT * FROM S WHERE SDEP IN (SELECT SDEP
FROM S WHERE SNAME ='劉晨');
SELECT SNO, SNAME, SAGE, SSEX, SDEP FROM S WHERE SNAME = '劉晨';
SELECT S1.SNO, S1.SNAME, S1.SAGE, S1.SSEX, S1.SDEP FROM S S1, S S2
WHERE S1.SDEPT = S2.SDEPT AND S2.SNAME = '劉晨';
6.“查詢計算機科學系年齡大于19歲的學生”的SQL語句為()。
正确答案:A、B
**SELECT * FROM S WHERE SDEP = ‘CS’ EXCEPT SELECT * FROM S WHERE SAGE<=19;
**SELECT * FROM S WHERE SDEP = 'CS' AND SAGE > 19;****
SELECT * FROM S WHERE SDEP = 'CS' AND SAGE <= 19;
SELECT * FROM S WHERE SDEP != 'CS' AND SAGE <= 19;
7.在SQL語言中,為了資料庫的安全性,設定了對資料的存取進行控制的語句,對使用者授權、收回所授權限的關鍵字為()。正确答案:A、B
GRANT
REVOKE
CREATE
DELETE
8.強制存取控制的安全級别高于自主存取控制。正确答案:A
A. 對
B. 錯
9.資料庫加密技術提高了安全性的同時,增加了查詢處理的複雜性,效率降低。正确答案:A
A.對
B.錯