Q1. View the Exhibit and examine the table structure of DEPARTMENTS and LOCATIONS tables. 

You want to display all the cities that have no departments and the departments that have not been allocated cities. 

Which type of join between DEPARTMENTS and LOCATIONS tables would produce this information as part of its output? 

A. NATURAL JOIN 

B. FULL OUTERJOIN 

C. LEFT OUTERJOIN 

D. RIGHT OUTER JOIN 

View Answer

Q2. Evaluate the following expression using meta character for regular expression: 

'[AAle|ax.r$]' 

Which two matches would be returned by this expression? (Choose two.) 

A. Alex 

B. Alax 

C. Alxer 

D. Alaxendar 

E. Alexender 

View Answer

Q3. 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

Q4. View the Exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables. 

Evaluate the following MERGE statement: 

MERGE INTO orders_master o USING monthly_orders m ON (o.order_id = m.order_id) WHEN MATCHED THEN UPDATE SET o.order_total = m.order_total DELETE WHERE (m.order_total IS NULL) WHEN NOT MATCHED THEN INSERT VALUES (m.order_id, m.order_total); 

What would be the outcome of the above statement? 

A. The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2. 

B. The ORDERS_MASTER table would contain the ORDER_IDs 1,2 and 3. 

C. The ORDERS_MASTER table would contain the ORDER_IDs 1,2 and 4. 

D. The ORDERS_MASTER table would contain the ORDER IDs 1,2,3 and 4. 

View Answer

Q5. Which two statements are true regarding the execution of the correlated subqueries? (Choose two.) 

A. The nested query executes after the outer query returns the row. 

B. The nested query executes first and then the outer query executes. 

C. The outer query executes only once for the result returned by the inner query. 

D. Each row returned by the outer query is evaluated for the results returned by the inner query. 

View Answer

Q6. 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. Itwould becreated only if a unique index is manually created first. 

B. Itwould becreated andwould use an automatically created unique index. 

C. It would be createdandwould use an automaticallycreatednonunique index. 

D. Itwouldbecreated and remainsinadisabledstatebecauseno indexis specified in the command. 

View Answer

Q7. Which two statements are true regarding operators used with subqueries? (Choose two. 

A. The NOT IN operator is equivalent to IS NULL 

B. The <ANY operator means less than the maximum. 

C. =ANY and =ALL operators have the same functionality. 

D. The IN operator cannot be used in single-row subqueries. 

E. TheNOT operator can be used with IN, ANY and ALL operators. 

View Answer

Q8. Which two statements are true regarding roles? (Choose two.) 

A. A role can be granted to itself. 

B. A role can be granted to PUBLIC. 

C. A user can be granted only one role at any point of time. 

D. The REVOKE command can be used to remove privileges but not roles from other users. 

E. Roles are named groups of related privileges that can be granted to users or other roles. 

View Answer

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

Evaluate the following SQL statement: SELECT employee_id, last_name, jobjd, manager_id, LEVEL FROM employees START WITH employee_id = 101 CONNECT BY PRIOR employee_id=manager_id; 

Which two statements are true regarding the output of this command? (Choose two.) 

A. Theoutputwouldbeintop-downhierarchy starting with EMPLOYEE_ID having value 101. 

B. Theoutput wouldbein bottom-up hierarchystartingwith EMPLOYEE_ID havingvalue101. 

C. TheLEVEL columndisplaysthenumber of employees in the hierarchy under the employee having theEMPLOYEE_ID 101. 

D. The LEVEL column displays the level in the hierarchy at which the employee is placed undertheemployee having the EMPLOYEE_ID 101 

View Answer

Q10. Which statements are true? (Choose all that apply.) 

A. The data dictionary is created and maintained by the database administrator. 

B. The data dictionary views can consist of joins of dictionary base tables and user-defined tables. 

C. The usernames of all the users including the database administrators are stored in the data dictionary. 

D. The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies. 

E. Both USER_OBJECTS and CAT views provide the same information about all the objects that are owned by the user. 

F. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary 

View Answer