Examine this description of the TRANSACTIONS table
Which two SQL statements execute successfully? (Choose two.)

A.

SELECT customer_id AS “CUSTOMER-ID”, transaction_date AS DATE, amount + 100
“DUES” FROM transactions;

B.

SELECT customer_id AS “CUSTOMER-ID”, transaction_date AS “DATE”, amount + 100
DUES FROM transactions;

C.

SELECT customer_id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount
+ 100 “DUES AMOUNT” FROM transactions;

D.

SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount + 100 DUES
FROM transactions;

E.

SELECT customer_id AS ‘CUSTOMER-ID’, transaction_date AS DATE, amount + 100
‘DUES AMOUNT’ FROM transactions;

View the Exhibit and examine the structure of the PRODUCTS table.
Which two tasks require subqueries? (Choose two.)

A.

Display the number of products whose PROD_LIST_PRICE is more than the average
PROD_LIST_PRICE

B.

Display suppliers whose PROD_LIST_PRICE is less than 1000

C.

Display products whose PROD_MIN_PRICE is more than the average
PROD_LIST_PRICE of all products, and whose status is orderable

D.

Display the total number of products supplied by supplier 102 which have a product status of obsolete

E.

Display the minimum PROD_LIST_PRICE for each product status

Which two statements are true about the results of using the INTERSECT operator in compound queries? (Choose two.)

A.

Column names in each SELECT in the compound query can be different

B.

The number of columns in each SELECT in the compound query can be different

C.

Reversing the order of the intersected tables can sometimes affect the output

D.

INTERSECT returns rows common to both sides of the compound query

E.

INTERSECT ignores NULLs

Which two statements are true regarding the UNION and UNION ALL operators? (Choose two.)

A.

Duplicates are eliminated automatically by the UNION ALL operator

B.

The number of columns selected in each SELECT statement must be identical

C.

The names of columns selected in each SELECT statement must be identical

D.

The output is sorted by the UNION ALL operator

E.

NULLS are not ignored during duplicate checking

Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data? (Choose four.)

A.

A table can have only one primary key but multiple foreign keys

B.

A table can have only one primary key and foreign key

C.

The foreign key columns and parent table primary key columns must have the same
names

D.

It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted

E.

It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted

F.

Only the primary key can be defined at the column and table level

G.

Primary key and foreign key constraints can be defined at both the column and table level

The EMPLOYEES table contains columns EMP_ID of data type NUMBER and
HIRE_DATE of data type DATE.
You want to display the date of the first Monday after the completion of six months since
hiring.
The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore,
Sunday is the first day on the wee.
Which query can be used?

A.

SELECT emp_id, ADD_MONTHS(hire_date, 6), NEXT_DAY(‘MONDAY’) FROM
employees;

B.

SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), ‘MONDAY’) FROM
employees;

C.

SELECT emp_id, NEXT_DAY(MONTHS_BETWEEN(hire_date, SYSDATE), 6) FROM employees;

D.

SELECT emp_id, NEXT_DAY(ADD_MONTHS(hire_date, 6), 1) FROM employees;

Examine the description of the EMPLOYEES table:

Which query is valid?

A.

SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id;

B.

SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;

C.

SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id,
join_date;

D.

SELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id;

Examine the description of the CUSTOMERS table:

For customers whose income level has a value, you want to display the first name and due
amount as 5% of their credit limit. Customers whose due amount is null should not be
displayed.
Which query should be used?

A.

A. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM
customersWHERE cust_incoms_level IS NOT NULLAND due_amount IS NOT NULL;

B.

SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM
customersWHERE cust_income_level != NULLAND cust_credit_level !=NULL;

C.

SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM
customersWHERE cust_income_level <> NULLAND due_amount <> NULL;

D.

SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM
customersWHERE cust_income_level != NULLAND due_amount !=NULL;

E.

SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNTFROM
customersWHERE cust_income_level IS NOT NULLAND cust_credit_limit IS NOT NULL;

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

A.

A UNIQUE index can be altered to be non-unique

B.

A SELECT statement can access one or more indices without accessing any tables

C.

A table belonging to one user can have an index that belongs to a different user

D.

An update to a table can result in updates to any or all of the table’s indexes

E.

When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped

F.

An update to a table can result in no updates to any of the table’s indexes

You want to use table compression suitable for OLTP that will:
Compress rows for all DML statements on that table
Minimize the overheads associated with compression
Which compression option is best suited for this?

A.

COLUMN STORE COMPRESS FOR QUERY LOW

B.

ROW STORE COMPRESS BASIC

C.

COLUMN STORE COMPRESS FOR ARCHIVE LOW

D.

COLUMN STORE COMPRESS FOR ARCHIVE HIGH

E.

ROW STORE COMPRESS ADVANCED

Examine the description of the BOOKS table:

Examine this sequence of statements issued in a new session:
INSERT INTO books VALUES (‘ADV112’, ‘Adventures of Tom Sawyer’, NULL, NULL);
SAVEPOINT a;
DELETE FROM books;
ROLLBACK TO SAVEPOINT a;
ROLLBACK;
Which two statements are true? (Choose two.)

A.

The second ROLLBACK command does nothing

B.

The second ROLLBACK command replays the delete

C.

The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed

D.

The second ROLLBACK command undoes the insert

E.

The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row

Which two statements are true about space-saving features in an Oracle Database? (Choose two.)

A.

Private Temporary Tables (PTTS) store metadata in memory only

B.

An index created with the UNUSABLE attribute has no segment

C.

If they exist for a session, Private Temporary Tables (PTTs) are always dropped at the next COMMIT OR ROLLBACK statement

D.

An index that is altered to be UNUSABLE will retain its segment

E.

A table that is truncated will always have its segment removed