1z0-071 Practice Test

Which three statements are true about multiple-row subqueries?

A.

They can contain a subquery within a subquery.

B.

They can return multiple columns as well as rows.

C.

 They cannot contain a subquery within a subquery.

D.

They can return only one column but multiple rows.

E.

They can contain group functions and GROUP BY and HAVING clauses.

F.

They can contain group functions and the GROUP BY clause, but not the HAVING
clause.

Which two statement are true regarding table joins available in the Oracle Database
server? (Choose two.)

A.

You can use the ON clause to specify multiple conditions while joining tables.

B.

You can explicitly provide the join condition with a NATURAL JOIN.

C.

You can use the JOIN clause to join only two tables.

D.

You can use the USING clause to join tables on more than one column.

Which two statements are true regarding the WHERE and HAVING clauses in a SELECT
statement? (Choose two.)

A.

The WHERE and HAVING clauses can be used in the same statement only if they are
applied to different columns in the table.

B.

The aggregate functions and columns used in the HAVING clause must be specified in
the SELECT list of the query.

C.

The WHERE clause can be used to exclude rows after dividing them into groups.

D.

The HAVING clause can be used with aggregate functions in subqueries.

E.

The WHERE clause can be used to exclude rows before dividing them into groups.

Evaluate the following SQL statement
SQL> select cust_id, cust_last_name "Last name"
FROM customers
WHERE country_id = 10
UNION
SELECT cust_id CUST_NO, cust_last_name
FROM customers
WHERE country_id = 30
Identify three ORDER BY clauses either one of which can complete the query.

A.

ORDER BY "Last name"

B.

ORDER BY 2, cust_id

C.

ORDER BY CUST_NO

D.

ORDER BY 2, 1

E.

ORDER BY "CUST_NO"

You execute the SQL statement:
SQL> CREATE TABLE citizens
(citizen_id CHAR (10) PRIMARY KEY,
last_name VARCHAR2 (50) NOT NULL,
first_name VARCHAR2 (50),
address VARCHAR2 (100),
city VARCHAR2 (30) DEFAULT ‘SEATTLE’ NOT NULL,
CONSTRAINT cnames CHECK (first_name<>last_name) );
What is the outcome?

A.

It fails because the NOT NULL and DEFAULT options cannot be combined for the same
column.

B.

It succeeds and CITY can contain only ‘SEATTLE’ or null for all rows.

C.

It fails because the condition for the CANAMES constraint is not valid.

D.

It succeeds and an index is crated for CITIZEN_ID.