SQL multible select statements in one table-CodePudding

I have a SQL database with 2 tables called DEPT and EMP

  create table dept(  
  deptno     number(2,0),  
  dname      varchar2(14),  
  loc        varchar2(13),  
  constraint pk_dept primary key (deptno)  
);
create table emp(  
  empno    number(4,0),  
  ename    varchar2(10),  
  job      varchar2(9),  
  mgr      number(4,0),  
  hiredate date,  
  sal      number(7,2),  
  comm     number(7,2),  
  deptno   number(2,0),  
  constraint pk_emp primary key (empno),  
  constraint fk_deptno foreign key (deptno) references dept (deptno)  
);

What needs to be done is the following:

Department Location
Company average salary
Department average salary
Deviation from department average salary
Deviation from company average salary
EMPNo
Job
Salary

This is what I have ready so far

SELECT ROUND(AVG(sal) over(),2) AS COMPANY_AVERAGE_SALARY,sal - ROUND(AVG(sal) over(),2) AS DEVIATION_FROM_COMPANY_AVERAGE_SALARY, empno AS EMPNO, job AS JOB, sal AS SALARY  from emp;

I also have:

SELECT dept.loc AS DEPARTMENT_LOCATION FROM emp,dept WHERE emp.deptno=Dept.deptno;

My main problem is that I can't seem to get the different select lines to display in the same output and don't know how to get department average salary to display on each line and how to do DEVIATION_FROM_DEPARTMENT_AVERAGE_SALARY Would appreciate the help a lot.

CodePudding user response：

You were very close with your example queries. To find the deviation, you just need to find the difference between the current employee salary and the company/dept average salary like this:

SELECT d.loc
           AS department_location,
       ROUND (AVG (sal) OVER (), 2)
           AS company_average_salary,
       ROUND (AVG (sal) OVER (PARTITION BY e.deptno), 2)
           AS dept_average_salary,
       ROUND (sal - AVG (sal) OVER (PARTITION BY e.deptno), 2)
           AS dept_salary_deviation,
       ROUND (sal - AVG (sal) OVER (), 2)
           AS company_salary_deviation,
       e.empno,
       e.job,
       e.sal
  FROM emp e LEFT JOIN dept d ON (e.deptno = d.deptno);