Introduction to Aggregate Functions and Joining Tables in SQL

In this article, we will explore how to use aggregate functions and join tables in SQL to solve a problem that requires finding department numbers having the same first and last hiring date as department 10 and counting the years.

The problem statement asks us to write an SQL query that finds departments which hired also the same year as department 10 did. Suppose department 10 hires in two different years, we want to find all departments that hire in those same years.

Common Table Expressions (CTEs)

To solve this problem, we can use a common table expression (CTE) to compute the first and last hire year for each department, and then join these CTEs with another CTE or a regular table to filter out the desired departments.

The first approach uses two CTEs: depts and d10. The depts CTE computes the first and last hire year for each department, as well as the total number of hires. The d10 CTE computes the same information for department 10.

Joining Tables

To find departments that hired in the same years as department 10, we join these two CTEs on their corresponding columns (first_hire_year, last_hire_year, and deptno). This allows us to filter out the desired departments based on whether they have the same hire year information as department 10.

Alternative Approach

Another approach is to generate a list of all years when department 10 did hire, combine it with the list of departments, and then eliminate those that did not match. This method involves using two cross-joins to combine the lists of departments and years, and then filtering out the unwanted rows based on whether they have matching information.

SQL Queries

Here are the two SQL queries:

Query 1: Using CTEs

with depts as (
    select 
        deptno, 
        year(min(hiredate)) first_hire_year, 
        year(max(hiredate)) last_hire_year,
        count(*) total_hires
    from emp
    group by deptno
),
d10 as (
    select 
        year(min(hiredate)) first_hire_year, 
        year(max(hiredate)) last_hire_year, 
        count(*) total_hires
    from emp
    where deptno = 10
)
select d.*
from depts d
inner join d10 d10
    on  d10.deptno = 10
    and d10.first_hire_year = d.first_hire_year
    and d10.last_hire_year = d.last_hire_year

Query 2: Alternative Approach

select 
    d.deptno, 
    min(y.hire_year) first_hire_year,
    max(y.hire_year) last_hire_year,
    count(d.deptno) count_hire_year
from 
    (select distinct year(hiredate) hire_year from emp where deptno = 10) y
    cross join (select distinct deptno from emp) d
    left join (select distinct deptno, year(hiredate) hire_year from emp) e
        on  e.hire_year = y.hire_year
        and e.deptno = d.deptno
group by d.deptno
having count(d.deptno) = count(e.deptno)

Results

Both queries produce the same result:

The first query uses CTEs to compute the first and last hire year for each department, and then joins these CTEs with another CTE or a regular table to filter out the desired departments.

The second query generates a list of all years when department 10 did hire, combines it with the list of departments, and then eliminates those that did not match.

Conclusion

In this article, we explored how to use aggregate functions and join tables in SQL to solve a problem that requires finding department numbers having the same first and last hiring date as department 10 and counting the years. We provided two approaches using CTEs and an alternative approach without using CTEs.

Last modified on 2023-11-22