Oracle 1Z0-047 Actual PDF : Oracle Database SQL Expert

Which statement is true regarding the ROLLUP operator specified in the GROUP BY clause of a SQL statement?

• A. It produces higher-level subtotals, moving in all the directions through the list of grouping columns specified in the GROUP BY clause.
• B. It produces only the subtotals for the groups specified in the GROUP BY clause.
• C. It produces higher-level subtotals, moving from right to left through the list of grouping columns specified in the GROUP BY clause.
• D. It produces only the grand totals for the groups specified in the GROUP BY clause.

View the Exhibit and examine the structure of the
PRODUCT INFORMATION table.
Which two queries would work? (Choose two.)

• A. SELECT product_name
  FROM product_jnformation WHERE list_price < ANY(SELECT AVG(list_price)
  FROM product_jnformation GROUP BY product_status);
• B. SELECT product_status FROM product_information GROUP BY product_status HAVING list_price > (SELECT AVG(list_price) FROM product_information);
• C. SELECT product_status FROM product_information GROUP BY product_status WHERE list_price < (SELECT AVG(list_price) FROM product_information);
• D. SELECT product_name
  FROM product_information
  WHERE list_price = (SELECT AVG(list_price)
  FROM product_information);

View the Exhibit and examine the description of the ORDERS table.
Evaluate the following SQL statement:
SELECT order_id, customer_id FROM orders WHERE order_date > 'June 30 2001';
Which statement is true regarding the execution of this SQL statement?

• A. It would execute and would return ORDER_ID and CUSTOMER_ID for all records having ORDER_DATE greater than 'June 30 2001'.
• B. It would not execute because 'June 30 2001' in the WHERE condition cannot be converted implicitly and needs the use of the TO_DATE conversion function for proper execution.
• C. It would not execute because 'June 30 2001' in the WHERE condition is not enclosed within double quotation marks.
• D. It would not execute because 'June 30 2001' in the WHERE condition cannot be converted implicitly and needs the use of the TO_CHAR conversion function for proper execution.

Evaluate the CREATE TABLE statement:
CREATE TABLE products
(product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY,
product_name VARCHAR2(15));
Which statement is true regarding the PROD_ID_PK constraint?

• A. It would be created and would use an automatically created unique index.
• B. It would be created only if a unique index is manually created first.
• C. It would be created and would use an automatically created nonunique index.
• D. It would be created and remains in a disabled state because no index is specified in the command.

View the Exhibit and examine the description of the EMPLOYEES table.
Your company decided to give a monthly bonus of $50 to all the employees who have completed five years in the company. The following statement is written to display the LAST_NAME,
DEPARTMENT_ID, and the total annual salary:
SELECT last_name, department_id, salary+50*12 "Annual Compensation" FROM employees WHERE MONTHS_BETWEEN(SYSDATE, hire_date)/12 >= 5;
When you execute the statement, the "Annual Compensation" is not computed correctly. What changes would you make to the query to calculate the annual compensation correctly?

• A. Change the SELECT clause to SELECT last_name, department_id, salary*12+50 "Annual Compensation".
• B. Change the SELECT clause to SELECT last_name, department_id, salary+(50*12) "Annual Compensation".
• C. Change the SELECT clause to SELECT last_name, department_id, (salary +50)*12 "Annual Compensation".
• D. Change the SELECT clause to SELECT last_name, department_id, (salary*12)+50 "Annual Compensation".