Oracle 1Z0-071 Quiz:73 Topic:14 Questions:361-365

Free Practice Mock Questions Set 361-365 () for Oracle 1Z0-071 Exam, according to official Oracle Database SQL exam syllabus topic # 14

Question: 1

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.

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

Which is the correct sequence in which the Oracle server evaluates a correlated subquery?

A2, 1, 4, 3

B4, 1, 2, 3

C4, 2, 1, 3

D2, 4, 1, 3

Answer : D

Show Answer

Hide Answer

Question: 2

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?

ASELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE SUBSTR (cust_name, INSTR (cust_name, ' ')+1)LIKE INITCAP ('MC%');

BSELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) ='Mc';

CSELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1))LIKE 'Mc%';

DSELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1)) =INITCAP 'MC%';

Answer : C

Show Answer

Hide Answer

Question: 3

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

"ORDER_DATE" IS NOT NULL

ENABLED

ORDER_CUSTOMER_ID_NN

"CUSTOMER_ID" IS NOT NULL

ENABLED

ORDER_MODE_LOV

order _mode in ('direct', 'online')

ENABLED

ORDER TOTAL MIN

order total >= 0

ENABLED

ORDER PK

ENABLED

ORDERS CUSTOMER ID

CUSTOMERS ID

SET NULL

ENABLED

ORDERS SALES REP

EMP EMP ID

SET NULL

ENABLED

Which two statements are true about the output? (Choose two.)

AThe R_CONSTRAINT_NAME column gives the alternative name for the constraint.

BIn the second column, 'c' indicates a check constraint.

CThe STATUS column indicates whether the table is currently in use.

DThe 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 : B, D

Show Answer

Hide Answer

Question: 4

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

AThe data dictionary is created and maintained by the database administrator.

BThe data dictionary views consists of joins of dictionary base tables and user-defined tables.

CThe usernames of all the users including the database administrators are stored in the data dictionary.

DThe USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.

EBoth USER_OBJECTS and CAT views provide the same information about all the objects that are owned by the user.

FViews with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary.

Answer : C, D, F

Show Answer

Hide Answer

Question: 5

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

q5_1Z0-071

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_date-

promo_begin_date)));

What would be the outcome of the above SQL statement?

AIt displays prod IDs in the promo with the lowest cost.

BIt displays prod IDs in the promos with the lowest cost in the same time interval.

CIt displays prod IDs in the promos with the highest cost in the same time interval.

DIt displays prod IDs in the promos which cost less than the highest cost in the same time interval.

Answer : D

Show Answer

Hide Answer

Quiz 69

Quiz 70

Quiz 71

Quiz 72

Quiz 73

Quiz 74

Quiz 75

Quiz 76

Quiz 77