With any language, the only way to improve is practice and lots of it! Today I did exactly that with SQL, using a great resource called SQL 50, which breaks the topic into focused challenges covering SELECT, JOINs, aggregate functions, and more.
Rather than memorising scripts, each exercise builds on the previous ones, helping you understand how to approach problems, structure queries, and discover (or refresh) functions you might not have used before. Below, I’ve listed some of those functions which were new to me or important in executing the queries.
Function Cheat sheet
1) CHAR_LENGTH
What it does: Returns the number of characters in a string (not bytes).
When to use: Validating input lengths, filtering by name/title length, formatting.
Example: Find users with short names (≤ 5 chars)
SELECT user_id, name, CHAR_LENGTH(name) AS name_len
FROM Users
WHERE CHAR_LENGTH(name) <= 5;
2) DATEDIFF(date1, date2)
What it does:
Returns the number of days between two dates (dialect-specific argument order).
When to use:
- To compare dates by a specific day-gap (e.g., “exactly 1 day apart”).
- Quick filters on day differences (be mindful of index use).
Example: Return each day’s weather row whose temperature is higher than the previous day’s.
SELECT w1.id
FROM Weather w1, Weather w2
WHERE DATEDIFF(w1.recordDate, w2.recordDate) = 1
AND w1.temperature > w2.temperature;
3) DATEADD
What it does:
Adds (or subtracts) a time interval to a date.
When to use:
- To shift a date by days/months/years.
- To join “today” to “yesterday” (often more index-friendly than wrapping both sides in a function).
SELECT w1.id
FROM Weather AS w1
JOIN Weather AS w2
ON w1.recordDate = DATE_ADD(w2.recordDate, INTERVAL 1 DAY)
WHERE w1.temperature > w2.temperature;
4) MOD
What it does: The MOD(x, y) function returns the remainder after dividing x by y.
When to use:
Used to calculate odd and even numbers:
MOD(id, 2) = 0 – even numbers
MOD(id, 2) = 1 – odd numbers
Example: Want only odd ID's from Cinema table and where the description is not "boring"
SELECT *
FROM Cinema
WHERE MOD(id, 2) = 1
AND description <> 'boring'
ORDER BY rating DESC;