Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Creating Basic SQL Reports

Introduction
Custom classifications of business objects
3. Custom classifications – exercise
Custom grouping of business objects
Custom counting of business objects
Summary

Instruction

Good job! It's time to write your first query using CASE WHEN.

Exercise

We want to create a report measuring the level of experience each Northwind employee has with the company. Show the first_name, last_name, hire_date, and experience columns for each employee. The experience column should display the following values:

  • 'junior' for employees hired after Jan. 1, 2014.
  • 'middle' for employees hired after Jan. 1, 2013 but before Jan. 1, 2014.
  • 'senior' for employees hired on or before Jan. 1, 2013.

Stuck? Here's a hint!

Use the following CASE WHEN template in the SELECT clause:

CASE
  WHEN ... THEN 'junior'
  WHEN ... THEN 'middle'
  WHEN ... THEN 'senior'
END AS experience

You can check if an employee was hired after Jan. 1, 2014 in the following way:

hire_date > '2014-01-01'