個人比較在意的047題庫中的若干考題(一)

9. Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seq1
START WITH 100
INCREMENT BY 10
MAXVALUE 200
CYCLE
NOCACHE
The sequence SEQ1 has generated numbers up to the maximum limit of 200.
You issue the following SQL statement: SELECT seq1.nextval FROM dual
What is displayed by the SELECT statement?
A. 1
B. 10
C. 100
D. an error
Answer: A

34. View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables.
Evaluate the following SQL statement:
SELECT oi.order_id, product_id, order_date
FROM order_items oi JOIN orders o
USING(order_id)
Which statement is true regarding the execution of this SQL statement?
A. The statement would not execute because table aliases are not allowed in the JOIN clause.
B. The statement would not execute because the table alias prefix is not used in the USING clause.
C. The statement would not execute because all the columns in the SELECT clause
are not prefixed with table aliases.
D. The statement would not execute because the column part of the USING clause cannot have a
qualifier in the SELECT list.
Answer: D

36. Evaluate the following statements:
CREATE TABLE digits
(id NUMBER(2),
description VARCHAR2(15))
INSERT INTO digits VALUES (1,'ONE')
UPDATE digits SET description ='TWO' WHERE id=1
INSERT INTO digits VALUES (2,'TWO')
COMMIT
DELETE FROM digits
SELECT description FROM digits
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
What would be the outcome of the above query?
A. It would not display any values.
B. It would display the value TWO once.
C. It would display the value TWO twice.
D. It would display the values ONE, TWO, and TWO.
Answer: C

42. SCOTT is a user in the database.
Evaluate the commands issued by the DBA:
1 CREATE
ROLE mgr
2 GRANT
CREATE TABLE, SELECT
ON oe.orders
TO mgr
3 GRANT
mgr, create table TO SCOTT
Which statement is true regarding the execution of the above commands?
A. Statement 1 would not execute because the WITH GRANT option is missing.
B. Statement 1 would not execute because the IDENTIFIED BY clause is missing.
C. Statement 3 would not execute because role and system privileges cannot be granted
together in a single GRANT statement.
D. Statement 2 would not execute because system privileges and object privileges cannot
be granted together in a single GRANT command.
Answer: D

43. Which statement best describes the GROUPING function?
A. It is used to set the order for the groups to be used for calculating the grand totals and subtotals.
B. It is used to form. various groups to calculate total and subtotals created using ROLLUP and CUBE
operators.
C. It is used to identify if the NULL value in an expression is a stored NULL value or created by ROLLUP
or CUBE.
D. It is used to specify the concatenated group expressions to be used for calculating the grand totals and
subtotals.
Answer: C

45. View the Exhibit and examine the structure of the CUSTOMERS table.
CUSTOMER_VU is a view based on CUSTOMERS_BR1 table which has the same structure as
CUSTOMERS table. CUSTOMERS needs to be updated to reflect the latest information about the
customers.
What is the error in the following MERGE statement?
MERGE INTO customers c
USING customer_vu cv
ON (c.customer_id = cv.customer_id)
WHEN MATCHED THEN
UPDATE SET
c.customer_id = cv.customer_id,
c.cust_name = cv.cust_name,
c.cust_email = cv.cust_email,
c.income_level = cv.income_level
WHEN NOT MATCHED THEN
INSERT VALUES(cv.customer_id,cv.cust_name,cv.cust_email,cv,income_level)
WHERE cv.income_level >100000
A. The CUSTOMER_ID column cannot be updated.
B. The INTO clause is misplaced in the command.
C. The WHERE clause cannot be used with INSERT.
D. CUSTOMER_VU cannot be used as a data source.
Answer: A

50. View the Exhibit and examine the data in the DEPARTMENTS tables.
Evaluate the following SQL statement:
SELECT department_id "DEPT_ID", department_name , 'b'
FROM departments
WHERE department_id=90
UNION
SELECT department_id, department_name DEPT_NAME, 'a'
FROM departments
WHERE department_id=10
Which two ORDER BY clauses can be used to sort the output of the above statement? (Choose two.)
A. ORDER BY 3
B. ORDER BY 'b'
C. ORDER BY DEPT_ID
D. ORDER BY DEPT_NAME
Answer: AC(如果每個select中對應的欄位都有alias,那麼order by時必須加"，
如果只有第一個select有alias，那麼 order by時絕對不能加"，否則報錯)

60. View the Exhibit and examine DEPARTMENTS and the LOCATIONS tables.
Evaluate the following SQL statement:
SELECT location_id, city
FROM locations
l WHERE NOT EXISTS (SELECT location_id
FROM departments
WHERE location_id <> l.location_id)
This statement was written to display LOCATION_ID and CITY where there are no departments located.
Which statement is true regarding the execution and output of the command?
A. The statement would execute and would return the desired results.
B. The statement would not execute because the = comparison operator is missing
in the WHERE clause of the outer query.
C. The statement would execute but it will return zero rows because the WHERE clause
in the inner query should have the = operator instead of <>.
D. The statement would not execute because the WHERE clause in the outer query is
missing the column name for comparison with the inner query result.
Answer: C