Oracle Introduction to Oracle9i: SQL Sample Questions:

1. What does the FORCE option for creating a view do?

A) creates a view with constraints
B) creates a view even if the underlying parent table has constraints
C) creates a view in another schema even if you don't have privileges
D) creates a view regardless of whether or not the base tables exist

2. Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
EMPLOYEES EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE
NEW_EMPLOYEES EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2(60) Which MERGE statement is valid?

A) MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES (e.employee_id, e.first_name ||', '||e.last_name);
B) MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);
C) MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);
D) MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

3. A data manipulation language statement _____.

A) modifies the data but not the structure of a table
B) modifies the structure and data in a table
C) modifies the structure but not the data of a table
D) completes a transaction on a table

4. A subquery can be used to _________.

A) Retrieve data based on an unknown condition
B) Sort data in a specific order
C) Create groups of data
D) Convert data to a different format

5. Which four statements correctly describe functions that are available in SQL? (Choose four)

A) NULLIF compares twp expressions and returns null if they are equal, or the first expression if they are not equal.
B) NVL2 returns the first non-null expression in the expression list.
C) NVL compares two expressions and returns null if they are equal, or the first expression of they are not equal.
D) DECODE translates an expression after comparing it to each search value.
E) TRUNCATE rounds the column, expression, or value to n decimal places.
F) TRIM trims the heading of trailing characters (or both) from a character string.
G) INSTR returns the numeric position of a named character.

Solutions: