1z0-071 Practice Test

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


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?

A.

CREATE 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;

B.

CREATE 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;

C.

CREATE 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;

D.

CREATE 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;

Examine the structure of the BOOKS_TRANSACTIONS table:

You want to display the member IDs, due date, and late fee as $2 for all transactions.
Which SQL statement must you execute?

A.

SELECT member_id AS MEMBER_ID, due_date AS DUE_DATE, $2 AS LATE_FEE
FROM BOOKS_TRANSACTIONS;

B.

SELECT member_id 'MEMBER ID', due_date 'DUE DATE', '$2 AS LATE FEE' FROM
BOOKS_TRANSACTIONS;

C.

SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", '$2' AS "LATE
FEE" FROM BOOKS_TRANSACTIONS;

D.

SELECT member_id AS "MEMBER ID", due_date AS "DUE DATE", $2 AS "LATE FEE"
FROM BOOKS_TRANSACTIONS;

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

A.

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

B.

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

C.

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

D.

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

E.

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

Examine the structure of the PROGRAMS table:

Which two SQL statements would execute successfully?

A.

SELECT NVL (ADD_MONTHS (END_DATE,1) SYSDATE) FROM programs;

B.

SELECT TO_DATE (NVL (SYSDATE-END_DATE, SYSDATE)) FROM programs;

C.

SELECT NVL (MONTHS_BETWEEN (start_date, end_date), ‘Ongoing’) FROM
programs;

D.

SELECT NVL (TO_CHAR (MONTHS_BETWEEN (start-date, end_date)), ‘Ongoing’)
FROM programs

View the Exhibit and examine the structure in the EMPLOYEES tables.

Evaluate the following SQL statement:
SELECT employee_id, department_id
FROM employees
WHERE department_id= 50 ORDER BY department_id
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id=90
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id=10;
What would be the outcome of the above SQL statement?

A.

The statement would not execute because the positional notation instead of the column
name should be used with the ORDER BY clause.

B.

The statement would execute successfully and display all the rows in the ascending
order of DEPARTMENT_ID.

C.

The statement would execute successfully but it will ignore the ORDER BY clause and
display the rows in random order.

D.

The statement would not execute because the ORDER BY clause should appear only at
the end of the SQL statement, that is, in the last SELECT statement.