Writing SQL · Chapter 6

Working with columns

7 min read · 8 questions

SELECT does more than pick columns: it can also compute new ones, rename them, add constants and remove duplicates. This chapter covers these operations.

Selecting every column

The star * selects every column of the table named in the FROM:

-- The star selects every column of the product table
SELECT *
FROM product

-- Question 6.1: write your query here
SELECT 

SELECT *
FROM customer

Column names with a space

When a column name contains a space or a special character, like VolKilo (COPA), it must be written in double quotes:

SELECT CustomerID, ProductID, "VolKilo (COPA)"
FROM perform21

Putting spaces in column names is nonetheless bad practice. They force you to add quotes everywhere, which makes queries heavier and multiplies mistakes. And each DBMS has its own syntax for them, which makes code less portable:

-- SQL Server: square brackets
SELECT [Creation Date], [Customer Name] FROM Orders;

-- PostgreSQL, Oracle, DuckDB: double quotes
SELECT "Creation Date", "Customer Name" FROM Orders;

-- MySQL: backticks
SELECT `Creation Date`, `Customer Name` FROM Orders;

-- Question 6.2: write your query here
SELECT 

SELECT CustomerID, ProductID, "VolKilo (COPA)", "NOS (COPA)", "COGS (COPA)"
FROM perform21

Computing a new column

SELECT can display the result of a calculation on existing columns, with the operators +, -, *, /.

Imagine the scale that weighs orders is badly calibrated and takes 5 kg off every weighing. To correct it, add 5 kg to each volume:

SELECT CustomerID, ProductID, "VolKilo (COPA)" + 5
FROM perform21

The new column is named after its calculation, which is hard to read. The keyword AS gives it a name, called an alias:

SELECT CustomerID, ProductID, "VolKilo (COPA)" + 5 AS corrected_volume
FROM perform21

-- Question 6.3: write your query here
SELECT 

SELECT CustomerID, ProductID, "NOS (COPA)", "COGS (COPA)",
       "NOS (COPA)" + "COGS (COPA)" AS margin
FROM perform21

-- Question 6.4: write your query here
SELECT 

SELECT CustomerID, ProductID, Date,
       ROUND("GP (COPA)" / "NOS (COPA)" * 100, 1) AS margin_rate
FROM perform21

Adding a constant

A column can hold the same value on every row. Just write the value in the SELECT: in single quotes for text, without quotes for a number.

SELECT *, 'FR' AS country_code
FROM customer

This trick is useful to label data before stacking it with other data (we will see this in the chapter on UNION).

-- Question 6.5: write your query here
SELECT 

SELECT CustomerID, CustomerName, 'customer' AS type
FROM customer

Concatenating text

The || operator joins pieces of text end to end. Use it to build a label or a key:

SELECT CustomerName || ' (' || Channel || ')' AS label
FROM customer

-- Question 6.6: write your query here
SELECT 

SELECT CustomerID || '-' || ProductID || '-' || Date AS row_id,
       "NOS (COPA)"
FROM perform21

Removing duplicates with DISTINCT

DISTINCT, placed right after SELECT, removes duplicate rows from the result.

-- One row per sale: 21,736 rows
SELECT ProductID
FROM perform21

-- One row per distinct product: 520 rows
SELECT DISTINCT ProductID
FROM perform21

With several columns, DISTINCT compares whole rows: it keeps each distinct combination.

-- The distinct customer-product pairs: 2,845 rows
SELECT DISTINCT CustomerID, ProductID
FROM perform21

-- Question 6.7: write your query here
SELECT 

SELECT DISTINCT Date
FROM perform21

-- Question 6.8: write your query here
SELECT 

SELECT DISTINCT Category, SubCategory
FROM product