bhylu.com

Advertisements

MySQL Quiz

Explain a result from SQL semantics rather than treating a short query as a guessing game.

Your brief

Answer before opening the worked query. How many part rows exist, how many have a known note and how many distinct categories exist? Does an empty string count as a known note? Why is note = NULL not a valid missing-value test? The fixture deliberately contains a NULL and an empty string so the answers require reasoning about the data rather than repeating one generic definition.

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 COUNT(*) AS rows_present,
       COUNT(note) AS known_notes,
       COUNT(DISTINCT category) AS categories
FROM parts;

Expected result

4 | 3 | 3

There are four rows, three non-NULL notes and three category values. The empty string is included in COUNT(note). DISTINCT applies to the selected category values, not to an imagined count of physical items. A missing note is tested with IS NULL. Use these distinctions when interpreting reports, because a correct query can still answer a different business question than the one a reader assumes.

Extend and test

Predict the result after filtering to quantity > 0. Then run a fresh lab and explain which counts changed and which meaning stayed the same.

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

Advertisements