Lab 6

Lab schema (HR schema) is listed as below (primary keys are underlined):

Regions(region_id, region_name);
Countries(country_id, country_name, region_id);
Locations(location_id, street_address, postal_code, city, state_province, country_id);
Departments(department_id, department_name, manager_id, location_id);
Jobs(job_id, job_title, min_salary, max_salary);
Employees(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id);
Job_History(employee_id, start_date, end_date, job_id, department_id);

Learning Objectives:

Aggregation Queries

Write a single SQL query for each of the following questions.

For each country, count the number of departments located in that country. List the name of the country and the count as numOfDepartments. If no department is there, the count should be shown as 0.

select country_name, count(department_id) as numOfDepartments
from   Departments natural join Locations X right join Countries C
       on X.country_id = C.country_id
group by country_name;

select country_name, count(department_id) as numOfDepartments
from   Departments natural join Locations natural join Countries
group by country_name
union all
select country_name, 0 as numOfDepartments
from   Countries
where  country_id not in (select country_id
                          from   Departments natural join Locations);

List the name of each department that has more than 20 employees working in it.

select department_name
from   Departments D join Employees E on D.department_id = E.department_id
group by department_name
having count(employee_id) >= 20;

For each department whose human resource cost (sum of salary of each employee in it) is greater than or equal to $40,000, list the department's name, the total salary and the average salary of the department.
```
select department_name, sum(salary), avg(salary)
from   Departments D join Employees E on D.department_id = E.department_id
group by department_name
having sum(salary) >= 40000;
```

Find the department with the most people working in it. List the department's name.

Using aggregation function max

select department_name
from   (select department_name, count(employee_id) as empCount
        from   Departments D join Employees E
               on D.department_id = E.department_id
        group by department_name) X,
       (select max(empCount) as maxCount
        from   (select department_name, count(employee_id) as empCount
                from   Departments D join Employees E
                       on D.department_id = E.department_id
                group by department_name) ) Y
where empCount = maxCount;

Without using max, using operator against a set instead

select department_name
from   (select department_name, count(employee_id) empCount
        from   Departments D join Employees E
               on D.department_id = E.department_id
        group by department_name)
where   empCount >= all (select count(employee_id)
                         from   Departments D join Employees E
                                on D.department_id = E.department_id
                         group by department_name);

Without using max, using having clause and operator against a set

select department_name
from  Departments D join Employees E
      on D.department_id = E.department_id
group by department_id, department_name
having count(employee_id) >= all (select count(employee_id)
                                  from   Employees
                                  group by department_id);

List the name of each employee who has more than 5 employees directly report to him/her.

Using having clause

select E1.first_name, E1.last_name
from   Employees E1, Employees E2
where  E1.employee_id = E2.manager_id
group by E1.first_name, E1.last_name
having count(E2.employee_id) >= 5;

Using subquery in the from list

select first_name, last_name
from   (select E1.first_name, E1.last_name, count(E2.employee_id) subCount
        from   Employees E1, Employees E2
        where  E1.employee_id = E2.manager_id
        group by E1.first_name, E1.last_name)
where  subCount >= 5;