SQL Tutorial: From Basics to Advanced Queries

What Is SQL?

SQL (Structured Query Language) is the standard language for managing and querying relational databases. Created in the 1970s, SQL has remained remarkably relevant for over five decades, powering everything from simple web applications to massive enterprise data warehouses. Every data professional—whether analyst, engineer, or scientist—needs SQL proficiency.

SQL is used with relational database management systems (RDBMS) like PostgreSQL, MySQL, Microsoft SQL Server, Oracle, and SQLite. While each system has minor syntax differences, the core SQL language is consistent across all of them.

SQL Basics: CRUD Operations

Creating Tables

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    hire_date DATE,
    is_active BOOLEAN DEFAULT TRUE
);

Inserting Data

INSERT INTO employees (first_name, last_name, email, department, salary, hire_date)
VALUES ('Alice', 'Johnson', '[email protected]', 'Engineering', 95000, '2024-03-15');

-- Insert multiple rows
INSERT INTO employees (first_name, last_name, email, department, salary, hire_date)
VALUES 
    ('Bob', 'Smith', '[email protected]', 'Marketing', 75000, '2023-07-01'),
    ('Carol', 'Williams', '[email protected]', 'Engineering', 105000, '2022-01-10');

Querying Data

-- Select all columns
SELECT * FROM employees;

-- Select specific columns
SELECT first_name, last_name, salary FROM employees;

-- Filter with WHERE
SELECT * FROM employees WHERE department = 'Engineering';

-- Multiple conditions
SELECT * FROM employees 
WHERE department = 'Engineering' AND salary > 90000;

-- Pattern matching
SELECT * FROM employees WHERE last_name LIKE 'J%';

-- Sorting
SELECT * FROM employees ORDER BY salary DESC;

-- Limiting results
SELECT * FROM employees ORDER BY salary DESC LIMIT 10;

Updating Data

UPDATE employees SET salary = 100000 WHERE id = 1;

UPDATE employees SET is_active = FALSE WHERE hire_date < '2020-01-01';

Deleting Data

DELETE FROM employees WHERE id = 5;

-- Always use WHERE with DELETE to avoid removing all rows

Intermediate SQL: Joins and Aggregations

JOIN Operations

Joins combine rows from two or more tables based on related columns. Understanding joins is essential for working with normalized databases.

-- INNER JOIN: Only matching rows from both tables
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- LEFT JOIN: All rows from left table, matching from right
SELECT e.first_name, o.order_total
FROM employees e
LEFT JOIN orders o ON e.id = o.employee_id;

-- RIGHT JOIN: All rows from right table, matching from left
-- FULL OUTER JOIN: All rows from both tables

Aggregation Functions

-- Count, Sum, Average, Min, Max
SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department;

-- Filter aggregated results with HAVING
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 80000;

Advanced SQL Techniques

Subqueries

-- Find employees earning above average
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Correlated subquery
SELECT e.first_name, e.salary, e.department
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary) FROM employees 
    WHERE department = e.department
);

Common Table Expressions (CTEs)

CTEs make complex queries more readable by defining temporary named result sets:

WITH department_stats AS (
    SELECT 
        department,
        AVG(salary) AS avg_salary,
        COUNT(*) AS emp_count
    FROM employees
    GROUP BY department
)
SELECT 
    e.first_name,
    e.salary,
    ds.avg_salary,
    e.salary - ds.avg_salary AS salary_diff
FROM employees e
JOIN department_stats ds ON e.department = ds.department
ORDER BY salary_diff DESC;

Window Functions

Window functions perform calculations across rows related to the current row without collapsing the result set:

-- Rank employees by salary within each department
SELECT 
    first_name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_rank
FROM employees;

-- Running total
SELECT 
    order_date,
    order_total,
    SUM(order_total) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- Moving average
SELECT 
    order_date,
    order_total,
    AVG(order_total) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7day
FROM orders;

Indexing for Performance

Indexes dramatically improve query performance on large tables by creating efficient lookup structures:

-- Create an index on frequently queried columns
CREATE INDEX idx_department ON employees(department);

-- Composite index for multi-column queries
CREATE INDEX idx_dept_salary ON employees(department, salary);

-- Unique index
CREATE UNIQUE INDEX idx_email ON employees(email);

Use indexes on columns that appear in WHERE clauses, JOIN conditions, and ORDER BY clauses. However, each index adds overhead to write operations, so index strategically.

SQL Best Practices

Use meaningful aliases — e for employees, o for orders, not t1 and t2
Avoid SELECT * — Specify only the columns you need for better performance and clarity
Use parameterized queries — Never concatenate user input directly into SQL strings to prevent SQL injection attacks
Write readable queries — Use proper indentation, line breaks, and capitalization for SQL keywords
Test with EXPLAIN — Use EXPLAIN or EXPLAIN ANALYZE to understand query execution plans and identify bottlenecks
Use transactions — Wrap related operations in transactions to maintain data consistency

SQL in the Modern Data Stack

SQL's relevance extends far beyond traditional databases. Modern tools like dbt use SQL for data transformations, BigQuery and Snowflake use SQL for cloud analytics, and even stream processing engines like ksqlDB bring SQL syntax to real-time data.

Conclusion

SQL is one of the most valuable and enduring skills in technology. From basic CRUD operations to advanced window functions and CTEs, mastering SQL opens doors to data analysis, data engineering, backend development, and data science. Start with the fundamentals, practice on real datasets, and progressively tackle more complex queries. The investment in SQL skills pays dividends across your entire career.

SQL Tutorial: From Basics to Advanced