Quizz

The full text of this chapter, to read or revisit. To practise, do the exercises.

β–Ά Do the exercises

Exercise 1 β€” Shout it

What does UPPER('pyrochu') return?

Options
β€’ 'PYROCHU'
β€’ 'Pyrochu'
β€’ 'pyrochu'
β€’ 7

Hint : Every letter becomes a capital.

Exercise 2 β€” Invisible characters

What does LENGTH(' alex') return (two leading spaces)?

Options
β€’ 6
β€’ 4
β€’ 5
β€’ 7

Hint : 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?

Options
β€’ 'Azure City'
β€’ 'AzureCity'
β€’ ' Azure City'
β€’ 'Azure City '

Hint : Only the ends are trimmed.

Exercise 5 β€” Four tools

Match each function with what it does.

Items to match
β€’ UPPER
β€’ LOWER
β€’ LENGTH
β€’ TRIM
Definitions
β€’ Converts to upper case
β€’ Converts to lower case
β€’ Counts the characters
β€’ Removes the spaces at both ends

Exercise 6 β€” From position 8

What does SUBSTR('Sunset Valley', 8) return?

Options
β€’ '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?

Options
β€’ 7
β€’ 6
β€’ 8
β€’ 0

Hint : 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?

Options
β€’ '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?

Options
β€’ 12.3
β€’ 12.33
β€’ 12.0
β€’ 12

Hint : The second argument is the number of decimals kept.

Exercise 11 β€” Half way

What does ROUND(4.5) return in SQLite?

Options
β€’ 5.0
β€’ 4.0
β€’ 4
β€’ 4.5

Hint : 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?

Options
β€’ 3.2
β€’ -3.2
β€’ 3
β€’ 10.8

Hint : Compute the difference, then drop the sign.

Exercise 14 β€” Text plus text

What does CAST('12' AS INTEGER) + CAST('9' AS INTEGER) return?

Options
β€’ 21
β€’ '129'
β€’ 12
β€’ An error

Hint : Once cast, they are numbers and + adds them.

Exercise 15 β€” What CAST does

Match each expression with its result.

Items to match
β€’ CAST('14' AS INTEGER)
β€’ CAST('unknown' AS INTEGER)
β€’ CAST(7.9 AS INTEGER)
β€’ CAST(7 AS REAL)
Definitions
β€’ 14
β€’ 0
β€’ 7
β€’ 7.0

Exercise 16 β€” Nine after fourteen

ORDER BY level_text puts '9' after '14'. Why?

Options
β€’ Text sorts character by character, and '9' comes after '1'
β€’ Because 9 is greater than 14 in SQLite
β€’ Because of a NULL in the column
β€’ Because ORDER BY ignores digits

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?

Options
β€’ 6
β€’ 8
β€’ 7
β€’ 5

Hint : Two spaces removed from 8 characters.

Exercise 19 β€” Official spelling

Which expression turns 'PLANTACHU' into 'Plantachu'?

Options
β€’ 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?

Options
β€’ '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'.

Items to match
β€’ INSTR(x, ' - ')
β€’ SUBSTR(x, 1, 10)
β€’ SUBSTR(x, 14)
β€’ REPLACE(x, ' - ', '/')
Definitions
β€’ 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))?

Options
β€’ Because those values cast to 0 and would lower the average
β€’ Because CAST fails on them with an error
β€’ Because AVG refuses text columns
β€’ No reason: CAST turns them into NULL, which AVG skips

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?

Options
β€’ codemon LIKE 'aquachu'
β€’ codemon = 'aquachu'
β€’ codemon = 'Aquachu'
β€’ codemon IN ('aquachu')

Hint : LIKE ignores case for ASCII letters; = does not.

Back to the course