Problem Set 15

In this assignment you will be creating views. Views are a way to display information from various tables or views in a way that is easily comprehended by end-users.

1. Log on to your Oracle ApEx account.

2. Create a View called emp_view that is a join of the emp table and the dept table. The view consists of a SQL query that shows the empno, ename, sal * 26 + comm * 12 (dont forget the NVL() function on COMM ), the job and the loc from the dept table.

This view is for the Human Resources department, and will consist of the following columns:

Empno will show in the view as Employee Number

Ename will show as Employee Name

Job you can leave as JOB

Loc will show in the view as Location

Copy and paste the SQL to create the view into your assignment.

Views can also be used to update the base tables that they are derived from. In this example you create a view that has data from the emp and dept tables, and then you will use the view to update the emp base table via the emp_view_updt view.

3. Create a View called emp_view_updt that will be updatable. Create the SQL view using a SQL statement that will select empno, ename, job, dptno, loc from the emp and dept tables. Leave the column names the same this time.

4. Update the emp_view_updt view by setting the deptno = 30 for the employee with the empno 7369.

Now query the base table emp (not the emp_view_updt view) to see if your update changed the base table (emp) though the use of the emp_view_updt.

Copy and paste the SQL to create the view into your assignment, along with the SQL to update the base table through the view and a small screenshot of the resulting update.