1z0-071: Oracle Datbase 12c SQL - Free download from Exam Files

View the exhibit and examine the descriptions of the DEPT and LOCATIONS tables.

You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.

Which SQL statement would you execute to accomplish the task?

UPDATE dept dSET city = ALL (SELECT city
FROM locations l
WHERE d.location_id = l.location_id);
UPDATE dept dSET city = (SELECT city
FROM locations l)
WHERE d.location_id = l.location_id;
UPDATE dept dSET city = ANY (SELECT city
FROM locations l)
UPDATE dept dSET city = (SELECT city
FROM locations l
WHERE d.location_id = l.location_id);

Correct answer: D

View the exhibits and examine the structures of the COSTS and PROMOTIONS tables.

Evaluate the following SQL statement:

SQL> SELECT prod_id FROM costs

WHERE promo_id IN (SELECT promo_id FROM promotions

WHERE promo_cost < ALL

(SELECT MAX(promo_cost) FROM promotions

GROUP BY (promo_end_datepromo_begin_date)));

What would be the outcome of the above SQL statement?

It displays prod IDs in the promo with the lowest cost.
It displays prod IDs in the promos with the lowest cost in the same time interval.
It displays prod IDs in the promos with the highest cost in the same time interval.
It displays prod IDs in the promos which cost less than the highest cost in the same time interval.

Correct answer: D

Which statements are true? (Choose all that apply.)

The data dictionary is created and maintained by the database administrator.
The data dictionary views consists of joins of dictionary base tables and user-defined tables.
The usernames of all the users including the database administrators are stored in the data dictionary.
The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.
Both USER_OBJECTS and CAT views provide the same information about all the objects that are owned by the user.
Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary.

Correct answer: CDF

Explanation:

https://docs.oracle.com/cd/B10501_01/server.920/a96524/c05dicti.htm

Which three statements are true regarding group functions? (Choose three.)

They can be used on columns or expressions.
They can be passed as an argument to another group function.
They can be used only with a SQL statement that has the GROUP BY clause.
They can be used on only one column in the SELECT clause of a SQL statement.
They can be used along with the single-row function in the SELECT clause of a SQL statement.

Correct answer: ABE

Explanation:

https://www.safaribooksonline.com/library/view/mastering-oracle-sql/0596006322/ch04.html

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_CONDITI

ON

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

The R_CONSTRAINT_NAME column gives the alternative name for the constraint.
In the second column, 'c' indicates a check constraint.
The STATUS column indicates whether the table is currently in use.
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.

Correct answer: BD

View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.

The PROD_ID column is the foreign key in the SALES tables, which references the PRODUCTS table.

Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.

Evaluate the following CREATE TABLE command:

CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)

AS

SELECT prod_id, cust_id, time_id

FROM sales;

Which statement is true regarding the above command?

The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.
The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table.

Correct answer: A

Examine the data in the CUST_NAME column of the CUSTOMERS table.

CUST_NAME

-------

Renske Ladwig

Jason Mallin

Samuel McCain

Allan MCEwen

Irene Mikilineni

Julia Nayer

You need to display customers' second names where the second name starts with "Mc" or "MC".

Which query gives the required output?

SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customers
WHERE SUBSTR (cust_name, INSTR (cust_name, ' ')+1) LIKE INITCAP ('MC%');
SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customers
WHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) ='Mc';
SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customers
WHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) LIKE 'Mc%';
SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customers
WHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) = INITCAP 'MC%';

Correct answer: C

Evaluate the following query:

SQL> SELECT TRUNC (ROUND(156.00, -1),-1)

FROM DUAL;

What would be the outcome?

150
200
160
16
100

Correct answer: C

Explanation:

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions135.htm https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_2127.htm

The following are the steps for a correlated subquery, listed in random order:

The WHERE clause of the outer query is evaluated.
The candidate row is fetched from the table specified in the outer query.
The procedure is repeated for the subsequent rows of the table, till all the rows are processed.
Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query.

Identify the option that contains the steps in the correct sequence in which the Oracle server evaluates a correlated subquery.

2, 1, 4, 3
4, 1, 2, 3
4, 2, 1, 3
2, 4, 1, 3

Correct answer: D

Explanation:

http://rajanimohanty.blogspot.co.uk/2014/01/correlated-subquery.html

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?

It recovers only the table structure.
It recovers the table structure, data, and the indexes.
It recovers the table structure and data but not the related indexes.
It is not possible to recover the table structure, data, or the related indexes.

Correct answer: D

Explanation:

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

Vendor:	Oracle
Exam Code:	1z0-071
Exam Name:	Oracle Datbase 12c SQL
Date:	Dec 10, 2017
File Size:	6 MB
Downloads:	2

Download Oracle Datbase 12c SQL.1z0-071.CertDumps.2017-12-10.72q.vcex

How to open VCEX files?

Demo Questions

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Question 8

Question 9

Question 10

ProfExam at a 20% markdown