Question

(SQL Coding)

Create a READ ONLY view called: VIEW_EMP_SAL_INFO

Calculate the following: The minimum, average, maximum, and sum of all salaries, and a count to show the records used. Have the calculations grouped by department name. (You will need to join with the departments table.) Round the functions to two decimal points. Give the read only constraint the name of vw_emp_sal_info_readonly.

Answer 1

// Rate my Solution:

//Comments if any doubt:

CODE:

-- (with check option) or (with read only) is used to create a read-only view.
-- joining employee and department table with dno in employee and dnum in department.
-- grouping table values according to the dname and finding min max sum avg count using aggerate functions.

create or replace view view_emp_sal_info as
   select
       dname ,
       round(min(salary),2) minimum_salary ,
       round(max(salary),2) maximum_salary ,
      round(avg(salary),2) Average_salary,
      round(sum(salary),2) Sum_of_salaries ,
      count(*) Number_of_employee
   from employee join department on dno=dnum
    group by dname
    with check option;

//THERE IS ANOTHER METHOD

create or replace view view_emp_sal_info as
   select
       dname ,
       round(min(salary),2) minimum_salary ,
       round(max(salary),2) maximum_salary ,
      round(avg(salary),2) Average_salary,
      round(sum(salary),2) Sum_of_salaries ,
      count(*) Number_of_employee
   from employee join department on dno=dnum
    group by dname
    with read only;

Code(Screenshots):

Output:

MY EMPLOYEE AND DEPARTMENT TABLE: