SQL Joins SQL Topic exerciseMediumVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

Employee Details — Department + Job (Multi-INNER JOIN)

Chain the employee-to-department and employee-to-job relationships and return the requested profile.

  • Joins
  • Sorting

Exercise brief

Understand the request

HR reporting lead A compensation roster needs employee, department, and job attributes at employee grain.

For every employee, show department, job title, and salary by chaining two INNER JOINs (employees → departments, employees → jobs). Return employee_id, first_name, last_name, department_name, job_title, salary — ordered by salary DESC, employee_id.

Return

  • Return employee_id, first_name, last_name, department_name, job_title, and salary.
  • Order by salary descending and employee_id.

Constraints

  • Use two explicit INNER JOIN clauses.
  • Keep one row per employee.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

employees

  • employee_idINTEGER
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)
  • job_idVARCHAR(20)
  • salaryINTEGER
  • department_idINTEGER

departments

  • department_idINTEGER
  • department_nameVARCHAR(50)

jobs

  • job_idVARCHAR(20)
  • job_titleVARCHAR(100)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Each INNER JOIN keeps only matched rows — and the chain is cumulative (a row must match BOTH joins to survive).

Hint 2

You can chain as many JOINs as you need. Each gets its own ON clause.

Hint 3

Pattern: start FROM the most central table (employees), then JOIN each lookup table (departments, jobs).

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT e.employee_id, e.first_name, e.last_name, d.department_name, j.job_title, e.salary FROM employees e INNER JOIN departments d ON e.department_id = d.department_id INNER JOIN jobs j ON e.job_id = j.job_id ORDER BY e.salary DESC, e.employee_id;

Why this works

Multi-table joins are the bread and butter of reporting. The 'star' shape (one fact table joined to multiple dimension tables) is so common that most BI tools optimize for it specifically.

Success check

Every employee appears exactly once with the correct department and job.

Expected result

Use this output to verify values, aliases, ordering, and row count.

employee_idfirst_namelast_namedepartment_namejob_titlesalary
100JohnSmithITIT Manager120000
105EmmaTaylorMarketingMarketing Manager95000
107GraceWhiteITSoftware Developer90000
101AliceJohnsonITSoftware Developer85000
102BobWilsonITSoftware Developer80000
104DavidBrownFinanceFinancial Analyst70000
109IvyMartinezFinanceFinancial Analyst68000
106FrankGreenMarketingSales Representative65000
103CarolDavisHRHR Representative60000
108HenryClarkHRHR Representative55000

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.