CREATE Data Set 2: Employees Table
TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
Department VARCHAR(50),
Salary DECIMAL(10, 2),
HireDate DATE
);
Sample Data:
INSERT INTO Employees (EmployeeID, Name, Department,
Salary, HireDate)
VALUES
(1, 'John Doe', 'HR', 50000.00, '2020-02-15'),
(2, 'Jane Smith', 'Finance', 75000.00, '2019-03-25'),
(3, 'Michael Brown', 'IT', 60000.00, '2021-01-10'),
(4, 'Emily Davis', 'Marketing', 55000.00, '2018-07-18'),
(5, 'David Wilson', 'IT', 80000.00, '2023-04-30'),
(6, 'Sarah Johnson', 'Finance', 95000.00, '2022-06-01');
Exercises:
Exercise 11: Simple CASE Statement for Salary Bands
Q11. Write a query to categorize employees based on their
salary into bands: Low (below 60000), Medium (60000-
79999), and High (80000 and above).
SELECT *,
CASE
WHEN salary < 60000 THEN 'Low'
WHEN salary BETWEEN 60000 AND 79999 THEN
'Medium'
ELSE 'High'
END AS salary_band
FROM
employees;
Exercise 12: Searched CASE Statement for Department and
Salary
Q12. Write a query to categorize employees into Finance and
Non-Finance. Within these categories, further classify them
as High Salary (above 70000) or Average Salary (70000 and
below).
SELECT *,
CASE
WHEN department = 'Finance' AND salary > 70000 THEN
'Finance - High Salary'
WHEN department = 'Finance' AND salary <= 70000
THEN 'Finance - Average Salary'
WHEN department <> 'Finance' AND salary > 70000
THEN 'Non-Finance - High Salary'
ELSE 'Non-Finance - Average Salary'
END AS employee_category
FROM
employees;
Exercise 13: Using CASE in a WHERE Clause
Q13. Write a query to select employees who were hired after
2020 but only include employees from the IT department if
their salary is above 70000.
SELECT *,
FROM
employees
WHERE
hiredate > '2020-01-01'
AND (
CASE
WHEN department = 'IT' THEN
CASE
WHEN salary > 70000 THEN 1
ELSE 0
END
ELSE 1
END = 1
);
Exercise 14: CASE with Aggregate Functions
Q14. Write a query that calculates the count of employees in
each department, categorizing them as Junior (hired after
2020) and Senior (hired before or in 2020).
SELECT department,
COUNT(CASE WHEN hiredate > '2020-01-01' THEN 1 END)
AS Junior_Count, COUNT(CASE WHEN hiredate <= '2020-
01-01' THEN 1 END) AS Senior_Count
FROM employees
GROUP BY department;
Exercise 15: Nested CASE Statements
Q15. Write a query that categorizes employees based on
their department and salary: Top Earners in Finance if salary
is above 80000, Moderate Earners in Finance if between
60000 and 80000, and Others for all other cases.
SELECT *,
CASE
WHEN department = 'Finance' THEN CASE
WHEN salary > 80000 THEN 'Top Earners in Finance'
WHEN salary BETWEEN 60000 AND 80000 THEN 'Moderate
Earners in Finance' ELSE 'Others'
END ELSE 'Others'
END AS salary_categoryFROM employees;
Exercise 16: Handling NULL Values with CASE
Q16. Suppose some employees have no department assigned
(NULL). Write a query to return 'Unassigned' for those
employees and 'Assigned' for others.
SELECT *,
CASE
WHEN department IS NULL THEN 'Unassigned'
ELSE 'Assigned'
END AS department_status
FROM
employees;
Exercise 17: Using CASE in ORDER BY Clause
Q17. Write a query to sort employees by their department
with Finance employees first, followed by IT, and then others.
Within each department, sort by High Salary first.
Exercise 18: Complex CASE Statement with Multiple
Conditions
Q18. Write a query that categorizes employees into Highly
Paid Veterans (hired before 2020 and salary above 70000),
New Talents (hired in 2020 or later), and Others.
SELECT *,
FROM
employees
ORDER BY
CASE
WHEN department = 'Finance' THEN 1
WHEN department = 'IT'
Exercise 19: Using CASE with Date Functions
Q19. Write a query to categorize employees based on their
hire year: Recent Hire (hired in 2023), Established (hired
between 2020 and 2022), and Veteran (hired before 2020).
SELECT *,
CASE
WHEN YEAR(hiredate) = 2023 THEN 'Recent Hire'
WHEN YEAR(hiredate) BETWEEN 2020 AND 2022 THEN
'Established'
ELSE 'Veteran'
END AS hirecategory
FROM
employees;
Exercise 20: Combining CASE with String Functions
Q20. Write a query that appends '(Finance)' to the names of
employees in the Finance department, '(IT)' for those in IT,
and '(Other)' for all others.
SELECT *,
name,
CASE
WHEN department = 'Finance' THEN ' (Finance)'
WHEN department = 'IT' THEN ' (IT)'
ELSE ' (Other)'
END AS labeled_employee_name
FROM
employees;
