Basic SQL

This chapter covers the fundamental building blocks of every SQL query you will ever write. By the end you will be able to retrieve exactly the data you need, shape it with expressions and aliases, sort it, remove duplicates, and limit output to a manageable number of rows.

We will use this employees table for all examples:

Comments in SQL

Before writing queries, it is important to know how to add comments — notes for yourself and other developers that are ignored by the database.

-- This is a single-line comment. Everything after -- is ignored.

SELECT name, salary  -- you can put comments at the end of a line too
FROM   employees;

/*
  This is a multi-line comment.
  Useful for longer explanations
  or for temporarily disabling a block of SQL.
*/
SELECT *
FROM   employees;

Use comments to explain why a query does something, not just what it does. Future you will be grateful.

The SELECT Statement — Retrieving Data

SELECT is the command that retrieves data from a table. It is the core of DQL (Data Query Language) and the command you will write most often.

Selecting All Columns with *

The asterisk * means "give me every column".

SELECT * FROM employees;

Result:

Selecting Specific Columns

List the column names separated by commas:

SELECT name, salary
FROM   employees;

Result:

Column Aliases with AS

An alias renames a column in the output. It does not change the actual table — it only affects what the column header looks like in the result set.

SELECT name        AS employee_name,
       salary      AS annual_salary,
       hire_date   AS "Date Hired"     -- use quotes for aliases with spaces
FROM   employees;

Result:

The AS keyword is optional in most databases — SELECT name employee_name works too. However, always write AS for clarity.

The FROM Clause

FROM tells SQL which table (or tables) to read data from. Every SELECT query needs a FROM clause (except in some databases that allow SELECT without FROM for expressions).

SELECT name
FROM   employees;   -- read from the employees table

Table Aliases

Just like columns, tables can have aliases. This is especially useful when:

• The table name is long
• You are joining multiple tables
• You need to self-join a table (a table joined to itself)

-- Give the employees table a short alias 'e'
SELECT e.name, e.salary
FROM   employees e;

-- Using AS keyword (both styles are valid)
SELECT e.name, e.salary
FROM   employees AS e;

When you use a table alias, you can prefix column names with the alias to make clear which table they come from: e.name means "the name column from the table aliased as e".

Arithmetic in SELECT

You can perform calculations directly in the SELECT clause. The result appears as a new column in the output.

SELECT name,
       salary,
       salary * 12               AS annual_salary,      -- yearly total
       salary * 0.10             AS bonus_estimate,     -- 10% bonus
       (salary + 5000) * 12      AS new_annual_salary,  -- with raise
       ROUND(salary / 22, 2)     AS daily_rate          -- approx daily rate
FROM   employees;

Result (showing key columns):

Common arithmetic operators:

String Concatenation

To combine text values, use the concatenation operator.

-- Standard SQL / Oracle / PostgreSQL: use ||
SELECT name || ' works in ' || dept AS description
FROM   employees;

-- MySQL / SQL Server: use CONCAT()
SELECT CONCAT(name, ' works in ', dept) AS description
FROM   employees;

-- PostgreSQL also supports CONCAT()
SELECT CONCAT(name, ' (', dept, ')') AS name_dept
FROM   employees;

Result:

-- Practical example: build a display label
SELECT emp_id,
       name || ' - ' || dept       AS display_label,
       'Salary: $' || salary        AS salary_label
FROM   employees;

Useful Scalar Functions in SELECT

These functions transform a single value and are used directly in SELECT:

SELECT name,
       UPPER(name)         AS name_upper,     -- 'alice' → 'ALICE'
       LOWER(name)         AS name_lower,     -- 'ALICE' → 'alice'
       LENGTH(name)        AS name_length,    -- number of characters
       SUBSTR(name, 1, 3)  AS first_3_chars,  -- first 3 characters
       TRIM(name)          AS name_trimmed,   -- remove leading/trailing spaces
       ROUND(salary, -3)   AS rounded_salary, -- round to nearest 1000
       ABS(-500)           AS absolute_val    -- absolute value
FROM   employees;

The WHERE Clause — Filtering Rows

WHERE limits the output to only rows that match a condition. Only rows where the condition evaluates to TRUE are included in the result.

-- Only employees in the IT department
SELECT name, dept, salary
FROM   employees
WHERE  dept = 'IT';

Result:

Comparison Operators

These operators build the conditions used in WHERE:

-- Salary exactly 85000
SELECT name FROM employees WHERE salary = 85000;

-- Not in HR department
SELECT name, dept FROM employees WHERE dept <> 'HR';

-- Hired on or after 2020
SELECT name, hire_date FROM employees WHERE hire_date >= '2020-01-01';

-- Salary between 70000 and 90000 (using basic operators)
SELECT name, salary FROM employees WHERE salary >= 70000 AND salary <= 90000;

String Comparisons

String values in WHERE must use single quotes. SQL is case-sensitive for string comparisons depending on the database collation.

-- Correct: single quotes
WHERE dept = 'IT'

-- Wrong: double quotes are for identifiers (column/table names), not values
WHERE dept = "IT"   -- This will ERROR in most databases

-- Case-insensitive search using UPPER()
WHERE UPPER(dept) = 'IT'   -- matches 'IT', 'it', 'It'

ORDER BY — Sorting Results

ORDER BY sorts the output. Without ORDER BY, the database makes no guarantee about the order of rows returned.

-- Sort by salary, highest first (DESC = descending)
SELECT name, salary
FROM   employees
ORDER BY salary DESC;

Result:

-- Sort alphabetically by name (ASC = ascending, this is the default)
SELECT name, dept, salary
FROM   employees
ORDER BY name ASC;

-- You can omit ASC since it is the default
ORDER BY name

-- Sort by department first, then by salary descending within each department
SELECT name, dept, salary
FROM   employees
ORDER BY dept ASC, salary DESC;

Result of multi-column ORDER BY:

NULL Ordering

When a column contains NULL values, their sort position depends on the database. You can control it explicitly:

-- Oracle / PostgreSQL: put NULLs at the end
SELECT name, manager_id
FROM   employees
ORDER BY manager_id ASC NULLS LAST;

-- Put NULLs at the beginning
ORDER BY manager_id DESC NULLS FIRST;

Ordering by Column Position (Avoid in Production)

You can reference a column by its position number in the SELECT list:

-- Order by the 2nd column in SELECT (salary)
SELECT name, salary FROM employees ORDER BY 2 DESC;

This is fragile — if you reorder the SELECT columns, the sort changes silently. Use column names instead.

DISTINCT — Removing Duplicates

DISTINCT removes duplicate rows from the result. It applies to the combination of all listed columns.

-- See all unique departments (no duplicates)
SELECT DISTINCT dept
FROM   employees;

Result:

-- Without DISTINCT: shows 6 rows with repeats
SELECT dept FROM employees;
-- Result: HR, IT, IT, HR, Finance, IT (6 rows, IT and HR repeated)

-- With DISTINCT on two columns: unique (dept, salary) combinations
SELECT DISTINCT dept, salary
FROM   employees
ORDER BY dept, salary;

COUNT DISTINCT

A very common pattern is counting how many unique values exist:

-- How many different departments are there?
SELECT COUNT(DISTINCT dept) AS num_departments
FROM   employees;
-- Result: 3

LIMIT / FETCH FIRST — Restricting Row Count

Often you only want the top N rows — the top 10 customers, the 5 most recent orders, etc.

Different databases have slightly different syntax:

-- MySQL / PostgreSQL / SQLite — LIMIT
SELECT name, salary
FROM   employees
ORDER BY salary DESC
LIMIT 3;   -- only return the top 3

-- Oracle 12c+ / SQL Server 2012+ — FETCH FIRST
SELECT name, salary
FROM   employees
ORDER BY salary DESC
FETCH FIRST 3 ROWS ONLY;

-- SQL Server — TOP (put it right after SELECT)
SELECT TOP 3 name, salary
FROM   employees
ORDER BY salary DESC;

-- Oracle pre-12c — use ROWNUM in a subquery
SELECT name, salary
FROM (
    SELECT name, salary FROM employees ORDER BY salary DESC
)
WHERE ROWNUM <= 3;

Result of all the above (top 3 earners):

Pagination — Skipping Rows

Pagination means showing "page 2", "page 3", etc. — skipping some rows and showing the next batch.

-- MySQL / PostgreSQL: LIMIT + OFFSET
-- Skip first 3 rows, then return 3 (rows 4, 5, 6 = "page 2")
SELECT name, salary
FROM   employees
ORDER BY emp_id
LIMIT  3 OFFSET 3;

-- Oracle / SQL Server: OFFSET ... FETCH NEXT
SELECT name, salary
FROM   employees
ORDER BY emp_id
OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY;

CASE Expression — Conditional Logic in SELECT

The CASE expression lets you add if/then/else logic inside a query. It comes in two forms.

Simple CASE (compare one value to many)

-- Translate department codes into readable names
SELECT name,
       CASE dept
           WHEN 'HR'      THEN 'Human Resources'
           WHEN 'IT'      THEN 'Information Technology'
           WHEN 'Finance' THEN 'Finance Department'
           ELSE                'Other'
       END AS dept_full_name
FROM   employees;

Searched CASE (evaluate different conditions)

-- Categorise employees by salary range
SELECT name,
       salary,
       CASE
           WHEN salary >= 90000 THEN 'Senior'
           WHEN salary >= 70000 THEN 'Mid-level'
           WHEN salary >= 50000 THEN 'Junior'
           ELSE                      'Entry-level'
       END AS seniority_band
FROM   employees;

Result:

-- CASE can also be used in ORDER BY to control custom sort order
SELECT name, dept
FROM   employees
ORDER BY
    CASE dept
        WHEN 'IT'      THEN 1   -- IT first
        WHEN 'Finance' THEN 2
        WHEN 'HR'      THEN 3
        ELSE                4
    END;

The Mandatory Clause Order Rule

This is one of the most important rules to memorise. SQL clauses must appear in this exact order. The database will throw a syntax error if you put them in a different sequence:

SELECT   ...         -- 1st: which columns
FROM     ...         -- 2nd: which table
WHERE    ...         -- 3rd: filter rows (optional)
GROUP BY ...         -- 4th: group rows (optional)
HAVING   ...         -- 5th: filter groups (optional)
ORDER BY ...         -- 6th: sort result (optional)
LIMIT    ...         -- 7th: restrict row count (optional)

-- WRONG — WHERE comes after ORDER BY (will error)
SELECT name FROM employees
ORDER BY salary
WHERE dept = 'IT';

-- CORRECT — WHERE before ORDER BY
SELECT name FROM employees
WHERE  dept = 'IT'
ORDER BY salary;

SQL Formatting Best Practices

Well-formatted SQL is much easier to read and debug. Here are the widely-accepted conventions:

-- BAD: everything on one line, hard to scan
select name,salary,dept from employees where salary>70000 order by salary desc;

-- GOOD: readable formatting
SELECT name,
       salary,
       dept
FROM   employees
WHERE  salary > 70000
ORDER BY salary DESC;

Formatting rules to follow:

1. Write SQL keywords in UPPERCASE (SELECT, FROM, WHERE, etc.)
2. Write table and column names in lowercase
3. Put each major clause on its own line
4. Indent column lists and conditions with consistent spacing
5. Align the keywords vertically (many developers align FROM, WHERE, ORDER BY with SELECT)
6. Always end a statement with a semicolon ;
7. Use one clause per line for readability

-- Well-formatted example
SELECT e.name            AS employee_name,
       e.dept,
       e.salary,
       e.salary * 12     AS annual_salary
FROM   employees e
WHERE  e.dept IN ('IT', 'Finance')
  AND  e.salary > 70000
ORDER BY e.dept ASC,
         e.salary DESC
LIMIT 10;

Putting It All Together — Complete Examples

-- Example 1: Top-earning IT employees with formatted output
SELECT name                        AS employee,
       salary,
       salary * 12                 AS annual_salary,
       'IT Department'             AS team
FROM   employees
WHERE  dept = 'IT'
ORDER BY salary DESC
FETCH FIRST 2 ROWS ONLY;

-- Example 2: Salary category for all employees
SELECT name,
       dept,
       salary,
       CASE
           WHEN salary >= 90000 THEN 'Top earner'
           WHEN salary >= 70000 THEN 'Mid earner'
           ELSE                      'Below average'
       END AS category
FROM   employees
ORDER BY salary DESC;

-- Example 3: Unique departments with employee count (preview of aggregates)
SELECT DISTINCT dept
FROM   employees
ORDER BY dept;

Common Errors

Interview Corner

SELECT last_name, commission_pct
FROM   employees
ORDER BY commission_pct ASC NULLS LAST;   -- NULLs at the bottom regardless

Related Topics

• Filtering & Conditions — narrow results with WHERE, BETWEEN, IN, LIKE, and NULL checks
• Aggregate Functions — COUNT, SUM, AVG, GROUP BY for summarising result sets
• Joins — combining columns from multiple tables with JOIN
• Subqueries — embedding one SELECT inside another