find emp_names,max,min salary and number of employees on each department?

Question

I use oracle 11g , so i have 2 tables(employees,departments):

desc employees:      desc departments

 EMPLOYEE_ID        DEPARTMENT_ID
 FIRST_NAME         DEPARTMENT_NAME
 LAST_NAME          MANAGER_ID
 EMAIL              LOCATION_ID
 PHONE_NUMBER
 HIRE_DATE
 JOB_ID
 SALARY
 COMMISSION_PCT
 MANAGER_ID
 DEPARTMENT_ID

and i want to get

employee_name,emp_names,emp_salary,dep_id,dep_names,max salary on each dep , and min salary on each dep , and number of employees on each department .

so i do this qouery:

 select FIRST_NAME,DEPARTMENT_ID,max(SALARY),min(SALARY),count(EMPLOYEE_ID)
from employees join departments on employees.department_id = departments.departm
ent_id group by first_name,department_id;

but its give an error:

ERROR at line 1: ORA-00918: column ambiguously defined

however does my sql query right ?

Answer 1

I don't have your tables so I created views out of Scott's ones, to simulate what you have.

SQL> create or replace view employees as
  2    select empno  employee_id,
  3           ename  last_name,
  4           deptno department_id,
  5           sal    salary
  6    from emp;

View created.

SQL> create or replace view departments as
  2    select deptno department_id,
  3           dname  department_name
  4    from dept;

View created.

SQL>

Here's how I understood the question: list of employees per department should be separated from the rest (minimums, maximums, counts).

So: list of employees:

SQL> select d.department_name, e.last_name
  2  from departments d join employees e on d.department_id = e.department_id
  3  order by d.department_name;

DEPARTMENT_NAM LAST_NAME
-------------- ----------
ACCOUNTING     CLARK
ACCOUNTING     KING
ACCOUNTING     MILLER
RESEARCH       JONES
RESEARCH       FORD
RESEARCH       ADAMS
RESEARCH       SMITH
RESEARCH       SCOTT
SALES          WARD
SALES          TURNER
SALES          ALLEN
SALES          JAMES
SALES          BLAKE
SALES          MARTIN

14 rows selected.

Aggregates: outer join for departments that don't have any employees:

SQL> select d.department_name,
  2    min(e.salary) min_sal,
  3    max(e.salary) max_sal,
  4    count(e.employee_id) cnt_emp
  5  from departments d left join employees e on d.department_id = e.department_id
  6  group by d.department_name
  7  order by d.department_name;

DEPARTMENT_NAM    MIN_SAL    MAX_SAL    CNT_EMP
-------------- ---------- ---------- ----------
ACCOUNTING           1300       5000          3
OPERATIONS                                    0
RESEARCH              800       3000          5
SALES                 950       2850          6

LISTAGG allows you to list all employees per department in the same statement, though; see line 5. I, somehow, doubt that you learnt about that function yet (as you struggle with such a problem).

SQL> select d.department_name,
  2    min(e.salary) min_sal,
  3    max(e.salary) max_sal,
  4    count(e.employee_id) cnt_emp,
  5    listagg(e.last_name, ', ') within group (order by e.last_name) employees
  6  from departments d left join employees e on d.department_id = e.department_id
  7  group by d.department_name
  8  order by d.department_name;

DEPARTMENT_NAM    MIN_SAL    MAX_SAL    CNT_EMP EMPLOYEES
-------------- ---------- ---------- ---------- -------------------------------------------
ACCOUNTING           1300       5000          3 CLARK, KING, MILLER
OPERATIONS                                    0
RESEARCH              800       3000          5 ADAMS, FORD, JONES, SCOTT, SMITH
SALES                 950       2850          6 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

SQL>