SQL, lesson 3
A table has no built-in order. SQLite often hands rows back in the order they were inserted, but
nothing promises that, and it can change once a table grows or gets an index. If order matters,
say so with ORDER BY.
ORDER BY sorts from smallest to largest by default (ASC). Add DESC to reverse it. Numbers
sort by value, text sorts alphabetically (capital letters before lowercase ones).
List more than one sort key, separated by commas. The second key is only used when two rows tie on the first, and so on. Each key gets its own direction.
Adding a tie-breaker is a good habit: it makes your output the same every time you run it.
You can also sort by an alias or an expression: ORDER BY length(player) sorts by name length.
2 exercises to try what you just read.
Where do NULL values go? SQLite treats NULL as smaller than everything, so it comes first in
ascending order and last in descending order. Add NULLS LAST or NULLS FIRST to choose.
LIMIT n stops after n rows. Combined with ORDER BY, it answers "top three" questions.
OFFSET k skips the first k rows first, which is how pages of results work.
The clauses must appear in this order: SELECT … FROM … WHERE … ORDER BY … LIMIT … OFFSET ….
SELECT DISTINCT drops repeated rows from the result. It looks at all the selected columns
together, so DISTINCT city, country keeps each pair once.
The table scores has the columns player and score:
Print the player and score of the top three players: highest score first. When two players
have the same score, list them in alphabetical order of name. Expected output:
The table trips records holidays, with the columns city and year. Some cities were visited
more than once:
Print each city once, in alphabetical order. Expected output:
SELECT player, score FROM scores ORDER BY score;SELECT player, score FROM scores ORDER BY score DESC;-- Highest score first; equal scores in alphabetical order of name
SELECT player, score FROM scores ORDER BY score DESC, player ASC;SELECT player, score FROM scores ORDER BY score DESC LIMIT 3; -- podium
SELECT player, score FROM scores ORDER BY score DESC LIMIT 3 OFFSET 3; -- places 4 to 6SELECT DISTINCT team FROM scores ORDER BY team;player | score
Maya | 820
Priya | 940
Sam | 610
Leo | 940
Noah | 875
Zoe | 700Leo|940
Priya|940
Noah|875city | year
Lisbon | 2022
Oslo | 2023
Lisbon | 2024
Kyoto | 2023
Oslo | 2025
Accra | 2024Accra
Kyoto
Lisbon
Oslo