1Z0-071 Exam Questions and Answers | Exam-Dumps.net (Page: 11)

QUESTION: 51

View the exhibit for the structure of the STUDENT and FACULTY tables.

You need to display the faculty name followed by the number of students handled by the faculty at the base location.
Examine the following two SQL statements:

Statement 1

SQL>SELECT faculty_name, COUNT(student_id)
FROM student JOIN faculty
USING (faculty_id, location_id)
GROUP BY faculty_name;

Statement 2

SQL>SELECT faculty_name, COUNT(student_id)
FROM student NATURAL JOIN faculty
GROUP BY faculty_name;

Which statement is true regarding the outcome?

Only statement 2 executes successfully and gives the required result.
Only statement 1 executes successfully and gives the required result.
Both statements 1 and 2 execute successfully and give different results.
Both statements 1 and 2 execute successfully and give the same required result.

Answer(s): B

Reveal Solution Next Question

QUESTION: 52

Which statement correctly grants a system privilege?

GRANT CREATE VIEW
ON table1 TO
user1;
GRANT ALTER TABLE
TO PUBLIC;
GRANT CREATE TABLE
TO user1, user2;
GRANT CREATE SESSION
TO ALL;

Answer(s): C

Reveal Solution Next Question

QUESTION: 53

View the exhibit and examine the structure of ORDERS and CUSTOMERS tables.

Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600? Assume there exists only one row with CUST_LAST_NAME as Roberts and CREDIT_LIMIT as 600.

Option A
Option B
Option C
Option D

Answer(s): C

Reveal Solution Next Question

QUESTION: 54

Which three statements are correct regarding indexes? (Choose three.)

A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically attempts to create a unique index.
Indexes should be created on columns that are frequently referenced as part of any expression.
When a table is dropped, corresponding indexes are automatically dropped.
For each DML operation performed on a table, the corresponding indexes are automatically updated if required.

Answer(s): A,C,D

Reference:

http://viralpatel.net/blogs/understanding-primary-keypk-constraint-in-oracle/

Reveal Solution Next Question

QUESTION: 55

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

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?

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

Answer(s): D

Explanation:

Natural join needs only one column to be the same in each table. The EMPLOYEES and DEPARTMENTS tables have two columns that are the same (Department_ID and Manager_ID)

Reveal Solution Next Question

Oracle 1Z0-071 Exam (page: 11) Oracle Database SQL Updated on: 01-Sep-2025

QUESTION: 51

QUESTION: 52

QUESTION: 53

QUESTION: 54

Reference:

QUESTION: 55

Explanation:

Oracle 1Z0-071 Exam (page: 11)
Oracle Database SQL
Updated on: 01-Sep-2025