Introduction to Oracle 9i SQL - Assessment Test

1. Which operator will be evaluated first in the following SELECT statement?
SELECT (2+3*4/2-5) FROM dual;
A. +
B. *
C. /
D. -

Ans: B

2. Which line of the following code has an error?
SELECT *FROM emp WHERE comm = NULL ORDER BY ename;
A. SELECT *
B. FROM emp
C. WHERE comm = NULL
D. There is no error in this statement.

Ans. D.
Although there is no error in this statement, the statement will not return the desired result. When a NULL is compared, you cannot use the = or != operators; you must use the IS NULL or IS NOT NULL operator. See Chapter 1 for more information about the comparison operators.

3. Which two statements are true about NULL values?
A. You cannot search for a NULL value in a column using the WHERE clause.
B. If a NULL value is returned in the subquery or if NULL is included in the list when using a NOT IN operator, no rows will be returned.
C. Only = and != operators can be used to search for NULL values in a column.
D. In an ascending order sort, NULL values appear at the bottom of the result set.
E. Concatenating a NULL value to a non-NULL string results in a NULL.

Ans: B, D.
You can use the IS NULL or IS NOT NULL operator to search for NULLs or non-NULLs in a column. Since NULLs are sorted higher, they appear at the bottom of the result set in an ascending order sort.

4. Which components are required to run iSQL*Plus from your PC? (Choose all that apply.)
A. SQL*Plus installed on the PC
B. Oracle Net on the PC
C. HTTP Server
D. iSQL*Plus Server

Ans: C, D.
iSQL*Plus architecture includes three layers. The client layer is the web browser. The middle layer has the HTTP Server, iSQL*Plus server, and Oracle Net. The third layer is the Oracle database.

5. When you use the DEFINE variable command, what datatype is the variable?
A. VARCHAR2
B. CHAR
C. LONG
D. NUMBER
E. None of the above; you must specify the datatype along with the variable.

Ans: B.
Variables declared using the DEFINE command take the CHAR datatype. To assign a value to a variable, use DEFINE variable=value


6. Which function can return a non-NULL value if passed NULL arguments?
A. NULLIF
B. LENGTH
C. CONCAT
D. INSTR
E. TAN

Ans C.
CONCAT will return a non-NULL if only one parameter is NULL. Both CONCAT parameters would need to be NULL for CONCAT to return NULL. The NULLIF function returns NULL if the two parameters are equal. The LENGTH of a NULL is NULL. INSTR will return NULL if NULL is passed in, and the tangent of a NULL is NULL.

7. (skip)

8. The following statement will raise an exception on which line?
select dept_name, avg(all salary) ,count(*) "number of employees"
from emp , dept
where deptno = dept_no and count(*) > 5
group by dept_name
order by 2 desc;

A. select dept_name, avg(all salary), count(*) "number of employees"
B. where deptno = dept_no
C. and count(*) > 5
D. group by dept_name
E.order by 2 desc;

Ans C.
Group functions cannot appear in the WHERE clause.

9. Your HR department wants to recognize the most senior employees in each department. You need to produce a report with the following requirements:
Display each department ID
For each department, show the earliest hire date
Show how many employees from each department were hired on the earliest hire date
Will all three requirements be met with the following SQL statement?

select department_id ,min(hire_date) ,count(*) keep (dense_rank last order by hire_date asc)
from hr.employees
group by department_id;

A. The statement meets all three requirements.
B. The statement meets two of the three requirements.
C. The statement meets one of the three requirements.
D. The statement meets none of the three requirements.
E. The statement will raise an exception.


Ans B.
The first two columns (lines 1 and 2) will meet the first two requirements, but the third column (lines 3 and 4) will report the number of employees with the most recent hire date. To report the number of employees with the oldest hire date, you need either count(*) keep (dense_rank first order by hire_date asc) or count(*) keep (dense_rank last order by hire_date desc). See Chapter 4 for more information about group functions.

10. The DEPT table has the following data.
SQL> SELECT * FROM dept;
DEPTNO DNAME LOC
---------- -------------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Consider this INSERT statement:
INSERT INTO (SELECT * FROM dept WHERE deptno = 10)VALUES (50, 'MARKETING', 'FORT WORTH');

Choose the best answer.

A. The INSERT statement is invalid; a valid table name is missing.
B. 50 is not a valid DEPTNO value, since the subquery limits DEPTNO to 10.
C. The statement will work without error.
D. A subquery and a VALUES clause cannot appear together.

Ans C.
The statement will work without error. Option B would be correct if you used the WITH CHECK OPTION clause in the subquery. See Chapter 5 for more information about subqueries.

11. At a minimum, how many join conditions should there be to avoid a Cartesian join if there are three tables in the FROM clause?
A. 1
B. 2
C. 3
D. There is no minimum.


Ans: B.
There should be at least n-1 join conditions when joining n tables to avoid a Cartesian join. To learn more about joins, see Chapter 5.

12. Which two of the following queries is valid syntax that would return all rows from the EMPLOYEES and DEPARTMENTS tables, even if there are no corresponding/related rows in the other table.
A. SELECT last_name, first_name, department_name FROM employees e FULL JOIN departments d ON e.department_id = d.department_id;
B. SELECT last_name, first_name, department_name FROM employees e OUTER JOIN departments d ON e.department_id = d.department_id;
C. SELECT e.last_name, e.first_name, d.department_name FROM employees e LEFT OUTER JOIN departments d ON e.department_id = d.department_id RIGHT OUTER JOIN employees f ON f.department_id = d.department_id;
D. SELECT e.last_name, e.first_name, d.department_nameFROM employees e CROSS JOIN departments d ON e.department_id = d.department_id;
E. SELECT last_name, first_name, department_name FROM employees FULL OUTER JOIN departments USING (department_id);

Ans: A, E.
An outer join on both tables can be achieved using the FULL OUTER JOIN syntax. The join condition can be specified using the ON clause to specify the columns explicitly or using the USING clause to specify columns with common column names. Options B and D would result in errors. In option B, the join type is not specified; OUTER is an optional keyword. In option D, CROSS JOIN is used to get a Cartesian result, and Oracle9i does not expect a join condition. To learn more about joins, read Chapter 5.

13. Why does the following statement fail?
CREATE TABLE FRUITS&VEGETABLES( NAME VARCHAR2 (40));
A. The table should have more than one column defined.
B. NAME is a reserved word, which cannot be used as a column name.
C. The table name is invalid.
D. Column length cannot exceed 30 characters.

Ans C. Table and column names can have only letters, numbers, and three special characters: dollar sign ($), underscore (_), and pound sign (#).

14. Which datatype stores data outside the Oracle database?
A. UROWID
B. BFILE
C. BLOB
D. NCLOB
E. EXTERNAL

Ans B.
The BFILE datatype stores only the locator to an external file in the database; the actual data is stored as operating system files. BLOB, NCLOB, CLOB, and BFILE are the LOB datatypes in Oracle9i. EXTERNAL is not a valid datatype.

15. Which of the following statements are true? (Choose all that apply.)
A. Primary key constraints allow NULL values in the columns.
B. Unique key constraints allow NULL values in the columns.
C. Primary key constraints do not allow NULL values in columns.
D. A nonunique index cannot be used to enforce a primary key
constraint.

Ans: B, C.
Primary key and unique key constraints can be enforced using nonunique indexes. Unique keys allow NULL values in the columns, but a primary key does not. See Chapter 7 for more information about constraints.

16. Which operation cannot be performed using the ALTER TABLE statement?
A. Rename table
B. Rename column
C. Drop column
D. Drop NOT NULL constraint

Ans:B.
You cannot rename a column in the table. To rename a column, you must re-create a table or create a view on the table with the new column name. See Chapter 7 for more information about modifying tables.

17. INTERVAL datatypes store a period of time. Which components are included in the INTERVAL DAY TO SECOND column? (Choose all that apply.)
A. Years
B. Quarters
C. Months
D. Days
E. Hours
F. Minutes
G. Seconds
H. Fractional seconds

Ans: D, E, F, G.
The INTERVAL DAY TO SECOND datatype is new to Oracle9i and is used to store an interval between two date/time components. See Chapter 7 for more information about Oracle9i datatypes.

18. Which of the following statements are true? (Choose all that apply.)
A. The TRUNCATE statement is used to selectively remove rows from table.
B. The TRUNCATE statement is used to remove all rows from a table.
C. Rows removed using the TRUNCATE command cannot be undone (rolled back).
D. The TRUNCATE statement drops the constraints and triggers associated with the table.
E. The TRUNCATE statement invalidates all the constraints and triggers associated with the table.

Ans: B, C.
You cannot specify a WHERE clause in the TRUNCATE statement; it removes all the rows in the table, releases the storage space (this is the default if you did not explicitly specify KEEP STORAGE), and does not drop or invalidate any of the dependent objects.

19. Which data dictionary view holds information about the columns in a view?
A. USER_VIEWS
B. USER_VIEW_COLUMNS
C. USER_TAB_COLUMNS
D. USER_ALL_COLUMNS

Ans C.
USER_VIEWS shows the SQL used to create the view. The view columns are in the USER_TAB_COLUMNS view. The view USER_UPDATABLE_COLUMNS will show the columns of the view that can be updated. See Chapter 8 for more information about views.


20. The primary key of the STATE table is STATE_CD. The primary key of the CITY table is STATE_CD and CITY_CD. The STATE_CD column of the CITY table is the foreign key to the STATE table. There are no other constraints on these two tables. Consider the following view definition.
CREATE OR REPLACE VIEW state_city
AS SELECT a.state_cd, a.state_name, b.city_cd, b.city_name
FROM state a, city b
WHERE a.state_cd = b.state_cd;

Which of the following operations are permitted on the base tables of the view? (Choose all that apply.)
A. Insert a record into the CITY table
B. Insert a record into the STATE table
C. Update the STATE_CD column of the CITY table
D. Update the CITY_CD column of the CITY table
E. Update the CITY_NAME column of the CITY table
F. Update the STATE_NAME column of the STATE table

Ans: D, E.
In the join view, CITY is the key-preserved table. You can update the columns of the CITY table, except STATE_CD, because STATE_CD is not part of the view definition (the STATE_CD column in the view is from the STATE table). Since we did not include the STATE_CD column from the CITY table, no INSERT operations are permitted (STATE_CD is part of the primary key). If the view were defined as follows, all the columns of the CITY table would have been updatable, and new records could be inserted into the CITY table.

CREATE OR REPLACE VIEW state_city AS
SELECT b.state_cd, a.state_name, b.city_cd, b.city_name
FROM states a, cities b
WHERE a.state_cd = b.state_cd;
See Chapter 8 for more information about views.

Note by Denis:
This question seems missing a condition. That is the operations are performed through the view.

A table in the join view is key-preserved, if the primary and unique keys of the table are unique on the view's result set.

21. In Oracle9i, outer join syntax can be specified using the LEFT JOIN or RIGHT JOIN keywords or by using the (+) operator. Suppose that you have the two tables PRODUCTS and ORDERS. You need to get the ORDER# and PRODUCT# for all orders, even if there is no order placed for a particular product; that is, you want to get all of the rows from the PRODUCTS table. The PRODUCT# column is common to both tables. Which condition would return the desired result?
A. WHERE PRODUCTS.PRODUCT# = ORDERS.PRODUCT#
B. WHERE PRODUCTS.PRODUCT# (+) = ORDERS.PRODUCT#
C. WHERE PRODUCTS.PRODUCT# = ORDERS.PRODUCT# (+)
D. WHERE PRODUCTS.PRODUCT# (+) = ORDERS.PRODUCT# (+)

A (+) is specified after the column name of the table where there may not be a corresponding row. Since we want to get all rows from the PRODUCTS table, the outer-join operator is placed beside the column names of the ORDERS table. See Chapter 5 for more information about joins.
Ans: C.
---

22. Oracle9i supports the ISO SQL99 standard for specifying joins in queries. Which keywords are used to specify a Cartesian join using this syntax?
A. NATURAL JOIN
B. OUTER JOIN
C. INNER JOIN
D. CROSS JOIN

CROSS JOIN specifies a Cartesian join. A Cartesian join occurs when you do not have a common column to join two tables. All combinations of all rows from both tables will be retrieved. If Table A has m rows and Table B has n rows, a Cartesian join would retrieve m × n rows. See Chapter 5 for more information about Cartesian joins.

Ans: D
------

23. Outer joins in Oracle9i can be specified using the syntax

Which keyword is optional?
A. JOIN
B. OUTER
C. JOIN and OUTER
D. None

In specifying joins using SQL 1999 syntax, the OUTER and INNER keywords are optional. See Chapter 5 for more information about the ISO SQL99 syntax for joins.

Ans: B

-------

24. The ORDERS table contains the following data:

select order_mode, sum(order_total)

from oe.orders

group by order_mode;

ORDER_MO SUM(ORDER_TOTAL)

-------- ----------------

direct 1903629.2

online 1764425.5

How much revenue will be inserted into the DIRECT_ORDERS table with the following SQL statement?

INSERT ALL

WHEN order_mode='online'

THEN INTO online_orders (customer_id, sales_rep_id, order_total) VALUES (customer_id, sales_rep_id, order_total)

WHEN order_mode ='direct'

THEN INTO direct_orders (customer_id, sales_rep_id, order_total) VALUES (customer_id, sales_rep_id, order_total)

WHEN order_mode in ('online','direct') THEN INTO direct_orders (customer_id, sales_rep_id, order_total) VALUES (customer_id, sales_rep_id, order_total)

SELECT order_mode, customer_id, sales_rep_id, order_total

FROM orders;

A. 3668054.7
B. 1903629.2
C. 1764425.5
D. 5571683.9

The ALL clause tells Oracle to execute each and every WHEN clause it evaluates to TRUE. Two of the three WHEN clauses evaluate to TRUE. So, the DIRECT_ORDERS rows are inserted twice: in the second and third WHEN clause. Additionally, the ONLINE_ORDERS would be inserted in the third WHEN clause into the DIRECT_SALES table. To pass the certification exam, you must understand how to correctly interpret SQL to both identify problems and satisfy requirements. See Chapter 6 for more information about the INSERT statement.

Ans D.

25. With regard to the following SQL statements, which of the following options is most correct?

UPDATE emp
  SET salary = salary * 1.10
  WHERE class_code = 'A';
SAVEPOINT ClassA_Floor Adjusted;

UPDATE emp
  SET salary = salary * 1.07
  WHERE class_code = 'B';
SAVEPOINT ClassB_FloorAdjusted;

UPDATE emp SET salary = salary * 1.05
  WHERE class_code = 'C';
SAVEPOINT ClassC_FloorAdjusted;
ROLLBACK TO SAVEPOINT ClassB_FloorAdjusted;

UPDATE taxes SET max_tax = 76200*0.075
WHERE tax_type = 'FICA';
SAVEPOINT MaxTax;
ROLLBACK to MaxTax;
ROLLBACK to ClassA_FloorAdjusted;
COMMIT;

A. No changes occur to the EMP table, but the TAXES table is changed.
B. Both the EMP and TAXES tables are changed.
C. Only EMP rows with CLASS_CODE equal to 'A' are changed.
D. Only EMP rows with CLASS_CODES equal to 'C' are changed.
E. No changes occur to either the EMP or the TAXES table.

Only CLASS_CODE 'A' EMP rows are changed. The furthest we roll back is to the savepoint named ClassA_FloorAdjusted, so the only changes that are committed are those occurring before this savepoint (CLASS_CODE 'A') or after the rollback to savepoint (nothing). Chapter 6 discusses savepoints and rollbacks.

Ans: C

-----------

26. You need to change employees in department 50 who have a job ID of 'ST_CLERK' to department 80 and to manager ID 145. Which option will best satisfy these requirements?

A. update employees set department_id = 80 and manager_id = 145
where department_id = 50 and job_id = 'ST_CLERK';
B. update employees set (department_id, manager_id) = (80, 145)
where department_id = 50 and job_id = 'ST_CLERK';
C. update employees set department_id = 80,manager_id = 145
where department_id = 50 and job_id = 'ST_CLERK';
D. You need to use two UPDATE statements:
one for DEPARTMENT_ID and one for MANAGER_ID.

You can update multiple columns in a single UPDATE statement. The correct syntax to use when setting the columns to explicit values is to comma delimit each column = value clause. See Chapter 6 for more information on changing data with an UPDATE statement.
Ans: C.


27. The Marketing department has produced a master list of promotions for next month and placed it in table named NEW_PROMOTIONS. Some promotions are new and some have a new end date. You need to apply these promotions to the PROMOTIONS table using primary key PROMOTION_ID. Which statement best satisfies these requirements?

A.
update promotions p set promo_end_date =
(select promo_end_date from new_promotions np
where np.promo_id = p.promo_id);

B.
merge into promotions p using
select promo_id, end_date from new_promotions) np
on (p.promo_id = np.promo_id)
when matched then update
set p.end_date = np.end_date
when not matched then
insert (select promo_id, end_date)
values (np.promo_id, np.end_date);

C
upsert promotions p with new_promotions np
on (p.promo_id = np.promo_id)
when matched then update
set p.end_date = np.end_date
when not matched then insert(select promo_id, end_date)
values (np.promo_id, np.end_date);

D.
merge into promotions p using
(select promo_id, end_date from new_promotions) np
on (p.promo_id = np.promo_id)
if joined then update set p.end_date = np.end_date
else insert(select promo_id, end_date)
values (np.promo_id, np.end_date);

Option A will only update existing promotions, missing the new promotions. UPSERT appeared in marketing announcements of new Oracle9i features that are implemented via a MERGE statement. The correct syntax for the MERGE statement does not use an IF JOINED and ELSE construct; it uses a WHEN MATCHED and WHEN NOT MATCHED construct. See Chapter 6 for more information about modifying data with the MERGE statement.

Ans: B

28. What order does Oracle use in resolving a table or view referenced in a SQL statement?

A. Table/view within user's schema, public synonym, private synonym
B. Table/view within user's schema, private synonym, public synonym
C. Public synonym, table/view within user's schema, private synonym
D. Private synonym, public synonym, table/view within user's schema

Ans: B. Private synonyms override public synonyms, and tables or views owned by the user always resolve first. To learn more about synonyms, see Chapter 9.


29. Which statement will assign the next number from the sequence EMP_SEQ to the variable EMP_KEY?

A. emp_key := emp_seq.nextval;
B. emp_key := emp_seq.next_val;
C. emp_key := emp_seq.nextvalue;
D. emp_key := emp_seq.next_value;

Ans: A. This kind of question, which quizzes you on the precise syntax, really does appear on the exam. You'll need to know the correct spelling for sequence assignments. You can read about sequences in Chapter 9.

30. The table WKSYS.WK$CRAWLER_STAT has a B-tree index on the three columns WK$ITD, ID, and STAT_NAME. Which of the following statements could benefit from this index?
A. insert into wk$crawler_stat values (12,25,'timeout',NULL);
B. delete from wh$crawler_stat where id = 25;
C. select * from wk$crawler_stat where wk$itd between 2 and 12;
D. select * from wk$crawler_stat where id = 25 or stat_name like 'cache%';

Ans: C.

Indexes cannot improve the performance of INSERT statements. B-tree indexes can be used if a leading subset of columns is specified. A leading subset of columns for this index would need to include WK$ITD and optionally ID. Options B and D do not reference a leading subset of columns in the index. Option C is the only statement that references WK$ITD or a leading subset of indexed columns. You can read about indexes in Chapter 9.

31. Which of the following statements could use an index on the columns PRODUCT_ID and WAREHOUSE_ID of the OE.INVENTORIES table?
A. select count (distinct warehouse_id) from oe.inventories;
B. select product_id, quantity_on_hand from oe.inventories where warehouse_id = 100;
C. insert into oe.inventories values (5,100,32);
D. None of these statements could use the index

The index contains all the information needed to satisfy the query in option A, and a full-index scan would be faster than a full-table scan. A leading subset of indexes columns is not specified in the WHERE clause of option B, and INSERT operations, as in option C, are slowed down by indexes. For more information on indexes, see Chapter 9.

Ans: A.

32. Which one of the following statements will succeed?
A. grant create user, alter user to Katrina with admin option;

B. grant grant any privilege to Katrina with grant option;

C. grant create user, alter user to Katrina with grant option;

D. grant revoke any privilege to Katrina with admin option;

Ans: A. The grant option cannot be used on system privileges, and revoke any privilege is not a valid privilege. For more information on privileges, see Chapter 10.

33. What does the following statement do?
alter user effie identified by kerberos;
A. Creates user account effie
B. Changes the external authentication service for user effie
C. Makes effie a globally identified account
D. Changes user effie's password

Ans: D. Option A would be possible in Oracle6, but the exam is on Oracle9i. The kerberos password is just there to obfuscate. Chapter 10 discusses authentication and user accounts.

34. Which of the following system privileges cannot be granted to a role?
A. BECOME USER
B. UNLIMITED TABLESPACE
C. GRANT ANY ROLE
D. GRANT ANY PRIVILEGE

Ans: B.

UNLIMITED TABLESPACE is a special system privilege that must be granted to a user. BECOME USER is used for full database imports and comes standard as part of the IMP_FULL_DATABASE role. GRANT ANY ROLE and GRANT ANY PRIVILEGE have no restrictions on the grantee. Chapter 10 discusses system privileges and their restrictions.

35. User Rob granted SELECT on table OUTLN.OL$ to Chip WITH GRANT OPTION, and Chip has granted SELECT on OUTLN.OL$ to Ernie. Rob has also granted the DBA role to Chip WITH ADMIN OPTION, and Chip has granted DBA to Ernie. Chip leaves the department, and his account is dropped. Which privileges will Ernie still have if no other privileges are granted?

A. Both SELECT on table OUTLN.OL$ and DBA
B. Neither privilege
C. Only SELECT on table OUTLN.OL$
D. Only DBA

Ans: D. Revocations of object privileges cascade, but system and role privilege revocations do not. The DBA role will remain after user Chip is dropped, but the object privilege SELECT on OUTLN.OL$ that Chip granted will be dropped when user Chip is dropped. For more information on database privileges, see Chapter 10.