Oracle SQL Nested Statement Issue

Question

I'm currently working with a database containing the following schema:

Schema

Here are the tasks I'm required to carry out:

tasks

For task 1, I formulated the following query (inclusion of avg(salary) was for testing purposes):

select dname, count(*), avg(salary)
from department, employee 
where dno = dnumber
group by dname having avg(salary)>30000;

giving the output:

DNAME                       COUNT(*) AVG(SALARY)
------------------------- ---------- -----------
Hardware                          10       63450
Research                           4       33250
Headquarters                       1       55000
Administration                     3       31000
Software                           8       60000
Sales                             14  40821.4286

However I can not figure out task two. **I'm required to have the same values, however only with the count of males but the same average as the previous query **. I tried the following statement:

select dname, count(*), avg(salary) 
from department, employee 
where dno = dnumber and (dno,sex) in (select dno, sex from employee where sex = 'M' ) 
group by dname having avg(salary)>30000;

Which resulted in the correct count value, but resulted in the departmental average salary for males, not males and females. As seen below:

DNAME                       COUNT(*) AVG(SALARY)
------------------------- ---------- -----------
Hardware                           7  65785.7143
Research                           3       36000
Headquarters                       1       55000
Software                           7  57285.7143
Sales                             10       42150

Please note I have to do this using nested queries, not CASE.

Hope this makes sense, any help would be appreciated.

Thanks.

radar · Accepted Answer · 2014-10-21 20:38:43Z

2

you can do male employee count using CASE

Also converted explicit join ( comma separated tables in FROM CLAUSE) to implicit join

select D.dname, count(case when E.sex='M' then 1 else 0), avg(E.salary) 
from department D
join employee E
on E.dno = D.dnumber  
group by D.dname 
having  avg(Esalary)>30000;

As per OP comment, if this needs to be done using only nested queries, it can be done with subquery

select T.dname, count(*), T.salary as AverageSalary
FROM employee E
join ( select D.dname, D.dnumber, avg(salary) as salary 
       from employee E 
       join department D 
       on E.dno = D.dnumber 
       group by  D.dname, D.dnumber 
       having  avg(salary) > 30000 ) T
on E.sex ='M'
and E.dno = T.dnumber
group by T.dname, T.salary

edited Oct 21, 2014 at 20:38

answered Oct 21, 2014 at 20:04

radar

13.4k2 gold badges27 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JWyatt Over a year ago

A friend did show me this way, but according to the lecturer we're required to do it using nested queries. I will clarify this in the post. I understand this is the best way to do it but it's not the way we've been told to do it. Thanks though

JWyatt Over a year ago

I'm receiving a missing key word error on line 9? AND E.sex = 'M' ?

StuartLC · Accepted Answer · 2014-10-21 20:13:18Z

1

This can also be done by evaluating the overall average in a subquery, derived table or cte per department before applying it to the filtered employees. Also note the preference for joining in join not in the where.

with cteSalary as
(
    select dname, dno, avg(salary) as avgSalary
    from department inner join employee on dno = dnumber
    group by dname, dno
)
select s.dname, count(e.fname), s.avgSalary
from employee e inner join cteSalary s on e.dno = s.dno
where e.sex = 'M' and s.avgSalary > 30000
group by s.dname, s.avgSalary;

answered Oct 21, 2014 at 20:13

StuartLC

108k18 gold badges225 silver badges294 bronze badges

Comments

Wernfried Domscheit · Accepted Answer · 2014-10-22 06:26:26Z

0

Another solution would be this one (not tested):

with t as
   (select dname, sex,
      count(*) over (partition by dno, sex) as count_emp, 
      avg(salary) over (partition by dno) as avg_salary
   from department
      join employee on dno = dnumber)
select distinct *
from t
where avg_salary > 30000
   and sex = 'M';

answered Oct 22, 2014 at 6:26

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Collectives™ on Stack Overflow

Oracle SQL Nested Statement Issue

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related