1

ABC is an organization operating in a certain country. Below are the data for the database that manages the information on this organization's employees:

employee(employee_name, street, city)

works(employee_name, department_name, position, salary)

department(department_name, city)

manages(employee_name, manager_name).

I have 10 different select queries about this database, and I've solved 8 of them, but am stuck on solving 2.

The first query is 'Select the names of the employees that live in the same street and city as their managers'.

The second query is 'select the department with the highest average salary'.

Any help is appreciated, if any more details are necessary, please tell me.

Improve this question
2

First query:

select
    e.employee_name as employee
    ,e.street
    ,e.city
    ,mgr.employee_name as manager
    ,mgr.street
    ,mgr.city
from 
    employee e
    join manages mgs on mgs.employee_name = e.employee_name
    join employee mgr on mgr.employee_name = mgs.manager_name
where
    e.street = mgr.street
    and e.city = mgr.city

Second query:

select 
    d.department_name 
    ,avg(salary) as AvgSalary
from 
   department d
   join works w on w.department_name = d.department_name
group by
   d.department_name
order by
   AvgSalary desc
Improve this answer
2
  • Thank you very much, for what we've studied so far, the first query sounds too much for me, the second one I guess I could have solved it if I tried a bit harder. I just added a 'top 1' at the select given that I only wanted one department, but anyway thanks again! – Arben Jan 4 '15 at 18:39
  • You're welcome. For the first query, you had to the join the employee table to the manages table to get the manager, and then join back to the employee table to get the manager address – Ben Jan 4 '15 at 19:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.