SELECT: reading data from a table, choosing columns, aliases, calculations and DISTINCT

SELECT is the SQL command you'll use more than any other. It asks the database a question and gets back a result set: a table of rows and columns. Every report, dashboard, app screen and data analysis starts with a SELECT. When you open your M-Pesa statement, an online shop's product list or your school portal results, a SELECT query ran behind the scenes.

This unit teaches SELECT thoroughly using the sample Kenyan shop database built into this hub: Customers, Products and Orders. Every example can be run with the Run button.

The sample database

The basic shape

SELECT column1, column2
FROM table_name;

• SELECT lists what you want (columns or calculations).
• FROM says where the data comes from (a table).
• The semicolon ; ends the statement. Many tools accept a single query without it, but it's a good habit.

SQL keywords are not case-sensitive (select works like SELECT), but writing keywords in capitals makes queries easier to read. Table and column names in this database start with capitals.

Select every column

SELECT * FROM Products;

* means "all columns". It's great for exploring a table, but in real applications list the columns you need: it's faster, clearer, and won't break if someone adds a column later.

Select specific columns

SELECT Name, Price FROM Products;

The result shows columns in the order you list them, not the order in the table:

SELECT Price, Name, Category FROM Products;

Aliases: renaming columns in the result

AS gives a column a friendlier name in the output. It doesn't change the table.

SELECT Name AS Product, Price AS "Price (KSh)" FROM Products;

Use double quotes for aliases with spaces or special characters. Aliases are very useful for calculated columns, which otherwise get ugly names.

Calculated columns

You can do arithmetic in SELECT: +, -, *, /.

SELECT Name,
       Price,
       Price * 1.16 AS PriceWithVAT,
       Price * 0.9 AS SalePrice
FROM Products;

Rounding makes money look right:

SELECT Name, ROUND(Price * 1.16, 2) AS PriceWithVAT FROM Products;

SELECT 7 / 2 AS WholeDivision, 7 / 2.0 AS DecimalDivision;

Joining text together

The || operator joins (concatenates) text in SQLite, PostgreSQL and Oracle. MySQL uses CONCAT().

SELECT Name || ' (' || City || ')' AS CustomerLabel FROM Customers;

DISTINCT: unique values only

Which cities do our customers live in?

SELECT City FROM Customers;

Some cities repeat. DISTINCT removes duplicates:

SELECT DISTINCT City FROM Customers;

With several columns, DISTINCT removes rows where all listed columns are the same:

SELECT DISTINCT Category FROM Products;

LIMIT: just a few rows

Large tables can have millions of rows. LIMIT returns only the first few:

SELECT Name, Price FROM Products LIMIT 3;

Different databases spell this differently: SQL Server uses SELECT TOP 3 ..., Oracle uses FETCH FIRST 3 ROWS ONLY. Without ORDER BY (next lesson) "the first 3" can be any 3 rows.

Selecting without a table

You can use SELECT as a calculator or to test functions:

SELECT 2500 * 3 AS Total, UPPER('nairobi') AS Town, DATE('now') AS Today;

Comments and formatting

-- This is a single-line comment
/* This is a
   multi-line comment */
SELECT Name,      -- product name
       Price      -- in KSh
FROM Products;

Put each column on its own line in longer queries. Readable SQL is easier to debug and review.

How the database processes a SELECT

You write SELECT ... FROM ..., but the database logically works in this order:

1. FROM: find the table(s)
2. WHERE: filter rows
3. GROUP BY: make groups
4. HAVING: filter groups
5. SELECT: compute the columns
6. ORDER BY: sort
7. LIMIT: cut the rows

This explains things later, such as why you can't use a SELECT alias in WHERE in most databases.

Common errors

Summary

• SELECT columns FROM table reads data; * selects all columns.
• AS creates aliases; calculated columns use arithmetic and functions like ROUND.
• || joins text in SQLite; watch out for integer division.
• DISTINCT removes duplicate rows; LIMIT returns a few rows.
• The database processes FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.