A question that ends “…in descending order of salary” is telling you the last clause of the query. Sorting is easy marks — provided you put order by in the right place and remember what happens to the missing values.
1Sorting on one column
order by goes at the end of the query, after where. The default is ascending; desc reverses it.
1
Display the name and salary of all employees, highest paid first.
2 marks
▶Show the answer
MySQL command line client
mysql> select name, salary from emp order by salary desc;
+---------------+----------+
| name | salary |
+---------------+----------+
| Karan Bhutia | 61000.00 |
| Arun Thapa | 58000.00 |
| Deepa Nair | 54000.00 |
| Hema Subba | 49500.00 |
| Emil Lepcha | 47000.00 |
| Jyoti Tamang | 33000.00 |
| Chandan Rai | 31000.00 |
| Farida Khan | 29500.00 |
| Lata Gurung | 27500.00 |
| Bina Sharma | 26000.00 |
| Ishan Pradhan | 25500.00 |
| Gopal Das | 24000.00 |
+---------------+----------+
12 rows in set (0.00 sec)
“Highest first”, “in descending order” and “top to bottom by salary” all mean desc.
2Sorting on two columns
Give order by two columns and it sorts by the first, then breaks ties with the second. Each column carries its own asc or desc.
2
Display name, department number and salary, ordered by department, and within a department by salary highest first.
2 marks
▶Show the answer
MySQL command line client
mysql> select name, deptno, salary from emp order by deptno, salary desc;
+---------------+--------+----------+
| name | deptno | salary |
+---------------+--------+----------+
| Lata Gurung | NULL | 27500.00 |
| Arun Thapa | 10 | 58000.00 |
| Chandan Rai | 10 | 31000.00 |
| Farida Khan | 10 | 29500.00 |
| Ishan Pradhan | 10 | 25500.00 |
| Karan Bhutia | 20 | 61000.00 |
| Hema Subba | 20 | 49500.00 |
| Emil Lepcha | 20 | 47000.00 |
| Bina Sharma | 20 | 26000.00 |
| Deepa Nair | 30 | 54000.00 |
| Jyoti Tamang | 30 | 33000.00 |
| Gopal Das | 30 | 24000.00 |
+---------------+--------+----------+
12 rows in set (0.00 sec)
deptno is ascending (no keyword given) while salary is descending. Note where Lata Gurung landed — that is the next section.
3Where the NULLs go
This is a favourite of examiners because most textbooks never mention it. In MySQL, sorting ascending puts NULLfirst, and sorting descending puts it last. NULL behaves as though it were smaller than every real value.
MySQL command line client
mysql> select name, bonus from emp order by bonus;
+---------------+---------+
| name | bonus |
+---------------+---------+
| Bina Sharma | NULL |
| Emil Lepcha | NULL |
| Ishan Pradhan | NULL |
| Gopal Das | 900.00 |
| Lata Gurung | 1200.00 |
| Farida Khan | 1800.00 |
| Chandan Rai | 2200.00 |
| Jyoti Tamang | 2500.00 |
| Hema Subba | 3100.00 |
| Deepa Nair | 4000.00 |
| Arun Thapa | 4500.00 |
| Karan Bhutia | 5000.00 |
+---------------+---------+
12 rows in set (0.00 sec)
Reverse the sort and the three NULLs move to the bottom:
MySQL command line client
mysql> select name, bonus from emp order by bonus desc;
+---------------+---------+
| name | bonus |
+---------------+---------+
| Karan Bhutia | 5000.00 |
| Arun Thapa | 4500.00 |
| Deepa Nair | 4000.00 |
| Hema Subba | 3100.00 |
| Jyoti Tamang | 2500.00 |
| Chandan Rai | 2200.00 |
| Farida Khan | 1800.00 |
| Lata Gurung | 1200.00 |
| Gopal Das | 900.00 |
| Bina Sharma | NULL |
| Emil Lepcha | NULL |
| Ishan Pradhan | NULL |
+---------------+---------+
12 rows in set (0.00 sec)
⚠️Sorted does not mean filtered
The NULL rows are still there in both answers. If a question asks for the employees with a bonus in order, you need where bonus is not null as well as order by. Sorting never removes a row.
4Giving a column a readable heading
By default the heading of a calculated column is the expression itself, which looks like machinery rather than an answer. as renames it for the resultset only — the table is untouched.
3
For department 10, display each employee’s name under the heading Employee and their yearly salary under the heading Annual Salary.
2 marks
▶Show the answer
MySQL command line client
mysql> select name as "Employee", salary * 12 as "Annual Salary" from emp where deptno = 10;
+---------------+---------------+
| Employee | Annual Salary |
+---------------+---------------+
| Arun Thapa | 696000.00 |
| Chandan Rai | 372000.00 |
| Farida Khan | 354000.00 |
| Ishan Pradhan | 306000.00 |
+---------------+---------------+
4 rows in set (0.00 sec)
An alias containing a space must be quoted. A single-word alias, like as Employee, needs no quotes at all. The word as itself is optional in MySQL, but write it — it reads better and no examiner has ever taken a mark off for it.
5The order the clauses must come in
select→from→where→group by→having→order by
Swap any two and the query will not run. Putting order by before where is the version that turns up in papers as a “find the error” question, and it has a page of its own later in this track.
✏️ Quick Check
In MySQL, where do NULL values appear when you sort a column with ORDER BY (ascending)?
✏️ Quick Check
Which clause order is valid?
✏️ Quick Check
What does the alias in 'salary * 12 as Annual Salary' change in the table?