Practice: The Price List

Pixel Shop is redoing its price list, and you have all the tools for it. A price list is a good test of this lesson: almost every column in it is calculated.

Functions can go inside each other, and so can everything else you've learned: a function can be an argument of another function, a calculation can be inside a function, and a function can be in a condition of CASE. The initials of each customer, for example, are the first letter of the name and the first letter after the space:

SELECT name,
       SUBSTR(name, 1, 1) || SUBSTR(name, INSTR(name, ' ') + 1, 1) AS initials
FROM customers
LIMIT 3;

A few tips for queries like this:

• Build a long column step by step. Write the innermost part first, such as INSTR(name, ' '), run it and check the result, and only then wrap it in the next function.
• Count the parentheses. Every function opens one, and every one has to be closed; incomplete input or a syntax error often means that one is missing.
• Think of whole division and of NULL: does the calculation need a .0? Can a column be NULL, and what should the result be then?
• Give every calculated column a name with AS.