Post

Mini Challenge 8 — SUBSTRING, CASE, Self Joins, Multiple Joins

Posted
By Rahul Chandna
6 min read
Mini Challenge 8 — SUBSTRING, CASE, Self Joins, Multiple Joins

title: “Mini Challenge 8 — Part 2 Topics 1-4” part: 2 topic_number: 0 slug: “mini-challenge-08” difficulty: “Intermediate” prerequisites: “substring, case-when, self-joins, multiple-joins” —

Mini Challenge 8 — SUBSTRING, CASE, Self Joins, Multiple Joins

Overview

Practice intermediate techniques with realistic, combined scenarios.

Challenge 1: Employee Code Generation

Generate employee codes in format: First 3 letters of last name (uppercase) + employee_id (padded to 4 digits). For example, employee “Alice Johnson” with ID 1 becomes “JOH0001”.

Show first_name, last_name, employee_id, and generated employee_code. Sort by employee_code.

Hint Use CONCAT, UPPER, SUBSTRING for name part, LPAD for zero-padding the ID.

Challenge 2: Salary Performance Tiers

Create a report with performance tiers based on salary AND tenure:

  • “Executive”: salary >= $90k AND hired before 2020
  • “Senior”: salary >= $75k AND hired before 2021
  • “Mid-Level”: salary >= $60k
  • “Entry”: all others

Show first_name, last_name, salary, hire_date, and performance_tier. Sort by tier, then salary DESC.

Hint Use CASE WHEN with AND conditions combining salary and hire_date.

Challenge 3: Manager-Report Salary Comparison

Create a report showing managers and their direct reports with salary comparison. Show:

  • manager_name (first + last)
  • report_name (first + last)
  • manager_salary
  • report_salary
  • salary_gap (manager_salary - report_salary)
  • gap_category: ‘Large Gap’ if > $15k, ‘Moderate Gap’ if $5k-$15k, ‘Small Gap’ if < $5k

Only include managers with at least one report. Sort by manager_name, then salary_gap DESC.

Hint Self join employees, CONCAT for names, CASE WHEN for gap categories.

Challenge 4: Complete Project Dashboard

Create a comprehensive project report with:

  • project_name
  • status
  • team_size (count of assigned employees)
  • departments_involved (count of unique departments)
  • total_hours (sum of hours_allocated)
  • avg_employee_salary (average salary of assigned employees)
  • project_health: ‘Excellent’ if team_size >= 3 AND status = ‘In Progress’, ‘Active’ if status = ‘In Progress’, ‘Completed’ if status = ‘Completed’, ‘At Risk’ otherwise

Only include projects with at least one employee. Sort by project_health, then total_hours DESC.

Hint Join projects -> employee_projects -> employees -> departments, GROUP BY project, use COUNT, SUM, AVG, CASE WHEN.

Challenge 5: Employee Initials Report with Manager Context

Create a detailed report:

  • employee_initials (e.g., “A.J.” for Alice Johnson)
  • full_name
  • department_name
  • manager_initials (or ‘No Manager’ if NULL)
  • manager_full_name (or ‘Top Level’ if NULL)
  • has_projects: ‘Yes’ if assigned to any project, ‘No’ otherwise

Sort by department_name, then has_projects DESC (Yes first), then last_name.

Hint Multiple joins (departments, self-join for managers, LEFT JOIN to employee_projects), SUBSTRING + UPPER + CONCAT for initials, CASE or EXISTS for has_projects.

Answer Key

Challenge 1 Answer ```sql SELECT first_name, last_name, employee_id, CONCAT( UPPER(SUBSTRING(last_name, 1, 3)), LPAD(employee_id, 4, '0') ) AS employee_code FROM employees ORDER BY employee_code; ``` **Expected output:** | first_name | last_name | employee_id | employee_code | |------------|-----------|-------------|---------------| | Jack | Anderson | 9 | AND0009 | | David | Brown | 4 | BRO0004 | | Sam | Clark | 7 | CLA0007 | | Eve | Davis | 5 | DAV0005 | | Paul | Garcia | 11 | GAR0011 | | ... | ... | ... | ... | Standardized employee codes. **Note:** LPAD(employee_id, 4, '0') pads with zeros to make 4-digit IDs.
Challenge 2 Answer ```sql SELECT first_name, last_name, salary, hire_date, CASE WHEN salary >= 90000 AND hire_date < '2020-01-01' THEN 'Executive' WHEN salary >= 75000 AND hire_date < '2021-01-01' THEN 'Senior' WHEN salary >= 60000 THEN 'Mid-Level' ELSE 'Entry' END AS performance_tier FROM employees ORDER BY CASE performance_tier WHEN 'Executive' THEN 1 WHEN 'Senior' THEN 2 WHEN 'Mid-Level' THEN 3 ELSE 4 END, salary DESC; ``` **Expected output:** | first_name | last_name | salary | hire_date | performance_tier | |------------|-----------|---------|-----------|------------------| | Frank | Miller | 93000.00 | 2018-09-01 | Executive | | Henry | Moore | 92000.00 | 2019-04-18 | Executive | | Alice | Johnson | 75000.00 | 2019-03-15 | Senior | | Ivy | Anderson | 74000.00 | 2018-02-20 | Senior | | Sam | Clark | 95000.00 | 2021-08-05 | Mid-Level | | ... | ... | ... | ... | ... | Employees categorized by performance tier.
Challenge 3 Answer ```sql SELECT CONCAT(m.first_name, ' ', m.last_name) AS manager_name, CONCAT(e.first_name, ' ', e.last_name) AS report_name, m.salary AS manager_salary, e.salary AS report_salary, m.salary - e.salary AS salary_gap, CASE WHEN m.salary - e.salary > 15000 THEN 'Large Gap' WHEN m.salary - e.salary >= 5000 THEN 'Moderate Gap' ELSE 'Small Gap' END AS gap_category FROM employees e JOIN employees m ON e.manager_id = m.employee_id ORDER BY manager_name, salary_gap DESC; ``` **Expected output:** | manager_name | report_name | manager_salary | report_salary | salary_gap | gap_category | |--------------|-------------|----------------|---------------|------------|--------------| | Alice Johnson | Carol Williams | 75000.00 | 78000.00 | -3000.00 | Small Gap | | Alice Johnson | George Jones | 75000.00 | 71000.00 | 4000.00 | Small Gap | | Bob Smith | Frank Miller | 82000.00 | 93000.00 | -11000.00 | Moderate Gap | | ... | ... | ... | ... | ... | ... | Manager-report salary dynamics. **Note:** Negative gaps mean reports earn more than their managers!
Challenge 4 Answer ```sql SELECT p.project_name, p.status, COUNT(DISTINCT ep.employee_id) AS team_size, COUNT(DISTINCT e.department_id) AS departments_involved, SUM(ep.hours_allocated) AS total_hours, ROUND(AVG(e.salary), 2) AS avg_employee_salary, CASE WHEN COUNT(DISTINCT ep.employee_id) >= 3 AND p.status = 'In Progress' THEN 'Excellent' WHEN p.status = 'In Progress' THEN 'Active' WHEN p.status = 'Completed' THEN 'Completed' ELSE 'At Risk' END AS project_health FROM projects p JOIN employee_projects ep ON p.project_id = ep.project_id JOIN employees e ON ep.employee_id = e.employee_id GROUP BY p.project_id, p.project_name, p.status ORDER BY CASE project_health WHEN 'Excellent' THEN 1 WHEN 'Active' THEN 2 WHEN 'Completed' THEN 3 ELSE 4 END, total_hours DESC; ``` **Expected output:** | project_name | status | team_size | departments_involved | total_hours | avg_employee_salary | project_health | |--------------|--------|-----------|----------------------|-------------|---------------------|----------------| | Website Redesign | In Progress | 4 | 3 | 500 | 82250.00 | Excellent | | Data Pipeline | In Progress | 3 | 2 | 390 | 77333.33 | Excellent | | API Development | In Progress | 2 | 1 | 200 | 79000.00 | Active | | CRM System | Completed | 2 | 2 | 240 | 73500.00 | Completed | | ... | ... | ... | ... | ... | ... | ... | Comprehensive project analytics.
Challenge 5 Answer ```sql SELECT CONCAT(UPPER(SUBSTRING(e.first_name, 1, 1)), '.', UPPER(SUBSTRING(e.last_name, 1, 1)), '.') AS employee_initials, CONCAT(e.first_name, ' ', e.last_name) AS full_name, d.department_name, COALESCE( CONCAT(UPPER(SUBSTRING(m.first_name, 1, 1)), '.', UPPER(SUBSTRING(m.last_name, 1, 1)), '.'), 'No Manager' ) AS manager_initials, COALESCE( CONCAT(m.first_name, ' ', m.last_name), 'Top Level' ) AS manager_full_name, CASE WHEN EXISTS (SELECT 1 FROM employee_projects ep WHERE ep.employee_id = e.employee_id) THEN 'Yes' ELSE 'No' END AS has_projects FROM employees e JOIN departments d ON e.department_id = d.department_id LEFT JOIN employees m ON e.manager_id = m.employee_id ORDER BY d.department_name, has_projects DESC, e.last_name; ``` **Expected output:** | employee_initials | full_name | department_name | manager_initials | manager_full_name | has_projects | |-------------------|-----------|-----------------|------------------|-------------------|--------------| | S.C. | Sam Clark | Engineering | No Manager | Top Level | Yes | | P.G. | Paul Garcia | Engineering | A.J. | Alice Johnson | Yes | | A.J. | Alice Johnson | Engineering | No Manager | Top Level | Yes | | F.M. | Frank Miller | Marketing | B.S. | Bob Smith | Yes | | ... | ... | ... | ... | ... | ... | Detailed employee overview with organizational context.

Key Takeaways

• SUBSTRING + CONCAT create formatted codes and labels • CASE WHEN handles complex multi-condition logic • Self joins reveal manager-report relationships • Multiple joins combine data across entire schema • EXISTS checks relationship presence efficiently • Combining techniques creates powerful analytics

Up Next

Next topic: COALESCE → part2_05_coalesce.md

Ready to learn advanced NULL handling?

Graduate Training, MySQL
Graduate Training
This post is licensed under CC BY 4.0 by the author.