Free Oracle 1z1-071 Exam Dumps Questions & Answers

View the exhibit and examine the description of the DEPARTMENTSand EMPLOYEEStables.

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?

• A.  The DEPARTMENTS table is not used before the EMPLOYEES table in the FROM clause.
• B.  The table prefix is missing for the column names in the SELECT clause.
• C.  The EMPLOYEES and DEPARTMENTS tables have more than one column with the same column name and data type.
• D.  The NATURAL JOIN clause is missing the USING clause.

Which two statements are true regarding multiple-row subqueries? (Choose two.)

• A.  They can contain group functions.
• B.  They always contain a subquery within a subquery.
• C.  They use the < ALL operator to imply less than the maximum.
• D.  They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery.
• E.  They can be used to retrieve multiple rows from a single table only.

Examine thestructure 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;

Examine the description of the BOOKStable:

The table has 100 rows.
Examine this sequence of statements issued in a new session:

Which two statements are true? (Choose two.)

• A.  The second ROLLBACK command undoes the insert.
• B.  The second ROLLBACK command does nothing.
• C.  The second ROLLBACK command replays the delete.
• D.  The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row.
• E.  The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed.

Examine this SELECT statement and view the Exhibit to see its output:

SELECT constraints_name, constraints_type, search_condition, r_constraints_name, delete_rule, status, FROM user_constraints WHERE table_name = 'ORDERS'; Which two statements are true about the output? (Choose two.)

• A.  The STATUS column indicates whether the table is currently in use.
• B.  The R_CONSTRAINT_NAME column contains an alternative name for the constraint.
• C.  In the second column, 'c' indicates a check constraint.
• D.  The DELETE_RULE column indicates the desired state of related rows in the child table when the corresponding row is deleted from the parent table.