SQL and Databases

What databases are and why apps need them, how tables, rows, columns and data types work (and how SQLite differs), primary and foreign keys, CREATE TABLE with constraints, and the families of SQL statements. After this module you can read any table in the practice databases, design a small table with the right types and rules, and explain how tables link to each other.

1. What a database isData is facts, like a name or a price. A database is an organised store of data, and a DBMS (database management system) is the software that keeps it safe and answers questions about it. Apps like IRCTC, banks and school portals all keep their data in a database and talk to it in SQL.25 min
2. Tables, rows, columns and data typesA table holds one kind of thing, each row is one item, and each column is one detail with a data type such as INTEGER, DECIMAL, VARCHAR, DATE or BOOLEAN. SQLite is relaxed about types: it stores dates as text like '2025-01-05' and booleans as 1 and 0, and typeof() tells you what was really stored.30 min
3. Keys and relationshipsA primary key identifies exactly one row; a foreign key is a column that points to a primary key in another table. Tables relate one-to-one, one-to-many or many-to-many, and a many-to-many link needs a third, junction table such as order_items. In SQLite you must run PRAGMA foreign_keys = ON before foreign keys are enforced.35 min
4. CREATE TABLE and constraintsCREATE TABLE makes a new table and sets the rules (constraints) every row must follow: NOT NULL, UNIQUE, CHECK, DEFAULT, PRIMARY KEY and FOREIGN KEY. The database rejects any row that breaks a rule. ALTER TABLE changes a table later, and DROP TABLE deletes it with all its data, so use it with great care.35 min
5. The SQL language and your toolsSQL is a standard language with small differences (dialects) in each database product. Its statements fall into families: DDL defines tables, DML changes rows, DQL reads them, DCL controls access and TCL controls transactions. You need only this site to practise; PostgreSQL, MySQL and DBeaver are free when you want a real install.25 min

Choose and compute columns with SELECT, filter rows with WHERE (comparisons, AND, OR, NOT, IN, BETWEEN, LIKE and IS NULL), sort and page results with ORDER BY, LIMIT and OFFSET, and remove repeats with DISTINCT. After this module you can answer everyday questions from one table, such as "the 3 most expensive kitchen products" or "customers in Pune with no email", and explain the order in which a query runs.

1. SELECT, columns, aliases and expressionsAfter SELECT you list the columns you want, in the order you want them. You can also compute new columns with expressions such as price * qty or price * 1.18, round them with ROUND, and name them with AS. In real apps, list the columns you need instead of SELECT *.30 min
2. WHERE: comparison and logicWHERE keeps only the rows for which a condition is true. Conditions use the comparison operators =, <> (or !=), <, >, <= and >=, and are combined with AND, OR and NOT. AND is worked out before OR, so put brackets around OR conditions, or you get a classic bug.30 min
3. IN, BETWEEN, LIKE and NULLIN checks a value against a list, BETWEEN checks a range including both ends, and LIKE matches text patterns with % (any number of characters) and _ (exactly one). NULL means unknown: = NULL never matches, so you must write IS NULL or IS NOT NULL.35 min
4. ORDER BY, LIMIT and OFFSETWithout ORDER BY, rows come back in no promised order. ORDER BY sorts by one or more columns, each ASC (smallest first, the default) or DESC. LIMIT keeps the first n rows and OFFSET skips some, which gives top-N lists and pages. SQLite puts NULLs first in ascending order unless you write NULLS LAST.30 min
5. DISTINCT and how a query runsDISTINCT removes repeated rows from the result, judging the whole row of selected columns. A query is written SELECT, FROM, WHERE, ORDER BY, LIMIT, but it is worked out in a different order: FROM, WHERE, SELECT, DISTINCT, ORDER BY, LIMIT. That order explains why an alias works in ORDER BY but not in WHERE.30 min

Clean and take apart text with UPPER, LOWER, TRIM, SUBSTR, INSTR, REPLACE and ||; do correct maths (integer division, CAST, ROUND, %); work with ISO dates using date(), strftime() and julianday(); label and sort rows with CASE; and handle missing values with COALESCE and NULLIF. After this module you can turn raw columns into clean, report-ready values, such as an email domain, a price with GST, an order month, a grade or a safe percentage.

1. Text functionsText functions change or take apart text inside a query: UPPER and LOWER change case, LENGTH counts characters, TRIM removes spaces, SUBSTR cuts out a piece, INSTR finds where a character is, REPLACE swaps text and || joins text. Together they clean messy names and emails and pull out parts such as an email domain.35 min
2. Numbers and mathsSQL does arithmetic with + − * / and % (the remainder). Dividing two whole numbers drops the fraction in SQLite, PostgreSQL and SQL Server, so 7 / 2 is 3; multiply by 1.0 or CAST to REAL to get 3.5. ROUND, ABS and CAST tidy the answers, which is how GST, discounts and percentages are worked out.30 min
3. Working with datesSQLite has no separate date type: dates are stored as ISO text like '2025-03-15', which sorts and compares correctly. date(), strftime(), julianday() and modifiers such as '+30 days' pull out the year or month, count days between dates and add time. PostgreSQL, MySQL and SQL Server have real date types and their own functions, shown here side by side.35 min
4. CASE expressionsCASE is SQL's "if this, then that". It checks conditions in order and returns the value of the first one that is true, or the ELSE value, or NULL when there is no ELSE. Use it to put values into bands (grades, salary bands, price bands), to turn codes into words, and in ORDER BY to sort in a custom order.30 min
5. NULL: the missing valueNULL marks a value that is missing or unknown. Arithmetic and text joins with NULL give NULL, and comparisons with NULL are unknown, so rows with NULL quietly drop out of filters. COALESCE replaces NULL with a fallback (IFNULL in SQLite and MySQL, ISNULL in SQL Server), and NULLIF turns a value into NULL, which avoids dividing by zero.30 min

Summarise many rows with COUNT, SUM, AVG, MIN and MAX (and know how NULLs are skipped), get one result row per group with GROUP BY, keep only some groups with HAVING, build pivot columns with SUM(CASE ...) and FILTER, list a group's values with GROUP_CONCAT or STRING_AGG, and turn business questions into checked reports. After this module you can answer questions like "revenue per month", "cities with more than one student" or "share of orders delivered", and check that the numbers add up.

1. Aggregate functionsAn aggregate function squeezes many rows into one value: COUNT counts, SUM adds, AVG averages, MIN and MAX find the smallest and largest. COUNT(*) counts rows, COUNT(column) counts only values that are not NULL, and COUNT(DISTINCT column) counts different values. SUM, AVG, MIN and MAX skip NULLs, which changes averages in ways you must notice.30 min
2. GROUP BYGROUP BY splits the rows into groups that share the same value, and the aggregates then run once per group, giving one result row per group: students per city, revenue per order, average salary per department. Every column in the SELECT list must either be in GROUP BY or be inside an aggregate.35 min
3. HAVING versus WHEREWHERE filters single rows before they are grouped; HAVING filters whole groups after the aggregates are worked out. So "students with marks of 60 or more" is WHERE, and "cities with more than 1 student" is HAVING. A query can use both, and the logical order FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT explains which goes where.30 min
4. Conditional aggregation and string aggregationPut a CASE inside SUM or COUNT to count only some rows, and you can turn values into columns: one row per category with an in-stock count and an out-of-stock count, or one row with a column per order status. PostgreSQL and SQLite also offer FILTER (WHERE ...). GROUP_CONCAT (SQLite, MySQL) and STRING_AGG (PostgreSQL, SQL Server) join a group's text into one value.35 min
5. Writing reports with aggregationA report starts as a business question ("revenue per month", "which category sells most", "what share of orders is delivered"). Turn it into SQL by deciding what one result row means, which rows count, how to group and which aggregate to use. Then round and label the numbers, and check that the parts add up to the total before anyone relies on them.40 min

Combine tables the way real databases need: INNER JOIN for matching rows, LEFT, RIGHT and FULL OUTER JOIN to keep rows without a partner, anti-joins to find what is missing, chains of three or four tables, self joins for managers and pairs, and CROSS JOIN for all combinations. After this module you can answer questions such as "revenue per customer", "products that never sold" and "who reports to whom", and avoid the fan-out and ON-versus-WHERE bugs that give wrong totals.

1. Why joins, and INNER JOINA well-designed database splits data into several tables and links them with keys, so each fact is stored once. A JOIN puts the pieces back together for one query by matching rows on those keys. INNER JOIN keeps only the rows that find a partner on the other side.35 min
2. LEFT, RIGHT and FULL OUTER JOINAn outer join keeps rows even when they have no partner, and fills the missing side with NULL. LEFT JOIN keeps every row of the left table, RIGHT JOIN every row of the right, and FULL OUTER JOIN every row of both. LEFT JOIN plus WHERE right.id IS NULL finds rows with no match, such as customers who never ordered.35 min
3. Joining three or more tablesReal questions often need a chain of tables: customers → orders → order_items → products. You add one JOIN at a time, each with its own ON, and check the row count after each step. Then you can group the joined rows to get revenue per customer or per category.35 min
4. Self joins and cross joinsA self join joins a table to itself, using two aliases, which is how you put an employee next to their manager when both live in the employees table. A cross join pairs every row of one table with every row of another, which is useful for building all combinations, such as sizes × colours or dates × stores.30 min
5. Join pitfalls and how to avoid themMost join bugs return wrong numbers, not errors. Joining two one-to-many tables at once duplicates rows and inflates SUM and COUNT (fan-out). A filter on the right table in WHERE turns a LEFT JOIN into an inner join. Wrong keys, NULL keys and NATURAL JOIN cause quiet mistakes too.35 min

Break hard questions into steps: scalar subqueries to compare with an average or maximum, IN and EXISTS for lists and "has any", NOT EXISTS for "has none" (and why NOT IN fails with NULLs), correlated subqueries and derived tables, named steps with WITH, recursive CTEs for series and org charts, and UNION, INTERSECT and EXCEPT to combine results. After this module you can answer two-step questions such as "employees above their department average", "customers above the average spend" and "everyone under this manager" in one readable query.

1. Scalar subqueriesA subquery is a SELECT inside another query, written in brackets. A scalar subquery returns exactly one value (one row, one column), so you can use it anywhere a single value fits: in WHERE to compare with the average, or in SELECT to show the gap from the average on every row.30 min
2. IN, EXISTS and NOT EXISTSA subquery can return a list, and IN checks a value against that list. EXISTS asks "is there at least one matching row?" and NOT EXISTS asks "are there none?". Beware NOT IN: if the list contains a NULL, NOT IN returns no rows at all, so prefer NOT EXISTS.35 min
3. Correlated subqueries and derived tablesA correlated subquery uses a value from the outer row, so its answer is different for each row: "above the average of their own department". A derived table is a subquery in FROM that acts as a temporary table with an alias, which is the way to aggregate an aggregate, such as the average of per-customer totals.35 min
4. Common table expressions (WITH)A common table expression (CTE) names a subquery at the top of a query with WITH name AS (...), so the main query can read it like a table. CTEs turn a tangle of nested subqueries into named steps that read from top to bottom, and one CTE can use the ones before it.35 min
5. Recursive CTEsA recursive CTE refers to itself. It has an anchor member that gives the starting rows, and a recursive member that makes new rows from the previous ones, joined with UNION ALL, until a stop condition gives no more rows. Use it to generate number and date series and to walk hierarchies such as an org chart.40 min
6. UNION, INTERSECT and EXCEPTSet operations combine the results of two SELECTs with the same number of columns. UNION gives the rows in either (removing duplicates), UNION ALL keeps every row, INTERSECT gives rows in both, and EXCEPT gives rows in the first but not the second (MINUS in Oracle). ORDER BY goes once, at the very end.30 min

Window functions compute over related rows without collapsing them: averages and shares next to each row with OVER and PARTITION BY, rankings with ROW_NUMBER, RANK, DENSE_RANK and NTILE, running totals and moving averages with frames, and comparisons with LAG, LEAD and FIRST_VALUE. After this module you can write running totals, day-over-day changes, top-N-per-group and de-duplication queries, and explain how ties and frames change the results.

1. OVER and PARTITION BYA window function works out a value from a group of related rows, called a window, but keeps every row in the result. GROUP BY squeezes each group into one row; a window function such as AVG(salary) OVER (PARTITION BY dept_id) puts the department average next to every employee. OVER () with empty brackets means "all rows".35 min
2. Ranking: ROW_NUMBER, RANK, DENSE_RANK and NTILEROW_NUMBER, RANK and DENSE_RANK number the rows in the order given by ORDER BY inside OVER. They differ only on ties: ROW_NUMBER always gives 1, 2, 3, RANK gives 1, 1, 3 (it skips), and DENSE_RANK gives 1, 1, 2 (no gaps). PARTITION BY restarts the numbering in each group, and NTILE(n) splits the rows into n nearly equal buckets.35 min
3. Running totals, moving averages and framesWhen you put ORDER BY inside OVER, an aggregate becomes cumulative: SUM(revenue) OVER (ORDER BY sale_date) is a running total. Which rows are added up is set by the frame. The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats tied rows together; ROWS BETWEEN 2 PRECEDING AND CURRENT ROW gives a 3-day moving average.40 min
4. LAG, LEAD, FIRST_VALUE and LAST_VALUELAG(x) reads x from the previous row and LEAD(x) from the next row, in the order you give inside OVER. They turn "change since yesterday" and "days since the last order" into simple subtraction. FIRST_VALUE and LAST_VALUE read the first and last row of the frame, and LAST_VALUE needs a full frame to work as you expect.35 min
5. Top-N per group and de-duplicationTo get the top N rows of each group, number the rows with ROW_NUMBER (or RANK) in a CTE, then keep rn <= N in the outer query. You cannot filter a window function in WHERE, because WHERE runs first. The same pattern finds the latest record per key and finds duplicate rows to delete, keeping the lowest id.35 min

Add, change and remove rows with INSERT, UPDATE and DELETE, merge new data with upserts, and group changes into transactions with BEGIN, COMMIT, ROLLBACK and savepoints. Then see how ACID, isolation levels, locks and optimistic locking keep data correct when many users write at once. After this module you can change data safely (preview, WHERE, row counts), write a money transfer as one transaction, and explain dirty reads, lost updates and how to prevent double bookings.

1. INSERTINSERT INTO table (columns) VALUES (...) adds rows. Always name the columns, so the statement keeps working when the table changes. Columns you leave out get their DEFAULT value or NULL. INSERT ... SELECT copies rows from a query, and RETURNING (SQLite 3.35+, PostgreSQL) gives back the new rows, for example their generated ids.35 min
2. UPDATE and DELETE safelyUPDATE table SET column = value WHERE ... changes existing rows, and DELETE FROM table WHERE ... removes them. Without a WHERE they hit every row, so always write the WHERE first and preview it with a SELECT. SET can use expressions and subqueries. TRUNCATE and DROP are different tools, and a soft delete only marks a row as removed.35 min
3. Upserts: insert or updateAn upsert inserts a row if its key is new and updates it if the key already exists, in one statement. SQLite and PostgreSQL write INSERT ... ON CONFLICT (key) DO UPDATE SET ..., using excluded.column for the new values. MySQL uses ON DUPLICATE KEY UPDATE, and SQL Server uses MERGE. INSERT OR IGNORE skips duplicates, and REPLACE deletes and re-inserts, which can lose data.30 min
4. Transactions: all or nothingA transaction groups several statements so they succeed or fail together. BEGIN starts it, COMMIT makes the changes permanent, and ROLLBACK undoes everything since BEGIN. A money transfer, a debit and a credit, must be one transaction. Without BEGIN, each statement commits on its own (autocommit). SAVEPOINT lets you undo part of a transaction.35 min
5. ACID, isolation and lockingACID names four promises of a transaction: Atomicity (all or nothing), Consistency (rules always hold), Isolation (concurrent transactions do not see each other's half-done work) and Durability (a commit survives a crash). Isolation levels trade safety for speed and decide which anomalies, such as dirty reads and lost updates, can happen. Locks, SELECT ... FOR UPDATE and a version column protect the last berth from being sold twice.40 min

How to turn requirements into a database design: entities, relationships and ER diagrams, the normal forms and the anomalies they prevent, when to denormalise, OLTP versus OLAP and star schemas, sound choices for types and names, and a full online store design. After this module you can design a normalised schema for a real app, explain your choices in an interview, and test the design with reporting queries.

1. Modelling data: entities and relationshipsBefore you write CREATE TABLE, you turn the requirements into a model: entities (the things), attributes (their details) and relationships (how they connect), with a cardinality such as one-to-many. An ER diagram draws this model, often with crow's foot lines. Many-to-many relationships become junction tables, and every table gets a key chosen on purpose.35 min
2. Normalisation: 1NF, 2NF, 3NF and BCNFNormalisation means splitting data so that each fact is stored in exactly one place. A flat, spreadsheet-like table repeats facts, which causes update, insert and delete anomalies. The normal forms (1NF, 2NF, 3NF and BCNF) are checks that remove those repeats step by step, using the idea of functional dependency.40 min
3. Denormalisation, OLTP and OLAPSometimes you copy data on purpose to make reads fast, for example a cached order total; this is denormalisation, and its price is keeping the copies in sync. App databases that handle many small writes (OLTP) stay normalised, while analytics systems (OLAP) use star schemas: one big fact table surrounded by dimension tables, often inside a cloud data warehouse.35 min
4. Data types, naming and good habitsGood schemas pick types that keep data exact: money as DECIMAL or whole paise (never FLOAT), times stored in UTC, and allowed values enforced with CHECK, enums or lookup tables. Consistent names (snake_case, one plural or singular rule), created_at and updated_at columns, soft deletes and a deliberate choice between integer and UUID keys make a schema easy to live with for years.35 min
5. Design project: an online storePut the module together by designing the database of a small online store: requirements, tables (customers, addresses, categories, products, orders, order_items, payments, reviews), keys and constraints. A design is finished only when it can answer the questions the business will ask, so you test it with real reporting queries.40 min

How indexes work inside (B-trees), which kinds to use (composite, covering, unique, partial, expression), how to read query plans in SQLite, PostgreSQL and MySQL, how to write queries that use indexes (sargable filters, keyset pagination, no N+1), and how views and materialized views simplify and speed up reports. After this module you can find out why a query is slow, prove it with a plan, and fix it with the right index or a better query.

1. How indexes workWithout an index, the database must read every row to find matches: a full table scan. An index is a separate, sorted structure (usually a B-tree) that lets it jump to the matching rows in a few steps, like the index at the back of a book. Indexes make reads fast but cost disk space and slow down every insert, update and delete a little, so you add them for real queries, not everywhere.30 min
2. Composite, covering, unique and partial indexesA composite index covers several columns, and its column order matters: it helps queries that use its leftmost columns. A covering index holds every column a query needs, so the table is never touched. Unique indexes enforce rules, partial indexes cover only the rows you search often, and expression indexes cover values such as lower(email).35 min
3. Reading query plansA query plan is the database's description of how it will run a query: which tables it scans, which indexes it searches, and where it sorts. SQLite shows it with EXPLAIN QUERY PLAN (SCAN or SEARCH ... USING INDEX), PostgreSQL with EXPLAIN and EXPLAIN ANALYZE (Seq Scan, Index Scan, actual times), and MySQL with EXPLAIN (type, key, rows). Reading plans is how you prove an index helps.35 min
4. Writing fast queriesIndexes only help if the query lets them. Keep indexed columns bare in WHERE (sargable predicates), use date ranges instead of functions on dates, avoid leading-wildcard LIKE, select only the columns you need, and replace the N+1 pattern with one query. Page with keyset pagination instead of big OFFSETs, prefer EXISTS for "is there any", batch your writes, and keep statistics fresh.35 min
5. Views and materialized viewsA view is a saved query with a name that you can SELECT from like a table. Views simplify reports and can hide private columns, but they store no data and are not faster than their query. A materialized view (PostgreSQL) or indexed view (SQL Server) stores the result and must be refreshed; in SQLite you emulate one with a summary table rebuilt by DELETE plus INSERT ... SELECT.30 min

Store logic in the database with procedures, functions and triggers; compare MySQL, PostgreSQL, SQLite, SQL Server and Oracle; meet the NoSQL families, the CAP theorem, MongoDB and Redis; and choose and secure a database. After this module you can read and write procedures and triggers, write SQL that moves between engines, query JSON documents in SQL, pick the right database for each job and defend an app against SQL injection.

1. Stored procedures and functionsA stored procedure or stored function is a named block of SQL code saved inside the database and run by name. It keeps logic next to the data, saves round trips and lets you grant "run this" instead of "touch these tables". The price is code that is harder to test, version and move to another engine. PostgreSQL, MySQL and SQL Server have them; SQLite does not.35 min
2. TriggersA trigger is code the database runs by itself when a row is inserted, updated or deleted. It can fire BEFORE or AFTER the change (or INSTEAD OF it, on views) and can read the row as it was (OLD) and as it will be (NEW). Triggers are good for audit logs and keeping counts in sync, but they hide logic, so use them sparingly.35 min
3. MySQL vs PostgreSQL vs SQLite vs SQL ServerAll four speak SQL, but they differ in licence, typical use and many small details: how you make an auto-increment key, how you limit rows, join text, upsert, store true/false and compare text. Most of what you learned in SQLite works everywhere; knowing the differences saves you hours when you change jobs or engines.35 min
4. NoSQL: documents, key-value, wide-column and graphNoSQL databases store data in shapes other than tables: documents, key-value pairs, wide columns or graphs. They grew out of the need to spread huge, changing data over many cheap servers. The CAP theorem says that during a network split a distributed database must choose between consistency and availability. NoSQL does not mean "no transactions": MongoDB has multi-document ACID transactions since version 4.0.30 min
5. MongoDB and Redis in practiceMongoDB stores JSON-like documents in collections and is queried with filters such as { city: "Pune", amount: { $gt: 500 } }, updates such as $set and $inc, and aggregation pipelines that work like WHERE, GROUP BY and ORDER BY. Redis keeps keys in memory for caching, counters, leaderboards and queues. SQLite's JSON functions let you practise the same ideas in SQL.40 min
6. Choosing a database, and keeping it safeStart with a relational database unless you have a clear reason not to, and add Redis, a document store, a search engine or a time-series database only for the jobs they do better. Keep data safe with least-privilege users, GRANT and REVOKE, parameterised queries (never glue user input into SQL), tested backups, and a managed service when you do not want to run servers yourself.35 min

Solve the classic SQL interview queries (Nth highest salary, duplicates, self joins, streaks, growth, pivots, medians), answer the common theory questions with a clear define-example-trade-off pattern, and build two projects: a simplified railway booking database and e-commerce analytics on the SHOP data. After this module you can clear a SQL interview round, show a small portfolio of real queries and plan your next steps towards a data or backend job.

1. Classic interview queries, part 1A handful of SQL questions appear in almost every interview: the second (or Nth) highest salary, finding and deleting duplicates, employees who earn more than their manager, the top earner in each department, and departments with no employees. Learn two or three ways to solve each, and say how each way treats ties and NULLs.40 min
2. Classic interview queries, part 2The harder interview questions are about sequences and comparisons: running totals, streaks of consecutive days (the gaps-and-islands trick), month-over-month growth with LAG, customers who bought A but not B, a median with window functions, and pivoting rows into columns with CASE. Each has a pattern you can learn once and reuse.40 min
3. Theory questions you will be askedInterviews mix queries with short theory questions: DELETE vs TRUNCATE vs DROP, WHERE vs HAVING, UNION vs UNION ALL, primary vs unique keys, clustered vs non-clustered indexes, normalisation, ACID, isolation levels, views, OLTP vs OLAP and CHAR vs VARCHAR. Answer each in three steps: define it, give a small example, and name the trade-off.35 min
4. Project: a railway booking database (IRCTC-like)Build and query a small railway booking database: stations, trains, the stops of each train in order, travel classes, passengers and bookings with a PNR and a status of CNF, RAC or WL. You will find trains between two stations in the right direction, count bookings by status, work out waiting-list positions and add up revenue per class. It is a simplified learning model, not how IRCTC really works.40 min
5. Project: e-commerce analytics, and your next stepsAnswer the questions every online shop asks, on the SHOP database: monthly revenue, average order value, repeat customers, top products, customers who never ordered and a simple cohort by join month. Then plan what comes next: practice sites, a portfolio of queries, the jobs that use SQL (with honest salary ranges) and certifications.40 min