1Z0-047 by Oracle Valid Free Exam Practice Test

Question 1

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*12+50 "Annual Compensation". D. Change the SELECT clause to SELECT last_name, department_id, salary+(50*12) "Annual Compensation".

Correct Answer: B

Question 2

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

A. It produces 2 n possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause. B. It produces n+1 possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause. C. It finds all the NULL values in the superaggregates for the groups specified in the GROUP BY clause. D. It produces only aggregates for the groups specified in the GROUP BY clause.

Correct Answer: A

Question 3

Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seql START WITH 100
INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE;
The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seql.nextval FROM dual;
What is displayed by the SELECT statement?

A. 10 B. an error C. 100 D. 1

Correct Answer: D

Question 4

In which scenario would you use the ROLLUP operator for expression or columns within a GROUP BY clause?

A. to create group-wise grand totals for the groups specified within a GROUP BY clause B. to create a grouping for expressions or columns specified within a GROUP BY clause in all possible directions, which is cross-tabular report for calculating the subtotals C. to create a grouping for expressions or columns specified within a GROUP BY clause in one direction, from right to left for calculating the subtotals D. to find the groups forming the subtotal in a row

Correct Answer: C

Question 5

View the Exhibit and examine the structure of the EMPLOYEES table.
You want to retrieve hierarchical data of the employees using the top-down hierarchy. Which SQL clause would let you choose the direction to walk through the hierarchy tree?

A. CONNECT BY PRIOR B. START WITH C. HAVING D. GROUP BY E. WHERE

Correct Answer: A

Question 6

View the Exhibit and examine DEPARTMENTS and the LOCATIONS tables.
Evaluate the following SOL statement:
SELECT location_id, city FROM locations I WHERE NOT EXISTS (SELECT location_id FROM departments WHERE location_id <> I. location_id);
This statement was written to display LOCATION_ID and CITY where there are no departments located. Which statement is true regarding the execution and output of the command?

A. The statement would not execute because the = comparison operator is missing in the WHERE clause of the outer query. B. The statement would execute and would return the desired results. C. The statement would not execute because the WHERE clause in the outer query is missing the column name for comparison with the inner query result. D. The statement would execute but it will return zero rows because the WHERE clause in the inner query should have the = operator instead of <>.

Correct Answer: D

Question 7

Which statement is true regarding synonyms?

A. Synonyms are used to reference only those tables that are owned by another user. B. The DROP SYNONYM statement removes the synonym, and the status of the table on which the synonym has been created becomes invalid. C. Synonyms can be created for tables but not views. D. A public synonym and a private synonym can exist with the same name for the same table.

Correct Answer: D

Question 8

Evaluate the following CREATE TABLE command:
CREATE TABLE order_item
(order_id NUMBER(3),
item_id NUMBER(2),
qty NUMBER(4),
CONSTRAINT ord_itm_id_pk PRIMARY KEY (order_id item_id) USING INDEX (CREATE INDEX ord_itm_idx
ON order_item(order_id,item_id)));
Which statement is true regarding the above SOL statement?

A. It would give an error because the USING INDEX clause is not permitted in the CREATE TABLE command. B. It would execute successfully and only ORD_ITM_IDX index would be created. C. It would give an error because the USING INDEX clause cannot be used on a composite primary key. D. It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID_PK would be created.

Correct Answer: B

Question 9

View the Exhibit and examine the structure of the LOCATIONS and DEPARTMENTS tables.
Which SET operator should be used in the blank space in the following SQL statement to display the cities that have departments located in them?
SELECT location_id, city
FROM locations
SELECT location_id, city FROM locations JOIN departments USING(location_id);

A. UNION B. INTERSECT C. MINUS D. UNION ALL

Correct Answer: B

Question 10

View the Exhibit and examine the description of the ORDERS table.
The orders in the ORDERS table are placed through sales representatives only. You are given the task to get the SALES_REP_ID from the ORDERS table of those sales representatives who have successfully referred more than 10 customers. Which statement would achieve this purpose?

A. SELECT sales_rep_id, COUNT(customer_id) "Total"
FROM orders "
GROUP BY sales_rep_id
HAVING COUNT(customer_id) > 10; B. SELECT sales_rep_id, COUNT(customer_id) "Total"
FROM orders "
HAVING COUNT(customer_id) > 10; C. SELECT sales_rep_id, COUNT(customer_id) "Total"
FROM orders "
GROUP BY sales_rep_id
HAVING total > 10; D. SELECT sales_rep_id, COUNT(customer_id) "Total"
FROM orders "
WHERE COUNT(customer_id) > 10
GROUP BY sales_rep_id;

Correct Answer: A

Question 11

View the Exhibit and examine the description of the ORDER_ITEMS table. The following SQL statement was written to retrieve the rows for the PRODUCT_ID that has a
UNIT_PRICE of more than 1,000 and has been ordered more than five times: SELECT product_id, COUNT(order_id) total, unit_price
FROM order_items
WHERE unit_price>1000 AND COUNT(order_id)>5
GROUP BY product_id, unit_price;
Which statement is true regarding this SQL statement?

A. The statement would not execute because the aggregate function is used in the WHERE clause. B. The statement would not execute because in the SELECT clause, the UNIT_PRICE column is placed after the column having the aggregate function. C. The statement would not execute because the WHERE clause should have the OR logical operator instead of AND. D. The statement would execute and give you the desired result.

Correct Answer: A

Question 12

View the Exhibit and examine the details of the EMPLOYEES table.
Evaluate the following SQL statements:
Statement 1:
SELECT employee_id, last_name, job_id, manager_id FROM employees START WITH
employee_id = 101
CONNECT BY PRIOR employee_id = manager_id AND manager_id != 108;
Statement 2:
SELECT employee_id, last_name, job_id, manager_id
FROM employees
WHERE manager_id != 108
START WITH employee_id = 101
CONNECT BY PRIOR employee_id = manager_id;
Which two statements are true regarding the above SQL statements? (Choose two.)

A. The output of statement 1 would neither display the employee with MANAGERJD 108 nor any employee below him or her in the hierarchy. B. The output for statement 1 would display the employee with MANAGERJD 108 and all the employees below him or her in the hierarchy. C. The output for statement 2 would not display the employee with MANAGERJD 108 but it would display all the employees below him or her in the hierarchy. D. Statement 2 would not execute because the WHERE clause condition is not allowed in a statement that has the START WITH clause.

Correct Answer: A,C

Oracle Database SQL Expert - 1Z0-047 Exam Practice Test