SQL — Select Top

Basic SELECT

The SELECT statement retrieves data from a table:

-- All columns
SELECT * FROM students;

-- Specific columns
SELECT name, age FROM students;

-- Column order matters
SELECT grade, name FROM students;

Column aliases

Rename columns in the output using AS:

SELECT
    name AS student_name,
    age AS student_age,
    grade AS letter_grade
FROM students;

Aliases don't change the actual column names — they only affect the query output.

Expressions in SELECT

Calculate values on the fly:

SELECT
    name,
    age,
    age + 1 AS next_year_age,
    price * 0.9 AS discounted_price,
    CONCAT(first_name, ' ', last_name) AS full_name
FROM students;

SQL treats the expression as a computed column in the result.

DISTINCT — removing duplicates

-- All grades (with duplicates)
SELECT grade FROM students;
-- A, B, A, C, B, A

-- Unique grades only
SELECT DISTINCT grade FROM students;
-- A, B, C

-- Multiple columns
SELECT DISTINCT grade, age FROM students;

DISTINCT applies to the entire row combination, not just one column.

LIMIT and OFFSET

-- First 10 results
SELECT * FROM students LIMIT 10;

-- Skip 20, get next 10 (pagination)
SELECT * FROM students LIMIT 10 OFFSET 20;

-- Alternative syntax
SELECT * FROM students LIMIT 20, 10;

Pagination pattern: page 1 = LIMIT 10 OFFSET 0, page 2 = LIMIT 10 OFFSET 10.

WHERE — filtering rows

-- Comparison operators
SELECT * FROM students WHERE age > 20;
SELECT * FROM students WHERE age >= 18;
SELECT * FROM students WHERE age = 20;
SELECT * FROM students WHERE age != 20;

-- AND / OR
SELECT * FROM students WHERE age > 18 AND grade = 'A';
SELECT * FROM students WHERE grade = 'A' OR grade = 'B';

-- BETWEEN
SELECT * FROM students WHERE age BETWEEN 18 AND 25;

-- IN
SELECT * FROM students WHERE grade IN ('A', 'B', 'C');

-- LIKE
SELECT * FROM students WHERE name LIKE 'A%';     -- starts with A
SELECT * FROM students WHERE name LIKE '%son';   -- ends with son
SELECT * FROM students WHERE name LIKE '%an%';   -- contains an

-- IS NULL
SELECT * FROM students WHERE email IS NULL;
SELECT * FROM students WHERE email IS NOT NULL;

ORDER BY — sorting

-- Ascending (default)
SELECT * FROM students ORDER BY name;

-- Descending
SELECT * FROM students ORDER BY age DESC;

-- Multiple sort keys
SELECT * FROM students ORDER BY grade ASC, name ASC;

-- Sort by expression
SELECT * FROM students ORDER BY (math_score + science_score) DESC;

Aggregate functions

Summarize data across rows:

-- Count
SELECT COUNT(*) FROM students;
SELECT COUNT(DISTINCT grade) FROM students;

-- Sum
SELECT SUM(salary) FROM employees;

-- Average
SELECT AVG(age) FROM students;

-- Min and Max
SELECT MIN(age), MAX(age) FROM students;

GROUP BY — grouping rows

-- Count students per grade
SELECT grade, COUNT(*) AS student_count
FROM students
GROUP BY grade;

-- Average age per grade
SELECT grade, AVG(age) AS avg_age
FROM students
GROUP BY grade;

GROUP BY collapses rows with the same value into one summary row.

HAVING — filtering groups

-- Grades with more than 5 students
SELECT grade, COUNT(*) AS student_count
FROM students
GROUP BY grade
HAVING COUNT(*) > 5;

HAVING filters groups after GROUP BY. WHERE filters rows before grouping.

NULL handling

-- COUNT ignores NULLs
SELECT COUNT(email) FROM students;  -- doesn't count NULL emails

-- COALESCE provides a default
SELECT name, COALESCE(email, 'No email') FROM students;

-- NULLIF returns NULL if values are equal
SELECT NULLIF(10, 10);  -- NULL
SELECT NULLIF(10, 5);   -- 10

Combining everything

SELECT
    grade,
    COUNT(*) AS student_count,
    ROUND(AVG(age), 1) AS avg_age,
    MIN(age) AS youngest,
    MAX(age) AS oldest
FROM students
WHERE age >= 18
GROUP BY grade
HAVING COUNT(*) >= 3
ORDER BY student_count DESC
LIMIT 5;

Execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

Mini Practice

1. Select all columns from a table where age is between 20 and 30
2. Use DISTINCT to find all unique values in a column
3. Calculate the average, min, and max of a numeric column
4. Group data by a category and count items in each group
5. Write a paginated query that returns 10 results starting from page 3

Next: the WHERE clause in depth →