Lesson 6 — Retrieving Data

SELECT

SELECT is the most important SQL statement. Every query begins with it. Understanding all its clauses — including aliases, DISTINCT, and calculated columns — lets you retrieve exactly the data you need.

The SELECT Anatomy

A SELECT statement specifies which columns to return, from which table, with optional filtering, grouping, and ordering. The clauses must appear in this order:

  1. SELECT — which columns
  2. FROM — which table(s)
  3. WHERE — filter rows
  4. GROUP BY — group rows
  5. HAVING — filter groups
  6. ORDER BY — sort results
  7. LIMIT / TOP — restrict count
SQL — Basic SELECT 
-- Select all columns (use sparingly in production — retrieves more data than needed):
SELECT * FROM learners;

-- Select specific columns:
SELECT full_name, course, mark
FROM   learners;

-- Select with a WHERE clause:
SELECT full_name, city, mark
FROM   learners
WHERE  mark >= 50;
SQL — Aliases and calculations 
-- Column aliases — rename columns in output:
SELECT
    full_name        AS name,
    mark             AS percentage,
    city             AS "Home City"   -- quotes needed for spaces
FROM learners;

-- Calculated columns:
SELECT
    full_name,
    mark,
    mark * 0.85             AS mark_with_penalty,
    mark / 100.0 * 25       AS out_of_25,
    ROUND(mark, 0)          AS rounded_mark
FROM learners;
SQL — DISTINCT 
-- DISTINCT — eliminate duplicate values:
SELECT DISTINCT course FROM learners;
-- Returns each course name once, even if 20 learners are in it

SELECT DISTINCT city, province
FROM learners;
-- Unique combinations of city + province

-- COUNT with DISTINCT:
SELECT COUNT(DISTINCT course) AS number_of_courses
FROM learners;
SQL — LIMIT / TOP 
-- Limiting results:
-- MySQL / PostgreSQL / SQLite:
SELECT full_name, mark
FROM   learners
ORDER  BY mark DESC
LIMIT  5;         -- top 5 marks

-- SQL Server / MS Access:
SELECT TOP 5 full_name, mark
FROM   learners
ORDER  BY mark DESC;

-- PostgreSQL also supports FETCH:
SELECT full_name, mark
FROM   learners
ORDER  BY mark DESC
FETCH  FIRST 5 ROWS ONLY;

Practice Task

Your Turn

Write queries that: (1) return all columns for all learners; (2) return only full_name and mark, aliased as 'Learner' and 'Score'; (3) return distinct courses offered; (4) return full_name, mark, and a calculated column 'mark_out_of_50' = mark / 2; (5) return the top 3 learners by mark using your database's syntax.

Common Mistakes

  • SELECT * in production code — retrieve only the columns you need; SELECT * breaks if columns are added or reordered.
  • Aliases with spaces need quotes — mark AS out of 25 is a syntax error; use mark AS "out of 25".
  • DISTINCT is applied to the whole row, not a single column — SELECT DISTINCT city, course returns unique city+course combinations.
  • ORDER BY is required for meaningful LIMIT — without it, LIMIT returns an arbitrary set.

Professional Tip

Think of SELECT as describing a report format: which columns appear, in what order, with what names. The FROM, WHERE, and ORDER BY clauses determine what data fills those columns.

Mini Quiz

What does SELECT DISTINCT do?