1z0-071 Practice Test

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