Calculations and aliases

SQL · Unit 1: First queries

Free
SQL
12 exercises

The store is putting everything on clearance at half price. What does each item come to? That number isn't stored, but it can be calculated.

A SELECT has room for calculations with +, -, and *, and SQL does them on every row. Between two values, the * multiplies: only on its own does it mean every column.

SELECT name, price * 0.5
FROM products;

Returns

name	price * 0.5
Keyboard	22.75
Mouse	9.95
Monitor	94.5
Cable	2.625
Laptop	449.5
Tablet	160
Headphones	29.95
Speaker	37

A calculation on its own doesn't need a table, so it goes without FROM and returns a single row. It's good for trying out a calculation before you use it on a whole column.

Decimals take a point, and big numbers take no comma. With a comma, SQL reads two columns: 1,000 * 0.5 gives a 1 and a 0.

SELECT 100 * 0.5;

Returns

100 * 0.5
50

A calculated column has no name. SQLite uses the calculation as its heading, and some databases use something worse. AS gives it a name, which is called an alias.

An alias is a single word. If you need two, join them with _.

SELECT name,
  stock - 1 AS remaining
FROM products;

Returns

name	remaining
Keyboard	11
Mouse	29
Monitor	4
Cable	-1
Laptop	2
Tablet	6
Headphones	14
Speaker	-1

So far everything you've written is SQL words, names, and numbers. Text of your own goes in single quotes, like 'Deal'.

The quotes tell SQL not to look it up or calculate it: it comes out as is.

SELECT 'Deal' AS sign;

Returns

sign
Deal

|| glues one piece of text to the next. That's how you build a different greeting on every row, with one fixed part and one that comes from the table.

It doesn't add spaces: if you want one, it goes inside the quotes. In MySQL this is written with CONCAT().

SELECT 'Hi, ' || name
  AS greeting
FROM customers;

Returns

greeting
Hi, Ann
Hi, Luke
Hi, Chloë
Hi, Mason
Hi, Valerie
Hi, Dylan
Hi, Hannah
Hi, Brian

Exercises in this lesson

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

1. Complete the code
A customer wants to buy one of every product. Fill in the blank to see how much stock would be left.
```
SELECT name, stock ___ 1
FROM products;
```
2. Predict the output
The Monitor costs 189. What does this query return?
```
SELECT 189 * 0.5;
```
3. Complete the code
There's a deal on buying two of the same item. Fill in the blank so that the column with the price of two is named the_pair.
```
SELECT name,
  price * 2 ___ the_pair
FROM products;
```
4. Multiple choice
The printed catalog says "product," not "name." What does AS product change in this query?
```
SELECT name AS product
FROM products;
```
5. Multiple choice
The owner asks how much money is sitting in the warehouse. The Speaker costs 74 and its stock is 0. What comes out in the value column for the Speaker?
```
SELECT name,
  price * stock AS value
FROM products;
```
6. Find the bug
Shipping costs 10 per order, and the store wants to see each product's price with shipping included. SQL complains about the word shipping. Which line has the error?
```
SELECT name,
  price + 10 AS plus shipping
FROM products;
```
7. Put the lines in order
Tomorrow a truck arrives with 10 units of every product. Put the query in order to see how many there'll be of each, in a column named with_truck. One line doesn't belong.
8. Predict the output
What does this query return?
```
SELECT '2 + 3';
```
9. Predict the output
What does this query return?
```
SELECT 'Ann' || 'Denver';
```
10. Put the lines in order
Each product's listing leads with its section, like "Accessories: Keyboard." Put in order the query that builds them. One line doesn't belong.
11. Find the bug
Every package gets a label with the name of whoever it's for, like "To Ann" on hers. This query runs without an error, but the labels come out wrong. Which line has the error?
```
SELECT 'To ' || 'name'
  AS label
FROM customers;
```
12. Write the code
Make the signs for the clearance sale. For each product, show two columns. The first is text like "Deal: Keyboard" with a space after the colon, and it takes the alias sign. The second is its price cut in half, with the alias deal.
```
SELECT name, price
FROM products;
```

Do this lesson

It opens in your browser. It is free and you do not need an account to start.

See all lessons →