MySQL Coding Challenges - Practice Exercises

Retrieve All Customers from a Specific City

Write a query to select all customers who live in 'New York'.

Expected output: List of customers with city = 'New York'.

Hint: Use the WHERE clause to filter by city.

List Orders with Customer Names and Order Dates

Write a query to list order IDs, customer names, and order dates using a JOIN.

Expected output: Table with order_id, first_name, last_name, and order_date columns.

Hint: Use INNER JOIN between orders and customers tables.

Count Orders per Customer and Filter

Write a query to count orders per customer and show only those with more than 3 orders.

Expected output: Customer IDs with order counts greater than 3.

Hint: Use GROUP BY and HAVING clauses.

Find Customers with Total Order Amount Above Average

Write a query to find customers whose total order amount exceeds the average order amount.

Expected output: Customer IDs and total order amounts above average.

Hint: Use a subquery in the HAVING clause.

INNER JOIN returns only matching rows between tables, while LEFT JOIN returns all rows from the left table and matched rows from the right table, filling with NULLs if no match exists.

You use the HAVING clause to filter groups after aggregation, unlike WHERE which filters rows before grouping.

A subquery is a query nested inside another query. It is used to perform operations that depend on the result of another query, such as filtering based on aggregated values.