1z0-071 Practice Test

Examine the commands used to create department details and course details:

What is the outcome?

A.

it fails because the Join type used is Incorrect

B.

It executes successfully and displays the required list.

C.

It executes successfully but displays an incorrect list.

D.

It fails because the ON clause condition is not valid

Which three statements are true regarding subqueries?

A.

Multiple columns or expressions can be compared between the main query and
subquery.

B.

Subqueries can contain ORDER BY but not the GROUP BY clause.

C.

Main query and subquery can get data from different tables.

D.

Subqueries can contain GROUP BY and ORDER BY clauses.

E.

Main query and subquery must get data from the same tables.

F.

Only one column or expression can be compared between the main query and
subquery.

Which three statements are true about the ALTER TABLE....DROP COLUMN....
command?

A.

A column can be dropped only if it does not contain any data.

B.

A column can be dropped only if another column exists in the table.

C.

A dropped column can be rolled back.

D.

The column in a composite PRIMARY KEY with the CASCADE option can be dropped.

E.

A parent key column in the table cannot be dropped.

View the Exhibit and examine the structure of the PRODUCTS table. (Choose the best
answer.)

You must display the category with the maximum number of items.
You issue this query:
SQL > SELECT COUNT(*), prod_category_id
FROM products
GROUP BY prod_category_id
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM porducts);
What is the result?
You must display the category with the maximum number of items.
You issue this query:
SQL > SELECT COUNT(*), prod_category_id
FROM products
GROUP BY prod_category_id
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM porducts);
What is the result?

A.

It generates an error because = is not valid and should be replaced by the IN operator.

B.

It executes successfully but does not give the correct output.

C.

It executes successfully and gives the correct output.

D.

It generate an error because the subquery does not have a GROUP BY clause.

Examine the structure of the EMPLOYEES table.
NameNull?Type
----------- ------
EMPLOYEE_IDNOT NULLNUMBER(6)
FIRST_NAMEVARCHAR2(20)
LAST_NAMENOT NULLVARCHAR2(25)
EMAILNOT NULLVARCHAR2(25)
PHONE NUMBERVARCHAR2(20)
HIRE_DATENOT NULLDATE
JOB_IDNOT NULLVARCHAR2(10)
SALARYNUMBER(8,2)
COMMISSION_PCTNUMBER(2,2)
MANAGER_IDNUMBER(6)
DEPARTMENT_IDNUMBER(4)
There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.
You want to display the last names and manager IDs of employees who work for the same
manager as the employee whose EMPLOYEE_ID is 123.
Which query provides the correct output?

A.

SELECT e.last_name, m.manager_idFROM employees e RIGHT OUTER JOIN
employees mon (e.manager_id = m.employee_id)AND e.employee_id = 123;

B.

SELECT e.last_name, m.manager_idFROM employees e RIGHT OUTER JOIN
employees mon (e.employee_id = m.manager_id)WHERE e.employee_id = 123;

C.

SELECT e.last_name, e.manager_idFROM employees e RIGHT OUTER JOIN
employees mon (e.employee_id = m.employee_id)WHERE e.employee_id = 123;

D.

SELECT m.last_name, e.manager_idFROM employees e LEFT OUTER JOIN
employees mon (e.manager_id = m.manager_id)WHERE e.employee_id = 123;