Text functions

SQL · Unit 14: Text and dates

Full plan
SQL
11 exercises

The store is printing labels for the shelves, with each product's name in capitals. A label has room for 8 letters. Of everything that isn't an accessory, which name doesn't fit?

A function takes a value in parentheses and returns another one, like ROUND with numbers. UPPER returns the text in uppercase, LOWER returns it in lowercase, and LENGTH counts its letters. The table doesn't change.

Headphones doesn't fit: it has 10. SQLite, out of the box, only uppercases unaccented letters. It would turn a customer's name like Chloë into CHLOë. In this app that's fixed.

SELECT UPPER(name) AS label,
  LENGTH(name) AS letters
FROM products
WHERE category <> 'Accessories';

Returns

label	letters
MONITOR	7
LAPTOP	6
TABLET	6
HEADPHONES	10
SPEAKER	7

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

1. Predict the output
On the screen of the store's app, a customer's name gets cut off if it's longer than 5 letters. Whose gets cut off? What does this query return?
2. Put the lines in order
Some names fill the label and some don't fit. Put in order the query that gives the products with names of 8 letters or more and how many each one has, longest first and alphabetically on a tie. Two lines don't belong.
3. Complete the code
The store gives each customer a username for signing in to the app: their name in lowercase. Fill in the blank for the two customers from Austin.
4. Predict the output
What does this query return for Ann, Luke, and Chloë, customers 1, 2, and 3?
5. Find the bug
The box codes for Devices were supposed to be LAP and TAB. The query gives two-letter codes. Which line has the problem?
6. Put the lines in order
So it never repeats, the full code ends with a hyphen and the id, like KEY-1. Put in order the query that gives the name and the code of the products that cost more than 100, most expensive first. Two lines don't belong.
7. Complete the code
With that space in Brian's profile, the list of Denver customers still has to show all three. Fill in the blank in the condition.
8. Predict the output
The store's accountant wants dates with slashes instead of hyphens. Valerie, customer 5, has placed two orders, one on January 10 and one on the 12th. What does this query return?
9. Find the bug
Someone looks for the Audio section without trusting how it's written in the table. The query runs and returns no products, but Audio has two. Which line has the problem?
10. Find the case that fails
The list of people who live in Denver has to find all of them, whether they typed their city in all caps, in lowercase, or with spaces around it. Which of these changes makes this query miss someone?
11. Write the code
Unit 2 said that real search boxes strip the accents from both sides before comparing. Someone types chloe, which is already clean, so all that's left is cleaning the table's side. Write a query that shows the name and the age, in that order, of the customer whose name, in lowercase and with the ë changed to e, is chloe. It has to find her too if the table said CHLOË, or Chloe, with no accent.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →