Oracle 1Z0-071 Quiz 50 Topic 6 Questions 246-250

Question: 1

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

q1_1Z0-071

You wrote this SQL statement to retrieve EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, for all employees:

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 Oracle database? (Choose two.)

AWhen the MAXVALUE limit for a sequence is reached, it can be increased by using the ALTER SEQUENCE statement.

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

CThe numbers generated by an explicitly defined sequence can only be used to insert data in one table.

DCURRVAL is used to refer to the most recent sequence number that has been generated for a particular sequence.

EWhen a database instance shuts down abnormally, sequence numbers that have been cached but not used are available again when the 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? (Choose two.)

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, whose 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

Which statement is true about transactions?

AA set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction.

BEach Data Definition Language (DDL) statement executed forms a single transaction.

CA set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.

DA combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.

Show Answer

Question: 5

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

q5_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 view 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

Oracle 1Z0-071 Quiz:50 Topic:6 Questions:246-250