Quizz
The full text of this chapter, to read or revisit. To practise, do the exercises.
βΆ Do the exercisesExercise 1 β Shout it
What does UPPER('pyrochu') return?
'PYROCHU''Pyrochu''pyrochu'7Hint : Every letter becomes a capital.
Exercise 2 β Invisible characters
What does LENGTH(' alex') return (two leading spaces)?
6457Hint : Spaces are characters too.
Exercise 3 β Voltachu, however typed
Complete the query so that it finds the sighting of Voltachu, even though it was typed with a different case and a trailing space.
Hint : Clean the spaces, and compare with a literal written in lowercase.
Exercise 4 β Inner space
What does TRIM(' Azure City ') return?
'Azure City''AzureCity'' Azure City''Azure City 'Hint : Only the ends are trimmed.
Exercise 5 β Four tools
Match each function with what it does.
UPPERLOWERLENGTHTRIMExercise 6 β From position 8
What does SUBSTR('Sunset Valley', 8) return?
'Valley''Sunset'' Valley''V'Hint : S-u-n-s-e-t is 6 characters, the space is the 7th.
Exercise 7 β Where is the space?
What does INSTR('Sunset Valley', ' ') return?
7680Hint : Count the characters up to and including the space.
Exercise 8 β Reports per region
Complete the query so that it counts the sightings per region β the first two characters of ref_code ('AZ', 'SV'β¦).
Hint : Cut from position 1, two characters long.
Exercise 9 β No more dashes
What does REPLACE('AZ-2023-0017', '-', '') return?
'AZ20230017''AZ 2023 0017''AZ2023-0017''2023'Hint : Every dash is replaced by nothing.
Exercise 10 β One decimal
What does ROUND(12.333, 1) return?
12.312.3312.012Hint : The second argument is the number of decimals kept.
Exercise 11 β Half way
What does ROUND(4.5) return in SQLite?
5.04.044.5Hint : Halves round away from zero, and ROUND returns a REAL.
Exercise 12 β Average weight
Complete the query so that it returns the average weight with two decimals.
Hint : The aggregate for an average, then the number of decimals.
Exercise 13 β Distance
What does ABS(3.8 - 7) return?
3.2-3.2310.8Hint : Compute the difference, then drop the sign.
Exercise 14 β Text plus text
What does CAST('12' AS INTEGER) + CAST('9' AS INTEGER) return?
21'129'12Hint : Once cast, they are numbers and + adds them.
Exercise 15 β What CAST does
Match each expression with its result.
CAST('14' AS INTEGER)CAST('unknown' AS INTEGER)CAST(7.9 AS INTEGER)CAST(7 AS REAL)14077.0Exercise 16 β Nine after fourteen
ORDER BY level_text puts '9' after '14'. Why?
Hint : Compare the first characters: '1' versus '9'.
Exercise 17 β Ten kilos
Complete the query so that it shows how far each weight is from 10 kg, with one decimal, closest first.
Hint : A distance has no sign; one decimal.
Exercise 18 β Trim then count
What does LENGTH(TRIM(' Taylor ')) return?
6875Hint : Two spaces removed from 8 characters.
Exercise 19 β Official spelling
Which expression turns 'PLANTACHU' into 'Plantachu'?
UPPER(SUBSTR('PLANTACHU', 1, 1)) || LOWER(SUBSTR('PLANTACHU', 2))LOWER('PLANTACHU')TRIM('PLANTACHU')SUBSTR('PLANTACHU', 1, 1) || SUBSTR('PLANTACHU', 2)Hint : First letter one way, the rest another way, glued back together.
Exercise 20 β City only
Complete the query so that it returns the city part of location β everything before ' - '.
Hint : The separator's position minus one gives the city's length.
Exercise 21 β From the end
What does SUBSTR('AZ-2023-0017', -4) return?
'0017''AZ-2''2023''-001'Hint : A negative start counts from the end.
Exercise 22 β Market day
Match each expression with its result, where x is 'Azure City - Market'.
INSTR(x, ' - ')SUBSTR(x, 1, 10)SUBSTR(x, 14)REPLACE(x, ' - ', '/')11'Azure City''Market''Azure City/Market'Exercise 23 β Why filter first?
Why write WHERE level_text NOT IN ('', 'unknown') before AVG(CAST(level_text AS INTEGER))?
Hint : CAST never complains; it answers 0.
Exercise 24 β Capitalize
Complete the query so that each CodΓ©mon name gets an upper-case first letter and the rest in lower case ('PLANTACHU' β 'Plantachu').
Hint : The rest starts at the second character and must be lower case.
Exercise 25 β Without any function
In SQLite, which comparison matches both 'Aquachu' and 'aquachu' without any function?
codemon LIKE 'aquachu'codemon = 'aquachu'codemon = 'Aquachu'codemon IN ('aquachu')Hint : LIKE ignores case for ASCII letters; = does not.