Create a view that will display department names and sum of all employee income by department.

Question

Use case study databases for the following queries.

Answer 1

```
SQL> CREATE OR REPLACE VIEW EMPLOYEE_DEPT_SAL_VU
2 AS
3 SELECT DEPTNAME, SUM(SALARY+NVL(COMMISSION,0)) TOTALSALARY
4 FROM EMPLOYEE E, DEPT D
5 WHERE E.DEPTID = D.DEPTID
6 GROUP BY DEPTNAME;

VIEW CREATED.

SQL> SELECT * FROM EMPLOYEE_DEPT_SAL_VU;

DEPTNAME TOTALSALARY
------------ -----------
FINANCE 410000
INFOSYS 166500
MARKETING 160000
SALES 77500
```