Oracle 1Z0-047 問題集

質問 1：
View the Exhibit and examine the structure of the ORDER_ITEMS table. You need to display the ORDER_ID of the order that has the highest total value among all the
orders in the ORDER_ITEMS table. Which query would produce the desired output?
ORDER ITEMS

A. SELECT order_id FROM order_items
WHERE(unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items) GROUP
BY order_id;
B. SELECT order_id FROM order_items
WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items GROUP
BY order_id);
C. SELECT order_id FROM order_items
WHERE(unit_price*quantity) = MAX(unit_price*quantity) GROUP BY order_id;
D. SELECT order_id FROM order_items GROUP BY order_id
HAVING SUM(unit_price*quantity) =(SELECT MAX(SUM(unit_price*quantity)j FROM
order_items GROUP BY order_id);
正解：D

質問 2：
View the Exhibit and examine the structure of the MARKS_DETAILS and MARKStables.
Which is the best method to load data from the MARKS DETAILStable to the MARKStable?

A. Conditional ALL INSERT
B. Conditional FIRST INSERT
C. Pivoting INSERT
D. Unconditional INSERT
正解：C

質問 3：
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

質問 4：
View the Exhibit and examine the data in the PRODUCTS table.
Which statement would add a column called PRICE, which cannot contain NULL?

A. ALTER TABLE products
ADD price NUMBER(8,2) DEFAULT NOT NULL;
B. ALTER TABLE products
ADD price NUMBER(8,2) NOT NULL;
C. ALTER TABLE products
ADD price NUMBER(8,2) DEFAULT 0 NOT NULL;
D. ALTER TABLE products
ADD price NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL;
正解：C

質問 5：
Given below is a list of datetime data types and examples of values stored in them in a random
order:
Datatype Example
1) INTERVAL YEAR TO MONTH a) 2003-04-15 8:00:00 -8:00'
2) TIMESTAMP WITH LOCAL TIME ZONE b) '-K)6 03:30:16.000000'
3) TIMESTAMP WITH TIME ZONE c) '17-JUN-03 12.00.00.000000 AM'4)INTERVAL DAY TO
SECOND d) '402-00'
Identify the option that correctly matches the data types with the values.
A. 1-d.2-c.3-b.4-a
B. 1-b.2-a.3-c.4-d
C. 1-d.2-c.3-a.4-b
D. 1-b.2-a, 3-d, 4-c
正解：C

質問 6：
The details of the order ID, order date, order total, and customer ID are obtained from the ORDERS table. If the order value is more than 30000, the details have to be added to the LARGE_DRDERS table. The order ID, order date, and order total should be added to the ORDER_HISTORY table, and order ID and customer ID should be added to the CUST_HISTORY table. Which multitable INSERT statement would you use?
A. Conditional ALL INSERT
B. Conditional FIRST INSERT
C. Pivoting INSERT
D. Unconditional INSERT
正解：A

質問 7：
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

質問 8：
View the Exhibit and examine the structure of the EMP table which is not partitioned and not an
index-organized table.
Evaluate the following SQL statement:
ALTER TABLE emp DROP COLUMN first_name;
Which two statements are true regarding the above command? (Choose two.)

A. The FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the above SQL statement.
B. The FIRST_NAME column would be dropped provided it does not contain any data.
C. The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is used.
D. The FIRST_NAME column would be dropped provided at least one or more columns remain in the table.
正解：C,D

質問 9：
Which CREATE TABLE statement is valid?
A. CREATE TABLE ord_details
(ord_no NUMBER(2) PRIMARY KEY,
item_no NUMBER(3) PRIMARY KEY,
ord_date date NOT NULL);
B. CREATE TABLE ord_details
(ord_no NUMBER(2) UNIQUE, NOT NULL,
item_no NUMBER(3),
ord_date date DEFAULT SYSDATE NOT NULL);
C. CREATE TABLE ord_details
(ord_no NUMBER(2),
item_no NUMBER(3),
ord_date date DEFAULT SYSDATE NOT NULL,
CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));
D. CREATE TABLE ord_details
(ord_no NUMBER(2) ,
item_no NUMBER(3),
ord_date date DEFAULT NOT NULL,
CONSTRAINT ord_uq UNIQUE (ord_no),
CONSTRAINT ord_pk PRIMARY KEY (ord_no));
正解：C