SQL exercises with solutions: ORDER BY and LIMIT

Updated on 5 October 2026

ORDER BY sorts (ASC ascending, DESC descending); LIMIT keeps the first n rows. These 6 exercises range from level 1 to level 2; they use the syntax of SQLite, SpeedQL’s SQL engine.

Tip: Add a second sort column to break ties.

Read the “ORDER BY, LIMIT” card in the cheat sheet

Exercise 1 · level 1

Show the name and salary of every employee, from the lowest to the highest salary.

Table `employees` (8 rows)
id	name	department	salary
1	Alice	Finance	32000
2	Bob	IT	41000
3	Claire	Finance	38000
4	David	HR	29000
5	Emma	IT	50000
6	Farid	IT	47000
7	Gaelle	HR	33000
8	Hugo	Finance	44000

Show the hint

Topics to use: ORDER BY, LIMIT.

Query structure:

SELECT …, …
FROM …
ORDER BY … ASC

Show the solution

SELECT name, salary
FROM employees
ORDER BY salary ASC;

Expected result (8 rows, in this order):

name	salary
David	29000
Alice	32000
Gaelle	33000
Claire	38000
Bob	41000
Hugo	44000
Farid	47000
Emma	50000

Exercise 2 · level 2

Show the name, department and salary of the employees, sorted by department (alphabetical order) and then, within each department, from the highest to the lowest salary.

Table `employees` (8 rows)
id	name	department	salary
1	Alice	Finance	32000
2	Bob	IT	41000
3	Claire	Finance	38000
4	David	HR	29000
5	Emma	IT	50000
6	Farid	IT	47000
7	Gaelle	HR	33000
8	Hugo	Finance	44000

Show the hint

Topics to use: ORDER BY, LIMIT.

Query structure:

SELECT …, …, …
FROM …
ORDER BY … ASC, … DESC

Show the solution

SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

Expected result (8 rows, in this order):

name	department	salary
Hugo	Finance	44000
Claire	Finance	38000
Alice	Finance	32000
Gaelle	HR	33000
David	HR	29000
Emma	IT	50000
Farid	IT	47000
Bob	IT	41000

name

department

salary

Hugo

Finance

44000

Claire

Finance

38000

Alice

Finance

32000

Gaelle

33000

David

29000

Emma

50000

Farid

47000

Bob

41000

Exercise 3 · level 2

Show the name of the 3 cheapest products, from the cheapest to the most expensive.

Table `products` (6 rows)
id	name	category	price	stock
1	Keyboard	Office	25	40
2	Mouse	Office	15	0
3	Screen	Display	180	12
4	Headset	Audio	60	8
5	Webcam	Office	45	0
6	Speaker	Audio	35	25

Show the hint

Topics to use: ORDER BY, LIMIT.

Query structure:

SELECT …
FROM …
ORDER BY … ASC
LIMIT …

Show the solution

SELECT name
FROM products
ORDER BY price ASC
LIMIT 3;

Expected result (3 rows, in this order):

name
Mouse
Keyboard
Speaker

name

Mouse

Keyboard

Speaker

Exercise 4 · level 2

Show the name of the 2 highest-paid employees, highest salary first.

Table `employees` (8 rows)
id	name	department	salary
1	Alice	Finance	32000
2	Bob	IT	41000
3	Claire	Finance	38000
4	David	HR	29000
5	Emma	IT	50000
6	Farid	IT	47000
7	Gaelle	HR	33000
8	Hugo	Finance	44000

Show the hint

Topics to use: ORDER BY, LIMIT.

Query structure:

SELECT …
FROM …
ORDER BY … DESC
LIMIT …

Show the solution

SELECT name
FROM employees
ORDER BY salary DESC
LIMIT 2;

Expected result (2 rows, in this order):

name
Emma
Farid

name

Emma

Farid

Exercise 5 · level 2

Show the name and number of goals of the 3 top scorers, from the most goals to the fewest.

Table `players` (9 rows)
id	name	team_id	position	goals
1	Alex Moreau	1	FW	9
2	Bilal Sow	1	MF	4
3	Carl Weber	2	FW	7
4	Diego Ruiz	2	DF	1
5	Eli Novak	3	FW	6
6	Femi Adeyemi	4	FW	11
7	Goran Petrov	4	GK	0
8	Hugo Lamy	5	MF	5
9	Ivan Kral	5	DF	2

Show the hint

Topics to use: ORDER BY, LIMIT.

Query structure:

SELECT …, …
FROM …
ORDER BY … DESC
LIMIT …

Show the solution

SELECT name, goals
FROM players
ORDER BY goals DESC
LIMIT 3;

Expected result (3 rows, in this order):

name	goals
Femi Adeyemi	11
Alex Moreau	9
Carl Weber	7

Exercise 6 · level 2

Show the city, day and maximum temperature of the 3 hottest readings, from the hottest down (on ties, by day then by city).

Table `readings` (15 rows)
id	city	day	temp_max	temp_min	rain_mm
1	Paris	2025-07-01	24	15	0
2	Paris	2025-07-02	27	17	0
3	Paris	2025-07-03	22	16	4.5
4	Paris	2025-07-04	19	14	12
5	Paris	2025-07-05	23	13	0
6	Lyon	2025-07-01	26	16	0
7	Lyon	2025-07-02	29	18	0
8	Lyon	2025-07-03	31	19	0
9	Lyon	2025-07-04	24	17	8.5
10	Lyon	2025-07-05	22	15	3
11	Marseille	2025-07-01	30	21	0
12	Marseille	2025-07-02	32	22	0
13	Marseille	2025-07-03	33	23	0
14	Marseille	2025-07-04	29	21	0
15	Marseille	2025-07-05	28	20	0

Show the hint

Topics to use: ORDER BY, LIMIT.

Query structure:

SELECT …, …, …
FROM …
ORDER BY … DESC, …, …
LIMIT …

Show the solution

SELECT city, day, temp_max
FROM readings
ORDER BY temp_max DESC, day, city
LIMIT 3;

Expected result (3 rows, in this order):

city	day	temp_max
Marseille	2025-07-03	33
Marseille	2025-07-02	32
Lyon	2025-07-03	31

More exercises: level 4 of the SQL course, “Sorting, limiting, removing duplicates”

These 5 exercises come from level 4 of the course, on other tables. Each one has written hints and its solution, folded by default.

Exercise 7 · course, level 4 · Guided

Show the title and year of the movies, from oldest to newest.

The row order matters.

Table `movies` (10 rows)
id	title	genre	year	duration	rating	director_id
1	Night Train	Thriller	2015	118	7.8	1
2	Blue Harbor	Drama	2018	102	7.1	2
3	Paper Moon City	Comedy	2012	95	6.4	5
4	Silent Peak	Drama	2020	131	8.2	3
5	Last Signal	Sci-Fi	2019	142	7.5	1
6	Summer Keys	Comedy	2016	88	5.9	4
7	Iron Garden	Sci-Fi	2021	125	8	3
8	Dust and Gold	Western	2014	110	6.8	2
9	The Quiet Hour	Drama	2022	97	7.4	5
10	Deep Current	Thriller	2017	105	6.9	NULL

Show the hints

Hint: ORDER BY year sorts in ascending order.

Show the solution

SELECT title, year
FROM movies
ORDER BY year;

Expected result (10 rows):

title	year
Paper Moon City	2012
Dust and Gold	2014
Night Train	2015
Summer Keys	2016
Deep Current	2017
Blue Harbor	2018
Last Signal	2019
Silent Peak	2020
Iron Garden	2021
The Quiet Hour	2022

ASC is implicit: ORDER BY year and ORDER BY year ASC are the same.

Exercise 8 · course, level 4 · Practice

Show the list of movie genres, without duplicates.

Table `movies` (10 rows)
id	title	genre	year	duration	rating	director_id
1	Night Train	Thriller	2015	118	7.8	1
2	Blue Harbor	Drama	2018	102	7.1	2
3	Paper Moon City	Comedy	2012	95	6.4	5
4	Silent Peak	Drama	2020	131	8.2	3
5	Last Signal	Sci-Fi	2019	142	7.5	1
6	Summer Keys	Comedy	2016	88	5.9	4
7	Iron Garden	Sci-Fi	2021	125	8	3
8	Dust and Gold	Western	2014	110	6.8	2
9	The Quiet Hour	Drama	2022	97	7.4	5
10	Deep Current	Thriller	2017	105	6.9	NULL

Show the hints

Hint 1 · lead: A keyword placed right after SELECT removes duplicate rows.

Hint 2 · method: SELECT DISTINCT genre …

Show the solution

SELECT DISTINCT genre
FROM movies;

Expected result (5 rows):

genre
Thriller
Drama
Comedy
Sci-Fi
Western

genre

Thriller

Drama

Comedy

Sci-Fi

Western

There are 10 movies but only 5 different genres.

Exercise 9 · course, level 4 · Practice

Show the title and the director id (director_id) of each movie, sorted by increasing id, with the movies without a director at the end. For equal ids, sort by title.

The row order matters.

Table `movies` (10 rows)
id	title	genre	year	duration	rating	director_id
1	Night Train	Thriller	2015	118	7.8	1
2	Blue Harbor	Drama	2018	102	7.1	2
3	Paper Moon City	Comedy	2012	95	6.4	5
4	Silent Peak	Drama	2020	131	8.2	3
5	Last Signal	Sci-Fi	2019	142	7.5	1
6	Summer Keys	Comedy	2016	88	5.9	4
7	Iron Garden	Sci-Fi	2021	125	8	3
8	Dust and Gold	Western	2014	110	6.8	2
9	The Quiet Hour	Drama	2022	97	7.4	5
10	Deep Current	Thriller	2017	105	6.9	NULL

Show the hints

Hint 1 · lead: In SQLite, where do NULL values end up in an ascending sort?

Hint 2 · method: ORDER BY director_id NULLS LAST, title.

Show the solution

SELECT title, director_id
FROM movies
ORDER BY director_id NULLS LAST, title;

Expected result (10 rows):

title	director_id
Last Signal	1
Night Train	1
Blue Harbor	2
Dust and Gold	2
Iron Garden	3
Silent Peak	3
Summer Keys	4
Paper Moon City	5
The Quiet Hour	5
Deep Current	NULL

Without NULLS LAST, Deep Current would come first: SQLite puts NULL first in an ascending sort.

Exercise 10 · course, level 4 · Practice

Show the name, department and salary of the employees, sorted by department (alphabetically), then by descending salary, then by name.

The row order matters.

Table `staff` (10 rows)
id	name	department	salary	manager_id	hired
1	Alice	Finance	52000	NULL	2015-03-01
2	Bob	IT	41000	1	2018-06-15
3	Claire	Finance	38000	1	2019-01-10
4	David	HR	29000	1	2020-09-01
5	Emma	IT	50000	2	2017-11-20
6	Farid	IT	47000	2	2021-02-14
7	Gaelle	HR	33000	4	2022-05-30
8	Hugo	Finance	44000	3	2016-08-08
9	Iris	IT	47000	5	2023-01-09
10	Jules	Finance	44000	3	2024-03-18

Show the hints

Hint 1 · lead: Three sort criteria, each with its own direction.

Hint 2 · method: ORDER BY department, salary DESC, name.

Show the solution

SELECT name, department, salary
FROM staff
ORDER BY department, salary DESC, name;

Expected result (10 rows):

name	department	salary
Alice	Finance	52000
Hugo	Finance	44000
Jules	Finance	44000
Claire	Finance	38000
Gaelle	HR	33000
David	HR	29000
Emma	IT	50000
Farid	IT	47000
Iris	IT	47000
Bob	IT	41000

name

department

salary

Alice

Finance

52000

Hugo

Finance

44000

Jules

Finance

44000

Claire

Finance

38000

Gaelle

33000

David

29000

Emma

50000

Farid

47000

Iris

47000

Bob

41000

Each sort column breaks the ties of the previous one: Hugo and Jules both earn 44000.

Exercise 11 · course, level 4 · Challenge

Show the airline, destination and price of the second cheapest flight departing from 'CDG'.

The row order matters.

Table `flights` (12 rows)
id	airline	origin	dest	departs	duration_min	price	seats_sold	capacity
1	SkyJet	CDG	MAD	2025-06-01 08:10	125	89	150	180
2	AirNova	CDG	LIS	2025-06-01 11:40	155	120	160	170
3	BlueWing	LYS	FCO	2025-06-02 07:30	95	75	110	150
4	SkyJet	MAD	CDG	2025-06-02 18:20	120	95	170	180
5	AirNova	BER	CDG	2025-06-03 09:05	110	105	140	160
6	BlueWing	NCE	BER	2025-06-03 13:50	130	140	90	150
7	SkyJet	CDG	FCO	2025-06-04 06:45	135	99	175	180
8	AirNova	LIS	MAD	2025-06-04 16:15	75	65	60	120
9	BlueWing	FCO	LYS	2025-06-05 20:30	100	82	130	150
10	SkyJet	CDG	BER	2025-06-05 10:00	105	110	120	180
11	AirNova	MAD	LIS	2025-06-06 12:25	80	70	95	120
12	BlueWing	LYS	MAD	2025-06-06 15:40	115	79	100	150

Show the hints

Hint 1 · lead: Filter, sort, then skip the first row.

Hint 2 · method: Filter on origin, sort by price, then LIMIT 1 OFFSET 1.

Show the solution

SELECT airline, dest, price
FROM flights
WHERE origin = 'CDG'
ORDER BY price
LIMIT 1 OFFSET 1;

Expected result (1 row):

airline	dest	price
SkyJet	FCO	99

airline

dest

price

SkyJet

FCO

OFFSET 1 skips the cheapest flight, LIMIT 1 keeps the next one.

Practice with automatic checking

In SpeedQL, you write your query and it is checked straight away, on these tables and then on a hidden dataset.

Practice on the 7 “ORDER BY, LIMIT” questions