View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and
TIMES tables.

The PROD_ID column is the foreign key in the SALES table referencing the PRODUCTS
table.
The CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing
the CUSTOMERS and TIMES tables, respectively.
Examine this command:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id
FROM sales;
Which statement is true?

A.

The NEW_SALES table would get created and all the FOREIGN KEY constraints
defined on the selected columns from the SALES table would be created on the
corresponding columns in the NEW_SALES table.

B.

The NEW_SALES table would not get created because the column names in the
CREATE TABLE command and the SELECT clause do not match.

C.

The NEW_SALES table would not get created because the DEFAULT value cannot be
specified in the column definition.

D.

The NEW_SALES table would get created and all the NOT NULL constraints defined on
the selected columns from the SALES table would be created on the corresponding
columns in the NEW_SALES table.

Examine the structure of the EMPLOYEES table. (Choose the best answer.)


You must display the details of employees who have manager with MANAGER_ID 100,
who were hired in the past 6 months and who have salaries greater than 10000.

A.

SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION
ALL SELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID =
(SELECT employee_id FROM employees WHERE employee_id =
100)INETRSECTSELECT last_name, hire_date, salaryFROM employees WHERE
hire_date > SYSDATE- 180;

B.

SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id =
(SELECT employee_id FROM employees WHERE employee_id = 100)UNION
ALL(SELECT last_name, hire_date, salaryFROM employeesWHERE hire_date >
SYSDATE -180INTERSECTSELECT last_name, hire_date, salaryFROM
employeesWHERE salary > 10000);

C.

SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id =
(SELECT employee_id FROM employees WHERE employee_id = '100')UNIONSELECT
last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE -
180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary >
10000;

D.

(SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION
ALLSELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID =
(SELECT employee_id FROM employees WHERE employee_id = 100))UNIONSELECT
last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE -180;

Which statement is true about transactions?

A.

A set of Data Manipulation Language (DML) statements executed in a sequence ending
with a SAVEPOINT forms a single transaction.

B.

Each Data Definition Language (DDL) statement executed forms a single transaction.

C.

A set of DDL statements executed in a sequence ending with a COMMIT forms a single
transaction.

D.

A combination of DDL and DML statements executed in a sequence ending with a
COMMIT forms a single transaction.

You must write a query that prompts users for column names and conditions every time it is
executed. (Choose the best answer.)
The user must be prompted only once for the table name.
Which statement achieves those objectives?

A.

SELECT &col1, '&col2'FROM &tableWHERE &&condition = '&cond';

B.

SELECT &col1, &col2 FROM "&table"WHERE &condition =&cond;

C.

SELECT &col1, &col2 FROM &&tableWHERE &condition = &cond;

D.

SELECT &col1, &col2 FROM &&tableWHERE &condition = &&cond

View the Exhibit and examine the data in the employees table.

What is the outcome?

A.

It executes successfully but does not give the correct output.

B.

It generates an error because the concatenation operator can be used to combine only
two items.

C.

It generates an error because the usage of the round function in the expression is not
valid

D.

It generates an error because the alias is not valid.

E.

It executes successfully and gives the correct output.

Examine the structure of the PROMOTIONS table: (Choose the best answer.)

Management requires a report of unique promotion costs in each promotion category.
Which query would satisfy this requirement?

A.

SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1

B.

SELECT promo_category, DISTINCT promo_cost FROM promotions

C.

SELECT DISTINCT promo_cost, promo_category FROM promotions

D.

SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;

Examine the structure of the BOOKS_ TRANSACTIONS table:

Which statement is true about the outcome?

A.

It displays details only for members who have borrowed before today with RM as
TRANSACTION_TYPE.

B.

It displays details for members who have borrowed before today's date with either RM
as TRANSACTION_TYPE or MEMBER_ID as A101 and A102.

C.

It displays details for only members A101and A102 who have borrowed before today
with RM as TRANSACTION_TYPE.

D.

It displays details for members who have borrowed before today with RM as
TRANSACTION_TYPE and the details for members A101 or A102.

View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.

You executed the following query to display PRODUCT_NAME and the number of times
the product has been ordered:
SQL>SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt
FROM order_items
GROUP BY product_id) i RIGHT OUTER JOIN products p
ON i.product_id = p.product_id;
What would happen when the above statement is executed?

A.

The statement would execute successfully to produce the required output.

B.

The statement would not execute because inline views and outer joins cannot be used
together.

C.

The statement would not execute because the ITEM_CNT alias cannot be displayed in
the outer query.

D.

The statement would not execute because the GROUP BY clause cannot be used in the
inline.

Which two statements best describe the benefits of using the WITH clause? (Choose two.)

A.

It can improve the performance of a large query by storing the result of a query block
having the WITH clause in the session's temporary tablespace.

B.

It enables sessions to reuse the same query block in a SELECT statement, if it occurs
more than once in a complex query.

C.

It enables sessions to store a query block permanently in memory and use it to create
complex queries.

D.

It enables sessions to store the results of a query permanently.

Examine the structure of the MEMBERS table:
NameNull?Type
--------- -------- ---------------
MEMBER_IDNOT NULLVARCHAR2 (6)
FIRST_NAMEVARCHAR2 (50)
LAST_NAMENOT NULLVARCHAR2 (50)
ADDRESSVARCHAR2 (50)
You execute the SQL statement:
SQL > SELECT member_id, ' ' , first_name, ' ' , last_name "ID FIRSTNAME LASTNAME "
FROM members;
What is the outcome?

A.

 It fails because the alias name specified after the column names is invalid.

B.

It fails because the space specified in single quotation marks after the first two column names is invalid.

C.

It executes successfully and displays the column details in a single column with only the
alias column heading.

D.

It executes successfully and displays the column details in three separate columns and
replaces only the last column heading with the alias.

You want to display the date for the first Monday of the next month and issue the following
command:
SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), 'MON'),
'dd "is the first Monday for" fmmonth rrrr')
FROM DUAL;
What is the outcome?

A.

In generates an error because rrrr should be replaced by rr in the format string.

B.

It executes successfully but does not return the correct result.

C.

It executes successfully and returns the correct result.

D.

In generates an error because TO_CHAR should be replaced with TO_DATE.

E.

In generates an error because fm and double quotation marks should not be used in the
format string.

View the exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS
tables.
ORDERS_MASTER
ORDER_ID
ORDER_TOTAL
1
1000
2
2000
3
3000
4
MONTHLY_ORDERS
ORDER_ID
ORDER_TOTAL
2
2500
3
Evaluate the following MERGE statement:
MERGE_INTO orders_master o
USING monthly_orders m
ON (o.order_id = m.order_id)
WHEN MATCHED THEN
UPDATE SET o.order_total = m.order_total
DELETE WHERE (m.order_total IS NULL)
WHEN NOT MATCHED THEN
INSERT VALUES (m.order_id, m.order_total)
What would be the outcome of the above statement?

A.

The ORDERS_MASTER table would contain the ORDER_IDs 1, 2, 3 and 4.

B.

The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 4.

C.

The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 3.

D.

The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.