Q1. - (Topic 1) 

Evaluate the following SQL statement: 

SQL> SELECT cust_id. cust_last_name FROM customers WHERE cust_credit_limit IN (select cust_credit_limit FROM customers WHERE cust_city='Singapore'): 

Which statement is true regarding the above query if one of the values generated by the sub query is NULL? 

A. It produces an error. 

B. It executes but returns no rows. 

C. It generates output for NULL as well as the other values produced by the sub query. 

D. It ignores the NULL value and generates output for the other values produced by the sub query. 

View Answer

Q2. - (Topic 1) 

Exhibit contains the structure of PRODUCTS table: 

Evaluate the following query: 

What would be the outcome of executing the above SQL statement? 

A. It produces an error 

B. It shows the names of products whose list price is the second highest in the table. 

C. It shown the names of all products whose list price is less than the maximum list price 

D. It shows the names of all products in the table 

View Answer

Q3. - (Topic 1) 

Examine these statements: 

CREATE ROLE registrar; 

GRANT UPDATE ON student_grades TO registrar; 

GRANT registrar to user1, user2, user3; 

What does this set of SQL statements do? 

A. The set of statements contains an error and does not work. 

B. It creates a role called REGISTRAR, adds the MODIFY privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users. 

C. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and gives the REGISTRAR role to three users. 

D. It creates a role called REGISTRAR, adds the UPDATE privilege on the STUDENT_GRADES object to the role, and creates three users with the role. 

E. It creates a role called REGISTRAR, adds the UPDATE privilege on three users, and gives the REGISTRAR role to the STUDENT_GRADES object. 

F. It creates a role called STUDENT_GRADES, adds the UPDATE privilege on three users, and gives the UPDATE role to the registrar. 

View Answer

Q4. - (Topic 2) 

The STUDENT_GRADES table has these columns: 

STUDENT_ID NUMBER(12) 

SEMESTER_END DATE 

GPA NUMBER(4,3) 

Which statement finds the highest grade point average (GPA) per semester? 

A. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL; 

B. SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL; 

C. SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end; 

D. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades; 

E. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL; 

View Answer

Q5. - (Topic 1) 

You work as a database administrator at ABC.com. You study the exhibit carefully. Exhibit: 

Evaluate the following query: Exhibit: 

The above query produces an error on execution. What is the reason for the error? 

A. An alias cannot be used in an expression 

B. The alias MIDPOINT should be enclosed within double quotation marks for the CUST_CREDIT_LIMIT/2 expression 

C. The MIDPOINT +100 expression gives an error because CUST_CREDIT_LIMIT contains NULL values 

D. The alias NAME should not be enclosed within double quotation marks 

View Answer

Q6. - (Topic 1) 

Evaluate the following SQL statements: Exhibit: 

Which is the correct output of the above query? 

A. +00-300, +54-02,+00 11:12:10.123457 

B. +00-300,+00-650,+00 11:12:10.123457 

C. +25-00, +54-02, +00 11:12:10.123457 

D. +25-00,+00-650,+00 11:12:10.123457 

View Answer

Q7. - (Topic 1) 

See the Exhibit and Examine the structure of the CUSTOMERS table: 

Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed. 

Which SQL statement would produce the required result? 

A. SELECT NVL(cust_credit_limit,'Not Available')*.15 "NEW CREDIT" FROM customers; 

B. SELECT NVL(cust_credit_limit*.15,'Not Available') "NEW CREDIT" FROM customers; 

C. SELECT TO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) "NEW CREDIT" FROM customers; 

D. SELECT NVL(TO_CHAR(cust_credit_limit*.15),'Not Available') "NEW CREDIT" FROM customers; 

View Answer

Q8. - (Topic 1) 

Evaluate the following two queries: Exhibit: 

Exhibit: 

Which statement is true regarding the above two queries? 

A. Performance would improve in query 2 only if there are null values in the CUST_CREDIT_LIMIT column 

B. Performance would degrade in query 2 

C. There would be no change in performance 

D. Performance would improve in query 2 

View Answer

Q9. - (Topic 1) 

The CUSTOMERS table has the following structure: Exhibit: 

You need to write a query that does the following task: 

* 

Display the first name and tax amount of the customers. Tax is 5% of their credit limit 

* 

Only those customers whose income level has a value should be considered 

* 

Customers whose tax amount is null should not be considered 

Which statement accomplishes all the required tasks? 

A. 

SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND tax_amount IS NOT NULL; 

B. 

SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL; 

C. 

SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE cust_income_level <> NULL AND tax_amount <> NULL; 

D. 

SELECT cust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT FROM customers WHERE (cust_income_level,tax_amount) IS NOT NULL; 

View Answer

Q10. - (Topic 2) 

Which constraint can be defined only at the column level? 

A. UNIQUE 

B. NOT NULL 

C. CHECK 

D. PRIMARY KEY 

E. FOREIGN KEY 

View Answer