《數(shù)據(jù)庫(kù)系統(tǒng)原理》ppt課件

1、數(shù)據(jù)庫(kù)系統(tǒng)原理測(cè)試武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院答題紙：答題紙： 總分答對(duì)題數(shù)：總分答對(duì)題數(shù)：武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院題號(hào)12345678910答案對(duì)錯(cuò)題號(hào)11121314151617181920答案對(duì)錯(cuò)題號(hào)21222324252627282930答案對(duì)錯(cuò)題號(hào)31323334353637383940答案對(duì)錯(cuò)題號(hào)41424344454647484950答案對(duì)錯(cuò)運(yùn)用運(yùn)用DROPDROP語(yǔ)句吊銷根本表時(shí)，運(yùn)用以下哪個(gè)子語(yǔ)句吊銷根本表時(shí)，運(yùn)用以下哪個(gè)子句，可以將下屬的視圖和約束援用全部吊銷？句，可以將下屬的視圖和約束援用全部吊銷？A ANULLNULLB BRESTRICTR

2、ESTRICTC CCASCADECASCADED DDISTINCTDISTINCT武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院定義根本表時(shí)，假設(shè)要求某一列的值不能為空，定義根本表時(shí)，假設(shè)要求某一列的值不能為空，那么應(yīng)在定義時(shí)運(yùn)用什么保管字？但假設(shè)該列是那么應(yīng)在定義時(shí)運(yùn)用什么保管字？但假設(shè)該列是主鍵，那么可省寫。主鍵，那么可省寫。A A NULLNULLB B NOT NULLNOT NULLC C DISTINCTDISTINCTD. UNIQUE D. UNIQUE 武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院當(dāng)當(dāng)FROMFROM子句中出現(xiàn)多個(gè)根本表或視圖時(shí)，系統(tǒng)子句中出現(xiàn)多個(gè)根本表或視圖時(shí)，系

3、統(tǒng)將執(zhí)行什么操作？將執(zhí)行什么操作？A A 并并B B 等值聯(lián)接等值聯(lián)接C C 自然聯(lián)接自然聯(lián)接D D 笛卡兒積笛卡兒積武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院在在SELECTSELECT語(yǔ)句的以下子句中，通常和語(yǔ)句的以下子句中，通常和HAVINGHAVING子子句同時(shí)運(yùn)用的是以下哪項(xiàng)？句同時(shí)運(yùn)用的是以下哪項(xiàng)？A A ORDER BYORDER BY子句子句B B WHEREWHERE子句子句C C GROUP BYGROUP BY子句子句D D 均不需求均不需求武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院假設(shè)用如下的SQL語(yǔ)句創(chuàng)建一個(gè)student表：CREATE TABLE student(NO

4、 CHAR4 NOT NULL， NAME CHAR8 NOT NULL，SEX CHAR2，AGE NUMBERIC2可以插入到student表中的是哪一項(xiàng)？A1031，曾華，男，23B1031，曾華，NULL，NULLCNULL，曾華，男，23D1031，NULL，男，23武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院下面定義的4個(gè)視圖，哪些不能進(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.

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=女武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院當(dāng)數(shù)據(jù)庫(kù)遭到破壞時(shí)，為了能迅速恢復(fù)，在進(jìn)展當(dāng)數(shù)據(jù)庫(kù)遭到破壞時(shí)，為了能迅速

6、恢復(fù)，在進(jìn)展事務(wù)處置過程中將對(duì)數(shù)據(jù)庫(kù)更新的全部?jī)?nèi)容寫入事務(wù)處置過程中將對(duì)數(shù)據(jù)庫(kù)更新的全部?jī)?nèi)容寫入以下哪項(xiàng)？以下哪項(xiàng)？A A副本文件副本文件B B日志文件日志文件C C檢查點(diǎn)文件檢查點(diǎn)文件D D死鎖文件死鎖文件武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院并發(fā)控制的主要方法是采用以下哪種機(jī)制？并發(fā)控制的主要方法是采用以下哪種機(jī)制？A A口令口令B B鎖鎖C C副本副本D. D. 檢查點(diǎn)檢查點(diǎn) 武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院以下以下SQLSQL語(yǔ)句中，可以實(shí)現(xiàn)收回語(yǔ)句中，可以實(shí)現(xiàn)收回U4U4對(duì)學(xué)生表對(duì)學(xué)生表(STUD)(STUD)中學(xué)號(hào)中學(xué)號(hào)(XH)(XH)的修正權(quán)這一功能的是以下的修正權(quán)這一

7、功能的是以下哪項(xiàng)？哪項(xiàng)？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武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院關(guān)于關(guān)于“死鎖，以下說法中錯(cuò)誤的有：死鎖，以下說法中錯(cuò)誤的有：A A死鎖是操作系統(tǒng)中的問題，數(shù)據(jù)庫(kù)操作中不死鎖是操作系統(tǒng)中的問題，數(shù)據(jù)庫(kù)操作中不存在存在B B在數(shù)據(jù)庫(kù)操作中防止死鎖的方法是制止兩個(gè)在數(shù)據(jù)庫(kù)操作中防止死鎖的方法是制止兩個(gè)用戶同時(shí)操作數(shù)據(jù)庫(kù)用戶同時(shí)操作數(shù)據(jù)庫(kù)C C當(dāng)兩個(gè)用戶競(jìng)爭(zhēng)一樣資源時(shí)不會(huì)發(fā)生死鎖當(dāng)兩個(gè)用戶競(jìng)爭(zhēng)一樣資源時(shí)不會(huì)發(fā)生死鎖D D只需出現(xiàn)并發(fā)操作時(shí)，才有能夠出現(xiàn)死鎖只需出現(xiàn)并發(fā)操作時(shí)，才有能夠出現(xiàn)死鎖 武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院 給定三個(gè)表：學(xué)生表S(S#，SN，SEX，AGE，DEPT)，課程

9、表C(C#，CN和學(xué)生選課表SC(S#，C#，GRADE)，其中：S#為學(xué)號(hào)，SN為姓名，SEX為性別，AGE為年齡，DEPT為系別，C#為課程號(hào)，CN為課程名，GRADE為成果。對(duì)表SC建立如下視圖：CREATE VIEW S_GRADE(S#,C_NUM,AVG_GRADE)AS SELECT S#,COUNT(C#),AVG(GRADE) FROM SC GROUP BY S#;判別下面查詢能否允許執(zhí)行，如允許，寫出轉(zhuǎn)換到根本表SC上的操作。 SELECT S#，C_NUM FROM S_GRADE WHERE AVG_GRADE80 A不允許查詢。由于視圖中運(yùn)用了分組和聚合函數(shù)。B允許

10、。對(duì)應(yīng)的操作為：SELECT S#，COUNTC#FROM SC WHERE AVG(GRADE)80C允許。對(duì)應(yīng)的操作為：SELECT S#,COUNT(C#) FROM SC GROUP BY S# HAVING AVG(GRADE)80D允許。對(duì)應(yīng)的操作為：SELECT S#, COUNT(C#) FROM SC HAVING AVG(GRADE)80 武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院向根本表添加一個(gè)新列后，原有元組在該列上向根本表添加一個(gè)新列后，原有元組在該列上的值是什么？的值是什么？A ATRUETRUEB BFALSEFALSEC C空值空值D D不確定不確定武漢大學(xué)國(guó)際軟

11、件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院嵌入式嵌入式SQLSQL語(yǔ)句中援用共享變量時(shí)，必需在變語(yǔ)句中援用共享變量時(shí)，必需在變量名前加什么標(biāo)志？量名前加什么標(biāo)志？A A逗號(hào)逗號(hào)B B分號(hào)分號(hào)C C句號(hào)句號(hào)D D冒號(hào)冒號(hào)武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院在在CREATE TABLECREATE TABLE語(yǔ)句中實(shí)現(xiàn)完好性約束的子句語(yǔ)句中實(shí)現(xiàn)完好性約束的子句有哪些？有哪些？A ANOT NULLNOT NULLB BPRIMARY KEYPRIMARY KEYC CFOREIGN KEYFOREIGN KEYD DCHECKCHECK武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院運(yùn)用運(yùn)用SQLSQL語(yǔ)句進(jìn)展查詢操

12、作時(shí)，假設(shè)希望查詢語(yǔ)句進(jìn)展查詢操作時(shí)，假設(shè)希望查詢結(jié)果不出現(xiàn)反復(fù)元組，應(yīng)在結(jié)果不出現(xiàn)反復(fù)元組，應(yīng)在SELECTSELECT子句中運(yùn)用什子句中運(yùn)用什么保管字？么保管字？A AUNIQUEUNIQUEB BALLALLC CEXCEPTEXCEPTD DDISTINCTDISTINCT武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院設(shè)有兩個(gè)事務(wù)設(shè)有兩個(gè)事務(wù)T1T1，T2T2，其并發(fā)操作如下所示：，其并發(fā)操作如下所示： T1T1：T2T2：1 1讀讀A=10A=10，B=5B=52 2 讀讀A=10A=103 3讀讀A=20A=20，B=5B=5求和求和25 25 驗(yàn)證錯(cuò)驗(yàn)證錯(cuò)正確的評(píng)價(jià)是哪個(gè)？正確的評(píng)價(jià)是

13、哪個(gè)？A A該操作不存在問題該操作不存在問題B B該操作喪失修正該操作喪失修正C C該操作不能反復(fù)讀該操作不能反復(fù)讀D D該操作讀該操作讀“臟數(shù)據(jù)臟數(shù)據(jù)武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院將查詢將查詢SC表的權(quán)限授予用戶表的權(quán)限授予用戶U1，并允許該用戶，并允許該用戶將此權(quán)限授予其他用戶。實(shí)現(xiàn)此功能的將此權(quán)限授予其他用戶。實(shí)現(xiàn)此功能的SQL語(yǔ)句語(yǔ)句是哪個(gè)？是哪個(gè)？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武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院有用戶組有用戶組GROUP1GROUP1和和GROUP2GROUP2，要使這兩個(gè)組中的一，要使這兩個(gè)組中的一切用戶對(duì)表切用戶對(duì)表TABTAB具有具有SELECTSELECT權(quán)限，運(yùn)用以下哪個(gè)權(quán)限，運(yùn)用以下哪個(gè)語(yǔ)句？語(yǔ)句？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武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院嵌入到宿主言語(yǔ)中的SQL語(yǔ)句的處置方式有哪些？A運(yùn)用游標(biāo)B采用預(yù)處置方式C擴(kuò)展主言語(yǔ)的編譯程序D. 運(yùn)用共享變量 武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院給定三個(gè)表：學(xué)生表給定三個(gè)表：學(xué)生表S S，課程表，課程表C C和學(xué)生選課表和學(xué)生選課表SCSC，它，它們的構(gòu)造分別如下

16、：們的構(gòu)造分別如下：S SS#S#，SNSN，SEXSEX，AGEAGE，DEPTDEPTC CC#C#，CNCNSCSCS#S#，C#C#，GRADEGRADE其中：其中：S#S#為學(xué)號(hào)，為學(xué)號(hào)，SNSN為姓名，為姓名，SEXSEX為性別，為性別，AGEAGE為年齡，為年齡，DEPTDEPT為系別，為系別，C#C#為課程號(hào)，為課程號(hào)，CNCN為課程名，為課程名，GRADEGRADE為成果。為成果。 刪除沒有課程名的課程的正確語(yǔ)句是哪個(gè)？刪除沒有課程名的課程的正確語(yǔ)句是哪個(gè)？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武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院For which of the following database objects can locks be obtained?A. View B. Table C. Trigger D. Buffer Pool 武

18、漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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) 武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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 武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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)武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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. 武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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 武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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) 武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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. 武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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 武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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)武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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)武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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. 武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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 武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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 武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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 武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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 武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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. 武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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 武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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 武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院NamesName NumberWayne Gretzky 99Jaromir Jagr 68Bobby Orr 4Bobby Hull 23Brett Hull 16Mario Lemieux 66Steve Yzerman 19Claude Lemieux 19Mark Messier 11Mats Sundin 13武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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 武漢大學(xué)國(guó)際軟件學(xué)院武漢大學(xué)國(guó)際軟件學(xué)院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