IBM A2090-730 問題集

質問 1：
The following triggers were defined for a table named SALES in the order shown:
CREATE TRIGGER trigger_a NO CASCADE BEFORE UPDATE ON sales REFERENCING NEW AS new FOR EACH ROW SET new.commission = sale_amt * .05 WHERE invoice = n.invoice;
CREATE TRIGGER trigger_b AFTER INSERT ON sales REFERENCING NEW AS new FOR EACH ROW UPDATE sales SET bill_date = CURRENT DATE + 30 DAYS WHERE invoice = n.invoice;
CREATE TRIGGER trigger_c NO CASCADE BEFORE DELETE ON sales FOR EACH ROW SIGNAL SQLSTATE '75005' SET MESSAGE_TEXT = 'Deletes not allowed!';
Which of the following statements is NOT true?
A. Whenever a row is inserted into the SALES table, the value in the BILL_DATE column is automatically set to 30 days from today
B. Whenever a row in the SALES table is updated, all three triggers are fired but nothing happens because none of the triggers have been coded to trap update operations
C. Once a row has been added to the SALES table, it cannot be removed
D. Each time a row is inserted into the SALES table, trigger TRIGGER_A is fired first, followed by trigger TRIGGER_B
正解：B

質問 2：
Given the following requirements:
Create a table named TESTTAB, which has an identity column named ACTIVITYNO. Define the identity column to generate the values for the column by default. Start the values at 10 and increment by 10. Make the identity column unique.
Which of the following CREATE statements will successfully create this table?
A. CREATE TABLE TESTTAB
(ACTIVITYNO SMALLINT NOT NULL
GENERATED BY DEFAULT AS IDENTITY
(START WITH 10
INCREMENT BY 10),
ACTKWD CHAR(6) NOT NULL,
ACTDESC VARCHAR(20) NOT NULL,
UNIQUE(ACTIVITYNO))
B. CREATE TABLE TESTTAB
(ACTIVITYNO SMALLINT NOT NULL
GENERATED BY DEFAULT AS IDENTITY (START WITH 10 INCREMENT BY 1),
ACTKWD CHAR(6) NOT NULL, ACTDESC VARCHAR(20) NOT NULL, UNIQUE(ACTIVITYNO))
C. CREATE TABLE TESTTAB
(ACTIVITYNO SMALLINT NOT NULL
GENERATED ALWAYS AS IDENTITY
(START WITH 10
INCREMENT BY 10),
ACTKWD CHAR(6) NOT NULL,
ACTDESC VARCHAR(20) NOT NULL,
UNIQUE(ACTIVITYNO))
D. CREATE TABLE TESTTAB
(ACTIVITYNO SMALLINT NOT NULL
GENERATED ALWAYS AS IDENTITY
(START WITH 1
INCREMENT BY 10),
ACTKWD CHAR(6) NOT NULL,
ACTDESC VARCHAR(20) NOT NULL,
UNIQUE(ACTNO))
正解：A

質問 3：
Two applications have created a deadlock cycle in the locking subsystem. If lock timeout is set to 30 and both applications were started at the same time, what action will the deadlock detector take when it "wakes up" and discovers the deadlock?
A. It will go back to sleep for 30 seconds, then if the deadlock still exists, it will randomly pick an application and rollback its current transaction
B. Itwill rollback the current transactions of both applications
C. It will randomly pick an application and rollback its current transaction
D. It will wait 30 seconds, then rollback the current transactions of both applications if the deadlock has not been resolved
正解：C

質問 4：
Given the following CREATE TABLE statement:
CREATE TABLE table2 LIKE table1
Which two of the following will NOT occur when the statement is executed?
A. TABLE2 will have the same column defaults as TABLE1
B. TABLE2 will have the same referential constraints as TABLE1
C. TABLE2 will have the same indexes as TABLE1.
D. TABLE2 will have the samenullability characteristics as TABLE1
E. TABLE2 will have the same column names and column data types as TABLE1
正解：B,C

質問 5：
A "trusted" client needs to communicate with a server that has been configured to use CLIENT authentication. Which of the following will be used to verify passwords?
A. Operating system
B. Client applications
C. User ID/password file
D. Communications layer
正解：A

質問 6：
The following SQL statements were executed in sequence:
CREATE DISTINCT TYPE salary AS decimal(7,2) WITH COMPARISONS;
CREATE TABLE staffsalary(empid INT, empsalary salary);
INSERT INTO staffsalary VALUES (10, 50000), (20, 50000.00);
UPDATE staffsalary SET empsalary = 60000
WHERE salary(50000) = empsalary;
What is the current content of the staffsalary table?
A. ID | EMPSALARY ----------------- 10 | 60000 20 | 50000.00 ----------------
B. ID | EMPSALARY ----------------- 10 | 50000.00 20 | 50000.00 ----------------
C. ID | EMPSALARY ----------------- 10 | 60000.00 20 | 60000.00 ----------------
D. ID | EMPSALARY ----------------- 10 | 60000.00 20 | 50000.00 ----------------
正解：C

質問 7：
Given the following statements:
CREATE TABLE tab1 (c1 INTEGER, c2 CHAR(5));
CREATE VIEW view1 AS SELECT c1, c2 FROM tab1 WHERE c1 < 100;
CREATE VIEW view2 AS SELECT c1, c2 FROM view1
WITH CASCADED CHECK OPTION;
Which of the following INSERT statements will fail to execute?
A. INSERT INTO view1VALUES(100, 'abc')
B. INSERT INTO view1 VALUES (100, 'abc')
C. INSERT INTO view2VALUES(150, 'abc')
D. INSERT INTO view2VALUES(50, 'abc')
正解：C

質問 8：
Given the following two tables:
TAB1 R1
A A A B B C C D E TAB2 R2
A A B B C C D Which of the following queries returns the following result set?
RETVAL
E
A. SELECT DISTINCT r1 ASretval FROM tab1, tab2 WHERE r1 <> r2
B. SELECT r1 ASretval FROM tab1 EXCEPT SELECT r2 AS retval FROM tab2
C. SELECT r1 ASretval FROM tab1 INTERSECT SELECT r2 AS retval FROM tab2
D. SELECT r1 ASretval FROM tab1 UNION SELECT r2 AS retval FROM tab2
正解：B