IBM A2090-730 問題集

質問 1：
Given the following expression:
SELECT QUANTITY,
CASE WHEN ITEMCODE = '099' THEN "SILVER"
WHEN ITEMCODE = '788' THEN "GOLD"
WHEN ITEMCODE = '899' THEN "PLATINUM"
ELSE ERROR
END
FROM SUPPLIER
What will be the result of the query if the following data is evaluated by the CASE expression?
QUANTITY ITEMCODE
3 099 4 099 1 788 1 899 5 009 3 788 1 899
A. 3, SILVER 4, GOLD 1, SILVER 1, GOLD 5, PLATINUM 3, SILVER 1, GOLD
B. 3, SILVER 4, SILVER 1, GOLD 1, GOLD 5, SILVER 3, ERROR 1, PLATINUM
C. 3, SILVER 4, SILVER 1, PLATINUM 1, ERROR 5, SILVER 3, GOLD 1, PLATINUM
D. 3, SILVER 4, SILVER 1, GOLD 1, PLATINUM 5, ERROR 3, GOLD 1, PLATINUM
正解：D

質問 2：
The opening of cursor CSR01 produces the following result set:
STUDENT LASTNM FIRSTNM CLASSNO 123 Brown John T100 213 Bailey James T100 312 Carter Arlene T210 465 Chas Devon T305 546 Davis Steven T405
If this Fetch statement is executed:
FETCH csr01 INTO :studnum, :firstname, :lastname, :class
Which of the following DELETE statements will cause this row to be deleted?
A. DELETE FROM tab01 WHERE CURRENT OF csr01 WITH RR
B. DELETE ALL FROM tab01 FOR CURRENT OF csr01
C. DELETE * FROM tab01 WHERE CURRENT csr01 WITH CS
D. DELETE FROM tab01 FOR CURRENT csr01 WITH RS
正解：A

質問 3：
Which two of the following are optional and do not have to be specified when creating a table?
A. Column name
B. Table name
C. Default constraint
D. Column data type
E. NOT NULL constraint
正解：C,E

質問 4：
If the following SQL statement is executed:
CREATE TABLE sales
(invoice_no NOT NULL PRIMARY KEY,
sales_date DATE,
sales_amt NUMERIC(7,2))
IN tbsp0, tbsp1, tbsp2, tbsp3
PARTITION BY RANGE (sales_date NULLS FIRST)
(STARTING '1/1/2007' ENDING '12/31/2007'
EVERY 3 MONTHS)
Which of the following statements is true?
A. When resolving queries against the SALES table, each partition used is scanned asynchronously and the results of each partition scan are merged to produce the result data set returned
B. Data can be quickly rolled in and out of the SALES table by using the ATTACH PARTITION and DETACH PARTITION clauses of the ALTER TABLE statement
C. Administrative tasks such as backing up, restoring, and reorganizing data stored in the SALES table must be done at the table level; not at the partition level
D. If an index is created for the SALES table, its data must be stored in table space TBSP0
正解：B

質問 5：
Given the following table:
CURRENT_EMPLOYEES
EMPID INTEGER NOT NULL
NAME CHAR(20)
SALARY DECIMAL(10,2)
PAST_EMPLOYEES
EMPID INTEGER NOT NULL
NAME CHAR(20)
SALARY DECIMAL(10,2)
Assuming both tables contain data, which of the following statements will NOT successfully add
data to table CURRENT_EMPLOYEES?
A. INSERT INTOcurrent_employees (empid) VALUES (10)
B. INSERT INTOcurrent_employees (name, salary) VALUES (SELECT name, salary FROM past_employees WHERE empid = 20)
C. INSERT INTOcurrent_employees SELECT empid, name, salary FROM past_employees WHERE empid = 20
D. INSERT INTOcurrent_employees VALUES (10, 'JAGGER', 85000.00)
正解：B

質問 6：
Which of the following will begin a new unit of work?
A. The first FETCH of a cursor
B. The BEGIN TRANSACTION statement
C. The CONNECT statement
D. The first executable SQL statement
正解：D

質問 7：
A view is created with the following statement:
CREATE VIEW v1 AS SELECT col1, col2, col3, col4 FROM t1 WHERE col4 > 1000 WITH CHECK OPTION
What is the effect of the CHECK OPTION clause?
A. Any row inserted or updated through view V1 must meet the condition that col4 > 1000.
B. Any row inserted or updated through view V1 must meet the condition that col4 > 1000 and no row in table T1 can be updated such that col4 <= 1000, but new rows in the table can be inserted with col4 <= 1000.
C. At view creation, DB2 will check the data in table T1, and if in any row doesn't meet the condition col4 > 1000, the view creation will be rejected.
D. From now on, any row inserted or updated in table T1 must meet the condition that col4 > 1000, but existing rows in the table are not checked.
正解：A

質問 8：
Which of the following is used to indicate a column will not accept NULL values and can be referenced in another table's foreign key specification?
A. Default constraint
B. Unique constraint
C. Check constraint
D. Informational constraint
正解：B