《数据库系统原理》ppt课件

1、数据库系统原理测试武汉大学国际软件学院武汉大学国际软件学院答题纸：答题纸： 总分答对题数：总分答对题数：武汉大学国际软件学院武汉大学国际软件学院题号12345678910答案对错题号11121314151617181920答案对错题号21222324252627282930答案对错题号31323334353637383940答案对错题号41424344454647484950答案对错运用运用DROPDROP语句吊销根本表时，运用以下哪个子语句吊销根本表时，运用以下哪个子句，可以将下属的视图和约束援用全部吊销？句，可以将下属的视图和约束援用全部吊销？A ANULLNULLB BRESTRICTR

2、ESTRICTC CCASCADECASCADED DDISTINCTDISTINCT武汉大学国际软件学院武汉大学国际软件学院定义根本表时，假设要求某一列的值不能为空，定义根本表时，假设要求某一列的值不能为空，那么应在定义时运用什么保管字？但假设该列是那么应在定义时运用什么保管字？但假设该列是主键，那么可省写。主键，那么可省写。A A NULLNULLB B NOT NULLNOT NULLC C DISTINCTDISTINCTD. UNIQUE D. UNIQUE 武汉大学国际软件学院武汉大学国际软件学院当当FROMFROM子句中出现多个根本表或视图时，系统子句中出现多个根本表或视图时，系

3、统将执行什么操作？将执行什么操作？A A 并并B B 等值联接等值联接C C 自然联接自然联接D D 笛卡儿积笛卡儿积武汉大学国际软件学院武汉大学国际软件学院在在SELECTSELECT语句的以下子句中，通常和语句的以下子句中，通常和HAVINGHAVING子子句同时运用的是以下哪项？句同时运用的是以下哪项？A A ORDER BYORDER BY子句子句B B WHEREWHERE子句子句C C GROUP BYGROUP BY子句子句D D 均不需求均不需求武汉大学国际软件学院武汉大学国际软件学院假设用如下的SQL语句创建一个student表：CREATE TABLE student(NO

4、 CHAR4 NOT NULL， NAME CHAR8 NOT NULL，SEX CHAR2，AGE NUMBERIC2可以插入到student表中的是哪一项？A1031，曾华，男，23B1031，曾华，NULL，NULLCNULL，曾华，男，23D1031，NULL，男，23武汉大学国际软件学院武汉大学国际软件学院下面定义的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.

5、CREATE VIEW S AVG_G(S#,AVG_GRADE) AS SELECT S#,AVG(GRADE) FROM SC WHERE GRADE IS NOT NULL GROUP BY S#C. CREATE VIEW S_MALE(S#,SNAME) AS SELECT S#,SNAME FROM S WHERE AGE=20D. CREATE VIEW S_FEMALE(SNAME,AGE) AS SELECT SNAME,AGE FROM S WHERE SEX=女武汉大学国际软件学院武汉大学国际软件学院当数据库遭到破坏时，为了能迅速恢复，在进展当数据库遭到破坏时，为了能迅速

6、恢复，在进展事务处置过程中将对数据库更新的全部内容写入事务处置过程中将对数据库更新的全部内容写入以下哪项？以下哪项？A A副本文件副本文件B B日志文件日志文件C C检查点文件检查点文件D D死锁文件死锁文件武汉大学国际软件学院武汉大学国际软件学院并发控制的主要方法是采用以下哪种机制？并发控制的主要方法是采用以下哪种机制？A A口令口令B B锁锁C C副本副本D. D. 检查点检查点 武汉大学国际软件学院武汉大学国际软件学院以下以下SQLSQL语句中，可以实现收回语句中，可以实现收回U4U4对学生表对学生表(STUD)(STUD)中学号中学号(XH)(XH)的修正权这一功能的是以下的修正权这一

7、功能的是以下哪项？哪项？A.REVOKE UPDATE(XH) ON TABLE FROM U4A.REVOKE UPDATE(XH) ON TABLE FROM U4B.REVOKE UPDATE(XH) ON TABLE FROM PUBLICB.REVOKE UPDATE(XH) ON TABLE FROM PUBLICC.REVOKE UPDATE(XH) ON STUD FROM U4C.REVOKE UPDATE(XH) ON STUD FROM U4D.REVOKE UPDATE(XH) ON STUD FROM PUBLICD.REVOKE UPDATE(XH) ON STUD

8、 FROM PUBLIC武汉大学国际软件学院武汉大学国际软件学院关于关于“死锁，以下说法中错误的有：死锁，以下说法中错误的有：A A死锁是操作系统中的问题，数据库操作中不死锁是操作系统中的问题，数据库操作中不存在存在B B在数据库操作中防止死锁的方法是制止两个在数据库操作中防止死锁的方法是制止两个用户同时操作数据库用户同时操作数据库C C当两个用户竞争一样资源时不会发生死锁当两个用户竞争一样资源时不会发生死锁D D只需出现并发操作时，才有能够出现死锁只需出现并发操作时，才有能够出现死锁 武汉大学国际软件学院武汉大学国际软件学院 给定三个表：学生表S(S#，SN，SEX，AGE，DEPT)，课程

9、表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#;判别下面查询能否允许执行，如允许，写出转换到根本表SC上的操作。 SELECT S#，C_NUM FROM S_GRADE WHERE AVG_GRADE80 A不允许查询。由于视图中运用了分组和聚合函数。B允许

10、。对应的操作为：SELECT S#，COUNTC#FROM SC WHERE AVG(GRADE)80C允许。对应的操作为：SELECT S#,COUNT(C#) FROM SC GROUP BY S# HAVING AVG(GRADE)80D允许。对应的操作为：SELECT S#, COUNT(C#) FROM SC HAVING AVG(GRADE)80 武汉大学国际软件学院武汉大学国际软件学院向根本表添加一个新列后，原有元组在该列上向根本表添加一个新列后，原有元组在该列上的值是什么？的值是什么？A ATRUETRUEB BFALSEFALSEC C空值空值D D不确定不确定武汉大学国际软

11、件学院武汉大学国际软件学院嵌入式嵌入式SQLSQL语句中援用共享变量时，必需在变语句中援用共享变量时，必需在变量名前加什么标志？量名前加什么标志？A A逗号逗号B B分号分号C C句号句号D D冒号冒号武汉大学国际软件学院武汉大学国际软件学院在在CREATE TABLECREATE TABLE语句中实现完好性约束的子句语句中实现完好性约束的子句有哪些？有哪些？A ANOT NULLNOT NULLB BPRIMARY KEYPRIMARY KEYC CFOREIGN KEYFOREIGN KEYD DCHECKCHECK武汉大学国际软件学院武汉大学国际软件学院运用运用SQLSQL语句进展查询操

12、作时，假设希望查询语句进展查询操作时，假设希望查询结果不出现反复元组，应在结果不出现反复元组，应在SELECTSELECT子句中运用什子句中运用什么保管字？么保管字？A AUNIQUEUNIQUEB BALLALLC CEXCEPTEXCEPTD DDISTINCTDISTINCT武汉大学国际软件学院武汉大学国际软件学院设有两个事务设有两个事务T1T1，T2T2，其并发操作如下所示：，其并发操作如下所示： T1T1：T2T2：1 1读读A=10A=10，B=5B=52 2 读读A=10A=103 3读读A=20A=20，B=5B=5求和求和25 25 验证错验证错正确的评价是哪个？正确的评价是

13、哪个？A A该操作不存在问题该操作不存在问题B B该操作丧失修正该操作丧失修正C C该操作不能反复读该操作不能反复读D D该操作读该操作读“脏数据脏数据武汉大学国际软件学院武汉大学国际软件学院将查询将查询SC表的权限授予用户表的权限授予用户U1，并允许该用户，并允许该用户将此权限授予其他用户。实现此功能的将此权限授予其他用户。实现此功能的SQL语句语句是哪个？是哪个？AGRANT SELECT TO SC ON U1 WITH PUBLICBGRANT SELECT ON SC TO U1 WITH PUBLICCGRANT SELECT TO SC ON U1 WITH GRANT OPTI

14、ONDGRANT SELECT ON SC TO U1 WITH GRANT OPTION武汉大学国际软件学院武汉大学国际软件学院有用户组有用户组GROUP1GROUP1和和GROUP2GROUP2，要使这两个组中的一，要使这两个组中的一切用户对表切用户对表TABTAB具有具有SELECTSELECT权限，运用以下哪个权限，运用以下哪个语句？语句？A AGRANT SELECT ON TAB TO ALLGRANT SELECT ON TAB TO ALLB BGRANT SELECT ON TAB TO PUBLICGRANT SELECT ON TAB TO PUBLICC CGRANT

15、SELECT ON TAB TO USER GROUP1GRANT SELECT ON TAB TO USER GROUP1， GROUP2GROUP2D DGRANT SELECT ON TAB TO ALL GROUPGRANT SELECT ON TAB TO ALL GROUP武汉大学国际软件学院武汉大学国际软件学院嵌入到宿主言语中的SQL语句的处置方式有哪些？A运用游标B采用预处置方式C扩展主言语的编译程序D. 运用共享变量 武汉大学国际软件学院武汉大学国际软件学院给定三个表：学生表给定三个表：学生表S S，课程表，课程表C C和学生选课表和学生选课表SCSC，它，它们的构造分别如下

16、：们的构造分别如下：S SS#S#，SNSN，SEXSEX，AGEAGE，DEPTDEPTC CC#C#，CNCNSCSCS#S#，C#C#，GRADEGRADE其中：其中：S#S#为学号，为学号，SNSN为姓名，为姓名，SEXSEX为性别，为性别，AGEAGE为年龄，为年龄，DEPTDEPT为系别，为系别，C#C#为课程号，为课程号，CNCN为课程名，为课程名，GRADEGRADE为成果。为成果。 删除没有课程名的课程的正确语句是哪个？删除没有课程名的课程的正确语句是哪个？A ADELETE FROM C WHERE CDELETE FROM C WHERE C =NULL=NULLB BD

17、ELETE FROM C WHERE C=DELETE FROM C WHERE C= C CDELETE FROM C WHERE C IS NULLDELETE FROM C WHERE C IS NULLD DDELETE FROM C WHERE C IS UNKNOWDELETE FROM C WHERE C IS UNKNOW武汉大学国际软件学院武汉大学国际软件学院For which of the following database objects can locks be obtained?A. View B. Table C. Trigger D. Buffer Pool 武

18、汉大学国际软件学院武汉大学国际软件学院Given the following scenario: An Given the following scenario: An application uses a 15 digit value to application uses a 15 digit value to uniquely identify customer transactions. uniquely identify customer transactions. This number is also used for arithmetic This number is also

19、 used for arithmetic operations. Which of the following is the operations. Which of the following is the most efficient data type for the column most efficient data type for the column definition for this purpose?definition for this purpose?A. CHAR A. CHAR B. CLOB B. CLOB C. INTEGER C. INTEGER D. NU

20、MERIC(15,2) D. NUMERIC(15,2) E. DECIMAL(15,0) E. DECIMAL(15,0) 武汉大学国际软件学院武汉大学国际软件学院A 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 table1B. GRANT READ TO user1 ON TABLE table1C. GRA

21、NT SELECT ON TABLE table1 TO user1D. GRANT ACCESS ON TABLE table1 TO user1武汉大学国际软件学院武汉大学国际软件学院Given the following two tableCOUNTRY STAFF ID NAME ID LASTNAME1 Argentina 1 Jones2 Canada 2 Smith3 Cuba4 Germany5 FranceThe statement SELECT * FROM STAFF,COUNTRY will return how many rows?A. 2 B. 4 C. 5 D.

22、7 E. 10 武汉大学国际软件学院武汉大学国际软件学院Given the table definition: CREATE TABLE student(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, na

23、me)B. CREATE INDEX youngest ON student(name, age)C. CREATE INDEX youngest ON student(name, age DESC)D. CREATE INDEX youngest ON student(name DESC) INCLUDE(age)武汉大学国际软件学院武汉大学国际软件学院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

24、 a unique index.B. Unique indexes can be defined in ascending or descending 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.

25、This is not allowed for primary keys because foreign keys cannot contain nulls. 武汉大学国际软件学院武汉大学国际软件学院Which two of the following modes can be used on the lock table statement?A. SHARE MODE B. EXCLUSIVE MODE C. REPEATABLE READ MODED. UNCOMMITTED READ MODE E. INTENT EXCLUSIVE MODE 武汉大学国际软件学院武汉大学国际软件学院Gi

26、ven the following column requirements: Col1 Numeric Identifier- From 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 secondsWhich of the following will minimize the disk space allocate to store the re

27、cords if Job Description has average length of 45?A. create table 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, c

28、ol2 varchar(2), col3 varchar(100), col4 int) 武汉大学国际软件学院武汉大学国际软件学院A view is used instead of a table for users to do which of the following?A. Avoid allocating more disk space per databaseB. Provide users with the ability to define indexesC. Restrict users access to a subset of the table dataD. Avoid

29、allocating frequently used query result tables. 武汉大学国际软件学院武汉大学国际软件学院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 statementSELECT * FR

30、OM dwaine.mytab?A. COL1 COL2 - - 0 record(s) selected B. COL1 COL2 - - 1 2 1 record(s) selectedC. SQLCODE 204 indicating that “DWAINE.MYTAB is an undefined nameD. COL1 COL2 - - 1 2 4 3 2 record(s) selected 武汉大学国际软件学院武汉大学国际软件学院Which of the following statements will create an index and prevent table T

31、1 from containing two or more rows with the same values 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)武汉大学国际软件学院武汉大学国际软件学院Given the following DDL statements CREATE TABLE t1( a IN

32、T, b INT , c INT) CREATE VIEW v1 AS SELECT a, b, c FROM t1 WHERE a 250 WITH CHECK OPTIONWhich 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)武汉大学国际软件学院武汉大学国际软件学院Which o

33、f the following is the best way to restrict user 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.

34、 Create two tables: one with the columns that 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

35、all data must be presented. 武汉大学国际软件学院武汉大学国际软件学院Which of the following describes when indexes can be explicitly referenced by name within an SQL statement? A. When dropping the indexB. When updating the indexC. When selecting on the indexD. When inserting using the index 武汉大学国际软件学院武汉大学国际软件学院Given th

36、e statement: CREATE TABLE t1 ( c1 INTEGER NOT NULL, c2 INTEGER, PRIMARY KEY(c1), FOREIGN KEY(c2) REFERENCES t2 )How many non-unique indexes are defined for table t1?A. 0B. 1C. 2D. 3 武汉大学国际软件学院武汉大学国际软件学院Which of the following CANNOT be used to restrict specific values from being inserted into a colum

37、n in a particular table? A. viewB. indexC. check constraintD. referential constraint武汉大学国际软件学院武汉大学国际软件学院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 r

38、emains active D. Current unit of work moves to pending state 武汉大学国际软件学院武汉大学国际软件学院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 databa

39、se.B. A savepoint implies that 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 cr

40、eated without an active connection to a database, but atomic operations can contain a CONNECT as a sub-statement 武汉大学国际软件学院武汉大学国际软件学院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

41、 statement fails because 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. 武汉大学国际软件学院武汉大学国际软件学院Gi

42、ven the following DDL statement: 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 a

43、s TAB1 武汉大学国际软件学院武汉大学国际软件学院Given the following: TAB1TAB2C1C2CXCY-A11A21B12C22C13D23The following results are desired: C1C2CXCY-A11A21B12-C13C22Which 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

44、 * FROM tab1 FULL OUTER JOIN tab2 ON c1=cx D. SELECT * FROM tab1 LEFT OUTER JOIN tab2 ON c1=cx 武汉大学国际软件学院武汉大学国际软件学院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

45、tab1 WHERE col1 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武汉大学国际软件学院武汉大学国际软件学院Given the two following table definitions: ORG STAFF deptnumb INTEGER id INTEGER deptname CHAR(30) name CHAR(30) manager INTEGER dept INTEGER d

46、ivision CHAR(30) 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.deptn

47、umb=b.dept ORDER BY a.deptnameB. SELECT b.deptname, SUM(a.salary) FROM org a, staff b WHERE a.deptnumb=b.dept ORDER BY a.deptnameC. SELECT a.deptname, SUM(b.salary) FROM org a, staff b WHERE a.deptnumb=b.dept GROUP BY a.deptnameD. SELECT b.deptname, SUM(a.salary) FROM org a, staff b WHERE a.deptnumb

48、=b.dept GROUP BY a.deptname武汉大学国际软件学院武汉大学国际软件学院NamesName NumberWayne Gretzky 99Jaromir Jagr 68Bobby Orr 4Bobby Hull 23Brett Hull 16Mario Lemieux 66Steve Yzerman 19Claude Lemieux 19Mark Messier 11Mats Sundin 13武汉大学国际软件学院武汉大学国际软件学院Given the two following tables:PointsName PointsWayne Gretzky244Jaromir

49、 Jagr 168Bobby Orr 129Bobby Hull 93Brett Hull 121Mario Lemieux189Joe Sakic 94Which 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.nam

50、e=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 RIGHT OU

51、TER JOIN points ON =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 following statements wil

52、l return the data with all managers together, all clerks together and all salespeople together in the output? A. SELECT * FROM staff ORDER BY jobB. SELECT job, name FROM staff GROUP BY name, jobC. SELECT * FROM staff GROUP BY name, job, id, dept, years, salary, commD. SELECT * FROM staff ORDER BY na

53、me, job, id, dept, years, salary, comm 武汉大学国际软件学院武汉大学国际软件学院Given the following table definition: STAFF id INTEGER nameCHAR(20) deptINTEGER jobCHAR(20) yearsINTEGER salaryDECIMAL(10,2) commDECIMAL(10,2)Which of the following SQL statements will return the total number of employees in each department

54、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 BY dept ORDER BY 2 DESCB