1Z0-071 Online Practice Questions and Answers

Examine this statement:

What is returned upon execution?

A. 2 rows

B. 0 rows

C. An error

D. 1 ROW

Which statement will execute successfully?

A. SELECT 1, 2 FROM DUAL UNION SELECT 3, 4 FROM DUAL ORDER BY 1, 2;

B. SELECT 3 FROM DUAL UNION SELECT 4 FROM DUAL ORDER BY 3 ;

C. SELECT 1, 2 FROM DUAL UNION SELECT 3, 4 FROM DUAL ORDER BY 3, 4;

D. SELECT 1 FROM DUAL UNION SELECT 2 FROM DUAL ORDER BY 1, 2;

Examine the description of the PRODUCTS table:

Which three queries use valid expressions? (Choose three.)

A. SELECT product_id, unit_price, S "Discount", unit_price + surcharge - discount FROM products;

B. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;

C. SELECT product_id, (expiry_date - delivery_date) * 2 FROM products;

D. SELECT product_id, unit_price || 5 "Discount", unit_price + surcharge - discount FROM products;

E. SELECT product_id, expiry_date * 2 FROM products;

F. SELECT product_id, unit_price, unit_price + surcharge FROM products;

Which two statements are true about date/time functions in a session where NLS_DATE_PORMAT is set to DD-MON-YYYY SH24:MI:SS

A. SYSDATE can be used in expressions only if the default date format is DD-MON-RR.

B. CURRENT_TIMESTAMP returns the same date as CURRENT_DATE.

C. CURRENT_DATE returns the current date and time as per the session time zone

D. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server.

E. CURRENT_TIMESTAMP returns the same date and time as SYSDATE with additional details of functional seconds.

F. SYSDATE can be queried only from the DUAL table.

Examine the description of the EMPLOYEES table:

Which query is valid?

A. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id;

B. SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id, join_date;

C. SELECT dept_id, MAX(AVG(salary)) FROM employees GROUP BY dept_id;

D. SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id;

The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type number.

Which two queries execute successfully? (Choose two.)

A. SELECT NVL2(cust_credit_limit * .15, 'Not Available') FROM customers;

B. SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers

C. SELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers;

D. SELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;

E. SELECT TO_CHAR(NVL(cust_credit_iimit * .15, 'Not Available')) FROM customers;

Which two statements are true about the DUAL table: (Choose two.)

A. It can display multiple rows but only a single column.

B. It can be accessed only by the SYS user.

C. It can be accessed by any user who has the SELECT privilege in any schema.

D. It can display multiple rows and columns.

E. It consists of a single row and single column of VARCHAR2 data type.

F. It can be used to display only constants or pseudo columns.

Which three statements are true regarding subqueries? (Choose three.)

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 regarding the usage of the WITH clause in complex correlated subqueries? (Choose three.)

A. It can be used only with the SELECT clause.

B. The WITH clause can hold more than one query.

C. If the query block name and the table name are the same, then the table name takes precedence.

D. The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block

Which three statements are true about external tables? (Choose three.)

A. They can be temporary tables.

B. DML statements can modify them.

C. They can be used in queries containing joins.

D. They can be used in queries containing sorts.

E. They can be indexed.

F. Their metadata is stored in the database.