bhylu.com

Advertisements

MySQL Study Plan

Use staged outcomes instead of rushing through function names or counting pages.

Your brief

Start by reproducing a SELECT result from the seed and explaining each row. Then write a precise filter, an empty-preserving join and a grouped report. Move to constraints before writes, and transactions before concurrent operations. Query plans and operational recovery come after the correctness contract is clear. The small query below gives a study checkpoint: explain why a part without any job belongs in the result and what would change after a valid new job.

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 p.name
FROM parts AS p
WHERE NOT EXISTS (SELECT 1 FROM jobs AS j WHERE j.part_id = p.id)
ORDER BY p.id;

Expected result

Cotton cord
Wood knob

A successful checkpoint includes a prediction, a reproduced result and a changed input whose effect you can explain. Use another empty schema for each write exercise. Keep notes on rejected cases, not just screenshots of success. The plan is guidance rather than saved course progress, a timed qualification or a promise about how quickly every learner becomes proficient.

Extend and test

Choose one unit and write a new question for the repair dataset. Include a missing or zero-value case and state the result grain before writing SQL.

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

Advertisements