SELECT

01The problem

The sales team is preparing calls to customers. They don't need every column in the customers table. They need the company name, the city and the industry, and nothing else cluttering the screen.

Later that day, finance asks for each shipment's weight in tonnes, but the database stores it in kilograms.

02The concept

SELECT * returns every column. Most of the time you want to be specific, and list only the columns you need, separated by commas.

You can also:

• Rename a column in the result with AS, which is called an alias.
• Calculate a new column from existing ones, using +, -, * and /.
• Remove duplicates with DISTINCT, so each value appears once.

Being specific makes queries faster, results easier to read, and your intent clear to the next person who reads your SQL.

03Example

SELECT
  company_name,
  city,
  industry
FROM customers;

And a calculated, renamed column:

SELECT
  shipment_id,
  weight_kg,
  weight_kg / 1000.0 AS weight_tonnes
FROM shipments;

04Walkthrough

In the first query, the columns come back in the order you list them, not the order they're stored in.

In the second:

• weight_kg / 1000.0 divides each row's weight by 1,000.
• AS weight_tonnes gives the calculated column a readable name. Without it, the column heading would be the expression itself.
• We divide by 1000.0 rather than 1000. In SQLite (and SQL Server), dividing one whole number by another throws away the decimals, so 1500 / 1000 gives 1. Adding .0 makes it decimal division, so 1500 / 1000.0 gives 1.5.

To see each value only once, use DISTINCT:

SELECT DISTINCT mode FROM routes;

Harbourline runs three transport modes, so you get three rows.

05Practice

06Challenge

07Check your understanding