1、1,數(shù)據(jù)庫系統(tǒng)原理測試,武漢大學(xué)國際軟件學(xué)院,2,答題紙： 總分（答對題數(shù)）：,武漢大學(xué)國際軟件學(xué)院,3,問題1：,使用DROP語句撤消基本表時，使用以下哪個子句，可以將下屬的視圖和約束引用全部撤消？ ANULL BRESTRICT CCASCADE DDISTINCT,武漢大學(xué)國際軟件學(xué)院,4,問題2：,定義基本表時，若要求某一列的值不能為空，則應(yīng)在定義時使用什么保留字？但如果該列是主鍵，則可省寫。 A NULL B NOT NULL C DISTINCT D. UNIQUE,武漢大學(xué)國際軟件學(xué)院,5,問題3：,當(dāng)FROM子句中出現(xiàn)多個基本表或視圖時，系統(tǒng)將執(zhí)行什么操作？ A并 B等值聯(lián)接

2、C自然聯(lián)接 D笛卡兒積,武漢大學(xué)國際軟件學(xué)院,6,問題4：,在SELECT語句的下列子句中，通常和HAVING子句同時使用的是以下哪項？ AORDER BY子句 BWHERE子句 CGROUP BY子句 D均不需要,武漢大學(xué)國際軟件學(xué)院,7,問題5：,若用如下的SQL語句創(chuàng)建一個student表： CREATE TABLE student(NO CHAR（4） NOT NULL， NAME CHAR（8） NOT NULL， SEX CHAR（2）， AGE NUMBERIC（2） 可以插入到student表中的是哪一項？ A（1031，曾華，男，23） B（1031，曾華，NULL，NULL

3、） C（NULL，曾華，男，23） D（1031，NULL，男，23）,武漢大學(xué)國際軟件學(xué)院,8,問題6：,下面定義的4個視圖，哪些不能進(jìn)行更新操作？ A. CREATE VIEW S_G(S#,SNAME,CNAME,GRADE) AS SELECT S.S#,SNAME,CNAME,GRADE FROM S,SC,C WHERE S.S#=SC.S# AND SC.C#=C.C# B. CREATE VIEW S AVG_G(S#,AVG_GRADE) AS SELECT S#,AVG(GRADE) FROM SC WHERE GRADE IS NOT NULL GROUP BY S# C

4、. CREATE VIEW S_MALE(S#,SNAME) AS SELECT S#,SNAME FROM S WHERE AGE=20 D. CREATE VIEW S_FEMALE(SNAME,AGE) AS SELECT SNAME,AGE FROM S WHERE SEX=女,武漢大學(xué)國際軟件學(xué)院,9,問題7：,當(dāng)數(shù)據(jù)庫遭到破壞時，為了能迅速恢復(fù)，在進(jìn)行事務(wù)處理過程中將對數(shù)據(jù)庫更新的全部內(nèi)容寫入以下哪項？ A副本文件 B日志文件 C檢查點文件 D死鎖文件,武漢大學(xué)國際軟件學(xué)院,10,問題8：,并發(fā)控制的主要方法是采用以下哪種機制？ A口令 B鎖 C副本 D. 檢查點,武漢大學(xué)國際軟件

5、學(xué)院,11,問題9：,下列SQL語句中，能夠?qū)崿F(xiàn)”收回U4對學(xué)生表(STUD)中學(xué)號(XH)的修改權(quán)”這一功能的是以下哪項？ A.REVOKE UPDATE(XH) ON TABLE FROM U4 B.REVOKE UPDATE(XH) ON TABLE FROM PUBLIC C.REVOKE UPDATE(XH) ON STUD FROM U4 D.REVOKE UPDATE(XH) ON STUD FROM PUBLIC,武漢大學(xué)國際軟件學(xué)院,12,問題10：,關(guān)于“死鎖”，下列說法中錯誤的有： A死鎖是操作系統(tǒng)中的問題，數(shù)據(jù)庫操作中不存在 B在數(shù)據(jù)庫操作中防止死鎖的方法是禁止兩個用戶

6、同時操作數(shù)據(jù)庫 C當(dāng)兩個用戶競爭相同資源時不會發(fā)生死鎖 D只有出現(xiàn)并發(fā)操作時，才有可能出現(xiàn)死鎖,武漢大學(xué)國際軟件學(xué)院,13,問題11：,給定三個表：學(xué)生表S(S#，SN，SEX，AGE，DEPT)，課程表C(C#，CN）和學(xué)生選課表SC(S#，C#，GRADE)，其中：S#為學(xué)號，SN為姓名，SEX為性別，AGE為年齡，DEPT為系別，C#為課程號，CN為課程名，GRADE為成績。 對表SC建立如下視圖： CREATE VIEW S_GRADE(S#,C_NUM,AVG_GRADE) AS SELECT S#,COUNT(C#),AVG(GRADE) FROM SC GROUP BY S#;

7、判斷下面查詢是否允許執(zhí)行，如允許，寫出轉(zhuǎn)換到基本表SC上的操作。 SELECT S#，C_NUM FROM S_GRADE WHERE AVG_GRADE80 A不允許查詢。因為視圖中使用了分組和聚合函數(shù)。 B允許。對應(yīng)的操作為： SELECT S#，COUNT（C#）FROM SC WHERE AVG(GRADE)80 C允許。對應(yīng)的操作為： SELECT S#,COUNT(C#) FROM SC GROUP BY S# HAVING AVG(GRADE)80 D允許。對應(yīng)的操作為： SELECT S#, COUNT(C#) FROM SC HAVING AVG(GRADE)80,武漢大學(xué)國

8、際軟件學(xué)院,14,問題12：,向基本表增加一個新列后，原有元組在該列上的值是什么？ ATRUE BFALSE C空值 D不確定,武漢大學(xué)國際軟件學(xué)院,15,問題13：,嵌入式SQL語句中引用共享變量時，必須在變量名前加什么標(biāo)志？ A逗號 B分號 C句號 D冒號,武漢大學(xué)國際軟件學(xué)院,16,問題14：,在CREATE TABLE語句中實現(xiàn)完整性約束的子句有哪些？ ANOT NULL BPRIMARY KEY CFOREIGN KEY DCHECK,武漢大學(xué)國際軟件學(xué)院,17,問題15：,使用SQL語句進(jìn)行查詢操作時，若希望查詢結(jié)果不出現(xiàn)重復(fù)元組，應(yīng)在SELECT子句中使用什么保留字？ AUNIQ

9、UE BALL CEXCEPT DDISTINCT,武漢大學(xué)國際軟件學(xué)院,18,問題16：,設(shè)有兩個事務(wù)T1，T2，其并發(fā)操作如下所示： T1：T2： 1）讀A=10，B=5 2） 讀A=10 3）讀A=20，B=5 求和25 驗證錯 正確的評價是哪個？ A該操作不存在問題 B該操作丟失修改 C該操作不能重復(fù)讀 D該操作讀“臟”數(shù)據(jù),武漢大學(xué)國際軟件學(xué)院,19,問題17：,將查詢SC表的權(quán)限授予用戶U1，并允許該用戶將此權(quán)限授予其他用戶。實現(xiàn)此功能的SQL語句是哪個？ AGRANT SELECT TO SC ON U1 WITH PUBLIC BGRANT SELECT ON SC TO U1

10、 WITH PUBLIC CGRANT SELECT TO SC ON U1 WITH GRANT OPTION DGRANT SELECT ON SC TO U1 WITH GRANT OPTION,武漢大學(xué)國際軟件學(xué)院,20,問題18：,有用戶組GROUP1和GROUP2，要使這兩個組中的所有用戶對表TAB具有SELECT權(quán)限，使用以下哪個語句？ AGRANT SELECT ON TAB TO ALL BGRANT SELECT ON TAB TO PUBLIC CGRANT SELECT ON TAB TO USER GROUP1， GROUP2 DGRANT SELECT ON TAB

11、 TO ALL GROUP,武漢大學(xué)國際軟件學(xué)院,21,問題19：,嵌入到宿主語言中的SQL語句的處理方式有哪些？ A使用游標(biāo) B采用預(yù)處理方式 C擴充主語言的編譯程序 D. 使用共享變量,武漢大學(xué)國際軟件學(xué)院,22,問題20：,給定三個表：學(xué)生表S，課程表C和學(xué)生選課表SC，它們的結(jié)構(gòu)分別如下： S（S#，SN，SEX，AGE，DEPT） C（C#，CN） SC（S#，C#，GRADE） 其中：S#為學(xué)號，SN為姓名，SEX為性別，AGE為年齡，DEPT為系別，C#為課程號，CN為課程名，GRADE為成績。 刪除沒有課程名的課程的正確語句是哪個？ ADELETE FROM C WHERE C

12、.CN=NULL BDELETE FROM C WHERE C.CN= CDELETE FROM C WHERE C.CN IS NULL DDELETE FROM C WHERE C.CN IS UNKNOW,武漢大學(xué)國際軟件學(xué)院,23,問題21：,For which of the following database objects can locks be obtained? A. View B. Table C. Trigger D. Buffer Pool,武漢大學(xué)國際軟件學(xué)院,24,問題22：,Given the following scenario: An application

13、 uses a 15 digit value to uniquely identify customer transactions. This number is also used for arithmetic operations. Which of the following is the most efficient data type for the column definition for this purpose? A. CHAR B. CLOB C. INTEGER D. NUMERIC(15,2) E. DECIMAL(15,0),武漢大學(xué)國際軟件學(xué)院,25,問題23：,A

14、 user creates the table TABLE1. Which of the following statements would explicitly give USER1 the ability to read rows from the table? A. GRANT VIEW TO user1 ON TABLE table1 B. GRANT READ TO user1 ON TABLE table1 C. GRANT SELECT ON TABLE table1 TO user1 D. GRANT ACCESS ON TABLE table1 TO user1,武漢大學(xué)國

15、際軟件學(xué)院,26,問題24：,Given the following two table COUNTRY STAFF ID NAME ID LASTNAME 1Argentina 1 Jones 2Canada 2 Smith 3Cuba 4Germany 5France The statement SELECT * FROM STAFF,COUNTRY will return how many rows? A. 2 B. 4 C. 5 D. 7 E. 10,武漢大學(xué)國際軟件學(xué)院,27,問題25：,Given the table definition: CREATE TABLE student

16、(name CHAR(30), age INTEGER) To list the names of the 10 youngest students, which of the following index definition statements on the student table may improve the query performance? A. CREATE INDEX youngest ON student(age, name) B. CREATE INDEX youngest ON student(name, age) C. CREATE INDEX younges

17、t ON student(name, age DESC) D. CREATE INDEX youngest ON student(name DESC) INCLUDE(age),武漢大學(xué)國際軟件學(xué)院,28,問題26：,Why is a unique index not sufficient for creation of a primary key? A. It is sufficient-a primary key is the same thing as a unique index. B.Unique indexes can be defined in ascending or desc

18、ending order. Primary keys must be ascending. C.A unique index can be defined over a column or columns that allow nulls. Primary keys cannot contain nulls. D. A unique index can be defined over a column or columns that allow nulls. This is not allowed for primary keys because foreign keys cannot con

19、tain nulls.,武漢大學(xué)國際軟件學(xué)院,29,問題27：,Which two of the following modes can be used on the lock table statement? A. SHARE MODE B. EXCLUSIVE MODE C. REPEATABLE READ MODE D. UNCOMMITTED READ MODE E. INTENT EXCLUSIVE MODE,武漢大學(xué)國際軟件學(xué)院,30,問題28：,Given the following column requirements: Col1 Numeric Identifier- Fr

20、om 1 to 1000000 Col2 Job Code-Variable, 1 to 2 character long Col3 Job Description-Variable, 1 to 100 characters long Col4 Job Length-Length of Job in seconds Which of the following will minimize the disk space allocate to store the records if Job Description has average length of 45? A. create tabl

21、e tab1 (col1 int, col2 char(2), col3 char(100), col4 int) B.create table tab1 (col1 int, col2 varchar(2), col3 char(100), col4 int) C. create table tab1(col1 int, col2 char(2), col3 varchar(100), col4 int) D. create table tab1(col1 int, col2 varchar(2), col3 varchar(100), col4 int),武漢大學(xué)國際軟件學(xué)院,31,問題2

22、9：,A view is used instead of a table for users to do which of the following? A. Avoid allocating more disk space per database B. Provide users with the ability to define indexes C.Restrict users access to a subset of the table data D. Avoid allocating frequently used query result tables.,武漢大學(xué)國際軟件學(xué)院,

23、32,問題30：,Given the following transaction: CREATE TABLE dwaine.mytab(col1 INT, col2 INT) INSERT INTO dwaine.mytab VALUES(1,2) INSERT INTO dwaine.mytab VALUES(4,3) ROLLBACK Which of the following would be returned from the statement SELECT * FROM dwaine.mytab? A. COL1 COL2 - - 0 record(s) selected B.

24、COL1 COL2 - - 1 2 1 record(s) selected C. SQLCODE 204 indicating that “DWAINE.MYTAB” is an undefined name D. COL1 COL2 - - 1 2 4 3 2 record(s) selected,武漢大學(xué)國際軟件學(xué)院,33,問題31：,Which of the following statements will create an index and prevent table T1 from containing two or more rows with the same value

25、s for column(c1) A. CREATE UNIQUE INDEX ix4 ON t1(c1) B. CREATE DISTINCT INDEX ix1 ON t1(c1) C. CREATE UNIQUE INDEX on t1( c1 , c2) D. CREATE DISTINCT INDEX ix3 on t1(c1, c2),武漢大學(xué)國際軟件學(xué)院,34,問題32：,Given the following DDL statements CREATE TABLE t1( a INT, b INT , c INT) CREATE VIEW v1 AS SELECT a, b,

26、c FROM t1 WHERE a 250 WITH CHECK OPTION Which of the following INSERT statements will fail? A. INSERT INTO t1 VALUES(200,2,3) B. INSERT INTO v1 VALUES(200,2,3) C. INSERT INTO t1 VALUES(300,2,3) D. INSERT INTO v1 VALUES(300,2,3),武漢大學(xué)國際軟件學(xué)院,35,問題33：,Which of the following is the best way to restrict u

27、ser access to a subset of column in a table? A. Only grant access to the columns within a table that a user is allowed to see. B. Create a view that only includes the columns a user is allowed to see. Grant the user access to the view, not the base table. C. Create two tables: one with the columns t

28、hat a user is allowed to see, and one that has the confidential columns, and use a join when all data must be presented. D. Create two tables: one with the columns that a user is allowed to see, and one that has the confidential columns, and use a union when all data must be presented.,武漢大學(xué)國際軟件學(xué)院,36

29、,問題34：,Which of the following describes when indexes can be explicitly referenced by name within an SQL statement? A. When dropping the index B. When updating the index C. When selecting on the index D. When inserting using the index,武漢大學(xué)國際軟件學(xué)院,37,問題35：,Given the statement: CREATE TABLE t1 ( c1 INTE

30、GER NOT NULL, c2 INTEGER, PRIMARY KEY(c1), FOREIGN KEY(c2) REFERENCES t2 ) How many non-unique indexes are defined for table t1? A. 0 B. 1 C. 2 D. 3,武漢大學(xué)國際軟件學(xué)院,38,問題36：,Which of the following CANNOT be used to restrict specific values from being inserted into a column in a particular table? A. view

31、B. index C. check constraint D. referential constraint,武漢大學(xué)國際軟件學(xué)院,39,問題37：,Which of the following occurs if an application ends abnormally during an active unit of work? A. Current unit of work is committed B. Current unit of work is rolled back C. Current unit of work remains active D. Current unit

32、 of work moves to pending state,武漢大學(xué)國際軟件學(xué)院,40,問題38：,Which of the following describes why savepoints are NOT allowed inside an atomic unit of work? A. Atomic units of work span multiple databases, but savepoints are limited to units of work which operate on a single database. B. A savepoint implies t

33、hat a subset of the work may be allowed to succeed, while atomic operations must succeed or fail as a unit. C. A savepoint requires an explicit commit to be released, and commit statements are not allowed in atomic operations such as compound SQL. D. A savepoint cannot be created without an active c

34、onnection to a database, but atomic operations can contain a CONNECT as a sub-statement,武漢大學(xué)國際軟件學(xué)院,41,問題39：,Which of the following is the result of the following SQL statement: ALTER TABLE table1 ADD col2 INT WITH DEFAULT A.The statement fails with a negative SQL code. B.The statement fails because

35、no default value is specified. C.A new column called COL2 is added to TABLE1 and populated with zeros. D.A new column called COL2 is added to TABLE1 and populated with nulls. E.A new column called COL2, which cannot contain nulls, is added to TABLE1.,武漢大學(xué)國際軟件學(xué)院,42,問題40：,Given the following DDL state

36、ment: CREATE TABLE newtab1 LIKE tab1 Which of the following would occur as a result of the statement execution? A. NEWTAB1 has same triggers as TAB1 B. NEWTAB1 is populated with TAB1 data C. NEWTAB1 has the same primary key as TAB1 D. NEWTAB1 columns have same attributes as TAB1,武漢大學(xué)國際軟件學(xué)院,43,問題41：,

37、Given the following: TAB1TAB2 C1C2CXCY - A11A21 B12C22 C13D23 The following results are desired: C1C2CXCY - A11A21 B12- C13C22 Which of the following joins will yield the desired results? A. SELECT * FROM tab1, tab2 WHERE c1=cx B. SELECT * FROM tab1 INNER JOIN tab2 ON c1=cx C. SELECT * FROM tab1 FUL

38、L OUTER JOIN tab2 ON c1=cx D. SELECT * FROM tab1 LEFT OUTER JOIN tab2 ON c1=cx,武漢大學(xué)國際軟件學(xué)院,44,問題42：,Given the following SQL statements: CREATE TABLE tab1 (col1 INT) CREATE TABLE tab2 (col1 INT) INSERT INTO tab1 VALUES (NULL),(1) INSERT INTO tab2 VALUES (NULL),(1) SELECT COUNT(*) FROM tab1 WHERE col1

39、IN (SELECT col1 FROM tab2) Which of the following is the result of the SELECT COUNT(*) statement? A. 1 B. 2 C. 3 D. 4 E. 0,武漢大學(xué)國際軟件學(xué)院,45,問題43：,Given the two following table definitions: ORG STAFF deptnumb INTEGER id INTEGER deptname CHAR(30) name CHAR(30) manager INTEGER dept INTEGER division CHAR(3

40、0) job CHAR(20) location CHAR(30) years INTEGER salary DECIMAL(10,2) comm DECIMAL(10,2) Which of the following statements will display each department, by name, and the total salary of all employees in the department? A. SELECT a.deptname, SUM(b.salary) FROM org a, staff b WHERE a.deptnumb=b.dept OR

41、DER BY a.deptname B. SELECT b.deptname, SUM(a.salary) FROM org a, staff b WHERE a.deptnumb=b.dept ORDER BY a.deptname C. SELECT a.deptname, SUM(b.salary) FROM org a, staff b WHERE a.deptnumb=b.dept GROUP BY a.deptname D. SELECT b.deptname, SUM(a.salary) FROM org a, staff b WHERE a.deptnumb=b.dept GR

42、OUP BY a.deptname,武漢大學(xué)國際軟件學(xué)院,46,問題44：,Names Name Number Wayne Gretzky 99 Jaromir Jagr 68 Bobby Orr 4 Bobby Hull 23 Brett Hull 16 Mario Lemieux 66 Steve Yzerman 19 Claude Lemieux 19 Mark Messier 11 Mats Sundin 13,武漢大學(xué)國際軟件學(xué)院,Given the two following tables: Points Name Points Wayne Gretzky244 Jaromir J

43、agr 168 Bobby Orr 129 Bobby Hull 93 Brett Hull 121 Mario Lemieux189 Joe Sakic 94,Which of the following statements will display the players Names, numbers and points for all players with an entry in both tables? A. SELECT s, names.number, points.points FROM names INNER JOIN points ON names

44、.name= B. SELECT , names.number, points.points FROM names FULL OUTER JOIN points ON = C. SELECT , names.number, points.points FROM names LEFT OUTER JOIN points ON = D. SELECT , names.number, points.points FROM names R

45、IGHT OUTER JOIN points ON =,47,問題45：,Given the following table definition: STAFF id INTEGER name CHAR(20) dept INTEGER job CHAR(20) years INTEGER salary DECIMAL(10,2) comm DECIMAL(10,2) The job column contains these job types: manager, clerk, and salesperson. Which of the follow

46、ing statements will return the data with all managers together, all clerks together and all salespeople together in the output? A. SELECT * FROM staff ORDER BY job B. SELECT job, name FROM staff GROUP BY name, job C. SELECT * FROM staff GROUP BY name, job, id, dept, years, salary, comm D. SELECT * F

47、ROM staff ORDER BY name, job, id, dept, years, salary, comm,武漢大學(xué)國際軟件學(xué)院,48,問題46：,Given the following table definition: STAFF idINTEGER nameCHAR(20) deptINTEGER jobCHAR(20) yearsINTEGER salaryDECIMAL(10,2) commDECIMAL(10,2) Which of the following SQL statements will return the total number of employee

48、s in each department and the corresponding department id under the following conditions: Only return departments with at least one employee receiving a commission greater than 5000. The result should be sorted by the department count from most to least. A. SELECT dept, COUNT(id) FROM staff WHERE comm 5000 GROUP B