1z0-071 Practice Test

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.

View the Exhibit and examine the description of the ORDERS table. (Choose two.)

Which two WHERE clause conditions demonstrate the correct usage of conversion
functions?

A.

WHERE Order_date_IN ( TO_DATE('OCT 21 2003', 'MON DD YYYY'), TO_CHAR('NOV
21 2003', 'MON DD YYYY') )

B.

WHERE Order_date > TO_CHAR(ADD_MONTHS(SYSDATE, 6), 'MON DD YYYY')

C.

WHERE TO_CHAR(Order_date, 'MON DD YYYY') = 'JAN 20 2003'

D.

WHERE Order_date > ( TO_DATE('JUL 10 2006', 'MON DD YYYY')

Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose
three.)

A.

SELECT TO_CHAR (1890.55, '$99G999D00') FROM DUAL

B.

SELECT TO_CHAR (1890.55, '$9,999V99') FROM DUAL;

C.

SELECT TO_CHAR (1890.55, '$0G000D00') FROM DUAL;

D.

SELECT TO_CHAR (1890.55, '$99,999D99') FROM DUAL;

E.

SELECT TO_CHAR (1890.55, '$99G999D99') FROM DUAL

View the exhibit and examine the structure of the PROMOTIONS table

You have to generate a report that displays the promo name and start date for all promos
that started after the last promo in the ‘INTERNET’ category.
Which query would give you the required output?

A.

SELECT promo_name, promo_begin_date FROM promotionsWHERE
promo_begin_date> ALL (SELECT MAX (promo_begin_date)FROM promotions)
ANDpromo_category= ‘INTERNET’;

B.

SELECT promo_name, promo_begin_date FROM promotionsWHERE
promo_begin_date IN (SELECT promo_begin_dateFROM promotionsWHERE
promo_category= ‘INTERNET’);

C.

SELECT promo_name, promo_begin_date FROM promotionsWHERE
promo_begin_date > ALL (SELECT promo_begin_dateFROM promotionsWHERE
promo_category = ‘INTERNET’);

D.

SELECT promo_name, promo_begin_date FROM promotionsWHERE
promo_begin_date> ANY (SELECT promo_begin_dateFROM promotionsWHERE
promo_category= ‘INTERNET’);

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

You must display the maximum and minimum salaries of employees hired 1 year ago.
Which two statements would provide the correct output?

A.

SELECT MIN(Salary) minsal, MAX(salary) maxsalFROM employeesWHERE hire_date
< SYSDATE-365GROUP BY MIN(salary), MAX(salary);

B.

SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsal
FROM employeesWHERE hire_date < SYSDATE-365)GROUP BY maxsal, minsal;

C.

SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsal
FROM employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary),
MAX(salary);

D.

SELECT MIN(Salary), MAX(salary)FROM (SELECT salary FROM employeesWHERE
hire_date < SYSDATE-365);