What is SQL? A Complete Guide to Structured Query Language

What is SQL?

SQL (Structured Query Language) is a standardized programming language used to manage and manipulate relational databases. It allows you to create, read, update, and delete data — commonly known as CRUD operations — as well as define database schemas, control access, and manage transactions.

SQL is declarative, meaning you describe what data you want rather than howto retrieve it. The database engine's query optimizer figures out the most efficient way to execute your query. This differs from imperative languages like JavaScript or Python where you write step-by-step instructions.

Despite being over 50 years old, SQL remains the de facto standard for data manipulation. It's used by virtually every relational database system and is one of the most in-demand skills across software engineering, data analysis, data science, and business intelligence.

A Brief History of SQL

SQL's story begins with Edgar F. Codd's groundbreaking 1970 paper "A Relational Model of Data for Large Shared Data Banks," which introduced the concept of organizing data into tables (relations) with rows and columns. IBM researchers Donald Chamberlin and Raymond Boyce then created SEQUEL(Structured English Query Language) in 1974 to interact with Codd's relational model.

Relational Databases Explained

A relational database organizes data into tables (also called relations). Each table has:

• Columns (attributes) — define the type of data (name, email, price)
• Rows (records/tuples) — each row is one entry
• Primary Key — a column (or combination) that uniquely identifies each row
• Foreign Key — a column that references a primary key in another table, creating relationships

Table: users
┌────┬───────────┬─────────────────────┬────────────┐
│ id │ name      │ email               │ created_at │
├────┼───────────┼─────────────────────┼────────────┤
│  1 │ Alice     │ alice@example.com   │ 2025-01-15 │
│  2 │ Bob       │ bob@example.com     │ 2025-02-20 │
│  3 │ Charlie   │ charlie@example.com │ 2025-03-10 │
└────┴───────────┴─────────────────────┴────────────┘

Table: orders
┌────┬─────────┬─────────┬────────────┐
│ id │ user_id │ total   │ order_date │
├────┼─────────┼─────────┼────────────┤
│  1 │       1 │  49.99  │ 2025-03-01 │
│  2 │       1 │  29.50  │ 2025-03-15 │
│  3 │       2 │ 199.00  │ 2025-03-20 │
└────┴─────────┴─────────┴────────────┘

orders.user_id → users.id  (foreign key relationship)

This relational structure eliminates data duplication. Instead of repeating Alice's name and email in every order, you store her info once in the users table and reference her id in the orders table. This is called normalization.

SQL Syntax Basics

SQL statements are divided into several categories:

SQL keywords are case-insensitive (SELECT, select, and Selectall work), but it's conventional to write them in uppercase for readability. Table and column names may be case-sensitive depending on the database system and operating system.

CRUD Operations

The four fundamental database operations — Create, Read, Update, Delete — map directly to SQL statements:

CREATE TABLE & INSERT (Create)

-- Define a table
CREATE TABLE products (
    id         INT PRIMARY KEY AUTO_INCREMENT,
    name       VARCHAR(100) NOT NULL,
    price      DECIMAL(10,2) NOT NULL,
    category   VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert a single row
INSERT INTO products (name, price, category)
VALUES ('Wireless Mouse', 29.99, 'Electronics');

-- Insert multiple rows
INSERT INTO products (name, price, category)
VALUES
    ('Keyboard', 49.99, 'Electronics'),
    ('Desk Lamp', 34.50, 'Office'),
    ('Notebook', 5.99, 'Stationery');

SELECT (Read)

-- Select all columns
SELECT * FROM products;

-- Select specific columns
SELECT name, price FROM products;

-- Select with alias
SELECT name AS product_name,
       price * 1.1 AS price_with_tax
FROM products;

UPDATE (Update)

-- Update a single record
UPDATE products
SET price = 24.99
WHERE id = 1;

-- Update multiple columns
UPDATE products
SET price = price * 0.9,
    category = 'Sale'
WHERE category = 'Electronics';

DELETE (Delete)

-- Delete specific rows
DELETE FROM products
WHERE id = 3;

-- Delete all rows (use with caution!)
DELETE FROM products;

-- TRUNCATE is faster for deleting all rows
-- (resets auto-increment, cannot be rolled back in some DBs)
TRUNCATE TABLE products;

Filtering, Sorting & Aggregation

WHERE — Filtering Rows

-- Comparison operators
SELECT * FROM products WHERE price > 20;
SELECT * FROM products WHERE category = 'Electronics';
SELECT * FROM products WHERE price BETWEEN 10 AND 50;

-- Pattern matching
SELECT * FROM products WHERE name LIKE 'Wire%';   -- starts with "Wire"
SELECT * FROM products WHERE name LIKE '%mouse%';  -- contains "mouse"

-- NULL checks
SELECT * FROM products WHERE category IS NULL;
SELECT * FROM products WHERE category IS NOT NULL;

-- Logical operators
SELECT * FROM products
WHERE price < 30
  AND category = 'Electronics';

SELECT * FROM products
WHERE category IN ('Electronics', 'Office');

ORDER BY — Sorting

-- Sort ascending (default)
SELECT * FROM products ORDER BY price;

-- Sort descending
SELECT * FROM products ORDER BY price DESC;

-- Multiple sort columns
SELECT * FROM products ORDER BY category, price DESC;

Aggregate Functions & GROUP BY

-- Common aggregate functions
SELECT
    COUNT(*) AS total_products,
    AVG(price) AS avg_price,
    MIN(price) AS cheapest,
    MAX(price) AS most_expensive,
    SUM(price) AS total_value
FROM products;

-- GROUP BY — aggregate per category
SELECT
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products
GROUP BY category;

-- HAVING — filter groups (like WHERE, but for aggregates)
SELECT
    category,
    COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) >= 2;

JOINs — Combining Tables

JOINs are one of SQL's most powerful features, allowing you to combine rows from two or more tables based on a related column.

-- INNER JOIN: users who have placed orders
SELECT
    u.name,
    u.email,
    o.id AS order_id,
    o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

-- LEFT JOIN: all users, including those with no orders
SELECT
    u.name,
    COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.name;

-- Multi-table JOIN
SELECT
    u.name,
    o.id AS order_id,
    p.name AS product_name,
    oi.quantity
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id;

Subqueries & CTEs

A subquery is a query nested inside another query. A CTE (Common Table Expression) is a named temporary result set that makes complex queries more readable.

-- Subquery in WHERE
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Subquery in FROM
SELECT category, avg_price
FROM (
    SELECT category, AVG(price) AS avg_price
    FROM products
    GROUP BY category
) AS category_stats
WHERE avg_price > 30;

-- CTE (Common Table Expression) — cleaner alternative
WITH category_stats AS (
    SELECT category, AVG(price) AS avg_price
    FROM products
    GROUP BY category
)
SELECT category, avg_price
FROM category_stats
WHERE avg_price > 30;

-- Recursive CTE — e.g., org chart hierarchy
WITH RECURSIVE org_tree AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY level;

Indexes & Performance

An index is a data structure (typically a B-tree) that speeds up data retrieval at the cost of additional storage and slower writes. Without an index, the database must scan every row in a table (a full table scan) to find matches.

-- Create an index on a frequently-queried column
CREATE INDEX idx_products_category ON products(category);

-- Composite index (for queries filtering on multiple columns)
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- Unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Check query performance with EXPLAIN
EXPLAIN SELECT * FROM products WHERE category = 'Electronics';

Transactions & ACID

A transaction is a sequence of SQL operations that are executed as a single unit of work. Transactions follow the ACID properties:

-- Transfer $100 from account A to account B
BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If both succeed:
COMMIT;

-- If something goes wrong:
-- ROLLBACK;

Without transactions, a system crash between the two UPDATE statements could deduct money from one account without adding it to the other. Transactions guarantee that either both updates happen or neither does.

SQL Dialects Compared

While SQL is standardized by ANSI/ISO, every database engine adds its own extensions and has minor syntax differences. Here's how the major dialects compare:

These dialect differences are why formatting your SQL consistently matters — especially when working across multiple database systems. A good SQL formatter can standardize your queries regardless of dialect.

SQL Injection & Security

SQL injection is one of the most dangerous and common web security vulnerabilities. It occurs when user input is directly concatenated into SQL queries, allowing an attacker to execute arbitrary SQL code.

-- ❌ VULNERABLE: string concatenation
query = "SELECT * FROM users WHERE name = '" + userInput + "'";

-- If userInput = "'; DROP TABLE users; --"
-- The query becomes:
-- SELECT * FROM users WHERE name = ''; DROP TABLE users; --'
-- This deletes the entire users table!

-- ✅ SAFE: parameterized query (prepared statement)
query = "SELECT * FROM users WHERE name = ?";
db.execute(query, [userInput]);

-- The database treats userInput as a value, never as SQL code.

Additional security best practices include using the principle of least privilege (grant only the permissions each user needs), keeping your database software updated, encrypting connections with TLS, and auditing access logs regularly.

Best Practices

• Format your SQL consistently — use uppercase keywords, consistent indentation, and one clause per line. This makes queries readable and maintainable. Use a SQL formatter tool to automate this.
• Use meaningful names — name tables and columns descriptively (e.g., order_date instead of od or col3).
• Avoid SELECT * — specify only the columns you need. This reduces network traffic, improves performance, and makes your intent clear.
• Add indexes strategically — index columns used in WHERE, JOIN, and ORDER BY. Use EXPLAIN to verify effectiveness.
• Use transactions for related changes — wrap multi-step operations in BEGIN/COMMIT to maintain data integrity.
• Always use parameterized queries — never concatenate user input into SQL to prevent injection attacks.
• Normalize your data — eliminate redundancy by splitting data into related tables. Denormalize only when you have measured performance needs.
• Comment complex queries — use -- for single-line and /* */ for multi-line comments to explain the "why" behind complex logic.
• Test with EXPLAIN — before deploying, check query plans to catch full table scans and missing indexes.
• Back up regularly — use automated backups and test your restore process periodically.

SQL vs NoSQL

NoSQL databases (MongoDB, Redis, Cassandra, DynamoDB) emerged to handle use cases where relational databases are less ideal. Here's how they compare:

In practice, many modern applications use both — SQL for transactional data (users, orders, inventory) and NoSQL for caching (Redis), full-text search (Elasticsearch), or document storage (MongoDB). The choice depends on your data structure, consistency requirements, and scale needs.