Objective

Write an SQL query to retrieve a list of employees who earn more than $50,000. For each qualifying employee, display their ID, first name, last name, job title, hire date, and the name of their department. If an employee is not assigned to any department, the department name should be shown as NULL. The resulting list should be ordered by the hire date in descending order, showing the most recently hired employees first.

Additional information

• Tables:

  The employees table:

  Column	Type	Description
  employee_id	Integer	Unique identifier for each employee
  first_name	String	Employee's first name
  last_name	String	Employee's last name
  department_id	Integer	Identifier for the department the employee belongs to. This can be NULL if the employee is not assigned to any department
  job_title	String	Title of the employee's job
  salary	Integer	Employee's salary
  hire_date	Date	Date the employee was hired

  The departments table:

  Column	Type	Description
  department_id	Integer	Unique identifier for each department
  department_name	String	Name of the department
  location	String	Location of the department

• Requirements:

  • Use a LEFT JOIN to combine employees and departments based on department_id.
  • Filter the employees to include only those with a salary greater than 50000.
  • The department_name should appear as NULL for employees without an associated department.
  • Order the results by hire_date in descending order.

SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name,
    e.job_title,
    e.hire_date
FROM
    employees e
    LEFT JOIN departments d ON e.department_id = d.department_id
WHERE
    e.salary > 50000
ORDER BY
    e.hire_date DESC;

Explanation

The goal is to fetch employees earning more than $50,000, displaying their ID, first name, last name, department name, job title, and hire date. Employees not assigned to any department should still appear with NULL as their department name. The list is ordered so the most recently hired employees appear first.

Step 1: SELECT clause

Choose which columns to display in the output.

SELECT e.employee_id, e.first_name, e.last_name, d.department_name, e.job_title, e.hire_date

• e.employee_id, e.first_name, e.last_name, e.job_title, e.hire_date - employee fields from the employees table.
• d.department_name - department name from the departments table; will be NULL if the employee has no assigned department.

Step 2: FROM clause and LEFT JOIN

Combine employee records with their department information.

FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id

• LEFT JOIN - ensures all employees are included even if their department_id has no match in the departments table, resulting in NULL for department_name.
• ON e.department_id = d.department_id - the join condition linking each employee to their department.

Step 3: WHERE clause

Filter to include only employees with a salary above $50,000.

WHERE e.salary > 50000

Excludes any employee whose salary is $50,000 or less from the result set.

Step 4: ORDER BY clause

Sort results so the most recently hired employees appear first.

ORDER BY e.hire_date DESC;

DESC reverses the default ascending sort, placing the latest hire dates at the top of the result.