Who can update the SALARY column through the MY_EMPLOYEE view? Hint: The USER function returns the name of the currently logged in user.
CREATE OR REPLACE VIEW my_employee AS SELECT employee_id, employee_name, salary, manager FROM employee WHERE manager = USER WITH CHECK OPTION CONSTRAINT my_employee_ck_manager
Solution:
Only managers can update their respective employee's salaries. The WITH CHECK OPTION constraint ensures that DML statements satisfy the condition in the WHERE clause. This condition enforces that only records are displayed, updated, inserted, and deleted where the value in the MANAGER column is equal to the user currently logged in. A SELECT statement against the MY_EMPLOYEE view for the user with the login ID of JONES could look like this:
EMPLOYEE_ID EMPLOYEE_NAME SALARY MANAG
----------- ------------------------- --------- -----
150 Gates 11787 JONES
251 Sheppard 11106 JONES
552 Edwards 7036 JONES
353 Philpotts 11373 JONES
Which columns in a table should you consider indexing?
Solution:
Columns frequently used in the WHERE clause of SQL statements are good candidates for indexes. Be sure to consider the selectivity of the values of the columns, that is, how many distinct values there are in the column. Sometimes it is useful to combine several columns with a low selectivity in a concatenated index. Make sure you properly access the index. You also see more examples on indexes and their impact in Chapter 16, "SQL Optimization." In addition, consider indexing foreign key columns, because they not only are frequently referenced in the WHERE clause of joins, but also improve the locking of records on the child table.
Explain the purpose of the Oracle SQL command below.
ALTER INDEX crse_crse_fk_i rebuild
Solution:
This command rebuilds an existing index named CRSE_CRSE_FK_I without having to drop the old index first and then re-create it.
Are NULLs stored in an index? Explain.
Solution:
NULLs are not stored in an index. The exception is if it is a concatenated index and the leading column of the index does not contain a NULL value. Another exception is a bitmapped index, which stores null values.