Q1. - (Topic 1) 

Which two statements are true about constraints? (Choose two.) 

A. The UNIQUE constraint does not permit a null value for the column. 

B. A UNIQUE index gets created for columns with PRIMARY KEY and UNIQUE constraints. 

C. The PRIMARY KEY and FOREIGN KEY constraints create a UNIQUE index. 

D. The NOT NULL constraint ensures that null values are not permitted for the column. 

View Answer

Q2. - (Topic 1) 

The SQL statements executed in a user session as follows: Exhibit: 

Which two statements describe the consequence of issuing the ROLLBACK TO SAVE POINT a command in the session? (Choose two.) 

A. Both the DELETE statements and the UPDATE statement are rolled back 

B. The rollback generates an error 

C. Only the DELETE statements are rolled back 

D. Only the seconds DELETE statement is rolled back 

E. No SQL statements are rolled back 

View Answer

Q3. - (Topic 2) 

Examine the structure of the EMPLOYEES and DEPARTMENTS tables: 

You want to create a report displaying employee last names, department names, and locations. Which query should you use to create an equi-join? 

A. SELECT last_name, department_name, location_id FROM employees , departments ; 

B. SELECT employees.last_name, departments.department_name, 

departments.location_id FROM employees e, departments D WHERE e.department_id =d.department_id; 

C. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE manager_id =manager_id; 

D. SELECT e.last_name, d.department_name, d.location_id FROM employees e, departments D WHERE e.department_id =d.department_id; 

View Answer

Q4. - (Topic 1) 

View the Exhibit and examine the structure of the CUSTOMERS table. Evaluate the following SQL statement: 

Which statement is true regarding the outcome of the above query? 

A. It executes successfully. 

B. It returns an error because the BETWEEN operator cannot be used in the HAVING clause. 

C. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement. 

D. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column. 

View Answer

Q5. - (Topic 1) 

You created an ORDERS table with the following description: Exhibit: 

You inserted some rows in the table. After some time, you want to alter the table by creating the PRIMARY KEY constraint on the ORD_ID column. 

Which statement is true in this scenario? 

A. You cannot add a primary key constraint if data exists in the column 

B. You can add the primary key constraint even if data exists, provided that there are no duplicate values 

C. The primary key constraint can be created only a the time of table creation 

D. You cannot have two constraints on one column 

View Answer

Q6. - (Topic 2) 

What is true about the WITH GRANT OPTION clause? 

A. It allows a grantee DBA privileges. 

B. It is required syntax for object privileges. 

C. It allows privileges on specified columns of tables. 

D. It is used to grant an object privilege on a foreign key column. 

E. It allows the grantee to grant object privileges to other users and roles. 

View Answer

Q7. - (Topic 1) 

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

You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the 'INTERNET' category. 

Which query would give you the required output? 

A. 

SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT MAX(promo_begin_date) FROM promotions )AND promo_category = 'INTERNET' 

B. 

SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date IN (SELECT promo_begin_date FROM promotions WHERE promo_category='INTERNET'); 

C. 

SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ALL (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET'); 

D. 

SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date > ANY (SELECT promo_begin_date FROM promotions WHERE promo_category = 'INTERNET'); 

View Answer

Q8. - (Topic 2) 

SLS is a private synonym for the SH.SALES table. 

The user SH issues the following command: 

DROP SYNONYM sls; 

Which statement is true regarding the above SQL statement? 

A. Only the synonym would be dropped. 

B. The synonym would be dropped and the corresponding table would become invalid. 

C. The synonym would be dropped and the packages referring to the synonym would be dropped. 

D. The synonym would be dropped and any PUBLIC synonym with the same name becomes invalid. 

View Answer

Q9. - (Topic 2) 

View the Exhibit and examine the structure of the ORDERS and CUSTOMERS tables. 

Evaluate the following SQL command: 

SQL> SELECT o.order_id, c.cust_name, o.order_total, c.credit_limit FROM orders o JOIN customers c USING (customer_id) WHERE o.order_total > c.credit_limit FOR UPDATE ORDER BY o.order_id; 

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

A. It locks all the rows that satisfy the condition in the statement. 

B. It locks only the columns that satisfy the condition in both the tables. 

C. The locks are released only when a COMMIT or ROLLBACK is issued. 

D. The locks are released after a DML statement is executed on the locked rows. 

View Answer

Q10. - (Topic 1) 

View the Exhibit and examine the data in the PRODUCTS table. You need to display product names from the PRODUCTS table that belong to the 'Software/Other1 category with minimum prices as either $2000 or $4000 and no unit of measure. You issue thej following query: 

Which statement is true regarding the above query? 

A. It executes successfully but returns no result. 

B. It executes successfully and returns the required result. 

C. It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid. 

D. It generates an error because the condition specified for the PROD_CATEGORY column is not valid. 

View Answer