1z1-071試験無料問題集（437題）「Oracle Database SQL 認定」

出題：1

Which two statements are true regarding indexes? (Choose two.)

A. A UNIQUE index can be altered to be non-unique.

B. When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped.

C. A table belonging to one user cannot have an index that belongs to a different user.

D. An update to a table can result in no updates to any of the table's indexes.

E. An update to a table can result in updates to any or all of the table's indexes.

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

出題：2

Which task can be performed by using a single Data Manipulation Language (OML) statement?

A. removing all data only from one single column on which a unique constraint is defined

B. adding a column with a default value when inserting a row into a table

C. removing all data only from one single column on which a primary key constraint is defined

D. adding a column constraint when inserting a row into a table

正解：A 解答を投票する

出題：3

View the Exhibit and examine the structure of the PRODUCTS table

Which two tasks would require subqueries? (Choose two.)

A. display the minimum PROD_LIST_PRICE for each product status

B. display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE

C. display all products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable

D. display suppliers whose PROD_LIST_PRICE is less than 1000

E. display the total number of products supplied by supplier 102 and have a product status as
`OBSOLETE'

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

出題：4

View and Exhibit and examine the structure and data in the INVOICE table.

Which two statements are true regarding data type conversion in query expressions? (Choose two.)

A. inv_date = '15-february-2008' :uses implicit conversion

B. inv_date > '01-02-2008' : uses implicit conversion

C. inv_amt = '0255982' : requires explicit conversion

D. inv_no BETWEEN '101' AND '110' : uses implicit conversion

E. CONCAT(inv_amt, inv_date) : requires explicit conversion

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

出題：5

Which three statements are true about sequences in a single instance Oracle database? (Choose three.)

A. A sequence can issue duplicate values

B. A sequence number that was allocated can be rolled back if a transaction fails

C. A sequence can only be dropped by a DBA

D. A sequence's unallocated cached value are lost if the instance shuts down

E. Sequences can always have gaps

F. Two or more tables cannot have keys generated from the same sequence

正解：B,C,D 解答を投票する

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

出題：6

You need to calculate the number of days from 1st Jan 2007 till date: Dates are stored in the default format of dd-mm-rr.
Which two SQL statements would give the required output? (Choose two.)

A. SELECT SYSDATE - TO_DATE('01-JANUARY-2007') FROM DUAL

B. SELECT TO_DATE(SYSDATE,'DD/MONTH/YYYY')-'01/JANUARY/2007' FROM DUAL;

C. SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY')-'01-JAN-2007' FROM DUAL;

D. SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL;

E. SELECT SYSDATE - '01-JAN-2007' FROM DUAL

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

出題：7

Table HR.EMPLOYEEScontains a row where the EMPLOYEE_IDis 109.
User ALICEhas no privileges to access HR.EMPLOYEES.
User ALICEstarts a session.
User HRstarts a session and successfully executes these statements:
GRANT DELETE ON employees TO alice;
UPDATE employees SET salary = 24000 WHERE employee_id = 109;
In her existing session ALICEthen executes:
DELETE FROM hr.employees WHERE employee_id = 109;
What is the result?

A. The DELETE command will wait for HR'S transaction to end then delete the row.

B. The DELETE command will immediately delete the row.

C. The DELETE command will wait for HR'S transaction to end then return an error.

D. The DELETE command will immediately return an error.

正解：B 解答を投票する

出題：8

View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.
ORDER__ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.

Which DELETE statement would execute successfully?

A. DELETE orders
WHERE order_total < 1000;

B. DELETE order_id
FROM orders
WHERE order_total < 1000;

C. DELETE
FROM orders
WHERE (SELECT order_id
FROM order_items);

D. DELETE orders o, order_items i
WHERE o.order id = i.order id;

正解：A 解答を投票する

出題：9

Evaluate the following SQL statement:

Which statement is true regarding the outcome of the above query?

A. It executes successfully and displays rows in the descending order of PROMO_CATEGORY.

B. It produces an error because the ORDER BY clause should appear only at the end of a compound query-that is, with the last SELECT statement.

C. It executes successfully but ignores the ORDER BY clause because it is not located at the end of the compound statement.

D. It produces an error because positional notation cannot be used in the ORDER BY clause with SET operators.

正解：B 解答を投票する

出題：10

Examine the data in the EMPLOYEES table.

Which statement will compute the total annual compensation for each employee?

A. SELECT last_name, (monthly_salary + monthly_commission_pct) * 12 AS
annual_comp
FROM employees;

B. SELECT last_name, (monthly_salary * 12) + (monthly_commission_pct * 12) AS annual_comp FROM employees;

C. SELECT last_name, (monthly_salary * 12) + (monthly_salary * 12 * monthly_commission_pct) AS annual_comp FROM employees;

D. SELECT last_name, (monthly_salary * 12) + (monthly_salary * 12 * NVL (monthly_commission_pct, 0)) AS annual_comp FROM employees;

正解：C 解答を投票する

出題：11

Examine the commands used to createDEPARTMENT_DETAILS andCOURSE_DETAILS:

You want to generate a list of all department IDs along with any course IDs that may have been assigned to them.
Which SQL statement must you use?

A.

B.

C.

D.

正解：D 解答を投票する

出題：12

View the Exhibit and examine the structure of the ORDER_ITEMS and ORDERStables.

You are asked to retrieve the ORDER_ID,product_ID, and total price (UNIT_PRICEmultiplied by QUANTITY), where the total price is greater than 50,000.
You executed the following SQL statement:
SELECTprder_id, product_id, unit_price*quantity "Total Price"
FROM order_items
WHERE unit_price*quantity > 50000
NATURAL JOIN orders;
Which statement is true regarding the execution of the statement?

A. The statement would not execute because the WHERE clause is before the NATURAL JOIN clause.

B. The statement would execute and provide the desired result.

C. The statement would not execute because the ON keyword is missing in the NATURAL JOIN clause.

D. The statement would not execute because the USING keyword is missing in the NATURAL JOIN clause.

正解：A 解答を投票する

1z1-071試験無料問題集「Oracle Database SQL 認定」