1z0-082 Practice Test

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