Examine the SQL statement used to create the TRANSACTION table. (Choose the best
answer.)
SQL > CREATE TABLE transaction
(trn_id char(2) primary key,
Start_date date DEFAULT SYSDATE,
End_date date NOT NULL);
The value 'A1' does not exist for trn_id in this table.
Which SQL statement successfully inserts a row into the table with the default value for
START_DATE?

A.

INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE(DEFAULT+10))

B.

INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE('SYSDATE+10'))

C.

INSERT INTO transaction (trn_id, end_date) VALUES ('A1', '10-DEC-2014')

D.

INSERT INTO transaction (trn_id, start_date, end_date) VALUES ('A1', , '10-DEC-2014')

The following are the steps for a correlated subquery, listed in random order:
The WHERE clause of the outer query is evaluated.
The candidate row is fetched from the table specified in the outer query.
This is repeated for the subsequent rows of the table, till all the rows are
processed.
Rows are returned by the inner query, after being evaluated with the value from
the candidate row in the outer query.
Which is the correct sequence in which the Oracle server evaluates a correlated subquery?

A.

2, 1, 4, 3

B.

4, 1, 2, 3

C.

4, 2, 1, 3

D.

2, 4, 1, 3

Examine the structure of the MEMBERS table:
NameNull?Type
------------------------------
MEMBER_IDNOT NULLVARCHAR2 (6)
FIRST_NAMEVARCHAR2 (50)
LAST_NAMENOT NULLVARCHAR2 (50)
ADDRESSVARCHAR2 (50)
CITYVARCHAR2 (25)
STATEVARCHAR2 (3)
You want to display details of all members who reside in states starting with the letter A
followed by exactly one character.
Which SQL statement must you execute?

A.

SELECT * FROM MEMBERS WHERE state LIKE '%A_*;

B.

SELECT * FROM MEMBERS WHERE state LIKE 'A_*;

C.

SELECT * FROM MEMBERS WHERE state LIKE 'A_%';

D.

SELECT * FROM MEMBERS WHERE state LIKE 'A%';

View the Exhibit and examine the structure of the EMP table which is not partitioned and
not an index-organized table. (Choose two.)

Evaluate this SQL statement:
ALTER TABLE emp
DROP COLUMN first_name;
Which two statements are true?

A.

The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY
KEY provided the CASCADE option is added to the SQL statement.

B.

The FIRST_NAME column would be dropped provided at least one column remains in
the table.

C.

The FIRST_NAME column would be dropped provided it does not contain any data.

D.

The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED
option is added to the SQL statement.

View the exhibit and examine the structure of the EMPLOYEES table

You want to display all employees and their managers having 100 as the MANAGER_ID.
You want the output in two columns: the first column would have the LAST_NAME of the
managers and the second column would have LAST_NAME of the employees.
Which SQL statement would you execute?

A.

SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN
employees eON m.employee_id = e.manager_idWHERE m.manager_id = 100;

B.

SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN
employees eON m.employee_id = e.manager_idWHERE e.manager_id = 100;

C.

SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN
employees eON e.employee_id = m.manager_idWHERE m.manager_id = 100;

D.

SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN
employees eWHERE m.employee_id = e.manager_id and AND e.manager_id = 100

View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.
ORDER_ID is the primary key in the ORDERS table. It is also the foreign key in the
ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.

A.

DELETE orders o, order_items IWHERE o.order_id = i.order_id;

B.

DELETEFROM ordersWHERE (SELECT order_idFROM order_items);

C.

DELETE ordersWHERE order_total < 1000;

D.

DELETE order_idFROM ordersWHERE order_total < 1000;

See the Exhibit and examine the structure of the PROMOTIONS table:

What would be the outcome?

A.

It generates an error because multiple conditions cannot be specified for the WHEN
clause.

B.

It executes successfully and gives the required result.

C.

It generates an error because CASE cannot be used with group functions.

D.

It generates an error because NULL cannot be specified as a return value.

View the exhibit and examine the structure and data in the invoice table. (Choose two.)

Which two SQL statements would execute successfully?

A.

SELECT MAX(AVG(SYSDATE -inv_date)) FROM invoice

B.

SELECT AVG(inv_date) FROM invoice

C.

SELECT MAX(inv_date), MIN(cust_id) FROM invoice

D.

SELECT AVG( inv_date -SYSDATE), AVG(inv_amt) FROM invoice

Examine the types and examples of relationship that follows: (Choose the best answer.)
1 One-to-one a) teacher to Student
2 One-to-many b) Employees to Manager
3 Many-to-one c) Person to SSN
4 Many-to-many d) Customers to Products
Which option indicates correctly matched relationships?

A.

1-d, 2-b, 3-a, and 4-c

B.

1-c, 2-d, 3-a, and 4-b

C.

1-a, 2-b, 3-c, and 4-d

D.

1-c, 2-a, 3-b, and 4-d

View and Exhibit and examine the structure and data in the INVOICE table. (Choose two.)

Which two statements are true regarding data type conversion in query expressions?

A.

inv_date = '15-february-2008' :uses implicit conversion

B.

inv_amt = '0255982' : requires explicit conversion

C.

inv_date > '01-02-2008' : uses implicit conversion

D.

CONCAT(inv_amt, inv_date) : requires explicit conversion

E.

inv_no BETWEEN '101' AND '110' : uses implicit conversion

Evaluate the following query:

Which is the correct output of the above query?

A.

+00-300, +54-02,+00 11:12:10.123457

B.

+00-300,+00-650,+00 11:12:10.123457

C.

+25-00, +54-02, +00 11:12:10.123457

D.

+25-00,+00-650,+00 11:12:10.123457

Examine the structure of the SALES table. (Choose two.)

Examine this statement:
SQL > CREATE TABLE sales1 (prod_id, cust_id, quantity_sold, price)
AS
SELECT product_id, customer_id, quantity_sold, price
FROM sales
WHERE 1 = 2;
Which two statements are true about the SALES1 table?

A.

It will not be created because the column-specified names in the SELECT and CREATE
TABLE clauses do not match.

B.

It will have NOT NULL constraints on the selected columns which had those constraints
in the SALES table.

C.

It will not be created because of the invalid WHERE clause.

D.

It is created with no rows.

E.

It has PRIMARY KEY and UNIQUE constraints on the selected columns which had
those constraints in the SALES table.