Post

Mini Challenge 5 — HAVING, Keys, and INNER JOIN

Posted
By Rahul Chandna
4 min read
Mini Challenge 5 — HAVING, Keys, and INNER JOIN

title: “Mini Challenge 5 — Topics 13-15” part: 1 topic_number: 0 slug: “mini-challenge-05” difficulty: “Beginner” prerequisites: “having-vs-where, primary-foreign-keys, inner-join” —

Mini Challenge 5 — HAVING, Keys, and INNER JOIN

Overview

Combine filtering grouped data, understanding relationships, and joining tables.

Find projects that have 3 or more employees assigned. Show project_id, project_name, and employee_count. Sort by employee_count (highest first).

Hint Join projects and employee_projects, GROUP BY project, use HAVING COUNT >= 3.

Challenge 2: High-Earning Departments

Find departments where the average employee salary is greater than $70,000. Show department_name and avg_salary (rounded to 2 decimals). Sort by avg_salary descending.

Hint Join employees and departments, GROUP BY department, HAVING AVG(salary) > 70000.

Challenge 3: Employee Project Details

Show all employee-project assignments with full details:

  • employee first_name and last_name
  • project_name
  • role (from employee_projects)
  • hours_allocated (from employee_projects)

Sort by last_name, then project_name.

Hint Three-way INNER JOIN: employees, employee_projects, projects.

Challenge 4: Departments with Few Employees

Find departments that have fewer than 2 employees. Show department_name and employee_count. Include departments with 0 employees (hint: you’ll need a LEFT JOIN for this, but if you haven’t learned it yet, just show departments with 1 employee).

Hint Join employees and departments, GROUP BY department, HAVING COUNT < 2.

Challenge 5: Project Budget Report

For each project with more than 2 assigned employees, calculate:

  • project_name
  • employee_count
  • total_hours (SUM of hours_allocated)
  • avg_hours_per_employee (total_hours / employee_count, rounded to 2 decimals)

Sort by total_hours (highest first).

Hint Join employee_projects and projects, GROUP BY project, HAVING COUNT > 2, calculate SUM and AVG.

Answer Key

Challenge 1 Answer ```sql SELECT p.project_id, p.project_name, COUNT(ep.employee_id) AS employee_count FROM projects p INNER JOIN employee_projects ep ON p.project_id = ep.project_id GROUP BY p.project_id, p.project_name HAVING COUNT(ep.employee_id) >= 3 ORDER BY employee_count DESC; ``` **Expected output:** | project_id | project_name | employee_count | |------------|--------------|----------------| | 1 | Website Redesign | 4 | | 3 | Mobile App | 3 | | 5 | Data Pipeline | 3 | Projects with 3+ employees.
Challenge 2 Answer ```sql SELECT d.department_name, ROUND(AVG(e.salary), 2) AS avg_salary FROM employees e INNER JOIN departments d ON e.department_id = d.department_id GROUP BY d.department_id, d.department_name HAVING AVG(e.salary) > 70000 ORDER BY avg_salary DESC; ``` **Expected output:** | department_name | avg_salary | |-----------------|------------| | Marketing | 87500.00 | | Engineering | 85666.67 | | Finance | 79000.00 | Departments with high average salaries.
Challenge 3 Answer ```sql SELECT e.first_name, e.last_name, p.project_name, ep.role, ep.hours_allocated FROM employees e INNER JOIN employee_projects ep ON e.employee_id = ep.employee_id INNER JOIN projects p ON ep.project_id = p.project_id ORDER BY e.last_name, p.project_name; ``` **Expected output:** | first_name | last_name | project_name | role | hours_allocated | |------------|-----------|--------------|------|-----------------| | Jack | Anderson | CRM System | Developer | 120 | | Jack | Anderson | Mobile App | Lead Developer | 160 | | David | Brown | API Development | Developer | 100 | | ... | ... | ... | ... | ... | All employee-project assignments with full details.
Challenge 4 Answer ```sql SELECT d.department_name, COUNT(e.employee_id) AS employee_count FROM departments d INNER JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_id, d.department_name HAVING COUNT(e.employee_id) < 2 ORDER BY employee_count, department_name; ``` **Expected output:** | department_name | employee_count | |-----------------|----------------| | Customer Support | 1 | | Data Science | 1 | | Legal | 1 | Departments with fewer than 2 employees. **Note:** This won't show departments with 0 employees (using INNER JOIN). For that, you'd need a LEFT JOIN (coming in the next topics!).
Challenge 5 Answer ```sql SELECT p.project_name, COUNT(ep.employee_id) AS employee_count, SUM(ep.hours_allocated) AS total_hours, ROUND(SUM(ep.hours_allocated) / COUNT(ep.employee_id), 2) AS avg_hours_per_employee FROM projects p INNER JOIN employee_projects ep ON p.project_id = ep.project_id GROUP BY p.project_id, p.project_name HAVING COUNT(ep.employee_id) > 2 ORDER BY total_hours DESC; ``` **Expected output:** | project_name | employee_count | total_hours | avg_hours_per_employee | |--------------|----------------|-------------|------------------------| | Website Redesign | 4 | 500 | 125.00 | | Data Pipeline | 3 | 390 | 130.00 | | Mobile App | 3 | 370 | 123.33 | Projects with resource allocation statistics.

Key Takeaways

• HAVING filters grouped results (after GROUP BY) • WHERE filters individual rows (before GROUP BY) • INNER JOIN returns only matching rows from both tables • Foreign keys ensure referential integrity • Multi-table JOINs enable complex queries across relationships • Combine aggregates with HAVING for powerful analytics

Up Next

Next topic: LEFT JOIN → part1_16_left_join.md

Continue learning about different types of joins!

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