Q1. View the Exhibit and examine the structure of the EMP table. 

You executed the following command to add a primary key to the EMP table: 

ALTER TABLE emp 

ADD CONSTRAINT emp_id_pk PRIMARY KEY (emp_id) 

USING INDEX emp_id_idx; 

Which statement is true regarding the effect of the command? 

A. The PRIMARY KEY is created along with a new index. 

B. The PRIMARY KEY is created and it would use an existing unique index. 

C. The PRIMARY KEY would be created in a disabled state because it is using an existing index. 

D. The statement produces an error because the USING clause is permitted only in the CREATE TABLE command. 

View Answer

Q2. Which two statements are true regarding views? (Choose two.) 

A. A simple view in which column aliases have been used cannot be updated. 

B. A subquery used in a complex view definition cannot contain group functions or joins. 

C. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword. 

D. Rows added through a view are deleted from the table automatically when the view is dropped. 

E. TheOR REPLACE option is used to change the definition of an existing view without dropping and re-creating it. 

F. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view. 

View Answer

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

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

You want to display the EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT_ID for all the employees who work in the same department and have the same manager as that of the employee having EMPLOYEE_ID 104. To accomplish the task, you execute the following SQL statement: 

SELECT employee_id, first_name, department_id 

FROM employees 

WHERE (manager_id, department_id) =(SELECT department_id, manager_id FROM employees WHERE employee_id = 104) 

AND employee_id <> 104; 

When you execute the statement it does not produce the desired output. What is the reason for this? 

A. The WHERE clause condition in the main query is using the = comparison operator, instead of EXISTS. 

B. The WHERE clause condition in the main query is using the = comparison operator, instead of the IN operator. 

C. The WHERE clause condition in the main query is using the = comparison operator, instead of the = ANY operator. 

D. The columns in the WHERE clause condition of the main query and the columns selected in the subquery should be in the same order. 

View Answer

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

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

B. It produces only the grand totals 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 higher-level subtotals, moving in all the directions through the list of grouping columns specified in the GROUP BY clause. 

View Answer

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