SQL exercises with solutions: text and date functions

SQL exercises with solutions: text and date functions

Updated on

Text and dates: UPPER, LOWER, LENGTH, SUBSTR, ||, LIKE, strftime, julianday, date. These 15 exercises range from level 2 to level 8; they use the syntax of SQLite, SpeedQL’s SQL engine.

Tip: In LIKE, % stands for any run of characters and _ for a single character.

Read the “Text and dates” card in the cheat sheet

Exercise 1 · level 2

Show the name of the customers whose name starts with the letter C.

Table customers (4 rows)
idnamecity
1AliceParis
2BrunoLyon
3ChloeParis
4DylanNantes
Show the hint

Topics to use: WHERE (filters), Text and dates.

Query structure:

SELECT …
FROM …
WHERE … LIKE …
Show the solution
SELECT name
FROM customers
WHERE name LIKE 'C%';

Expected result (1 row):

name
Chloe

Exercise 2 · level 4

Show the id of the invoices issued in March 2025.

Table invoices (6 rows)
idclientissueddueamountpaid_on
1Acme2025-01-152025-02-1412002025-02-10
2Acme2025-03-012025-03-16800NULL
3Bolt2025-01-202025-03-064502025-03-01
4Bolt2025-02-252025-03-279502025-04-02
5Cyan2025-03-052025-05-04300NULL
6Cyan2024-12-102024-12-306002025-01-05
Show the hint

Topics to use: WHERE (filters), Text and dates.

Query structure:

SELECT …
FROM …
WHERE STRFTIME(…, …) = …
Show the solution
SELECT id
FROM invoices
WHERE strftime('%Y-%m', issued) = '2025-03';

Expected result (2 rows):

id
2
5

Exercise 3 · level 4

Show each invoice's id and the number of days between its issue date and its due date.

Table invoices (6 rows)
idclientissueddueamountpaid_on
1Acme2025-01-152025-02-1412002025-02-10
2Acme2025-03-012025-03-16800NULL
3Bolt2025-01-202025-03-064502025-03-01
4Bolt2025-02-252025-03-279502025-04-02
5Cyan2025-03-052025-05-04300NULL
6Cyan2024-12-102024-12-306002025-01-05
Show the hint

Topics to use: Text and dates.

Query structure:

SELECT …, CAST(JULIANDAY(…) - JULIANDAY(…) AS INTEGER)
FROM …
Show the solution
SELECT id, CAST(julianday(due) - julianday(issued) AS INTEGER)
FROM invoices;

Expected result (6 rows):

idCAST(julianday(due) - julianday(issued) AS INTEGER)
130
215
345
430
560
620

Exercise 4 · level 4

Show each contact's name in uppercase, then the length of that name.

Table contacts (5 rows)
idnameemailphonecity
1Alice Martinalice@mail.com0612345678Paris
2bob durandNULL0698765432Lyon
3CLAIRE ROUXclaire@work.orgNULLNULL
4David LefevreNULLNULLNantes
5Emma Petitemma@mail.com0611223344NULL
Show the hint

Topics to use: Text and dates.

Query structure:

SELECT UPPER(…), LENGTH(…)
FROM …
Show the solution
SELECT UPPER(name), LENGTH(name)
FROM contacts;

Expected result (5 rows):

UPPER(name)LENGTH(name)
ALICE MARTIN12
BOB DURAND10
CLAIRE ROUX11
DAVID LEFEVRE13
EMMA PETIT10

Exercise 5 · level 4

Show the date of the matches played on a Sunday (strftime('%w', …) is '0' on Sundays).

Table matches (10 rows)
idplayed_onhome_idaway_idhome_goalsaway_goals
12025-08-021221
22025-08-033400
32025-08-095113
42025-08-102322
52025-08-164531
62025-08-171310
72025-08-232401
82025-08-243523
92025-08-304111
102025-08-315202
Show the hint

Topics to use: WHERE (filters), Text and dates.

Query structure:

SELECT …
FROM …
WHERE STRFTIME(…, …) = …
Show the solution
SELECT played_on
FROM matches
WHERE strftime('%w', played_on) = '0';

Expected result (5 rows):

played_on
2025-08-03
2025-08-10
2025-08-17
2025-08-24
2025-08-31

Exercise 6 · level 4

Show each student's name and their age in completed years on 2025-09-01 (a student has only gained a year once their birthday has passed).

Table students (8 rows)
idnameclassbirth_date
1AdeleA2009-03-14
2BrunoA2008-11-02
3CyrilB2009-07-21
4DinaB2009-01-30
5ElsaA2008-09-12
6FarahC2009-05-05
7GaelC2008-12-24
8HanaB2009-10-10
Show the hint

Topics to use: Text and dates.

Query structure:

SELECT …, CAST(STRFTIME(…, …) AS INTEGER) - CAST(STRFTIME(…, …) AS INTEGER) - (STRFTIME(…, …) < STRFTIME(…, …))
FROM …
Show the solution
SELECT name, CAST(strftime('%Y', '2025-09-01') AS INTEGER) - CAST(strftime('%Y', birth_date) AS INTEGER) - (strftime('%m-%d', '2025-09-01') < strftime('%m-%d', birth_date))
FROM students;

Expected result (8 rows):

nameCAST(strftime('%Y', '2025-09-01') AS INTEGER) - CAST(strftime('%Y', birth_date) AS INTEGER) - (strftime('%m-%d', '2025-09-01') < strftime('%m-%d', birth_date))
Adele16
Bruno16
Cyril16
Dina16
Elsa16
Farah16
Gael16
Hana15

Exercise 7 · level 4

Show the id of each flight and its arrival time in the format YYYY-MM-DD HH:MM (departure time + duration).

Table flights (12 rows)
idairlineorigindestdepartsduration_minpriceseats_soldcapacity
1SkyJetCDGMAD2025-06-01 08:1012589150180
2AirNovaCDGLIS2025-06-01 11:40155120160170
3BlueWingLYSFCO2025-06-02 07:309575110150
4SkyJetMADCDG2025-06-02 18:2012095170180
5AirNovaBERCDG2025-06-03 09:05110105140160
6BlueWingNCEBER2025-06-03 13:5013014090150
7SkyJetCDGFCO2025-06-04 06:4513599175180
8AirNovaLISMAD2025-06-04 16:15756560120
9BlueWingFCOLYS2025-06-05 20:3010082130150
10SkyJetCDGBER2025-06-05 10:00105110120180
11AirNovaMADLIS2025-06-06 12:25807095120
12BlueWingLYSMAD2025-06-06 15:4011579100150
Show the hint

Topics to use: Text and dates.

Query structure:

SELECT …, STRFTIME(…, …, … || … || …)
FROM …
Show the solution
SELECT id, strftime('%Y-%m-%d %H:%M', departs, '+' || duration_min || ' minutes')
FROM flights;

Expected result (12 rows):

idstrftime('%Y-%m-%d %H:%M', departs, '+' || duration_min || ' minutes')
12025-06-01 10:15
22025-06-01 14:15
32025-06-02 09:05
42025-06-02 20:20
52025-06-03 10:55
62025-06-03 16:00
72025-06-04 09:00
82025-06-04 17:30
92025-06-05 22:10
102025-06-05 11:45
112025-06-06 13:45
122025-06-06 17:35

Exercise 8 · level 4

Show the id and a column called route in the format ORIGIN-DESTINATION (for example CDG-MAD) of the flights of the airlines whose name starts with 'Air' or 'Blue' (columns: id, route).

Table flights (12 rows)
idairlineorigindestdepartsduration_minpriceseats_soldcapacity
1SkyJetCDGMAD2025-06-01 08:1012589150180
2AirNovaCDGLIS2025-06-01 11:40155120160170
3BlueWingLYSFCO2025-06-02 07:309575110150
4SkyJetMADCDG2025-06-02 18:2012095170180
5AirNovaBERCDG2025-06-03 09:05110105140160
6BlueWingNCEBER2025-06-03 13:5013014090150
7SkyJetCDGFCO2025-06-04 06:4513599175180
8AirNovaLISMAD2025-06-04 16:15756560120
9BlueWingFCOLYS2025-06-05 20:3010082130150
10SkyJetCDGBER2025-06-05 10:00105110120180
11AirNovaMADLIS2025-06-06 12:25807095120
12BlueWingLYSMAD2025-06-06 15:4011579100150
Show the hint

Topics to use: WHERE (filters), Text and dates.

Query structure:

SELECT …, … || … || … AS …
FROM …
WHERE … LIKE … OR … LIKE …
Show the solution
SELECT id, origin || '-' || dest AS route
FROM flights
WHERE airline LIKE 'Air%' OR airline LIKE 'Blue%';

Expected result (8 rows):

idroute
2CDG-LIS
3LYS-FCO
5BER-CDG
6NCE-BER
8LIS-MAD
9FCO-LYS
11MAD-LIS
12LYS-MAD

Exercise 9 · level 4

Show the title of each song and its duration in minutes:seconds format, always with 2 digits for the seconds (for example 3:34 or 4:01), in a column called mmss (columns: title, mmss).

Table songs (10 rows)
idtitleartist_idgenreduration_sreleased
1Glass Heart1Pop2142019-04-12
2Low Tide1Pop1982021-06-01
3Rust2Rock2562008-09-30
4Wires2Rock3012015-02-14
5Sunday Market3Afrobeat2332020-11-20
6Palm Wine3Afrobeat2452022-03-03
7Alma4Latin1892013-07-07
8Brisa4Pop2052018-05-25
9Night Drive5Electro2762021-10-10
10Pulse5Electro2302023-01-15
Show the hint

Topics to use: Text and dates.

Query structure:

SELECT …, (… / …) || … || PRINTF(…, … % …) AS …
FROM …
Show the solution
SELECT title, (duration_s / 60) || ':' || printf('%02d', duration_s % 60) AS mmss
FROM songs;

Expected result (10 rows):

titlemmss
Glass Heart3:34
Low Tide3:18
Rust4:16
Wires5:01
Sunday Market3:53
Palm Wine4:05
Alma3:09
Brisa3:25
Night Drive4:36
Pulse3:50

Exercise 10 · level 4

Show the city, the day and, in a column called weekday, the day of the week as a 3-letter English abbreviation (Sun, Mon, Tue, Wed, Thu, Fri, Sat), for the readings whose maximum temperature is above 25 degrees (columns: city, day, weekday).

Table readings (15 rows)
idcitydaytemp_maxtemp_minrain_mm
1Paris2025-07-0124150
2Paris2025-07-0227170
3Paris2025-07-0322164.5
4Paris2025-07-04191412
5Paris2025-07-0523130
6Lyon2025-07-0126160
7Lyon2025-07-0229180
8Lyon2025-07-0331190
9Lyon2025-07-0424178.5
10Lyon2025-07-0522153
11Marseille2025-07-0130210
12Marseille2025-07-0232220
13Marseille2025-07-0333230
14Marseille2025-07-0429210
15Marseille2025-07-0528200
Show the hint

Topics to use: WHERE (filters), Text and dates.

Query structure:

SELECT …, …, SUBSTR(…, … + … * CAST(STRFTIME(…, …) AS INTEGER), …) AS …
FROM …
WHERE … > …
Show the solution
SELECT city, day, substr('SunMonTueWedThuFriSat', 1 + 3 * CAST(strftime('%w', day) AS INTEGER), 3) AS weekday
FROM readings
WHERE temp_max > 25;

Expected result (9 rows):

citydayweekday
Paris2025-07-02Wed
Lyon2025-07-01Tue
Lyon2025-07-02Wed
Lyon2025-07-03Thu
Marseille2025-07-01Tue
Marseille2025-07-02Wed
Marseille2025-07-03Thu
Marseille2025-07-04Fri
Marseille2025-07-05Sat

Exercise 11 · level 4

Show the id of each booking and its number of nights (difference between check_out and check_in, in whole days).

Table bookings (11 rows)
idroom_idguest_idcheck_incheck_out
1212025-07-012025-07-04
2522025-07-022025-07-05
3832025-07-032025-07-06
4342025-07-052025-07-12
5652025-07-062025-07-08
6122025-07-082025-07-10
7932025-07-102025-07-11
8752025-07-112025-07-15
9412025-07-142025-07-16
10642025-07-152025-07-19
11652025-07-162025-07-18
Show the hint

Topics to use: Text and dates.

Query structure:

SELECT …, CAST(JULIANDAY(…) - JULIANDAY(…) AS INTEGER)
FROM …
Show the solution
SELECT id, CAST(julianday(check_out) - julianday(check_in) AS INTEGER)
FROM bookings;

Expected result (11 rows):

idCAST(julianday(check_out) - julianday(check_in) AS INTEGER)
13
23
33
47
52
62
71
84
92
104
112

Exercise 12 · level 4

For each month (format YYYY-MM, using strftime), show the month, the number of transactions and the sum of the amounts.

Table transactions (14 rows)
idaccount_idmade_onamountlabel
112025-01-022500salary
212025-01-05-60groceries
312025-01-12-800rent
422025-01-15500transfer
532025-01-031900salary
632025-01-20-120groceries
732025-02-01-950rent
842025-01-252100salary
942025-02-03-45restaurant
1012025-02-022600salary
1112025-02-06-75groceries
1252025-02-1030interest
1322025-02-15500transfer
1442025-02-18-600rent
Show the hint

Topics to use: COUNT, SUM, AVG, MIN, MAX, GROUP BY, Text and dates.

Query structure:

SELECT STRFTIME(…, …), COUNT(*), SUM(…)
FROM …
GROUP BY STRFTIME(…, …)
Show the solution
SELECT strftime('%Y-%m', made_on), COUNT(*), SUM(amount)
FROM transactions
GROUP BY strftime('%Y-%m', made_on);

Expected result (2 rows):

strftime('%Y-%m', made_on)COUNT(*)SUM(amount)
2025-0176020
2025-0271460

Exercise 13 · level 5

Show the possible connections (columns: flight 1 id, flight 2 id): flight 2 departs from the arrival airport of flight 1, between 0 and 48 hours after flight 1 departs.

Table flights (12 rows)
idairlineorigindestdepartsduration_minpriceseats_soldcapacity
1SkyJetCDGMAD2025-06-01 08:1012589150180
2AirNovaCDGLIS2025-06-01 11:40155120160170
3BlueWingLYSFCO2025-06-02 07:309575110150
4SkyJetMADCDG2025-06-02 18:2012095170180
5AirNovaBERCDG2025-06-03 09:05110105140160
6BlueWingNCEBER2025-06-03 13:5013014090150
7SkyJetCDGFCO2025-06-04 06:4513599175180
8AirNovaLISMAD2025-06-04 16:15756560120
9BlueWingFCOLYS2025-06-05 20:3010082130150
10SkyJetCDGBER2025-06-05 10:00105110120180
11AirNovaMADLIS2025-06-06 12:25807095120
12BlueWingLYSMAD2025-06-06 15:4011579100150
Show the hint

Topics to use: JOIN, Text and dates.

Query structure:

SELECT …, …
FROM … …
JOIN … … ON … = … AND JULIANDAY(…) - JULIANDAY(…) BETWEEN … AND …
Show the solution
SELECT f.id, g.id
FROM flights f
JOIN flights g ON g.origin = f.dest AND julianday(g.departs) - julianday(f.departs) BETWEEN 0 AND 2;

Expected result (6 rows):

idid
14
47
57
79
811
912

Exercise 14 · level 8

Show the name of the guests who stayed in at least 2 different cities, with the number of cities and their total number of nights.

Table guests (6 rows)
idnamecountry
1Ana SilvaPortugal
2Ben FordUSA
3Chen LiChina
4Dana WeissGermany
5Emma RoyFrance
6Farid NasserMorocco
Table bookings (11 rows)
idroom_idguest_idcheck_incheck_out
1212025-07-012025-07-04
2522025-07-022025-07-05
3832025-07-032025-07-06
4342025-07-052025-07-12
5652025-07-062025-07-08
6122025-07-082025-07-10
7932025-07-102025-07-11
8752025-07-112025-07-15
9412025-07-142025-07-16
10642025-07-152025-07-19
11652025-07-162025-07-18
Table rooms (10 rows)
idhotel_idtypeprice
11single70
21double95
32double130
42suite210
53single110
63double150
74double65
85double260
95suite480
105single190
Table hotels (5 rows)
idnamecitystars
1Seaside InnNice3
2Alpine LodgeAnnecy4
3City LoftParis4
4Old MillBordeaux2
5Grand PalaceParis5
Show the hint

Topics to use: COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN, HAVING, Text and dates.

Query structure:

SELECT …, COUNT(DISTINCT …), SUM(CAST(JULIANDAY(…) - JULIANDAY(…) AS INTEGER))
FROM … …
JOIN … … ON … = …
JOIN … … ON … = …
JOIN … … ON … = …
GROUP BY …
HAVING COUNT(DISTINCT …) >= …
Show the solution
SELECT g.name, COUNT(DISTINCT h.city), SUM(CAST(julianday(b.check_out) - julianday(b.check_in) AS INTEGER))
FROM guests g
JOIN bookings b ON b.guest_id = g.id
JOIN rooms r ON r.id = b.room_id
JOIN hotels h ON h.id = r.hotel_id
GROUP BY g.id
HAVING COUNT(DISTINCT h.city) >= 2;

Expected result (4 rows):

nameCOUNT(DISTINCT h.city)SUM(CAST(julianday(b.check_out) - julianday(b.check_in) AS INTEGER))
Ana Silva25
Ben Ford25
Dana Weiss211
Emma Roy28

Exercise 15 · level 8

Show the name, signup date, first order date and delay in days of the customers whose first order came less than 300 days after they signed up.

Table customers (6 rows)
idnamecitysignup
1AlbaParis2024-01-10
2BorisLyon2024-02-15
3CarlaParis2024-03-01
4DenisNantes2024-05-20
5EvaLyon2024-06-30
6FabioLille2024-08-08
Table orders (8 rows)
idcustomer_idorder_datestatus
112025-01-05shipped
222025-01-12shipped
312025-02-03paid
432025-02-10cancelled
542025-02-20shipped
652025-03-02shipped
722025-03-15paid
832025-03-28shipped
Show the hint

Topics to use: COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN, HAVING, Text and dates.

Query structure:

SELECT …, …, MIN(…), CAST(JULIANDAY(MIN(…)) - JULIANDAY(…) AS INTEGER)
FROM … …
JOIN … … ON … = …
GROUP BY …
HAVING JULIANDAY(MIN(…)) - JULIANDAY(…) < …
Show the solution
SELECT c.name, c.signup, MIN(o.order_date), CAST(julianday(MIN(o.order_date)) - julianday(c.signup) AS INTEGER)
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
HAVING julianday(MIN(o.order_date)) - julianday(c.signup) < 300;

Expected result (2 rows):

namesignupMIN(o.order_date)CAST(julianday(MIN(o.order_date)) - julianday(c.signup) AS INTEGER)
Denis2024-05-202025-02-20276
Eva2024-06-302025-03-02245

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 24 “Text and dates” questions