MySQL and phpMyAdmin lab with a sample shop database to practise SQL

This lab gives each student their own MySQL server and their own phpMyAdmin, already signed in, so the same tables can be changed with SQL in a terminal and looked at in a browser. It starts with a sample database to practise on, shop: a small online shop’s customers, products, orders and reviews, a few hundred rows in eight tables.

What students learn

SQL from a first SELECT through joins, grouping, a LEFT JOIN to find rows with no match, self-joins, subqueries, views and transactions with ROLLBACK, on data written to have something to find: customers who have never ordered, products never sold, and missing values. phpMyAdmin’s Designer draws how the tables connect.

Beyond queries, students see who they are in a database: the student account owns shop and any database whose name starts student_, so CREATE DATABASE student_notes works and CREATE DATABASE other is refused, and SHOW GRANTS says why. They back it up with mariadb-dump and restore it.

The shop database

Eight tables: categories, suppliers, products, customers, employees, orders, order_items and reviews, with keys and foreign keys throughout. The exercises in the repository’s README go from the products over fifty pounds, dearest first, to the takings per category, the customers who never ordered and the orders per month for a chart.

Run it in your browser

The database user is student with the password student.

  1. Open the Container page. Open Build on the bar and choose Run a container. Anybody may open it, and the Git lab and the SQL lab start with no account. Every other lab needs a free account: create one, sign in, then press Start.
  2. Find the lab. The Sample labs card lists every sample. Find MySQL with phpMyAdmin.
  3. Press Start. Start runs it as it is, in one press. Edit the file first opens the compose file so you can change it before you start.
  4. Open it. On the lab’s page, under In your browser, press phpMyAdmin: it opens signed in, and shop is on the left. In the terminal, tell the client once to accept the server’s own certificate, then mariadb -h db -u student -pstudent shop opens a session. The README gives the exact lines.

The code on GitHub

One compose file, with the sample data written out in it, checked on every push by a test that starts the lab the way the platform runs it. Fork it to change the data.

A basic lesson on this lab

SQL Basics and SQL Basics 2 are lessons worked in this lab, each step explained and with a Run button that types a SQL command into the student’s terminal. SQL Basics adds a new table to the shop database and inserts, selects, joins, updates and deletes rows. SQL Basics 2 asks questions of many rows at once: counting, grouping and finding the customers who have never ordered.

A free personal account is given them on the Basics module, which is on Home, every college can copy them from the Library’s Basics folder, and a new college opens with them in a module called Database (Sample). They are deliberately small: they show a teacher what a lesson worked in a lab is, and the lab does the rest. Copy one into a module of your own to change it.

Set it as work

Give a class the exercises in the README, or write your own against shop.

  1. Make the lab. In your module add a task and choose the Container lab card, then In a GitHub repository. Give https://github.com/mitsurutech/lab-mysql-phpmyadmin with the branch main. The compose file is the default, compose.yaml. Fork the repository first if you want to change it.
  2. Publish and try it. Publish pins the commit you were shown, so a later change in the repository does not alter a lab you have already set. Start your own copy first.
  3. Hand it out. Assign it like any task, with a due date if you want one, and say in the task what to do and what to hand in.
  4. Mark what is handed in. Open a student’s copy and the work they handed in, mark it, and send it back with a reason if it needs more.

Questions people ask

Can students create their own databases?
Yes, any whose name starts student_. The student account owns shop and those and nothing else; root, with the password root, is there to show the difference.
Does the data persist?
Yes. MySQL keeps its files in the named volume mysqldata, which the platform saves, so a table a student made is there next session. The lab is sized to stay inside the amount a lab may save.
What is in the sample shop database?
Eight tables for a small online shop, with keys and foreign keys throughout: categories, suppliers, products, customers, employees, orders, order_items and reviews, a few hundred rows in all. The repository’s README has thirteen SQL exercises over it, and the data is written so there is something to find, such as customers who never ordered.