Question 1

Which three statements are true regarding subqueries? (Choose three.)

Options :

A : The ORDER BY Clause can be used in a subquery.

B : A subquery can be used in the FROM clause of a SELECT statement.

C : If a subquery returns NULL, the main query may still return rows.

D : A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.

E : Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.

Answer: A,B,C

Question 2

View the Exhibit and examine the structure of the PRODUCT table.

Which two tasks would require subqueries? (Choose two.)

Options :

A : display all products whose PROD_MIN_PRICE is more than the average PROD_LIST_PRICE of all products, and whose status is orderable

B : display the total number of products supplied by supplier 102 and have product status as ‘OBSOLETE’

C : display the number of products whose PROD_LIST_PRICE is more than the average PROD_LIST_PRICE.

D : display suppliers whose PROD_LIST_PRICE is less than 1000

E : display the minimum PROD_LIST_PRICE for each product status

Answer: A,C

Question 3

Evaluate the following SQL statements that are issued in the given order:

CREATE TABLE emp

(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,

ename VARCHAR2(15),

salary NUMBER (8,2),

mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp(emp_no));

ALTER TABLE emp

DISABLE CONSTRAINT emp_emp_no_pk CASCADE;

ALTER TABLE emp

ENABLE CONSTRAINT emp_emp_no_pk;

What would be the status of the foreign key EMP_MGR_PK?

Options :

A : It would remain disabled and can be enabled only by dropping the foreign key constraint and recreating it.

B : It would remain disabled and has to be enabled manually using the ALTER TABLE command.

C : It would be automatically enabled and immediate.

D : It would be automatically enabled and deferred.

Answer: B

Question 4

Which two are true about creating tables in an Oracle database?

Options :

A : A create table statement can specify the maximum number of rows the table will contain.

B : The same table name can be used for tables in different schemas.

C : A system privilege is required.

D : Creating an external table will automatically create a file using the specified directory and file name.

E : A primary key constraint is manadatory.

Answer: B,C

Question 5

Which two are true about using constraints?

Options :

A :

A FOREIGN KEY column in a child table and the referenced PRIMARY KEY column in the parenttable must have the same names.

B : A table can have multiple PRIMARY KEY and multiple FOREIGN KEY constraints.

C : A table can have only one PRIMARY KEY and one FOREIGN KEY constraint.

D : PRIMARY KEY and FOREIGNY constraints can be specified at the column and at the table level

E : A table can have only one PRIMARY KEY but may have multiple FOREIGN KEY constraints.

F : NOT NULL can be specified at the column and at the table level.

Answer: D,E

Confidently Practice Online with Free 1Z0-071 Exam Cram

Buying Options: