Databases & SQL · Class 12 · Query Practice

MySQLpractice⏱️ 12 min read

Conditions, Patterns & NULLs

Four operators appear in the SQL section of almost every paper: between, in, like and is null. They are worth practising together because the examiner picks between them deliberately — the wording of the question tells you which one is wanted.

1BETWEEN — a range, both ends included

Display the name and salary of employees whose salary is between 25000 and 50000.

2 marks

▶Show the answer

MySQL command line client

mysql> select name, salary from emp where salary between 25000 and 50000;

+---------------+----------+

| name | salary |

+---------------+----------+

| Bina Sharma | 26000.00 |

| Chandan Rai | 31000.00 |

| Emil Lepcha | 47000.00 |

| Farida Khan | 29500.00 |

| Hema Subba | 49500.00 |

| Ishan Pradhan | 25500.00 |

| Jyoti Tamang | 33000.00 |

| Lata Gurung | 27500.00 |

+---------------+----------+

8 rows in set (0.00 sec)

Identical to salary >= 25000 and salary <= 50000. Either earns the mark.

Both ends are included

between is inclusive at both ends, which is the single most common slip. Counting the employees between 26000 and 58000 gives nine, and that count contains both Bina Sharma on exactly 26000 and Arun Thapa on exactly 58000:

MySQL command line client

mysql> select count(*) from emp where salary between 26000 and 58000;

+----------+

| count(*) |

+----------+

| 9 |

+----------+

1 row in set (0.00 sec)

2IN — one of a list

Display the name and job of all clerks and analysts.

2 marks

▶Show the answer

MySQL command line client

mysql> select name, job from emp where job in ('Clerk','Analyst');

+---------------+---------+

| name | job |

+---------------+---------+

| Bina Sharma | Clerk |

| Emil Lepcha | Analyst |

| Gopal Das | Clerk |

| Hema Subba | Analyst |

| Ishan Pradhan | Clerk |

| Lata Gurung | Clerk |

+---------------+---------+

6 rows in set (0.00 sec)

The long form — where job = 'Clerk' or job = 'Analyst' — is equally correct and equally accepted.

Display the name and job of everyone who is neither a clerk nor an analyst.

2 marks

▶Show the answer

MySQL command line client

mysql> select name, job from emp where job not in ('Clerk','Analyst');

+--------------+----------+

| name | job |

+--------------+----------+

| Arun Thapa | Manager |

| Chandan Rai | Salesman |

| Deepa Nair | Manager |

| Farida Khan | Salesman |

| Jyoti Tamang | Salesman |

| Karan Bhutia | Manager |

+--------------+----------+

6 rows in set (0.00 sec)

Six and six, adding to all twelve employees — which works here only because no employee has a NULL job. Where a column does hold NULLs, a condition and its negation do not add up to the whole table. That trap has its own question on the Predict the Output page.

3LIKE — matching a pattern

Two wildcards, and papers test both: % stands for any number of characters (including none), and _ stands for exactly one.

Display the names of employees whose name begins with A.

1 mark

▶Show the answer

MySQL command line client

mysql> select name from emp where name like 'A%';

+------------+

| name |

+------------+

| Arun Thapa |

+------------+

1 row in set (0.00 sec)

Display the names of employees whose name ends with a.

1 mark

▶Show the answer

MySQL command line client

mysql> select name from emp where name like '%a';

+--------------+

| name |

+--------------+

| Arun Thapa |

| Bina Sharma |

| Emil Lepcha |

| Hema Subba |

| Karan Bhutia |

+--------------+

5 rows in set (0.00 sec)

Display the names containing ra anywhere.

1 mark

▶Show the answer

MySQL command line client

mysql> select name from emp where name like '%ra%';

+---------------+

| name |

+---------------+

| Chandan Rai |

| Ishan Pradhan |

| Karan Bhutia |

+---------------+

3 rows in set (0.00 sec)

Chandan Rai matches on the space-then-Ra of the surname: the default collation here is case-insensitive, so ra also matches Ra.

Display the names whose second letter is i and which start with any single character followed by ina.

2 marks

▶Show the answer

MySQL command line client

mysql> select name from emp where name like '_ina%';

+-------------+

| name |

+-------------+

| Bina Sharma |

+-------------+

1 row in set (0.00 sec)

One underscore, one character. _ina% means: anything for the first letter, then i, n, a, then anything at all.

4LIKE works on dates too

A date is stored as YYYY-MM-DD, so a pattern can pick out a year, a month or a day without any date function at all.

Display the name and joining date of employees who joined in 2019.

2 marks

▶Show the answer

MySQL command line client

mysql> select name, doj from emp where doj like '2019%';

+-------------+------------+

| name | doj |

+-------------+------------+

| Bina Sharma | 2019-07-01 |

+-------------+------------+

1 row in set (0.00 sec)

where year(doj) = 2019 gives the same answer and is the clearer way to say it — but year() is a date function, which is on the Informatics Practices syllabus and not the Computer Science one. The like form works for everybody.

5IS NULL — the one that will not use =

Display the names of employees who have not been given a bonus.

1 mark

▶Show the answer

MySQL command line client

mysql> select name from emp where bonus is null;

+---------------+

| name |

+---------------+

| Bina Sharma |

| Emil Lepcha |

| Ishan Pradhan |

+---------------+

3 rows in set (0.01 sec)

Display the name and bonus of employees who did receive one.

1 mark

▶Show the answer

MySQL command line client

mysql> select name, bonus from emp where bonus is not null;

+--------------+---------+

| name | bonus |

+--------------+---------+

| Arun Thapa | 4500.00 |

| Chandan Rai | 2200.00 |

| Deepa Nair | 4000.00 |

| Farida Khan | 1800.00 |

| Gopal Das | 900.00 |

| Hema Subba | 3100.00 |

| Jyoti Tamang | 2500.00 |

| Karan Bhutia | 5000.00 |

| Lata Gurung | 1200.00 |

+--------------+---------+

9 rows in set (0.00 sec)

= NULL is not an error — it is worse

Writing where bonus = null does not fail. It quietly matches nothing, so you get an empty answer and no clue why:

MySQL command line client

mysql> select count(*) from emp where bonus = null;

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.00 sec)

mysql> select count(*) from emp where bonus is null;

+----------+

| count(*) |

+----------+

| 3 |

+----------+

1 row in set (0.00 sec)

Zero against three, from the same table in the same second. NULL means “not known”, and nothing — not even another NULL — is equal to something unknown.