1z0-071 Practice Test

View the exhibit for the structure of the STUDENT and FACULTY tables.
STUDENT
NameNull?Type
-------------------------
STUDENT_IDNOT NULLNUMBER(2)
STUDENT_NAMEVARCHAR2(20)
FACULTY_IDVARCHAR2(2)
LOCATION_IDNUMBER(2)
FACULTY
NameNull?Type
-------------------------
FACULTY_IDNOT NULLNUMBER(2)
FACULTY_NAMEVARCHAR2(20)
LOCATION_IDNUMBER(2)
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?

A.

Only statement 2 executes successfully and gives the required result.

B.

Only statement 1 executes successfully and gives the required result.

C.

Both statements 1 and 2 execute successfully and give different results.

D.

Both statements 1 and 2 execute successfully and give the same required result.

A subquery is called a single-row subquery when _______.

A.

There is only one subquery in the outer query and the inner query returns one or more
values

B.

The inner query returns a single value to the outer query.

C.

The inner query uses an aggregating function and returns one or more values.

D.

The inner query returns one or more values and the outer query returns a single value.

You issued this command:
CHOOSE THREE
SQL > DROP TABLE employees;
Which three statements are true?

A.

Sequences used in the EMPLOYEES table become invalid.

B.

If there is an uncommitted transaction in the session, it is committed.

C.

All indexes and constraints defined on the table being dropped are also dropped.

D.

The space used by the EMPLOYEES table is always reclaimed immediately.

E.

The EMPLOYEES table can be recovered using the ROLLBACK command.

F.

The EMPLOYEES table may be moved to the recycle bin.

Which statement is true about SQL query processing in an Oracle database instance?
(Choose the best answer.)

A.

During parsing, a SQL statement containing literals in the WHERE clause that has been
executed by any session and which is cached in memory, is always reused for the current
execution.

B.

During executing, the oracle server may read data from storage if the required data is
not already in memory.

C.

During row source generation, rows that satisfy the query are retrieved from the
database and stored in memory.

D.

During optimization, execution plans are formulated based on the statistics gathered by
the database instance, and the lowest cost plan is selected for execution.

View the Exhibit and examine the details of PRODUCT_INFORMATION table.
PRODUCT_NAME
CATEGORY_ID
SUPPLIER_ID
Inkjet C/8/HQ
12
102094
Inkjet C/4
12
102090
LaserPro 600/6/BW
12
102087
LaserPro 1200/8/BW
12
102099
Inkjet B/6
12
102096
Industrial 700/ID
12
102086
Industrial 600/DQ
12
102088
Compact 400/LQ
12
102087
Compact 400/DQ
12
102088
HD 12GB /R
13
102090
HD 10GB /I
13
102071
HD 12GB @7200 /SE
13
102057
HD 18.2GB @10000 /E
13
102078
HD 18.2GB @10000 /I
13
102050
HD 18GB /SE
13
102083
HD 6GB /I
13
102072
HD 8.2GB@5400
13
102093
You have the requirement to display PRODUCT_NAME from the table where the
CATEGORY_ID column has values 12 or 13, and the SUPPLIER_ID column has the value
102088. You executed the following SQL statement:
SELECT product_name
FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088;
Which statement is true regarding the execution of the query?

A.

It would not execute because the same column has been used in both sides of the AND
logical operator to form the condition.

B.

It would not execute because the entire WHERE clause condition is not enclosed within
the parentheses.

C.

It would execute and the output would display the desired result.

D.

It would execute but the output would return no rows.