LambdaLabTM
Databases & SQL · Class 12 · Query Practice
MySQLoutput⏱️ 12 min read

Predict the Output

The other direction. You are handed a finished query and asked what the server will print — and the queries chosen are never the obvious ones. Almost every question in this shape is really a question about NULL.

Cover the answer, write it down
Predicting is a different skill from writing, and it is the one students practise least. Write your prediction out fully — the box, the column headings, the row count — before you open the answer. Being “roughly right” is how marks disappear.

1Arithmetic that touches NULL

1
Write the output of select name, salary, bonus, salary + bonus from emp where deptno = 20;
2 marks
▶Show the answer
MySQL command line client
mysql> select name, salary, bonus, salary + bonus from emp where deptno = 20;
+--------------+----------+---------+----------------+
| name | salary | bonus | salary + bonus |
+--------------+----------+---------+----------------+
| Bina Sharma | 26000.00 | NULL | NULL |
| Emil Lepcha | 47000.00 | NULL | NULL |
| Hema Subba | 49500.00 | 3100.00 | 52600.00 |
| Karan Bhutia | 61000.00 | 5000.00 | 66000.00 |
+--------------+----------+---------+----------------+
4 rows in set (0.00 sec)

26000 + NULL is NULL, not 26000. NULL means “not known”, and a known number plus an unknown one is unknown. The row is still printed — only the computed column is empty.

2What the aggregates do with the same column

2
Write the output of select count(*), count(bonus), sum(bonus), avg(bonus) from emp;
3 marks
▶Show the answer
MySQL command line client
mysql> select count(*), count(bonus), sum(bonus), avg(bonus) from emp;
+----------+--------------+------------+-------------+
| count(*) | count(bonus) | sum(bonus) | avg(bonus) |
+----------+--------------+------------+-------------+
| 12 | 9 | 25200.00 | 2800.000000 |
+----------+--------------+------------+-------------+
1 row in set (0.00 sec)

Four numbers, three different behaviours. count(*) counts rows and gets twelve; count(bonus) counts values and gets nine; and avg divides by nine, not twelve — 25200 ÷ 9 = 2800. Writing 2100 here is the classic wrong answer.

3Does DISTINCT keep the NULL?

3
Write the output of select distinct city from emp; — and state how many rows it has.
2 marks
▶Show the answer
MySQL command line client
mysql> select distinct city from emp;
+----------+
| city |
+----------+
| Gangtok |
| Siliguri |
| Singtam |
| NULL |
| Namchi |
| Kolkata |
+----------+
6 rows in set (0.00 sec)

Six rows, and NULL is one of them. distinct treats “no city recorded” as a distinct thing worth listing once, even though two employees have it. Answering “five” is the trap.

4One NULL poisons the whole string

4
Write the output of select concat('Lambda', null);
1 mark
▶Show the answer
MySQL command line client
mysql> select concat('Lambda', null);
+------------------------+
| concat('Lambda', null) |
+------------------------+
| NULL |
+------------------------+
1 row in set (0.00 sec)

Not Lambda, and not an empty string — NULL. The same rule as the arithmetic in question 1: anything combined with an unknown is unknown.

5Negative numbers and half-way rounding

5
Write the output of select mod(-17, 5), round(2.5), round(3.5), round(-2.5);
2 marks
▶Show the answer
MySQL command line client
mysql> select mod(-17, 5), round(2.5), round(3.5), round(-2.5);
+-------------+------------+------------+-------------+
| mod(-17, 5) | round(2.5) | round(3.5) | round(-2.5) |
+-------------+------------+------------+-------------+
| -2 | 3 | 4 | -3 |
+-------------+------------+------------+-------------+
1 row in set (0.00 sec)

mod takes the sign of the left operand, so mod(-17, 5) is -2 and not 3. And MySQL rounds a half away from zero: 2.5 to 3, -2.5 to -3. This is not the “round half to even” rule Python uses, so do not carry that habit across.

6Counting rows you never printed

6
emp has 12 rows and dept has 4. Write the output of select count(*) from emp, dept;
1 mark
▶Show the answer
MySQL command line client
mysql> select count(*) from emp, dept;
+----------+
| count(*) |
+----------+
| 48 |
+----------+
1 row in set (0.01 sec)

No join condition means a cartesian product: 12 × 4 = 48. The examiner is checking that you noticed the missing where.

7A checklist for these questions

Is there a NULL in the column?

If yes, that is almost certainly the whole question.

Is there a join condition?

Two tables and no where means a cartesian product.

Count the rows, not just the values

Marks are given for the row count as well as the contents.

Copy the headings exactly

The heading of a computed column is the expression itself, unless an alias renames it.

Quick Check

A row has salary 26000 and bonus NULL. What does salary + bonus give?

Quick Check

A city column has 10 values across 6 distinct names, plus 2 NULLs. How many rows does SELECT DISTINCT city return?

Quick Check

What is mod(-17, 5) in MySQL?

← Previous
🧠 Joining emp and dept
Next →
Find the Error 🐛