Oracle SQL querying for average across several tables

Question

Here are the relations:

CREATE TABLE employee (
  name    varchar2(15) not null, 
  ssn      char(9),
  sex      char,
  salary   number(10,2),
  dno      number(4),
  primary key (ssn),
  foreign key (dno) references department(dnumber)
);

CREATE TABLE department (
  dname        varchar2(15) not null,
  dnumber      number(4),
  primary key (dnumber),
  unique (dname),
);

Q1: For each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for that department.

Q2: Suppose that we want the number of male employees in each department rather than all employees (as in Q1) to calculate the departmental averages and number of employees. Can we specify this query in SQL? Why or why not.

Thanks!

What have you tried? It appears that you've posted your homework assignment. We're happy to assist but you'll need to show us what you've been able to figure out thus far. — Justin Cave, Apr 10 '13 at 20:12

score 1 · Accepted Answer · answered Apr 10 '13 at 20:31

1

Your first query might look like

SELECT d.dname,
       COUNT(*) total_employees
  FROM department d LEFT JOIN
       employee e ON d.dnumber = e.dno
 GROUP BY d.dname
HAVING AVG(e.salary) > 30000;

And the second one

SELECT d.dname,
       COUNT(*) male_employees
  FROM department d LEFT JOIN
       employee e ON d.dnumber = e.dno
 WHERE e.sex = 'M'
 GROUP BY d.dname
HAVING AVG(e.salary) > 30000;

SQLFiddle

answered Apr 10 '13 at 20:31

peterm

91,357
15
148
157

1

You have a habit to type `inner join`. Why are you not typing `left outer join` for the sake of consistency and readability? – Egor Skriptunoff Apr 10 '13 at 20:41
@EgorSkriptunoff How is that relevant to the question/answer and why does it bother you so much? – peterm Apr 10 '13 at 21:28
I am not home yet but I will pull up what I have and try yours tonight. I have a couple of questions for now. Which attribute in employee are you counting? are the joins necessary here and can I do without them? – maxspiderx Apr 10 '13 at 22:21
@maxspiderx `COUNT` returns number of rows which in your case represent number of employees in a department after join and group. See more on `COUNT` [here](http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions032.htm). Your goal can be achieved without join using correlated subqueries . That means that two subqueries (for COUNT and AVG) will be executed for each department. See updated [sqlfiddle](http://sqlfiddle.com/#!4/cda74/10) for an example. Read about joins vs subqueries [here](http://stackoverflow.com/q/2577174/1920232) and [here](http://stackoverflow.com/q/141278/1920232) – peterm Apr 11 '13 at 07:46

score 0 · Answer 2 · answered Apr 11 '13 at 01:37

If you want to get the number of all employees and the number of male employees in one SQL,you can try this.

SELECT B.DNAME,
COUNT(*) AS TOTAL_EMPLOYEES,
COUNT(CASE WHEN A.SEX='M' THEN A.SEX END) AS MALE_EMPLOYEES,
AVG(A.SAL) AS ALL_AVG,
AVG(CASE WHEN A.SEX='M' THEN A.SAL END) AS MALE_AVG
FROM EMPLOYEE A JOIN DEPARTMENT B
ON A.DNO=B.DNUMBER
GROUP BY B.DNAME

score 0 · Answer 3 · edited Dec 09 '14 at 21:23

0

SELECT dname, COUNT(*) AS Num_of_Employees    
FROM   employee, department    
WHERE  dno = dnumber AND EXISTS (
       SELECT AVG(Salary)    
       FROM   employee    
       GROUP BY dno    
       HAVING AVG(Salary)>30000)    
GROUP BY dname;

edited Dec 09 '14 at 21:23

answered Dec 09 '14 at 21:06

Aktion World

1
1

Some explanations would be nice. – Silicomancer Dec 09 '14 at 21:26
As written, the outer query does not correlate to the inner query. The `EXISTS` will evaluate to true for _every_ department if _any_ department has an average employee salary over 30,000. – Allan Dec 09 '14 at 21:38

Oracle SQL querying for average across several tables

3 Answers3