LambdaLabTM
Databases & SQL · Class 12 · Query Practice
MySQLfull sets⏱️ 15 min read

Full Board-Style Sets

The real thing: a table you have not worked with, followed by parts (a) to (f) worth one or two marks each. Give yourself the paper’s time — about fifteen minutes for a set — and write all six answers before opening any of them.

1The table

Study it the way you would in the hall: the column names, the types you can infer, and above all the blanks.

MySQL command line client
mysql> select * from item;
+-------+--------------+------------+--------+------+-----------+------------+
| icode | iname | category | price | qty | supplier | pdate |
+-------+--------------+------------+--------+------+-----------+------------+
| I101 | Gel Pen | Stationery | 15.00 | 250 | Nataraj | 2024-01-15 |
| I102 | Notebook | Stationery | 45.50 | 180 | Classmate | 2024-02-20 |
| I103 | Geometry Box | Stationery | 120.00 | 60 | Camlin | 2023-11-08 |
| I104 | Water Bottle | Utility | 250.00 | 40 | Milton | 2024-03-01 |
| I105 | School Bag | Utility | 750.00 | 25 | Skybags | 2023-09-12 |
| I106 | Pencil Box | Stationery | 85.00 | NULL | Camlin | 2024-01-30 |
| I107 | Lunch Box | Utility | 320.00 | 35 | Milton | 2024-02-14 |
| I108 | Eraser | Stationery | 5.00 | 500 | Nataraj | NULL |
+-------+--------------+------------+--------+------+-----------+------------+
8 rows in set (0.00 sec)
Two blanks, planted on purpose
Pencil Box has no quantity and Eraser has no purchase date. In a real paper those blanks are never accidental — at least one part of the question will turn on them.

2Set A — write the query

A(a)
Display the item name and price of all items costing more than 100, most expensive first.
1 mark
▶Show the answer
MySQL command line client
mysql> select iname, price from item where price > 100 order by price desc;
+--------------+--------+
| iname | price |
+--------------+--------+
| School Bag | 750.00 |
| Lunch Box | 320.00 |
| Water Bottle | 250.00 |
| Geometry Box | 120.00 |
+--------------+--------+
4 rows in set (0.00 sec)
A(b)
Display the names of items whose quantity has not been recorded.
1 mark
▶Show the answer
MySQL command line client
mysql> select iname from item where qty is null;
+------------+
| iname |
+------------+
| Pencil Box |
+------------+
1 row in set (0.00 sec)

“Not recorded” is the phrase that means is null. Writing qty = null or qty = 0 both give the wrong answer — the first silently, the second because no item has a quantity of zero.

A(c)
Display the list of suppliers, with no supplier named twice.
1 mark
▶Show the answer
MySQL command line client
mysql> select distinct supplier from item;
+-----------+
| supplier |
+-----------+
| Nataraj |
| Classmate |
| Camlin |
| Milton |
| Skybags |
+-----------+
5 rows in set (0.00 sec)

Eight items, five suppliers — Nataraj, Camlin and Milton each supply two.

A(d)
Display the name and price of every item whose name contains the word Box.
2 marks
▶Show the answer
MySQL command line client
mysql> select iname, price from item where iname like '%Box%';
+--------------+--------+
| iname | price |
+--------------+--------+
| Geometry Box | 120.00 |
| Pencil Box | 85.00 |
| Lunch Box | 320.00 |
+--------------+--------+
3 rows in set (0.00 sec)

% on both sides, because Box is in the middle of one name and at the end of the others.

A(e)
Display each category with the number of items in it and the total quantity in stock.
2 marks
▶Show the answer
MySQL command line client
mysql> select category, count(*), sum(qty) from item group by category;
+------------+----------+----------+
| category | count(*) | sum(qty) |
+------------+----------+----------+
| Stationery | 5 | 990 |
| Utility | 3 | 100 |
+------------+----------+----------+
2 rows in set (0.00 sec)

Here is where the planted blank pays off. Stationery shows count(*) of 5 but its quantities add to 990 from only four items — Pencil Box’s NULL was skipped by sum while still being counted as a row by count(*).

A(f)
Display each category whose average price is above 50, with that average rounded to two decimals.
2 marks
▶Show the answer
MySQL command line client
mysql> select category, round(avg(price), 2) from item group by category having avg(price) > 50;
+------------+----------------------+
| category | round(avg(price), 2) |
+------------+----------------------+
| Stationery | 54.10 |
| Utility | 440.00 |
+------------+----------------------+
2 rows in set (0.00 sec)

Both categories qualify. Stationery only just does — four cheap items and one at 120 average to 54.10, just over the line.

3Set B — output and theory

The second half of a paper’s SQL question usually switches from writing queries to reading them.

B(a)
State the degree and cardinality of the item table.
1 mark
▶Show the answer

Degree 7 and cardinality 8 — seven columns, eight rows. Degree is the number of columns however many rows come and go.

B(b)
Which column would you choose as the primary key of item, and why?
1 mark
▶Show the answer

icode. It is different for every row and it is never empty, which is exactly what a primary key must guarantee. iname happens to be unique in these eight rows, but nothing stops two items sharing a name later, and supplier already repeats.

B(c)
What is the output of select count(*), count(qty), count(pdate) from item;
2 marks
▶Show the answer

8, 7 and 7. count(*) counts rows and gets all eight. count(qty) skips Pencil Box’s NULL, and count(pdate) skips Eraser’s — seven each, but for different rows.

B(d)
A student writes select category, count(*) from item where count(*) > 3 group by category; and it fails. Correct it.
2 marks
▶Show the answer

A condition on an aggregate cannot go in where, which is applied before the grouping exists. It belongs in having:

MySQL command line client
select category, count(*) from item group by category having count(*) > 3;

4Marking yourself honestly

Full marks

The query would run and answer the question asked. Different but equivalent wording is fine.

Half marks

Right approach, one slip — a missing quote, > for >=, where instead of having.

No marks

Right words in the wrong order, or a query that answers a different question.

For output questions

The row count and the headings are part of the answer, not decoration.

If you take one habit from this track
Before writing any query, say the answer’s shape out loud: how many columns, roughly how many rows, and whether a NULL could change either. Students who do that catch their own mistakes; students who start typing at select do not.
Quick Check

A category has 5 items but one has NULL qty. What do count(*) and sum(qty) report for it?

Quick Check

Why is icode a better primary key for item than iname?

← Previous
🛠️ CREATE, ALTER, INSERT, UPDATE & DELETE