Updated 1z0-047 Exam Study Guides With New Update Exam Questions

Q1. View the Exhibit and examine the description of the EMPLOYEES table. 

You executed the following SQL statement: 

SELECT first_name, department_id, salary 

FROM employees 

ORDER BY department_id, first_name, salary desc; 

Which two statements are true regarding the output of the above query? (Choose two.) 

A. The valuesinall the columns wouldbe sortedin the descending order. 

B. Thevalues in theSALARYcolumn wouldbesorted indescendingorder for all the employees havingthesame valueinthe DEPARTMENT_ID column. 

C. The values intheFIRST_NAME column would be sortedinascending order for alltheemployees having the same valueinthe DEPARTMENT_ID column. 

D. Thevalues in the FIRST_NAME column would be sorted in the descendingorder forall the employees having the same valueinthe DEPARTMENT_ID column. 

E. Thevalues in the SALARY column wouldbe sortedin descending order foralltheemployeeshaving thesame value intheDEPARTMENT_IDandFIRST_NAME column. 

View Answer

Q2. View the Exhibit and examine the description of EMPLOYEES and DEPARTMENTS tables. 

You want to display the EMPLOYEE_ID, LAST_NAME, and SALARY for the employees who get 

the maximum salary in their respective departments. The following SQL statement was written: 

WITH 

SELECT employee_id, last_name, salary 

FROM employees 

WHERE (department_id, salary) = ANY (SELECT* 

FROM dept_max) 

dept_max as (SELECT d.department_id, max(salary) 

FROM departments d JOIN employees j 

ON (d. department_id = j. department_id) 

GROUP BY d. department_id); 

Which statement is true regarding the execution and the output of this statement? 

A. The statement would execute and give the desired results. 

B. The statement would not execute because the = ANY comparison operator is used instead of=. 

C. The statement would not execute because the main query block uses the query name before it is even created. 

D. The statement would not execute because the comma is missing between the main query block and the query name. 

View Answer

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

A. It produces only aggregates for the groups specified in the GROUP BY clause. 

B. It finds all the NULL values in the superaggregates for the groups specified in the GROUP BY clause. 

C. It produces 2 n possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause. 

D. It produces n+1 possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause. 

View Answer

Q4. 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. WHERE 

B. HAVING 

C. GROUP BY 

D. START WITH 

E. CONNECT BY PRIOR 

View Answer

Q5. Which two statements are true regarding subqueries? (Choose two.) 

A. Only two subqueries can be placed at one level. 

B. A subquery can be used to access data from one or more tables or views. 

C. If the subquery returns 0 rows, then the value returned by the subquery expression is NULL. D. The columns in a subquery must always be qualified with the name or alias of the table used. E. A subquery in the WHERE clause of a SELECT statement can be nested up to three levels only. 

View Answer

Q6. User OE, the owner of the ORDERS table, issues the following command: 

GRANT SELECT,INSERT 

ON orders 

TO hr 

WITH GRANT OPTION; 

The user HR issues the following command: GRANT SELECT ON oe. orders TO scott; 

Then, OE issues the following command: 

REVOKE ALL ON orders FROM hr; 

Which statement is correct? 

A. The user SCOTT loses the privilege to select rows fromOE.ORDERS. 

B. The user SCOTT retains the privilege to select rows from OE.ORDERS. 

C. The REVOKE statement generates an error because OE has to first revoke the SELECT privilege from SCOTT. 

D. The REVOKE statement generates an error because the ALL keyword cannot be usedforprivilegesthathave been granted using WITH GRANT OPTION. 

View Answer

Q7. Which three tasks can be performed using regular expression support in Oracle Database 10g? (Choose three.) 

A. it can be used to concatenate two strings. 

B. it can be used to find out the total length of the string. 

C. it can be used for string manipulation and searching operations. 

D. it can be used to format the output for a column or expression having string data. 

E. it can be used to find and replace operations for a column or expression having string data. 

View Answer

Q8. The following are the steps for a correlated subquery, listed in random order: 

1) The WHERE clause of the outer query is evaluated. 

2) The candidate row is fetched from the table specified in the outer query. 

3) The procedure is repeated for the subsequent rows of the table, till all the rows are processed. 

4) Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query. Identify the option that contains the steps in the correct sequence in which the Oracle server evaluates a correlated subquery. 

A. 4,2,1,3 

B. 4,1,2,3 

C. 2,4,1,3 

D. 2,1,4,3 

View Answer

Q9. Which statement correctly differentiates a system privilege from an object privilege? 

A. System privileges can be granted only by the DBA whereas object privileges can be granted by DBAs or the owner of the object. 

B. System privileges give the rights to only create user schemas whereas object privileges give rights to manipulate objects in a schema. 

C. Users require system privileges to gain access to the database whereas they require object privileges to create objects in the database. 

D. A system privilege is the right to perform specific activities in a database whereas an object privilege is a right to perform activities on a specific object in the database. 

View Answer

Q10. View the Exhibit and examine the structure of the EMPLOYEES and DEPARTMENTS tables. 

Which SET operator would you use in the blank space in the following SQL statement to list the departments where all the employees have managers? 

SELECT department_id FROM departments 

SELECT department_id FROM employees WHERE manager_id IS NULL; 

A. UNION 

B. MINUS 

C. INTERSECT 

D. UNION ALL 

View Answer