Which statements are correct regarding indexes? (Choose all that apply.)

A.

A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically
attempts to creates a unique index.

B.

Indexes should be created on columns that are frequently referenced as part of any
expression.

C.

When a table is dropped, the corresponding indexes are automatically dropped.

D.

 For each DML operation performed, the corresponding indexes are automatically
updated.

Evaluate the following CRTEATE TABLE commands:
CREATE_TABLE orders
(ord_no NUMBER (2) CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE,
cust_id NUMBER (4) );
CREATE TABLE ord_items
(ord _no NUMBER (2),
item_no NUMBER(3),
qty NUMBER (3) CHECK (qty BETWEEEN 100 AND 200),
expiry_date date CHECK (expiry_date> SYSDATE),
CONSTRAINT it_pk PRIMARY KEY (ord_no, item_no),
CONSTARAINT ord_fk FOREIGN KEY (ord_no) REFERENCES orders (ord_no) );
Why would the ORD_ITEMS table not get created?

A.

SYSDATE cannot be used with the CHECK constraint.

B.

The BETWEEN clause cannot be used for the CHECK constraint.

C.

The CHECK constraint cannot be placed on columns having the DATE data type.

D.

ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO
is also the FOREIGN KEY.

Which two statements are true regarding single row functions? (Choose two.)

A.

MOD : returns the quotient of a division.

B.

TRUNC : can be used with NUMBER and DATE values.

C.

CONCAT : can be used to combine any number of values.

D.

SYSDATE : returns the database server current date and time.

E.

INSTR : can be used to find only the first occurrence of a character in a string.

F.

TRIM : can be used to remove all the occurrences of a character from a string.

Which statement is true regarding the INTERSECT operator?

A.

The names of columns in all SELECT statements must be identical.

B.

It ignores NULL values.

C.

Reversing the order of the intersected tables alters the result.

D.

The number of columns and data types must be identical for all SELECT statements in
the query.

When does a transaction complete? (Choose all that apply.)

A.

When a PL/SQL anonymous block is executed

B.

hen a DELETE statement is executed

C.

When a data definition language statement is executed

D.

When a TRUNCATE statement is executed after the pending transaction
E. When a ROLLBACK command is executed

Examine the commands used to create DEPARTMENT_DETAILS and
COURSE_DETAILS:
SQL>CREATE TABLE DEPARTMENT_DETAILS
(DEPARTMENT_ID NUMBER PRIMARY KEY,
DEPARTMENT_NAMEVARCHAR2(50),
HODVARCHAR2(50));
SQL>CREATE TABLE COURSE_DETAILS
(COURSE_IDNUMBER PRIMARY KEY,
COURSE_NAMEVARCHAR2(50),
DEPARTMENT_IDVARCHAR2(50));
You want to generate a list of all department IDs along with any course IDs that may have
been assigned to them.
Which SQL statement must you use?

A.

SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER
JOIN course_details c ON (d.department_id=c. department_id);

B.

SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN
course_details c ON (d.department_id=c. department_id);

C.

SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN
department_details d ON (c.department_id=d. department_id);

D.

SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER
JOIN course_details c ON (c.department_id=d. department_id);

Which statements are true? (Choose all that apply.)

A.

The data dictionary is created and maintained by the database administrator.

B.

The data dictionary views consists of joins of dictionary base tables and user-defined
tables.

C.

The usernames of all the users including the database administrators are stored in the
data dictionary.

D.

The USER_CONS_COLUMNS view should be queried to find the names of the columns
to which a constraint applies.

E.

Both USER_OBJECTS and CAT views provide the same information about all the
objects that are owned by the user.

F.

Views with the same name but different prefixes, such as DBA, ALL and USER, use the
same base tables from the data dictionary.

View the exhibit and examine the description of the PRODUCT_INFORMATION table.

Which SQL statement would retrieve from the table the number of products having
LIST_PRICE as NULL?

A.

SELECT COUNT (DISTINCT list_price)FROM product_informationWHERE list_price is
NULL

B.

SELECT COUNT (NVL(list_price, 0))FROM product_informationWHERE list_price is
NULL

C.

SELECT COUNT (list_price)FROM product_informationWHERE list_price i= NULL

D.

SELECT COUNT (list_price)FROM product_informationWHERE list_price is NULL

Evaluate the following CREATE TABLE command:

Which statement is true regarding the above SQL statement?

A.

It would execute successfully and only ORD_ITM_IDX index would be created.

B.

It would give an error because the USING INDEX clause cannot be used on a
composite primary.

C.

It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID PK
would be created.

D.

It would give an error because the USING INDEX is not permitted in the CRETAE
TABLE command.

You issue this command which succeeds:
SQL> DROP TABLE products;
Which three statements are true?

A.

All existing views and synonyms that refer to the table are invalidated but retained.

B.

Any uncommitted transaction in the session is committed.

C.

Table data and the table structure are deleted.

D.

All the table’s indexes if any exist, are invalidated but retained.

E.

Table data is deleted but the table structure is retained.X``

View the Exhibit and examine the details of the PRODUCT_INFORMATION table. 
You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table
where the CATEGORYJD column has values 12 or 13, and the SUPPLIER_ID column has
the value 102088. You executed the following SQL statement:
SELECT product_name, list_price
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 execute but the output would return no rows.

B.

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

C.

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

D.

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

Which two statements are true regarding multiple-row subqueries? (Choose two.)

A.

They can contain group functions.

B.

always contain a subquery within a subquery.

C.

They use the < ALL operator to imply less than the maximum.

D.

They can be used to retrieve multiple rows from a single table only.

E.

 They should not be used with the NOT IN operator in the main query if NULL is likely to
be a part of the result of the subquery.