Lesson 1 of 0
In Progress

Case Study: Employee Database Analysis

Kedeisha June 16, 2023


The Data in Motion Company has given you the responsibility of studying personnel statistics. Information on employees, departments, and wages can be found in the employee database. Your goal is to gather insightful data that will help you raise employee happiness overall, discover performance trends, and optimize HR procedures. You will respond to specific business queries and offer the management team data-driven recommendations by utilizing a variety of filtering approaches.


  • Apply filtering techniques using the WHERE clause.
  • Filter data based on specific conditions and logical operators.
  • Use the IN and NOT IN operators to filter data based on a list of values.
  • Utilize the LIKE operator and wildcard patterns to perform flexible data filtering.
  • Handle NULL values and filter data accordingly.

Database Tables

  1. Employees table:

    • employee_id (primary key)
    • first_name
    • last_name
    • hire_date
    • department_id (foreign key)
  2. Departments table:

    • department_id (primary key)
    • department_name
  3. Salaries table:

    • salary_id (primary key)
    • employee_id (foreign key)
    • salary_amount
    • effective_date

Challenge Questions

  1. Retrieve all employees hired after January 1, 2020.
  2. Find the employees who have a salary greater than $60,000.
  3. etrieve all employees in the Sales department.
  4. Find employees whose first name starts with ‘J’.
  5. Retrieve employees who are not assigned to any department.
  6. Find the employees who were hired in the year 2022
  7. Retrieve employees with salaries in the range of $50,000 to $70,000.
  8. Find the employees who have ‘Manager’ in their job title.
  9. Retrieve employees who have not received a salary increase.
  10. Find employees who have a missing last name (NULL value).

DB Fiddle Instructions

Click ‘Edit on DB Fiddle’ to be taken to the in browser SQL playground where you can run your queries.