Introduction to SQL

SQL (Structured Query Language) is the universal language for talking to databases. Whether you are building a web application, analysing business data, or working with any kind of stored information, SQL is the tool you will reach for. This chapter explains what SQL is, how databases work, and gives you your first real queries.

What is SQL and Why Does It Matter?

Imagine you own a library with millions of books. You need to:

• Find all books by a specific author
• List books published after 2010, sorted by title
• Count how many books are in each genre
• Update a book's status when it is checked out

Without a structured way to do this, you would have to manually search through rows of a spreadsheet — slow, error-prone, and impossible at scale. SQL solves this. It lets you express what data you want in plain, English-like sentences, and the database engine figures out the fastest way to retrieve it.

SQL was developed by IBM researchers in the early 1970s, became an ANSI/ISO standard in 1987, and today powers virtually every application that stores data — from small mobile apps to the largest enterprise systems in the world.

What is a Relational Database?

Think of a relational database as a collection of spreadsheets that can talk to each other.

In a spreadsheet you have:

• A sheet (tab) — like a table in a database
• Column headers — like column definitions
• Rows of data — like records

The key difference is that in a relational database, tables can be linked together through shared values. This avoids storing the same information in multiple places (known as data redundancy) and makes the data consistent.

Spreadsheet analogy:

Notice that "IT" and "London" are repeated for Bob and Carol. If the IT department moves to Paris, you have to update every row. In a relational database, you split this into two tables:

employees table:

departments table:

Now if IT moves to Paris, you update one row in departments and every employee automatically reflects the change.

Tables, Rows, Columns — The Building Blocks

Every relational database stores data in tables. Here is what each term means:

Table — A named collection of related data, like a spreadsheet tab. Examples: employees, orders, products.

Column (Field) — A named attribute that defines what type of data is stored. Every column has a data type (text, number, date, etc.). Examples: name, salary, hire_date.

Row (Record) — One complete entry in the table. A row is one employee, one order, one product.

Cell — The intersection of a row and a column — a single piece of data.

TABLE: employees
┌─────────┬───────┬──────┬────────┐
│ emp_id  │ name  │ dept │ salary │   ← COLUMNS (define what data goes here)
├─────────┼───────┼──────┼────────┤
│ 1       │ Alice │ HR   │ 60000  │   ← ROW (one complete record)
│ 2       │ Bob   │ IT   │ 85000  │   ← ROW
│ 3       │ Carol │ IT   │ 92000  │   ← ROW
└─────────┴───────┴──────┴────────┘
    ↑
 PRIMARY KEY column (unique identifier)

Primary Keys and Foreign Keys

Primary Key — A column (or set of columns) whose value uniquely identifies each row. Think of it as the row's ID card number — no two rows can have the same primary key, and it can never be blank (NULL).

Rules:

• Must be unique across every row
• Cannot be NULL (empty)
• One primary key per table (though it can span multiple columns)

Foreign Key — A column in one table that holds the primary key value of a row in another table. It is the link that connects two tables.

employees.dept_id  →  departments.dept_id
     (foreign key)         (primary key)

This connection is called referential integrity: the foreign key value must always match a real primary key in the referenced table. You cannot assign an employee to a department that does not exist.

SQL vs NoSQL — Which Is Which?

You will often hear "NoSQL" mentioned alongside SQL databases. Here is a clear comparison:

The 5 Types of SQL Commands

SQL commands are grouped into five categories based on what they do. You will eventually use all of them.

DDL — Data Definition Language

DDL commands define and change the structure of the database — creating tables, modifying columns, dropping objects.

-- Create a new table
CREATE TABLE departments (
    dept_id   INT          PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL,
    location  VARCHAR(100)
);

-- Add a column to an existing table
ALTER TABLE departments ADD budget DECIMAL(12,2);

-- Remove a table permanently
DROP TABLE old_archive;

-- Remove all rows but keep the table structure
TRUNCATE TABLE temp_staging;

DML — Data Manipulation Language

DML commands work with the data inside tables — adding, changing, removing rows.

-- Insert a new employee
INSERT INTO employees (emp_id, name, dept_id, salary)
VALUES (1, 'Alice', 10, 60000);

-- Give everyone in IT a 10% raise
UPDATE employees
SET    salary = salary * 1.10
WHERE  dept_id = 20;

-- Remove employees who left before 2020
DELETE FROM employees
WHERE  end_date < '2020-01-01';

DQL — Data Query Language

DQL retrieves data. The entire DQL category is just one command: SELECT — but it is the most powerful and complex SQL command.

-- Retrieve names and salaries of all employees
SELECT name, salary
FROM   employees
ORDER BY salary DESC;

DCL — Data Control Language

DCL controls who can do what in the database. Administrators use these to grant or revoke access.

-- Allow user 'john' to read the employees table
GRANT SELECT ON employees TO john;

-- Allow 'jane' to insert and update orders
GRANT INSERT, UPDATE ON orders TO jane;

-- Remove john's read access
REVOKE SELECT ON employees FROM john;

TCL — Transaction Control Language

A transaction is a group of SQL statements that should all succeed or all fail together. Think of a bank transfer: debit one account AND credit another — if one fails, the other must be undone.

-- Begin a transaction (implicit in most databases)
BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

-- If everything is fine, save the changes permanently
COMMIT;

-- If something went wrong, undo all changes since BEGIN
ROLLBACK;

-- Set a named checkpoint you can roll back to
SAVEPOINT before_update;
UPDATE employees SET salary = 99999 WHERE dept_id = 10;
ROLLBACK TO SAVEPOINT before_update;  -- undo just the update above

Popular Databases — A Comparison

Data Types — What Can a Column Store?

Every column in a table has a data type that defines what kind of value it can hold. Choosing the right type matters for storage efficiency, performance, and data validation.

Numeric Types

Text Types

Date and Time Types

Other Types

-- Example table showing common data type choices
CREATE TABLE employees (
    emp_id      INT              PRIMARY KEY,    -- whole number ID
    name        VARCHAR(100)     NOT NULL,       -- up to 100 chars
    email       VARCHAR(200)     UNIQUE,         -- email address
    salary      DECIMAL(10,2),                  -- e.g. 75000.00
    hire_date   DATE,                           -- e.g. 2021-03-15
    is_active   BOOLEAN,                        -- true/false
    notes       TEXT                            -- long free-form text
);

Your First SQL Query — Line by Line

Let us read your first real SQL query carefully. Assume this employees table exists:

-- Query: Find IT department employees earning above 80,000
SELECT name,           -- Which columns do you want to see?
       salary          -- You can list as many as you need
FROM   employees       -- Which table holds the data?
WHERE  dept = 'IT'     -- Filter: only rows where dept equals 'IT'
AND    salary > 80000  -- AND another filter: salary must exceed 80000
ORDER BY salary DESC;  -- Sort results: highest salary first

Breaking it down:

• SELECT name, salary — specifies exactly which columns appear in the result
• FROM employees — tells the database which table to read from
• WHERE dept = 'IT' — a filter; only rows where this condition is TRUE are included
• AND salary > 80000 — an additional filter; both conditions must be true
• ORDER BY salary DESC — sort the result by salary, largest first (DESC = descending)
• ; — the semicolon marks the end of the statement (required in many tools)

Result:

How SQL is Executed — The Logical Order of Clauses

One of the most important things to understand about SQL is that clauses are written in one order but executed in a different order. Many beginners are confused when they try to use a column alias in a WHERE clause and get an error — this is why.

Written order (how you type it):

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

Logical execution order (how the database processes it):

1. FROM        — identify the source table(s)
2. WHERE       — filter individual rows
3. GROUP BY    — group the remaining rows
4. HAVING      — filter the groups
5. SELECT      — calculate the output columns and aliases
6. ORDER BY    — sort the result
7. LIMIT       — restrict the number of output rows

This means:

• You cannot use a SELECT alias in the WHERE clause (WHERE runs before SELECT)
• You can use a SELECT alias in ORDER BY (ORDER BY runs after SELECT)
• HAVING can use aggregate functions; WHERE cannot

-- This is WRONG — alias used in WHERE (runs before SELECT)
SELECT salary * 12 AS annual_salary
FROM   employees
WHERE  annual_salary > 900000;   -- ERROR: column "annual_salary" does not exist

-- This is CORRECT — repeat the expression in WHERE
SELECT salary * 12 AS annual_salary
FROM   employees
WHERE  salary * 12 > 900000;

-- ORDER BY can use aliases (it runs after SELECT)
SELECT salary * 12 AS annual_salary
FROM   employees
ORDER BY annual_salary DESC;     -- This works fine

How to Read SQL Error Messages

When a SQL query fails, the database gives you an error message. Learning to read these is a crucial skill.

Common error types:

ORA-00942: table or view does not exist

You referenced a table name that does not exist, is misspelled, or is in a different schema.

ORA-00904: "ANNUAL_SALARY": invalid identifier

You used a column name or alias that the database cannot see at that point in the query. Usually caused by using a SELECT alias in a WHERE clause.

ORA-01858: a non-numeric character was found where a numeric was expected

You tried to insert or compare a text value where a number is expected — for example WHERE salary = 'lots'.

ERROR: syntax error at or near "FORM"

A typo in a keyword (FORM instead of FROM). Check the word just before the error location.

ORA-00001: unique constraint violated

You tried to insert a duplicate value into a PRIMARY KEY or UNIQUE column.

Debugging strategy:

1. Read the error message carefully — it usually tells you the line number or the exact word where SQL got confused
2. Check for typos in table and column names (they are case-insensitive in most databases, but must exist)
3. Check that string values use single quotes, not double quotes
4. Check clause order — SELECT must come before FROM, WHERE must come before GROUP BY

Putting It All Together — Your First 5 Queries

-- 1. See everything in a table (exploration query)
SELECT * FROM employees;

-- 2. See specific columns only
SELECT name, salary, hire_date
FROM   employees;

-- 3. Filter rows by condition
SELECT name, salary
FROM   employees
WHERE  dept = 'IT';

-- 4. Sort results
SELECT name, dept, salary
FROM   employees
ORDER BY dept ASC, salary DESC;

-- 5. Combine filtering and sorting
SELECT name, salary
FROM   employees
WHERE  salary > 70000
ORDER BY salary DESC;

These five patterns will cover a large proportion of your daily SQL work. Each subsequent chapter builds on these foundations — adding more powerful filtering, grouping, joining multiple tables, and beyond.

Common Errors

Interview Corner

Related Topics

• Basic SQL — the SELECT statement in depth: columns, aliases, DISTINCT, ORDER BY
• Filtering & Conditions — WHERE clause operators, NULL handling, pattern matching
• Aggregate Functions — COUNT, SUM, AVG, GROUP BY, HAVING
• DDL Commands — CREATE, ALTER, DROP, TRUNCATE
• DML Commands — INSERT, UPDATE, DELETE, MERGE