Post

Mini Challenge 3 — SELECT, Filtering, and ORDER BY

Posted
By Rahul Chandna
4 min read
Mini Challenge 3 — SELECT, Filtering, and ORDER BY

title: “Mini Challenge 3 — Topics 7-9” part: 1 topic_number: 0 slug: “mini-challenge-03” difficulty: “Beginner” prerequisites: “select-from-where, filtering-data, order-by” —

Mini Challenge 3 — SELECT, Filtering, and ORDER BY

Overview

Now that you can query data, filter results, and sort them, let’s practice combining these skills in real scenarios.

Challenge 1: Find Active Engineers

Find all employees in the Engineering department, sorted by last name alphabetically. Show their first_name, last_name, and salary.

Hint Join employees and departments, filter by department_name, ORDER BY last_name.

Challenge 2: High Earners Report

Find all employees earning more than $80,000, sorted from highest to lowest salary. Show their full name (first and last combined), salary, and department_id.

Hint Use CONCAT for full name, WHERE salary > 80000, ORDER BY salary DESC.

Challenge 3: Recent Hires

Find employees hired in 2022 or later, sorted by hire date (newest first), then by last name alphabetically. Show all columns.

Hint Use WHERE hire_date >= '2022-01-01', ORDER BY with multiple columns.

Find all departments whose name contains “e” (case-insensitive), sorted by department_name. Show department_name and location.

Hint Use LIKE with % wildcards, ORDER BY department_name.

Challenge 5: Complex Filter

Find employees who:

  • Work in departments with ID 1, 3, or 5
  • AND earn between $60,000 and $90,000
  • AND their last name starts with a letter between A and M

Sort by salary (highest first), then by last_name.

Hint Use IN for department_id, BETWEEN for salary, LIKE or comparison for last_name. Multiple ORDER BY columns.

Answer Key

Challenge 1 Answer ```sql SELECT e.first_name, e.last_name, e.salary FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.department_name = 'Engineering' ORDER BY e.last_name; ``` **Expected output:** | first_name | last_name | salary | |------------|-----------|---------| | Sam | Clark | 95000.00 | | Paul | Garcia | 87000.00 | | Alice | Johnson | 75000.00 | All Engineering employees, sorted alphabetically by last name.
Challenge 2 Answer ```sql SELECT CONCAT(first_name, ' ', last_name) AS full_name, salary, department_id FROM employees WHERE salary > 80000 ORDER BY salary DESC; ``` **Expected output:** | full_name | salary | department_id | |-----------|---------|---------------| | Sam Clark | 95000.00 | 1 | | Frank Miller | 93000.00 | 2 | | Henry Moore | 92000.00 | 4 | | Paul Garcia | 87000.00 | 1 | | ... | ... | ... | High earners sorted from highest to lowest salary.
Challenge 3 Answer ```sql SELECT * FROM employees WHERE hire_date >= '2022-01-01' ORDER BY hire_date DESC, last_name; ``` **Expected output:** | employee_id | first_name | last_name | department_id | hire_date | salary | |-------------|------------|-----------|---------------|-----------|---------| | 20 | Grace | Taylor | 5 | 2023-09-15 | 62000.00 | | 18 | Mia | White | 3 | 2023-06-10 | 57000.00 | | 19 | Olivia | Martin | 10 | 2023-08-20 | 60000.00 | | ... | ... | ... | ... | ... | ... | Recent hires, newest first, then alphabetical.
Challenge 4 Answer ```sql SELECT department_name, location FROM departments WHERE department_name LIKE '%e%' ORDER BY department_name; ``` **Expected output:** | department_name | location | |-----------------|----------| | Business Development | Seattle | | Customer Support | Chicago | | Data Science | Austin | | Engineering | San Francisco | | Legal | New York | | Marketing | New York | All departments with "e" in their name, sorted alphabetically.
Challenge 5 Answer ```sql SELECT * FROM employees WHERE department_id IN (1, 3, 5) AND salary BETWEEN 60000 AND 90000 AND last_name < 'N' ORDER BY salary DESC, last_name; ``` **Expected output:** | employee_id | first_name | last_name | department_id | hire_date | salary | |-------------|------------|-----------|---------------|-----------|---------| | 2 | Bob | Smith | 3 | 2020-05-10 | 82000.00 | | 1 | Alice | Johnson | 1 | 2019-03-15 | 75000.00 | | 9 | Ivy | Anderson | 5 | 2018-02-20 | 74000.00 | | 20 | Grace | Taylor | 5 | 2023-09-15 | 62000.00 | Employees matching all criteria, sorted by salary (high to low), then last name. **Note:** `last_name < 'N'` means last names starting with A-M (since 'N' comes after 'M' alphabetically).

Key Takeaways

• Combine SELECT, WHERE, and ORDER BY for precise queries • Use JOINs to access related data across tables • CONCAT formats output for better readability • Multiple ORDER BY columns provide fine-grained sorting • LIKE with wildcards finds pattern matches • IN, BETWEEN, and comparison operators filter ranges and sets

Up Next

Next topic: LIMIT → part1_10_limit.md

Ready to learn result limiting?

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