Imagine you could ask a warehouse full of information a single question and get a crisp, exact answer back in milliseconds. What you are imagining is not magic, it is SQL - Structured Query Language - the lingua franca of relational data. Over 50 years after Edgar F. Codd proposed the relational model, SQL remains the tool that turns messy data into clear decisions, from your phone app to multinational banks. If you want to shape the world with data, SQL is one of the sharpest tools you can master.
This course is a guided, linear journey from curiosity to competence. You will learn concepts, see concrete examples, practice with mini-challenges, and understand the real-life reasons why SQL behaves the way it does. Expect analogies, humor, and hands-on code. By the end you will feel confident reading, writing, and optimizing SQL for typical applications and analytics tasks.
At its heart SQL manipulates tables - rectangular grids of rows and columns. Think of a table as a spreadsheet that lives inside a database server and can be queried by many people at once. The relational model treats data as relations, where each row is a record and each column is an attribute. That model forces clarity: structure and rules reduce ambiguity, and SQL is the language that asks, filters, aggregates, and changes those relations.
Two important historical facts matter. First, Edgar F. Codd proposed the relational model in 1970, which is the intellectual foundation. Second, ANSI standardized SQL in the 1980s, so SQL commands are largely portable across database systems like PostgreSQL, MySQL, SQL Server, and SQLite. There are dialect differences, but the core concepts are stable. Keep in mind that practical SQL often involves learning a few system-specific features, but the concepts you learn here will transfer.
| Category | Purpose | Examples |
|---|---|---|
| DDL - Data Definition Language | Define or change database structure | CREATE TABLE, ALTER TABLE, DROP TABLE |
| DML - Data Manipulation Language | Query or modify data | SELECT, INSERT, UPDATE, DELETE |
| DCL - Data Control Language | Grant or revoke permissions | GRANT, REVOKE |
| TCL - Transaction Control Language | Manage transactions | BEGIN, COMMIT, ROLLBACK |
This table is your map - you will explore each category in practical depth as we progress. For now, remember that SELECT reads, INSERT/UPDATE/DELETE change, and CREATE/ALTER change structure.
You can practice SQL in many ways: install PostgreSQL locally, use SQLite for tiny projects, or run cloud-hosted notebooks. SQLite is excellent for beginning because it requires no server and uses standard SQL for most queries. PostgreSQL is a great next step because it is powerful, standards-compliant, and widely used in production. Pick one environment and stick with it while you learn, because the act of typing queries is where understanding forms.
Create a small practice dataset to make concepts concrete. Here is a tiny schema for a bookstore, which we will use throughout the course. Run these commands in your environment to create tables and insert data.
CREATE TABLE authors (
author_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
country TEXT
);
CREATE TABLE books (
book_id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
author_id INTEGER REFERENCES authors(author_id),
price NUMERIC(6,2),
published_date DATE
);
INSERT INTO authors (name, country) VALUES
('Ada Lovelace', 'United Kingdom'),
('Grace Hopper', 'United States');
INSERT INTO books (title, author_id, price, published_date) VALUES
('Analytical Engines', 1, 29.99, '1843-12-10'),
('COBOL for Humans', 2, 39.50, '1959-07-01');
Working with your own tiny datasets makes abstract ideas tangible. Keep this bookstore schema open - you will query it often.
SELECT is the most important SQL command. It asks questions and returns tables as answers. The simplest SELECT returns all rows and columns from a table, but the power comes from choosing columns, filtering rows, and transforming data. Practice is key: write many simple queries, read the results, and adjust.
Try these basic examples on the bookstore schema and experiment by changing column names and conditions.
-- Select all columns
SELECT * FROM books;
-- Select specific columns
SELECT title, price FROM books;
-- Filter rows
SELECT title, price FROM books WHERE price > 30;
-- Sort rows
SELECT title, price FROM books ORDER BY price DESC;
A quick mental model: SELECT chooses the columns, FROM chooses the table or tables, WHERE limits rows, ORDER BY sorts, and LIMIT (or FETCH) restricts count. These pieces compose like building blocks.
Filtering is where SELECT becomes specific. The WHERE clause accepts boolean expressions using equality, inequality, ranges, NULL checks, and pattern matching with LIKE. NULL means unknown, not zero or empty string, and it propagates in unexpected ways if you are not careful.
Here are examples and a reminder about NULL handling. Practice the examples and then try rewriting them to see different results.
-- Numeric and date filters
SELECT title FROM books WHERE price BETWEEN 20 AND 40;
SELECT title FROM books WHERE published_date >= '1900-01-01';
-- Pattern matching
SELECT title FROM books WHERE title LIKE '%Engines%';
-- NULL check
SELECT * FROM authors WHERE country IS NULL;
Reflective challenge - What happens if you write WHERE country != 'United Kingdom' and there are NULL countries present? Test and explain why NULL complicates inequality.
In real systems data lives in multiple tables, and joins combine them. There are common join types: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. An INNER JOIN returns rows that match in both tables. A LEFT JOIN returns all rows from the left table, with NULLs where the right table lacks matches. Joins are how you reconstruct rich, normalized data into flat answers.
This table summarizes the join behavior and a short code example shows how to list books with their authors.
| Join Type | What it returns |
|---|---|
| INNER JOIN | Only rows with matching keys in both tables |
| LEFT JOIN | All rows from left table, matched or NULL from right |
| RIGHT JOIN | All rows from right table, matched or NULL from left |
| FULL OUTER JOIN | All rows from both sides, NULLs where no match |
-- Books with authors (inner join)
SELECT b.title, a.name AS author
FROM books b
JOIN authors a ON b.author_id = a.author_id;
-- All books, even if author record missing
SELECT b.title, a.name AS author
FROM books b
LEFT JOIN authors a ON b.author_id = a.author_id;
Analogy: joins are like zipping two lists together - the zipper behavior changes based on which list you insist on keeping. Small challenge - write a query to find authors who have not published any books by using a LEFT JOIN and WHERE b.book_id IS NULL.
When you want roll-ups, counts, sums, averages, or other summaries, GROUP BY is the tool. Grouping collapses multiple rows into summary rows by an attribute and applies aggregate functions like COUNT, SUM, AVG, MIN, MAX. Use HAVING to filter groups after aggregation.
Here is an example that shows book counts by author and filters to authors with more than zero books.
SELECT a.name, COUNT(b.book_id) AS books_count, AVG(b.price) AS avg_price
FROM authors a
LEFT JOIN books b ON a.author_id = b.author_id
GROUP BY a.name
HAVING COUNT(b.book_id) > 0;
Important misconception to correct - you cannot SELECT non-aggregated columns that are not part of GROUP BY in standard SQL. Some databases allow extensions, but rely on grouping rules to avoid undefined results. Practice question - modify the query to show authors with no books by adjusting the HAVING clause.
Subqueries are queries inside queries. They can appear in SELECT, FROM, or WHERE. Common Table Expressions - CTEs declared with WITH - make long queries readable by naming intermediate results. Set operations like UNION combine result sets vertically. These tools let you decompose complex questions into understandable steps.
Example using a CTE to find authors with the most expensive book each.
WITH max_book AS (
SELECT author_id, MAX(price) AS max_price
FROM books
GROUP BY author_id
)
SELECT a.name, b.title, b.price
FROM books b
JOIN max_book m ON b.author_id = m.author_id AND b.price = m.max_price
JOIN authors a ON a.author_id = b.author_id;
Reflective exercise - rewrite the CTE as a subquery in the FROM clause and compare readability. Which version feels clearer and why?
Window functions compute values across rows related to the current row while preserving each row. They are perfect for running totals, ranks, and moving averages. Unlike aggregation, window functions do not collapse rows, making them ideal for analytics where you need group context beside individual rows.
Here is a simple example ranking books by price per author.
SELECT title, author_id, price,
RANK() OVER (PARTITION BY author_id ORDER BY price DESC) AS price_rank
FROM books;
Analogy: window functions are like looking out a window at your neighborhood - you see the current house but also the surrounding block. Try a challenge - compute a 3-book moving average of price ordered by published_date using AVG() OVER with ROWS BETWEEN.
When you insert, update, or delete, you alter the state of your database. Transactions group multiple DML statements into atomic units using BEGIN or START TRANSACTION, COMMIT, and ROLLBACK. ACID properties - Atomicity, Consistency, Isolation, Durability - are the backbone for reliable changes. Understanding transactions prevents data corruption and race conditions in concurrent environments.
Example of an atomic transfer pattern, adapted to our bookstore context and intentionally simple.
BEGIN;
UPDATE books SET price = price * 0.9 WHERE book_id = 1;
INSERT INTO books_log (book_id, change, changed_at) VALUES (1, 'price 10% off', NOW());
COMMIT;
Ask yourself - if something fails after the UPDATE but before the INSERT, what will happen if you do not use a transaction? Practice experiment: perform updates with and without transactions and observe partial changes.
Indexes are data structures that speed lookups, like the index in a book. They improve SELECT performance but cost extra storage and slower writes. The query planner decides how to execute your query and uses indexes if they help. Understanding explain plans is critical to diagnosing slow queries.
This table summarizes trade-offs.
| Benefit | Cost |
|---|---|
| Faster reads on indexed columns | Slower inserts/updates, extra storage |
| Can support ORDER BY and JOIN performance | Poor choice of index can mislead planner |
Use EXPLAIN or EXPLAIN ANALYZE in your database to inspect plans. Start by creating an index on author_id and observe the performance change on large datasets. Small challenge - create the following index and run an EXPLAIN to see whether it is used.
CREATE INDEX idx_books_author ON books(author_id);
EXPLAIN SELECT * FROM books WHERE author_id = 1;
Normalization is the process of designing tables to reduce redundancy and avoid anomalies. Normal forms (1NF, 2NF, 3NF) help structure data logically. However, normalization is not a dogma. For analytics and read-heavy workloads, denormalization or precomputed tables sometimes win on performance. The rule of thumb is to normalize for correctness and denormalize for performance only when justified by measurement.
Case study - An e-commerce site initially normalized customer and order items across several tables, which slowed analytics queries. The team implemented a nightly materialized view that pre-aggregated order totals, dramatically speeding reporting while keeping transactional data normalized. This hybrid approach combined the virtues of correctness and speed.
Reflective question - How would you model a "tags" relationship for books where each book can have multiple tags? Try designing the tables and explain why you would use a join table.
Developers often assume NULL behaves like a value and write WHERE column != 'x' expecting NULLs to be excluded. They also forget to index join keys, select *, or perform updates without a WHERE and accidentally modify all rows. Another myth is that SQL is only for databases - in fact, SQL-like query languages power many tools including big data engines and spreadsheets.
Here is a compact list of practical tips you will thank me for later:
A small humor: treat NULL like a shy guest at a party - they are neither absent nor present, and if you ignore them you will miscount.
Project 1 - Build a reporting view: create a view that lists authors, total books, and average price, and expose it to an analytics app. Project 2 - Implement a paginated API: write queries that return page X of books sorted by published_date using LIMIT and OFFSET, then optimize with keyset pagination. Project 3 - Diagnose slow queries: load a larger synthetic dataset and use EXPLAIN to find slow joins, then add appropriate indexes.
Each project reinforces practical skills - schema design, query composition, performance analysis, and integration with apps. Real-world practice builds intuition faster than theoretical study alone, because databases have unpredictable data patterns and edge cases.
| Task | Command example |
|---|---|
| List tables (Postgres) | \dt |
| Show schema of a table | \d+ books |
| Explain a query | EXPLAIN ANALYZE SELECT ... |
| Add index | CREATE INDEX idx_name ON table(column); |
| Start transaction | BEGIN; COMMIT; ROLLBACK; |
Quote to remember: "In data work, clarity beats cleverness." Keep queries readable, and prefer correctness over clever micro-optimizations until you measure bottlenecks.
Practice daily with small problems, contribute to open source projects that use SQL, and read books like "SQL Antipatterns" by Bill Karwin to learn what to avoid. Explore advanced topics such as partitioning, materialized views, procedural SQL (PL/pgSQL or T-SQL), and columnar stores for analytics. Follow community signals, surveys such as the Stack Overflow Developer Survey consistently show SQL among the most-used and essential skills, so learning pays off across industries.
Final challenge - build a small analytics dashboard that answers three business questions about your bookstore (for example, best-selling authors, monthly revenue, and price distribution). Use CTEs, window functions, and an index to make the queries responsive. Share your queries with a colleague or in a study group and explain your design choices.
SQL will reward your curiosity. As you practice, you will move from writing queries by trial and error to composing precise, efficient expressions of data intent. Keep experiments small, measure impact, and remember that good data design is both an art and a science. Return to this course when you need a refresher, and enjoy the satisfying clarity that comes from turning rows into insight.
Happy querying - may your joins be correct, your indexes timely, and your NULLs treated with respect.
Data Science & Analytics
What you will learn in this nib : You'll learn to confidently read, write, and optimize SQL queries to filter, join, aggregate, and transform relational data, use CTEs and window functions, manage transactions safely, design practical schemas and indexes, and apply these skills in hands-on projects to answer real-world questions.