bhylu.com

Advertisements

MySQL Depot Report Exercise

Build a report that retains empty depots and distinguishes part types from physical pieces.

Your brief

Before reading the solution, list every depot with its number of part types and its total stock pieces. West must appear even though it has no parts. Explain why COUNT(*) would give an incorrect type count for the unmatched depot and why a missing sum becomes zero under this report’s contract. Then compare your result with the four seed records. The task combines outer joins, grouping and an explicit missing-result policy.

Run in a disposable MySQL lab

Use Oracle MySQL 8.4 with InnoDB, not Bhylu’s SQLite playground. The examples were reviewed on MySQL 8.4.8. XAMPP may provide MariaDB, which has different feature and collation behaviour. Check your actual server before running the script. These lessons do not require a change to Bhylu’s PHP, Apache or normal database service.

  1. On a separate lab instance, create the empty bhylu_mysql_lab schema. Stop if it already exists; do not overwrite an existing database.
  2. Select that schema and import the complete repair-parts seed once. It sets the documented session mode, utf8mb4 collation, UTC and English date-name locale.
  3. Run this lesson in a fresh copy of the seed, not after another lesson’s writes. Use the command-line client or a compatible editor. DELIMITER lines are client instructions for stored-program examples.

Download this complete lesson script. Its setup is the linked seed; review the dataset before changing it. Administrative and destructive examples are restricted to the specifically named disposable objects. Every script repeats the session settings because each new connection has its own session. No MySQL browser runner is currently available.

Worked solution

Show the worked solution
lesson.sql

SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
SET SESSION time_zone = '+00:00';
SET SESSION lc_time_names = 'en_US';
SELECT d.name, COUNT(p.id) AS kinds, COALESCE(SUM(p.quantity),0) AS pieces
FROM depots AS d LEFT JOIN parts AS p ON p.depot_id = d.id
GROUP BY d.id,d.name
ORDER BY d.id;

Expected result

North | 2 | 16
East | 2 | 6
West | 0 | 0

The matched part ID drives the type count. SUM sees the placeholder NULL for West, so COALESCE applies the report’s agreed zero. This is a depot-level result, not one row per part. If another child table is added, aggregate it at its own grain before joining or the totals may multiply. Review both the populated and empty fixture rather than checking only North.

Extend and test

Add a count of zero-stock kinds without removing empty depots. Explain how the conditional aggregate treats the NULL placeholder.

Use the official MySQL 8.4 reference for statement contracts and version details.

Advertisements