Lesson 04 ~25 min read

Retrieving Data with SELECT

SELECT is the most important command in SQL. Every time you need to see data — whether it is one row or one billion — SELECT is how you ask for it. Learn to write it precisely, efficiently, and clearly.

The SELECT Statement

SELECT is the most important and most frequently used SQL command. It retrieves data from one or more tables and returns it as a result set. It never modifies the data — it only reads.

Every SELECT statement has at minimum two clauses: SELECT (what columns to return) and FROM (which table to read from).

sql — anatomy of a SELECT

SELECT  column1, column2   -- what to retrieve
FROM    table_name         -- where to get it from
WHERE   condition          -- filter (optional)
ORDER BY column            -- sort (optional)
LIMIT   n;                 -- how many rows (optional)

SELECT * — All Columns

The asterisk * is a wildcard meaning "all columns". It's the fastest way to explore a table you're not familiar with. In production code, avoid it — always name the columns you actually need.

sql

-- Retrieve every column and every row from students
SELECT * FROM students;

-- Result (example):
-- +------------+--------------+-------------------+---------------+-------------+-----------+
-- | student_id | full_name    | email             | date_of_birth | enrolled_on | is_active |
-- +------------+--------------+-------------------+---------------+-------------+-----------+
-- | 1          | Peter Banda  | peter@gmail.com   | 2004-05-12    | 2025-01-10  | 1         |
-- | 2          | Chiso Moyo   | chisomo@gmail.com | 2002-09-28    | 2025-01-11  | 1         |
-- | 3          | Mphatso Phiri| mphatso@gmail.com | 2003-11-03    | 2025-02-01  | 0         |
-- +------------+--------------+-------------------+---------------+-------------+-----------+

Selecting Specific Columns

Name only the columns you need, separated by commas. This is best practice — it reduces data transfer, makes queries faster, and makes your code clearer about exactly what data it uses.

sql

-- Only names and emails
SELECT full_name, email
FROM students;

-- With a column alias — rename the output column
SELECT
  full_name   AS 'Student Name',
  email       AS 'Contact Email',
  enrolled_on AS 'Start Date'
FROM students;

-- Computed column — SQL can do arithmetic
SELECT
  full_name,
  YEAR(CURRENT_DATE) - YEAR(date_of_birth) AS age
FROM students;

DISTINCT — Remove Duplicates

DISTINCT filters the result set to return only unique values. Useful when you want to know what values exist in a column, not how many rows have each value.

sql

-- All unique cities students come from
SELECT DISTINCT city FROM students;

-- Without DISTINCT, duplicates appear:
-- Blantyre
-- Blantyre
-- Lilongwe
-- Blantyre

-- With DISTINCT:
-- Blantyre
-- Lilongwe

LIMIT — Controlling Result Size

LIMIT restricts how many rows are returned. Critical for large tables — never run SELECT * FROM orders on a table with 10 million rows without a LIMIT.

sql

-- Return only the first 5 rows
SELECT * FROM students LIMIT 5;

-- LIMIT with OFFSET — skip the first 10, return the next 5
-- (used for pagination: page 1, page 2, page 3...)
SELECT * FROM students LIMIT 5 OFFSET 10;

// Knowledge Check

You want to display students' names in the result with the heading "Full Name" instead of "full_name". Which syntax achieves this?