Oracle 1Z0-047 問題集

質問 1：
You need to load information about new customers from the NEW_CUST table into the tables CUST and CUST_SPECIAL If a new customer has a credit limit greater than 10,000, then the details have to be inserted into CUST_SPECIAL All new customer details have to be inserted into the CUST table. Which technique should be used to load the data most efficiently?
A. external table
B. the MERGE command
C. the multitable INSERT command
D. INSERT using WITH CHECK OPTION
正解：C

質問 2：
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 display the value TWO twice.
B. It would display the values ONE, TWO, and TWO.
C. It would display the value TWO once.
D. It would not display any values.
正解：A

質問 3：
View the Exhibit and examine the data in ORDERS and ORDER_ITEMS tables.
You need to create a view that displays the ORDER ID, ORDER_DATE, and the total number of
items in each order.
Which CREATE VIEW statement would create the view successfully?

A. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)| NO OF ITEMS'
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date
WITH CHECK OPTION;
B. CREATE OR REPLACE VIEW ord_vu (order_id,order_date)
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)
"NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date;
C. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date;
D. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)
"NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id,o.order_date;
正解：D

質問 4：
Which statement is true regarding external tables?
A. The default REJECT LIMIT for external tables is UNLIMITED.
B. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.
C. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table.
D. The data and metadata for an external table are stored outside the database.
正解：C

質問 5：
Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seql 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 seql.nextval FROM dual;
What is displayed by the SELECT statement?
A. 10
B. an error
C. 1
D. 100
正解：C

質問 6：
View the Exhibit and examine the description of the EMPLOYEES table.
You want to display the EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT_ID for all the
employees who work in the same department and have the same manager as that of the
employee having EMPLOYEE_ID 104. To accomplish the task, you execute the following SQL
statement:
SELECT employee_id, first_name, department_id
FROM employees
WHERE (manager_id, department_id) =(SELECT department_id, manager_id FROM employees
WHERE employee_id = 104)
AND employee_id <> 104;
When you execute the statement it does not produce the desired output. What is the reason for
this?

A. The WHERE clause condition in the main query is using the = comparison operator, instead of EXISTS.
B. The columns in the WHERE clause condition of the main query and the columns selected in the subquery should be in the same order.
C. The WHERE clause condition in the main query is using the = comparison operator, instead of the IN operator.
D. The WHERE clause condition in the main query is using the = comparison operator, instead of the = ANY operator.
正解：B