1Z1-047試験無料問題集（264題）「Oracle Database SQL Expert 認定」

出題：1

View the Exhibit and examine the ORDERS table.
The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID column?

A. ALTER TABLE orders
MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);

B. ALTER TABLE orders
ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;

C. ALTER TABLE orders
ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);

D. ALTER TABLE orders
MODIFY customer_id CONSTRAINT orders_cust_id_nn NOT NULL;

正解：D 解答を投票する

出題：2

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 LARGEjDRDERS table. The order ID, order date, and order total should be added to the ORDERJHISTORY table, and order ID and customer ID should be added to the CUSTJHISTORY table. Which multitable INSERT statement would you use?

A. Conditional ALL INSERT

B. Pivoting INSERT

C. Conditional FIRST INSERT

D. Unconditional INSERT

正解：A 解答を投票する

出題：3

View the Exhibit and examine DEPARTMENTS and the LOCATIONS tables.
Evaluate the following SOL statement:
SELECT location_id, city FROM locations I WHERE NOT EXISTS (SELECT location_id FROM departments WHERE location_id <> I. 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 not execute because the = comparison operator is missing in the WHERE clause of the outer query.

B. 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.

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 execute and would return the desired results.

正解：C 解答を投票する

出題：4

Evaluate the following SQL statements that are issued in the given order:
CREATE TABLE emp
(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
enameVARCHAR2(15),
salary NUMBER(8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);
ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;
What would be the status of the foreign key EMP_MGR_FK?

A. It would remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.

B. It would remain disabled and has to be enabled manually using the ALTER TABLE command.

C. It would be automatically enabled and immediate.

D. It would be automatically enabled and deferred.

正解：D 解答を投票する

解説: (GoShiken メンバーにのみ表示されます)

出題：5

View the Exhibit and examine the details of the EMPLOYEES table.
Evaluate the following SQL statements:
Statement 1:
SELECT employee_id, last_name, job_id, manager_id
FROM employees
START WITH employee_id = 101
CONNECT BY PRIOR employee_id = manager_id AND manager_id != 108 ;
Statement 2:
SELECT employee_id, last_name, job_id, manager_id
FROM employees
WHERE manager_id != 108
START WITH employee_id = 101
CONNECT BY PRIOR employee_id = manager_id;
Which two statements are true regarding the above SQL statements? (Choose two.)

A. The output for statement 2 would not display the employee with MANAGER_ID 108 but it would display all the employees below him or her in the hierarchy.

B. The output for statement 1 would display the employee with MANAGER_ID 108 and all the employees below him or her in the hierarchy.

C. The output of statement 1 would neither display the employee with MANAGER_ID 108 nor any employee below him or her in the hierarchy.

D. Statement 2 would not execute because the WHERE clause condition is not allowed in a statement that has the START WITH clause.

正解：A,C 解答を投票する

出題：6

View the Exhibit and examine the descriptions of ORDER_ITEMS and ORDERS tables. You want to display the CUSTOMER_ID, PRODUCT_ID, and total (UNIT_PRICE multiplied by
QUANTITY) for the order placed. You also want to display the subtotals for a CUSTOMER_ID as
well as for a PRODUCT ID for the last six months.
Which SQL statement would you execute to get the desired output?

A. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Total
FROM order_items oi JOIN orders o
ON oi.order_id=o.order_id
GROUP BY ROLLUP (o.customer_id, oi.product_id)
HAVING MONTHS_BETWEEN(order_ date,SYSDATE)<=6;

B. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Total
FROM order_items oi JOIN orders o
ON oi.order_id=o.order_id
GROUP BY ROLLUP (o.customer_id, oi.product_id)
WHERE MONTHS_BETWEEN(order_ date,SYSDATE)>=6;

C. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Total
FROM order_items oi JOIN orders o
ON oi.order_id=o.order_id
WHERE MONTHS_BETWEEN(order date,SYSDATE)<=6
GROUP BY ROLLUP (o.customer_id, oi.product_id);

D. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Total
FROM order_items oi JOIN orders o
ON oi.order_id=o.order_id
GROUP BY ROLLUP (o.customer_id, oi.product_id)
WHERE MONTHS_BETWEEN(order_ date,SYSDATE)<=6;

正解：C 解答を投票する

出題：7

Which two statements are true regarding the execution of the correlated subqueries? (Choose two.)

A. Each row returned by the outer query is evaluated for the results returned by the inner query.

B. The outer query executes only once for the result returned by the inner query.

C. The nested query executes first and then the outer query executes.

D. The nested query executes after the outer query returns the row.

正解：A,D 解答を投票する

出題：8

View the Exhibit and examine the structure of the PRODUCT_INFORMATION and INVENTORIES tables.
You have a requirement from the supplies department to give a list containing PRODUCT_ID, SUPPLIER_ID, and QUANTITY_ON_HAND for all the products wherein QUANTITY_ON_HAND is less than five.
Which two SQL statements can accomplish the task? (Choose two.)

A. SELECT i.product_id, i.quantity_on_hand , pi.supplier_id
FROM product_information pi JOIN inventories i
ON (pi. product_id=i. product_id)
WHERE quantity_on_hand < 5;

B. SELECT i.product_id, i.quantity_on_hand , pi.supplier_id
FROM product_information pi JOIN inventories i
USING (product_id) AND quantity_on_hand < 5;

C. SELECT product_id, quantity_on_hand , supplier_id
FROM product_information
NATURAL JOIN inventories AND quantity_on_hand < 5;

D. SELECT i.product_id, i.quantity_on_hand , pi.supplier_id
FROM product_information pi JOIN inventories i
ON (pi. product_id=i. product_id) AND quantity_on_hand < 5;

正解：A,D 解答を投票する

出題：9

The first DROP operation is performed on PRODUCTS table using the following command:
DROP TABLE products PURGE;
Then you performed the FLASHBACK operation by using the following command:
FLASHBACK TABLE products TO BEFORE DROP;
Which statement describes the outcome of the FLASHBACK command?

A. It recovers the table structure and data but not the related indexes.

B. It recovers the table structure, data, and the indexes.

C. It is not possible to recover the table structure, data, or the related indexes.

D. It recovers only the table structure.

正解：C 解答を投票する

出題：10

Evaluate the following query:
SELECT INTERVAL 300' MONTH,
INTERVAL 54-2' YEAR TO MONTH,
INTERVAL '11:12:10.1234567' HOUR TO SECOND
FROM dual;
What is the correct output of the above query?

A. +00-300, +00-650, +00 11:12:10.123457

B. +25-00, +00-650, +00 11:12:10.123457

C. +00-300, +54-02, +00 11:12:10.123457

D. +25-00, +54-02, +00 11:12:10.123457

正解：D 解答を投票する

1Z1-047試験無料問題集「Oracle Database SQL Expert 認定」