Most Recent Oracle Database 12c SQL 1Z0-071 Training

NEW QUESTION 1
View the Exhibit and examine the data in the PRODUCT_INFORMATION table.

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

• A. displaying all the products whose minimum list prices are more than average list price of products having the status orderable
• B. displaying the total number of products supplied by supplier 102071 and having product status OBSOLETE
• C. displaying the number of products whose list prices are more than the average list price
• D. displaying all supplier IDs whose average list price is more than 500
• E. displaying the minimum list price for each product status

Answer: AC

NEW QUESTION 2
Which two statements are true regarding constraints?

• A. A foreign key column cannot contain null values.
• B. A column with the UNIQUE constraint can contain null values.
• C. A constraint is enforced only for INSERT operation on the table.
• D. A constraint can be disabled even if the constraint column contains data.
• E. All constraints can be defined at the column level and at the table level.

Answer: BD

NEW QUESTION 3
You must write a query that prompts users for column names and conditions every time it is executed. (Choose the best answer.)
The user must be prompted only once for the table name. Which statement achieves those objectives?

• A. SELECT &col1, '&col2'FROM &tableWHERE &&condition = '&cond';
• B. SELECT &col1, &col2 FROM "&table"WHERE &condition =&cond;
• C. SELECT &col1, &col2 FROM &&tableWHERE &condition = &cond;
• D. SELECT &col1, &col2 FROM &&tableWHERE &condition = &&cond

Answer: C

NEW QUESTION 4
Which three statements are true regarding the SQL WHERE and HAVING clauses?

• A. The HAVING clause conditions can have aggregating functions.
• B. The HAVING clause conditions can use aliases for the columns.
• C. The WHERE and HAVING clauses cannot be used together in a SQL statement.
• D. The WHERE clause is used to exclude rows before grouping data.
• E. The HAVING clause is used to exclude one or more aggregated results after grouping data.

Answer: ADE

NEW QUESTION 5
Which three tasks can be performed using SQL functions built into Oracle Database?

• A. displaying a date in a nondefault format
• B. finding the number of characters in an expression
• C. substituting a character string in a text expression with a specified string
• D. combining more than two columns or expressions into a single column in the output

Answer: ABC

NEW QUESTION 6
Examine the structure of the EMPLOYEES table. NameNull?Type
---------------------- ------------ EMPLOYEE_IDNOT NULLNUMBER(6) FIRST_NAMEVARCHAR2(20) LAST_NAMENOT NULLVARCHAR2(25) EMAILNOT NULLVARCHAR2(25) PHONE NUMBERVARCHAR2(20) HIRE_DATENOT NULLDATE JOB_IDNOT NULLVARCHAR2(10) SALARYNUMBER(8,2) COMMISSION_PCTNUMBER(2,2) MANAGER_IDNUMBER(6) DEPARTMENT_IDNUMBER(4)
There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.
You want to display the last names and manager IDs of employees who work for the same manager as the employee whose EMPLOYEE_ID is 123.
Which query provides the correct output?

• A. SELECT e.last_name, m.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.manager_id = m.employee_id)AND e.employee_id = 123;
• B. SELECT e.last_name, m.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.employee_id = m.manager_id)WHERE e.employee_id = 123;
• C. SELECT e.last_name, e.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.employee_id = m.employee_id)WHERE e.employee_id = 123;
• D. SELECT m.last_name, e.manager_idFROM employees e LEFT OUTER JOIN employees mon (e.manager_id = m.manager_id)WHERE e.employee_id = 123;

Answer: B

NEW QUESTION 7
Examine the structure of the CUSTOMERS table: (Choose two.)

CUSTNO is the PRIMARY KEY.
You must determine if any customers' details have been entered more than once using a different CUSTNO, by listing all duplicate names.
Which two methods can you use to get the required result?

• A. Subquery
• B. Self-join
• C. Full outer-join with self-join
• D. Left outer-join with self-join
• E. Right outer-join with self-join

Answer: AB

NEW QUESTION 8
View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the PRIMARY KEY in the ORDERS table.

Evaluate the following CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id) AS
SELECT order_id.order_date,customer_id FROM orders;
Which statement is true regarding the above command?

• A. The NEW_ODRDERS table would not get created because the DEFAULT value cannot be specified in the column definition.
• B. The NEW_ODRDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.
• C. The NEW_ODRDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
• D. The NEW_ODRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.

Answer: B

NEW QUESTION 9
Which statement is true about Data Manipulation Language (DML)?

• A. DML automatically disables foreign ley constraints when modifying primary key values in the parent table.
• B. Each DML statement forms a transaction by default.
• C. A transaction can consist of one or more DML statements.
• D. DML disables foreign key constraints when deleting primary key values in the parent table, only when the ON DELETE CASCADE option is set for the foreign key constraint.

Answer: C

NEW QUESTION 10
View the Exhibit and examine the data in the PRODUCTS table. (Choose the best answer.)

You must display product names from the PRODUCTS table that belong to the 'Software/other' category with minimum prices as either $2000 or $4000 and with no unit of measure.
You issue this query:
SQL > SELECT prod_name, prod_category, prod_min_price FROM products
Where prod_category LIKE '%Other%' AND (prod_min_price = 2000 OR prod_min_price = 4000) AND prod_unit_of_measure <> ' ';
Which statement is true?

• A. It executes successfully but returns no result.
• B. It executes successfully and returns the required result.
• C. It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.
• D. It generates an error because the condition specified for the PROD_CATEGORY column is not valid.

Answer: A

NEW QUESTION 11
View the Exhibit and examine, the description for the SALES and CHANNELS tables. (Choose the best answer.)

You issued this SQL statement:
INSERT INTO SALES VALUES (23, 2300, SYSDATE, (SELECT CAHNNEL_ID
FROM CHANNELS
WHERE CHANNEL_DESC='DIRECT SALES'), 12, 1, 500);
Which statement is true regarding the result?

• A. The statement will fail because the sub-query in the VALUES clause is not enclosed within single quotation marks.
• B. The statement will fail because a subquery cannot be used in a VALUES clause.
• C. The statement will execute and a new row will be inserted in the SALES table.
• D. The statement will fail because the VALUES clause is not required with the subquery.

Answer: C

NEW QUESTION 12
Which three statements are true regarding subqueries?

• A. Multiple columns or expressions can be compared between the main query and subquery.
• B. Subqueries can contain ORDER BY but not the GROUP BY clause.
• C. Main query and subquery can get data from different tables.
• D. Subqueries can contain GROUP BY and ORDER BY clauses.
• E. Main query and subquery must get data from the same tables.
• F. Only one column or expression can be compared between the main query and subquery.

Answer: ACD

Explanation:
References:
http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj13658.html

NEW QUESTION 13
Which two statements are true regarding subqueries? (Choose two.)

• A. A subquery can appear on either side of a comparison operator.
• B. Only two subqueries can be placed at one level.
• C. A subquery can retrieve zero or more rows.
• D. A subquery can be used only in SQL query statements.
• E. There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.

Answer: AC

NEW QUESTION 14
Which two statements are true regarding constraints?

• A. A table can have only one primary key and one foreign key.
• B. A table can have only one primary key but multiple foreign keys.
• C. Only the primary key can be defined at the column and table levels.
• D. The foreign key and parent table primary key must have the same name.
• E. Both primary key and foreign key constraints can be defined at both column and table levels.

Answer: BE

NEW QUESTION 15
Examine the structure of the INVOICE table. NameNull?Type
-------------------------------------------------- INV_NONOT NULLNUMBER(3) INV_DATEDATE INV_AMTNUMBER(10,2)
Which two SQL statements would execute successfully?

• A. SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete')FROM invoice;
• B. SELECT inv_no, NVL2(inv_amt, inv_date, 'Not Available')FROM invoice;
• C. SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate)FROM invoice;
• D. SELECT inv_no, NVL2(inv_amt, inv_amt*.25, 'Not Available')FROM invoice;

Answer: AC

NEW QUESTION 16
View the Exhibits and examine PRODUCTS and SALES tables. Exhibit 1

Exhibit 2

You issue the following query to display product name the number of times the product has been sold:

What happens when the above statement is executed?

• A. The statement executes successfully and produces the required output.
• B. The statement produces an error because a subquery in the FROM clause and outer-joins cannot be used together.
• C. The statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM clause.
• D. The statement produces an error because ITEM_CNT cannot be displayed in the outer query.

Answer: A

NEW QUESTION 17
Evaluate the following SELECT statement and view the exhibit to examine its output:
SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status, FROM user_constraints
WHERE table_name = 'ORDERS'; CONSTRAINT_NAME
CON SEARCH_CONDITION R_CONSTRAINT_NAME DELETE_RULE
STATUS ORDER_DATE_NN C
"ORDER_DATE" IS NOT NULL ENABLED ORDER_CUSTOMER_ID_NN C
"CUSTOMER_ID" IS NOT NULL ENABLED ORDER_MODE_LOV C
order _mode in ('direct', 'online') ENABLED
ORDER TOTAL MIN C
order total >= 0 ENABLED ORDER PK
P ENABLED
ORDERS CUSTOMER ID R
CUSTOMERS ID SET NULL ENABLED
ORDERS SALES REP R
EMP EMP ID SET NULL ENABLED
Which two statements are true about the output? (Choose two.)

• A. The R_CONSTRAINT_NAME column gives the alternative name for the constraint.
• B. In the second column, 'c' indicates a check constraint.
• C. The STATUS column indicates whether the table is currently in use.
• D. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table.

Answer: BD

NEW QUESTION 18
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 only the table structure.
• B. It recovers the table structure, data, and the indexes.
• C. It recovers the table structure and data but not the related indexes.
• D. It is not possible to recover the table structure, data, or the related indexes.

Answer: D

Explanation:
References:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9003.htm

NEW QUESTION 19
......