CIS 411w spring 2017 problem set 15 In this assignment you will be creating view
ID: 3820488 • Letter: C
Question
CIS 411w spring 2017 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 (don’t 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.
Submit your assignment to the Problem Set 15 Drop Box for grading.
Explanation / Answer
2
Create view emp_view as Select empno as “Employee Number”, ename “Employee Name”, NVL(sal * 26 + comm * 12 ,0) as "Gross Salary", job as "JOB" , loc as “Location” from dept ;
3.
Create view emp_view_updt as Select e.empno, e.ename, e.job, d.deptno, d.loc from emp e inner join dept d on e.deptno = d.deptno;
4. update emp_view_updt set deptno = 30 where empno = 7369;
Select deptno from emp where empno = 7369;
it should result 30 for deptno