数据库系统原理.ppt

1、1,数据库系统原理测试,武汉大学国际软件学院,2,答题纸： 总分（答对题数）：,武汉大学国际软件学院,3,问题1：,使用DROP语句撤消基本表时，使用以下哪个子句，可以将下属的视图和约束引用全部撤消？ ANULL BRESTRICT CCASCADE DDISTINCT,武汉大学国际软件学院,4,问题2：,定义基本表时，若要求某一列的值不能为空，则应在定义时使用什么保留字？但如果该列是主键，则可省写。 A NULL B NOT NULL C DISTINCT D. UNIQUE,武汉大学国际软件学院,5,问题3：,当FROM子句中出现多个基本表或视图时，系统将执行什么操作？ A并 B等值联接

2、C自然联接 D笛卡儿积,武汉大学国际软件学院,6,问题4：,在SELECT语句的下列子句中，通常和HAVING子句同时使用的是以下哪项？ AORDER BY子句 BWHERE子句 CGROUP BY子句 D均不需要,武汉大学国际软件学院,7,问题5：,若用如下的SQL语句创建一个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）,武汉大学国际软件学院,8,问题6：,下面定义的4个视图，哪些不能进行更新操作？ 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=女,武汉大学国际软件学院,9,问题7：,当数据库遭到破坏时，为了能迅速恢复，在进行事务处理过程中将对数据库更新的全部内容写入以下哪项？ A副本文件 B日志文件 C检查点文件 D死锁文件,武汉大学国际软件学院,10,问题8：,并发控制的主要方法是采用以下哪种机制？ A口令 B锁 C副本 D. 检查点,武汉大学国际软件

5、学院,11,问题9：,下列SQL语句中，能够实现”收回U4对学生表(STUD)中学号(XH)的修改权”这一功能的是以下哪项？ 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,武汉大学国际软件学院,12,问题10：,关于“死锁”，下列说法中错误的有： A死锁是操作系统中的问题，数据库操作中不存在 B在数据库操作中防止死锁的方法是禁止两个用户

6、同时操作数据库 C当两个用户竞争相同资源时不会发生死锁 D只有出现并发操作时，才有可能出现死锁,武汉大学国际软件学院,13,问题11：,给定三个表：学生表S(S#，SN，SEX，AGE，DEPT)，课程表C(C#，CN）和学生选课表SC(S#，C#，GRADE)，其中：S#为学号，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、判断下面查询是否允许执行，如允许，写出转换到基本表SC上的操作。 SELECT S#，C_NUM FROM S_GRADE WHERE AVG_GRADE80 A不允许查询。因为视图中使用了分组和聚合函数。 B允许。对应的操作为： SELECT S#，COUNT（C#）FROM SC WHERE AVG(GRADE)80 C允许。对应的操作为： SELECT S#,COUNT(C#) FROM SC GROUP BY S# HAVING AVG(GRADE)80 D允许。对应的操作为： SELECT S#, COUNT(C#) FROM SC HAVING AVG(GRADE)80,武汉大学国

8、际软件学院,14,问题12：,向基本表增加一个新列后，原有元组在该列上的值是什么？ ATRUE BFALSE C空值 D不确定,武汉大学国际软件学院,15,问题13：,嵌入式SQL语句中引用共享变量时，必须在变量名前加什么标志？ A逗号 B分号 C句号 D冒号,武汉大学国际软件学院,16,问题14：,在CREATE TABLE语句中实现完整性约束的子句有哪些？ ANOT NULL BPRIMARY KEY CFOREIGN KEY DCHECK,武汉大学国际软件学院,17,问题15：,使用SQL语句进行查询操作时，若希望查询结果不出现重复元组，应在SELECT子句中使用什么保留字？ AUNIQ

9、UE BALL CEXCEPT DDISTINCT,武汉大学国际软件学院,18,问题16：,设有两个事务T1，T2，其并发操作如下所示： T1：T2： 1）读A=10，B=5 2） 读A=10 3）读A=20，B=5 求和25 验证错 正确的评价是哪个？ A该操作不存在问题 B该操作丢失修改 C该操作不能重复读 D该操作读“脏”数据,武汉大学国际软件学院,19,问题17：,将查询SC表的权限授予用户U1，并允许该用户将此权限授予其他用户。实现此功能的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,武汉大学国际软件学院,20,问题18：,有用户组GROUP1和GROUP2，要使这两个组中的所有用户对表TAB具有SELECT权限，使用以下哪个语句？ 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,武汉大学国际软件学院,21,问题19：,嵌入到宿主语言中的SQL语句的处理方式有哪些？ A使用游标 B采用预处理方式 C扩充主语言的编译程序 D. 使用共享变量,武汉大学国际软件学院,22,问题20：,给定三个表：学生表S，课程表C和学生选课表SC，它们的结构分别如下： S（S#，SN，SEX，AGE，DEPT） C（C#，CN） SC（S#，C#，GRADE） 其中：S#为学号，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,武汉大学国际软件学院,23,问题21：,For which of the following database objects can locks be obtained? A. View B. Table C. Trigger D. Buffer Pool,武汉大学国际软件学院,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),武汉大学国际软件学院,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,武汉大学国

15、际软件学院,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,武汉大学国际软件学院,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),武汉大学国际软件学院,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.,武汉大学国际软件学院,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,武汉大学国际软件学院,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),武汉大学国际软件学院,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.,武汉大学国际软件学院,

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,武汉大学国际软件学院,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),武汉大学国际软件学院,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),武汉大学国际软件学院,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.,武汉大学国际软件学院,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,武汉大学国际软件学院,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,武汉大学国际软件学院,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,武汉大学国际软件学院,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,武汉大学国际软件学院,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,武汉大学国际软件学院,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.,武汉大学国际软件学院,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,武汉大学国际软件学院,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,武汉大学国际软件学院,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,武汉大学国际软件学院,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,武汉大学国际软件学院,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,武汉大学国际软件学院,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,武汉大学国际软件学院,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