The repair kiosk's messy sheet
A phone repair kiosk in a shopping centre keeps its jobs in a spreadsheet. One person types prices as "£89". Another types "eighty-nine". Dates are written three different ways.
At the end of the month nobody can add up the takings. A database table would have refused those cells on the day they were typed.
By the end of this lesson
Note
I can move data from a spreadsheet into a database table.
Key words
| Word | What it means | Example |
|---|---|---|
| Database | an organised store of data that a program looks after | |
| Table | rows and columns about one kind of thing | |
| Column | one fact that every row has | price |
| Data type | the kind of value a column accepts | DATE |
| Primary key | a column that is different in every row | id |
| NULL | the mark for a value that is missing |
The cells students fill in are left blank in this preview.
Each column gets a data type
A spreadsheet row becomes a row in a table. Each spreadsheet heading becomes a column.
The big change is the data type. A spreadsheet column takes anything. A database column takes one kind of value only. So you decide the type of each column before any data goes in.
Worked example: the kiosk's columns
| Spreadsheet cell | Column | Data type | Stored as |
|---|---|---|---|
| Pixel 8 | phone | VARCHAR(40), text up to 40 letters | Pixel 8 |
| Cracked screen | fault | VARCHAR(60) | Cracked screen |
| £89 | price | DECIMAL(6,2), money with pence | 89.00 |
| 5/9/26 | booked_on | DATE, year then month then day | 2026-09-05 |
| (no heading) | id | INT, a whole number the database counts | 1 |
The cells students fill in are left blank in this preview.
Start the lab and sign in
Press Start the lab on the bar at the top and wait for the terminal. Each Run button types one command into it.
The first Run tells the database program to trust the lab's own server. The second makes a short name, sql, for signing in to the shop database.
Trust the lab's server
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make a short name for signing in
alias sql='mariadb -h db -u student -pstudent shop -t -e'Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
CREATE TABLE sets the columns
You may make your own database if its name starts with student_. Make student_kiosk, then a second short name, kiosk, that signs in to it.
CREATE TABLE names each column and its type. NOT NULL means the cell may never be empty. AUTO_INCREMENT PRIMARY KEY makes the database number each row for you.
Make your own database
sql "CREATE DATABASE IF NOT EXISTS student_kiosk"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A short name for your database
alias kiosk='mariadb -h db -u student -pstudent student_kiosk -t -e'Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Start clean if you ran this before
kiosk "DROP TABLE IF EXISTS repairs"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make the repairs table
kiosk "CREATE TABLE repairs (id INT AUTO_INCREMENT PRIMARY KEY, phone VARCHAR(40) NOT NULL, fault VARCHAR(60) NOT NULL, price DECIMAL(6,2) NOT NULL, booked_on DATE NOT NULL)"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Move three rows from the sheet
kiosk "INSERT INTO repairs (phone, fault, price, booked_on) VALUES ('Pixel 8', 'Cracked screen', 89.00, '2026-09-05'), ('iPhone 13', 'New battery', 49.50, '2026-09-05'), ('Galaxy A54', 'Charging port', 35.00, '2026-09-06')"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Look at the table
kiosk "SELECT * FROM repairs ORDER BY id"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
The table refuses a bad row
Now try the mistakes from the kiosk's sheet. Each Run below ends with an ERROR, and that is the point.
The first types a price in words. The second writes the date the way a person might. The third leaves the date out, so it would be NULL.
A price typed in words
kiosk "INSERT INTO repairs (phone, fault, price, booked_on) VALUES ('Moto G', 'Speaker', 'twenty', '2026-09-06')"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A date written the British way
kiosk "INSERT INTO repairs (phone, fault, price, booked_on) VALUES ('Moto G', 'Speaker', 20, '06/09/2026')"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A row with no date
kiosk "INSERT INTO repairs (phone, fault, price) VALUES ('Moto G', 'Speaker', 20)"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A common mistake
Warning
Many people think a date like 06/09/2026 means the same to everybody. In the UK it is 6 September. In the USA it is 9 June. A DATE column always wants year, month, day: 2026-09-06. Change the dates in a sheet to that order before you move them.
Going further: importing a whole file
Typing an INSERT for every row is slow. phpMyAdmin, the link on the lab's bar, has an Import tab that reads a CSV file into a table. It still checks every row against the column types.
Save a small sheet of your own as CSV, with dates written 2026-09-06. Import it into a new table in student_kiosk.