Oracle 1Z0-071 Quiz 74 Topic 4 Questions 366-370

Question: 1

View the exhibit and examine the description of the DEPARTMENTS and EMPLOYEES tables.

q1_1Z0-071

The retrieve data for all the employees for their EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, the following SQL statement was written:

SELECT employee_id, first_name, department_name

FROM employees

NATURAL JOIN departments;

The desired output is not obtained after executing the above SQL statement. What could be the reason for this?

AThe table prefix is missing for the column names in the SELECT clause.

BThe NATURAL JOIN clause is missing the USING clause.

CThe DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.

DThe EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.

Show Answer

Question: 2

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

AWhen the MAXVALUE limit for the sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement.

BDELETE <sequencename> would remove a sequence from the database.

CThe numbers generated by a sequence can be used only for one table.

DCURRVAL is used to refer to the last sequence number that has been generated.

EWhen a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted.

Show Answer

Question: 3

View the exhibit and examine the structure of the CUSTOMERS table.

q3_1Z0-071

Which two tasks would require subqueries or joins to be executed in a single statement?

Afinding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers

Bfinding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'

Clisting of customers who do not have a credit limit and were born before 1980

Dfinding the number of customers, in each city, who's marital status is 'married'.

Elisting of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.

Show Answer

Question: 4

View the exhibit and examine the structure in ORDERS and ORDER_ITEMS tables.

q4_1Z0-071

You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.

Which CREATE VIEW statement would create the views successfully?

ACREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_date;

BCREATE OR REPLACE VIEW ord_vu (order_id, order_date)AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)'NO OF ITEMS'FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_date;

CCREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)'NO OF ITEMS'FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_date;

DCREATE OR REPLACE VIEW ord_vuAS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) ||'NO OF ITEMS'FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_dateWHITH CHECK OPTION;

Show Answer

Question: 5

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?

AIt recovers only the table structure.

BIt recovers the table structure, data, and the indexes.

CIt recovers the table structure and data but not the related indexes.

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

Show Answer

Oracle 1Z0-071 Quiz:74 Topic:4 Questions:366-370