Examine the commands used to create department details and course details:

What is the outcome?

A.

it fails because the Join type used is Incorrect

B.

It executes successfully and displays the required list.

C.

It executes successfully but displays an incorrect list.

D.

It fails because the ON clause condition is not valid

Which three statements are true regarding subqueries?

A.

Multiple columns or expressions can be compared between the main query and
subquery.

B.

Subqueries can contain ORDER BY but not the GROUP BY clause.

C.

Main query and subquery can get data from different tables.

D.

Subqueries can contain GROUP BY and ORDER BY clauses.

E.

Main query and subquery must get data from the same tables.

F.

Only one column or expression can be compared between the main query and
subquery.

Which three statements are true about the ALTER TABLE....DROP COLUMN....
command?

A.

A column can be dropped only if it does not contain any data.

B.

A column can be dropped only if another column exists in the table.

C.

A dropped column can be rolled back.

D.

The column in a composite PRIMARY KEY with the CASCADE option can be dropped.

E.

A parent key column in the table cannot be dropped.

View the Exhibit and examine the structure of the PRODUCTS table. (Choose the best
answer.)

You must display the category with the maximum number of items.
You issue this query:
SQL > SELECT COUNT(*), prod_category_id
FROM products
GROUP BY prod_category_id
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM porducts);
What is the result?
You must display the category with the maximum number of items.
You issue this query:
SQL > SELECT COUNT(*), prod_category_id
FROM products
GROUP BY prod_category_id
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM porducts);
What is the result?

A.

It generates an error because = is not valid and should be replaced by the IN operator.

B.

It executes successfully but does not give the correct output.

C.

It executes successfully and gives the correct output.

D.

It generate an error because the subquery does not have a GROUP BY clause.

Examine the structure of the EMPLOYEES table.
NameNull?Type
----------- ------
EMPLOYEE_IDNOT NULLNUMBER(6)
FIRST_NAMEVARCHAR2(20)
LAST_NAMENOT NULLVARCHAR2(25)
EMAILNOT NULLVARCHAR2(25)
PHONE NUMBERVARCHAR2(20)
HIRE_DATENOT NULLDATE
JOB_IDNOT NULLVARCHAR2(10)
SALARYNUMBER(8,2)
COMMISSION_PCTNUMBER(2,2)
MANAGER_IDNUMBER(6)
DEPARTMENT_IDNUMBER(4)
There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.
You want to display the last names and manager IDs of employees who work for the same
manager as the employee whose EMPLOYEE_ID is 123.
Which query provides the correct output?

A.

SELECT e.last_name, m.manager_idFROM employees e RIGHT OUTER JOIN
employees mon (e.manager_id = m.employee_id)AND e.employee_id = 123;

B.

SELECT e.last_name, m.manager_idFROM employees e RIGHT OUTER JOIN
employees mon (e.employee_id = m.manager_id)WHERE e.employee_id = 123;

C.

SELECT e.last_name, e.manager_idFROM employees e RIGHT OUTER JOIN
employees mon (e.employee_id = m.employee_id)WHERE e.employee_id = 123;

D.

SELECT m.last_name, e.manager_idFROM employees e LEFT OUTER JOIN
employees mon (e.manager_id = m.manager_id)WHERE e.employee_id = 123;

You execute the following commands:
SQL > DEFINE hiredate = '01-APR-2011'
SQL >SELECT employee_id, first_name, salary
FROM employees
WHERE hire_date > '&hiredate'
AND manager_id >&mgr_id;
For which substitution variables are you prompted for the input?

A.

none, because no input required

B.

both the substitution variables ''hiredate' and 'mgr_id'.

C.

only hiredate'

D.

only 'mgr_id'

View the exhibit and examine the structure of ORDERS and CUSTOMERS tables.
ORDERS
Name
Null?
Type
ORDER_ID
NOT NULL
NUMBER(4)
ORDER_DATE
NOT NULL
DATE
ORDER_MODE
VARCHAR2(8)
CUSTOMER_ID
NOT NULL
NUMBER(6)
ORDER_TOTAL
NUMBER(8, 2)
CUSTOMERS
Name
Null?
Type
CUSTOMER_ID
NOT NULL
NUMBER(6)
CUST_FIRST_NAME
NOT NULL
VARCHAR2(20)
CUST_LAST_NAME
NOT NULL
VARCHAR2(20)
CREDIT_LIMIT
NUMBER(9,2)
CUST_ADDRESS
VARCHAR2(40)
Which INSERT statement should be used to add a row into the ORDERS table for the
customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600? Assume
there exists only one row with CUST_LAST_NAME as Roberts and CREDIT_LIMIT as 600.

A.

INSERT INTO (SELECT o.order_id, o.order_date, o.order_mode, c.customer_id,
o.order_totalFROM orders o, customers cWHERE o.customer_id = c.customer_id AND
c.cust_last_name='Roberts' AND c.credit_limit=600)VALUES (1,'10-mar-2007', 'direct',
(SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND
credit_limit=600), 1000);

B.

INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer idFROM
customersWHERE cust_last_name='Roberts' AND credit_limit=600), order_total);VALUES
(1,'10-mar-2007', 'direct', &customer_id, 1000);

C.

INSERT INTO ordersVALUES (1,'10-mar-2007', 'direct',(SELECT customer_idFROM
customersWHERE cust_last_name='Roberts' AND credit_limit=600), 1000);

D.

INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer_idFROM
customersWHERE cust_last_name='Roberts' AND credit_limit=600), order_total);VALUES
(1,'10-mar-2007', 'direct', &customer_id, 1000);

View the Exhibit and examine, the description for the SALES and CHANNELS tables.
(Choose the best answer.)

You issued this SQL statement:
INSERT INTO SALES VALUES (23, 2300, SYSDATE,
(SELECT CAHNNEL_ID
FROM CHANNELS
WHERE CHANNEL_DESC='DIRECT SALES'), 12, 1, 500);
Which statement is true regarding the result?

A.

The statement will fail because the sub-query in the VALUES clause is not enclosed
within single quotation marks.

B.

The statement will fail because a subquery cannot be used in a VALUES clause.

C.

The statement will execute and a new row will be inserted in the SALES table.

D.

The statement will fail because the VALUES clause is not required with the subquery

View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY
tables. (Choose all that apply.)

Examine this query which must select the employee IDs of all the employees who have
held the job SA_MAN at any time during their employment.
SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE JOB_ID = 'SA_MAN'
-------------------
SELECT EMPLOYEE_ID
FROM JOB_HISTORY
WHERE JOB_ID = 'SA_MAN';
Choose two correct SET operators which would cause the query to return the desired
result.

A.

UNION

B.

MINUS

C.

INTERSECT

D.

UNION ALL

Examine this SELECT statement and view the Exhibit to see its output: (Choose two.)

SELECT constraints_name, constraints_type, search_condition, r_constraints_name,
delete_rule, status,
FROM user_constraints
WHERE table_name = 'ORDERS';
Which two statements are true about the output?

A.

The DELETE_RULE column indicates the desired state of related rows in the child table
when the corresponding row is deleted from the parent table.

B.

The R_CONSTRAINT_NAME column contains an alternative name for the constraint.

C.

In the second column, 'c' indicates a check constraint.

D.

The STATUS column indicates whether the table is currently in use

Using the CUSTOMERS table, you need to generate a report that shows 50% of each
credit amount in each income level. The report should NOT show any repeated credit
amounts in each income level.
Which query would give the required result?

A.

SELECT cust_income_level || ‘ ’ || cust_credit_limit * 0.50 AS “50% Credit Limit” FROM
customers.

B.

SELECT DISTINCT cust_income_level || ‘ ’ || cust_credit_limit * 0.50 AS “50% Credit
Limit” FROM customers.

C.

SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50%
Credit Limit” FROM customers.

D.

SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS “50% Credit Limit”
FROM customers

Evaluate the following query:
SQL> SELECT TRUNC (ROUND (156.00, -1),-1)
FROM DUAL;
What would be the outcome?

A.

150

B.

200

C.

160

D.

16

E.

100