Q1. - (Topic 2) 

Which four are valid Oracle constraint types? (Choose four.) 

A. CASCADE 

B. UNIQUE 

C. NONUNIQUE 

D. CHECK 

E. PRIMARY KEY 

F. CONSTANT 

G. NOT NULL 

View Answer

Q2. - (Topic 1) 

You need to perform these tasks: 

. Create and assign a MANAGER role to Blake and Clark . Grant CREATE TABLE and CREATE VIEW privileges to Blake and Clark 

Which set of SQL statements achieves the desired results? 

A. CREATE ROLE manager; 

GRANT create table, create view 

TO manager; 

GRANT manager TO BLAKE,CLARK; 

B. CREATE ROLE manager; 

GRANT create table, create voew 

TO manager; 

GRANT manager ROLE TO BLAKE,CLARK; 

C. GRANT manager ROLE TO BLAKE,CLARK; 

GRANT create table, create voew 

TO BLAKE CLARK; 

***MISSING*** 

View Answer

Q3. - (Topic 1) 

Which two statements about sub queries are true? (Choose two.) 

A. A sub query should retrieve only one row. 

B. A sub query can retrieve zero or more rows. 

C. A sub query can be used only in SQL query statements. 

D. Sub queries CANNOT be nested by more than two levels. 

E. A sub query CANNOT be used in an SQL query statement that uses group functions. 

F. When a sub query is used with an inequality comparison operator in the outer SQL statement, the column list in the SELECT clause of the sub query should contain only one column. 

View Answer

Q4. - (Topic 2) 

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: 

Which DELETE statement is valid? 

A. DELETE FROM employees WHERE employee_id = (SELECT employee_id FROM employees); 

B. DELETE * FROM employees WHERE employee_id = (SELECT employee_id FROM new_employees); 

C. DELETE FROM employees WHERE employee_id IN(SELECT employee_id FROM new_employees WHERE name = 'Carrey'); 

D. DELETE * FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE last_name = 'Carrey'); 

View Answer

Q5. - (Topic 2) 

View the Exhibits and examine the structures of the PRODUCTS and SALES tables. Which two SQL statements would give the same output? (Choose two.) 

A. 

SELECT prod_id FROM products INTERSECT SELECT prod_id FROM sales; 

B. 

SELECT prod_id FROM products MINUS SELECT prod_id FROM sales; 

C. 

SELECT DISTINCT p.prod_id FROM products p JOIN sales s ON p.prod_id=s.prod_id; 

D. 

SELECT DISTINCT p.prod_id FROM products p JOIN sales s ON p.prod_id <> s.prod_id; 

View Answer

Q6. - (Topic 2) 

View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables. 

You want to update the EMPLOYEES table as follows:4 ? 4; 

-Update only those employees who work in Boston or Seattle (locations 2900 and 2700). 

-Set department_id for these employees to the department_id corresponding to London 

 (location_id 2100). 

-Set the employees' salary in location_id 2100 to 1.1 times the average salary of their department. 

-Set the employees' commission in location_id 2100 to 1.5 times the average commission of their department. 

You issue the following command: SQL>UPDATE employees SET department_id = (SELECT department_id FROM departments WHERE location_id = 2100), (salary, commission) = (SELECT 1.1*AVG(salary), 1.5*AVG(commission) FROM employees, departments WHERE departments.location_id IN(2900,2700,2100)) WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 2900 OR location_id = 2700) 

What is the outcome? 

A. It executes successfully and gives the correct result. 

B. It executes successfully but does not give the correct result. 

C. It generates an error because a subquery cannot have a join condition in an UPDATE statement. 

D. It generates an error because multiple columns (SALARY, COMMISION) cannot be specified together in an UPDATE statement. 

View Answer

Q7. - (Topic 1) 

View the Exhibit and examine the data in the COSTS table. 

You need to generate a report that displays the IDs of all products in the COSTS table whose unit price is at least 25% more than the unit cost. The details should be displayed in the descending order of 25% of the unit cost. You issue the following query: 

Which statement is true regarding the above query? 

A. It executes and produces the required result. 

B. It produces an error because an expression cannot be used in the ORDER BY clause. 

C. It produces an error because the DESC option cannot be used with an expression in the ORDER BY clause. 

D. It produces an error because the expression in the ORDER BY clause should also be specified in the SELECT clause. 

View Answer

Q8. - (Topic 2) 

The EMPLOYEES table has these columns: 

LAST NAMEVARCHAR2(35) SALARYNUMBER(8,2) HIRE_DATEDATE 

Management wants to add a default value to the SALARY column. You plan to alter the table by using this SQL statement: 

ALTER TABLE EMPLOYEES MODIFY (SALARY DEFAULT 5000); 

What is true about your ALTER statement? 

A. Column definitions cannot be altered to add DEFAULT values. 

B. A change to the DEFAULT value affects only subsequent insertions to the table. 

C. Column definitions cannot be altered at add DEFAULT values for columns with a NUMBER data type. 

D. All the rows that have a NULL value for the SALARY column will be updated with the value 5000. 

View Answer

Q9. - (Topic 2) 

View the Exhibit and examine the structure of the PRODUCTS table. 

You want to display the category with the maximum number of items. You issue the following query: 

SQL>SELECT COUNT(*),prod_category_id FROM products GROUP BY prod_category_id HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM products); 

What is the outcome? 

A. It executes successfully and gives the correct output. 

B. It executes successfully but does not give the correct output. 

C. It generates an error because the subquery does not have a GROUP BY clause. 

D. It generates an error because = is not valid and should be replaced by the IN operator. 

View Answer

Q10. - (Topic 1) 

Which four are types of functions available in SQL? (Choose 4) 

A. string 

B. character 

C. integer 

D. calendar 

E. numeric 

F. translation 

G. date 

H. conversion 

View Answer