A dashboard at Zerodha shows yesterday's trade volume as zero. Support is already fielding questions. The pipeline logs show every task succeeded - no errors, no failed retries, nothing red anywhere in Airflow. The bug is not in the pipeline code at all. It is in a single SQL query three layers deep, where a `WHERE` clause silently excluded every row from the busiest exchange. This is the moment every data engineer eventually meets, and SQL is the language they meet it in. Python builds the pipeline that moves data around. SQL is what you write to actually look at that data, question it, reshape it, and prove it is correct - every single day, on every single system you touch. * Application SQL and analytical SQL are different jobs wearing the same syntax. A checkout page runs `SELECT * FROM orders WHERE id = 4471` against one row in milliseconds. A data engineer runs a query that scans two years of orders, joins them to five other tables, and groups the result by city and month - and that query has to finish in a reasonable time even at hundreds of millions of rows. * SQL shows up at every stage of a data engineer's day - querying a source system to understand what you are about to extract, transforming raw data inside a warehouse, and validating that a pipeline's output actually looks right before anyone downstream trusts it. * Every data engineer interview tests SQL directly, and nearly every job posting lists it as a required skill - not because it is trendy, but because nothing has replaced it as the way humans ask structured questions of structured data. > 📌 **Remember:** if a pipeline breaks, the first tool you reach for to diagnose it is almost never Python. It is a SQL query against the table that looks wrong.
Before querying anything, it helps to know how the tables you are about to query came to exist. **DDL (Data Definition Language)** is the set of SQL commands that create and change the structure of a database - tables, columns, types. **DML (Data Manipulation Language)** is the set of commands that add, change, and remove the actual rows inside those tables. ```sql -- DDL: define the shape of the table before any data exists CREATE TABLE orders ( order_id BIGINT PRIMARY KEY, customer_id BIGINT, amount DECIMAL(10, 2), order_date DATE, status VARCHAR(20) ); -- DDL: change a table's structure after it already exists ALTER TABLE orders ADD COLUMN delivery_partner_id BIGINT; -- DDL: remove a table entirely, structure and all data with it DROP TABLE IF EXISTS orders_staging_temp; ``` ```sql -- DML: add rows INSERT INTO orders (order_id, customer_id, amount, order_date, status) VALUES (100001, 5521, 649.00, '2026-08-10', 'delivered'); -- DML: change existing rows UPDATE orders SET status = 'cancelled' WHERE order_id = 100001; -- DML: remove rows DELETE FROM orders WHERE status = 'cancelled' AND order_date < '2025-01-01'; ``` > **Note:** `DECIMAL(10, 2)` reserves up to 10 total digits with 2 after the decimal point - the right choice for money amounts, since floating-point types can introduce tiny rounding errors that are unacceptable when the column represents Rs and paise. > 📌 **Remember:** DDL changes are structural and usually far-reaching - an `ALTER TABLE` or `DROP TABLE` run against the wrong environment can break every pipeline reading that table. DML changes affect rows, not structure, but an `UPDATE` or `DELETE` without a `WHERE` clause silently rewrites or empties an entire table. Always run a `SELECT` with the same `WHERE` clause first to see exactly which rows a DML statement is about to touch.
A **primary key** is the column, or set of columns, that uniquely identifies every row in a table - no two rows can share one, and it can never be NULL. A **foreign key** is a column in one table that points to a primary key in another, and it is how a database enforces that relationships between tables actually make sense. ```sql CREATE TABLE customers ( customer_id BIGINT PRIMARY KEY, email TEXT UNIQUE NOT NULL, signup_date DATE NOT NULL DEFAULT CURRENT_DATE ); CREATE TABLE orders ( order_id BIGINT PRIMARY KEY, customer_id BIGINT NOT NULL, amount DECIMAL(10, 2) CHECK (amount >= 0), FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); ``` * **PRIMARY KEY** guarantees every row is uniquely identifiable - the column a JOIN usually matches on. * **FOREIGN KEY** enforces that a value like `customer_id` in `orders` must already exist as a `customer_id` in `customers` - the database rejects an order for a customer that does not exist, instead of silently allowing an orphaned row. * **UNIQUE** guarantees no two rows share a value in that column, without making it the primary key. * **NOT NULL** guarantees a column can never be left empty. * **CHECK** enforces a business rule directly in the schema - here, an order amount can never be negative. > **Note:** this cluster of rules is called **referential integrity** - the guarantee that relationships declared between tables are never silently violated. It is exactly why a JOIN between `orders` and `customers` can be trusted to behave predictably: the foreign key constraint is what stops an order from ever pointing to a customer that does not exist in the first place. > 💡 **Practice:** write the `CREATE TABLE` statements for a `restaurants` table and an `orders` table for a Swiggy-style schema, with `restaurant_id` as the primary key on `restaurants` and a foreign key on `orders` pointing to it. Then try inserting an order with a `restaurant_id` that does not exist in `restaurants`, and confirm the database rejects it.
Every SQL query starts from the same three clauses. **SELECT** names the columns you want back, **FROM** names the table they come from, and **WHERE** filters which rows qualify before anything else happens. ```sql -- Every Flipkart order from Bengaluru placed in the last 30 days SELECT order_id, customer_id, amount, order_date FROM orders WHERE city = 'Bengaluru' AND order_date >= CURRENT_DATE - INTERVAL '30 days'; ``` > **Note:** `WHERE` runs before any grouping or sorting happens. Think of it as a bouncer at the door - rows that fail the condition never make it into the rest of the query at all. ### Filtering, matching, and converting values A handful of small operators do most of the day-to-day filtering work in pipeline queries, and it is worth knowing them cold before moving into JOINs and aggregation. ```sql -- DISTINCT removes duplicate rows from the result SELECT DISTINCT city FROM restaurants; -- IN checks membership against a list of values, cleaner than chained OR conditions SELECT * FROM orders WHERE status IN ('delivered', 'out_for_delivery'); -- BETWEEN checks an inclusive range SELECT * FROM orders WHERE amount BETWEEN 500 AND 2000; -- LIKE matches a text pattern - % means "any characters", _ means "one character" -- ILIKE is the case-insensitive version, available in PostgreSQL SELECT * FROM restaurants WHERE name ILIKE '%biryani%'; -- CAST converts a value from one type to another SELECT CAST(amount AS INTEGER) AS amount_rounded FROM orders; -- Common string functions used in cleaning raw data SELECT UPPER(city), LOWER(email), TRIM(restaurant_name), LENGTH(customer_id) FROM orders; -- COUNT(*) counts every row, COUNT(column) skips NULLs in that column SELECT COUNT(*) AS total_rows, COUNT(discount_code) AS rows_with_discount, COUNT(DISTINCT customer_id) AS unique_customers FROM orders; ``` > **Note:** `COUNT(*)` and `COUNT(some_column)` often give different answers on the same table, and this trips up beginners constantly - `COUNT(*)` counts rows regardless of NULLs, while `COUNT(some_column)` only counts rows where that specific column is not NULL. `COUNT(DISTINCT column)` adds deduplication on top, counting each unique non-NULL value once. > 💡 **Practice:** write a query using `LIKE` or `ILIKE` to find every restaurant with "Kitchen" in its name, then a second query using `IN` to select only orders with status `'delivered'` or `'cancelled'`, then compare `COUNT(*)` against `COUNT(DISTINCT customer_id)` on the `orders` table and explain in one sentence what the difference between the two numbers tells you. ### GROUP BY and aggregation - turning rows into answers Raw rows rarely answer a business question by themselves. **Aggregation** functions like `COUNT`, `SUM`, `AVG`, `MIN`, and `MAX` collapse many rows into one summary value, and `GROUP BY` tells SQL which column defines each group. ```sql -- Total revenue and order count per restaurant, Swiggy-style SELECT restaurant_id, COUNT(*) AS total_orders, SUM(amount) AS total_revenue, AVG(amount) AS avg_order_value FROM orders WHERE status = 'delivered' GROUP BY restaurant_id; ``` ### HAVING - filtering after the group forms **HAVING** filters groups after aggregation, while `WHERE` filters rows before it. This is the single most common reason a beginner's query throws a syntax error. ```sql -- Only restaurants with more than 500 delivered orders SELECT restaurant_id, COUNT(*) AS total_orders FROM orders WHERE status = 'delivered' GROUP BY restaurant_id HAVING COUNT(*) > 500; ``` > 🔴 **Common Mistake:** writing `WHERE COUNT(*) > 500` instead of `HAVING COUNT(*) > 500` throws a syntax error, because `WHERE` runs before `GROUP BY` has produced any counts to filter on. If your filter condition uses an aggregate function, it belongs in `HAVING`, never `WHERE`. ### ORDER BY, LIMIT, and aliases ```sql -- Top 5 restaurants by revenue, most readable output first SELECT restaurant_id, SUM(amount) AS total_revenue -- alias makes the output column readable FROM orders WHERE status = 'delivered' GROUP BY restaurant_id ORDER BY total_revenue DESC LIMIT 5; ``` ### Handling NULLs - because real data always has gaps **NULL** means "this value is unknown or missing" - it is not zero, not an empty string, and it does not equal anything, including itself. ```sql -- COALESCE substitutes a default value when a column is NULL SELECT order_id, COALESCE(discount_code, 'NONE') AS discount_code, -- NULLIF returns NULL if two values match - useful to avoid divide-by-zero amount / NULLIF(item_count, 0) AS price_per_item FROM orders WHERE delivery_partner_id IS NOT NULL; -- IS NULL / IS NOT NULL, never = NULL ``` > 🔴 **Common Mistake:** writing `WHERE discount_code = NULL` returns zero rows every time, because NULL never equals anything with `=`, not even another NULL. Always use `IS NULL` or `IS NOT NULL` to test for missing values. > 💡 **Practice:** using a sample `orders` table, write one query that counts delivered orders per city with more than 100 orders, and a second query that shows the average order value with NULL discount codes replaced by the string `'NONE'`.
**CASE WHEN** lets a query make a decision per row, the same way an `if/elif/else` does in Python, but expressed as an expression that produces a value for a column. ```sql -- Bucket every order into a size category, right inside the SELECT SELECT order_id, amount, CASE WHEN amount >= 5000 THEN 'high' WHEN amount >= 1000 THEN 'medium' ELSE 'low' END AS order_category FROM orders; ``` > **Note:** `CASE WHEN` checks each condition top to bottom and stops at the first match, so order matters - here, an amount of 6000 matches the first branch and never reaches the second. `ELSE` catches everything that fell through every prior condition; leaving it out means non-matching rows get NULL instead of a default label. `CASE WHEN` is not a beginner curiosity - it is one of the most-used pieces of syntax in real transformation work, showing up constantly inside dbt models, warehouse marts, and data quality checks wherever raw values need to become business-readable categories. > 💡 **Practice:** write a query that adds a `delivery_speed` column to `orders` using `CASE WHEN`, labelling orders `'fast'` if delivered within 30 minutes, `'normal'` within 60 minutes, and `'slow'` otherwise, based on a `delivery_minutes` column.
Production data is never one giant table. A Razorpay-style payments system keeps `payments`, `merchants`, and `payment_methods` as separate tables, and a **JOIN** is how you combine rows across them based on a matching column. orders restaurants +-----------+ +---------------+ | order_id | | restaurant_id | | rest_id |---------->| name | | amount | matches | city | +-----------+ +---------------+ * **INNER JOIN** keeps only rows that match in both tables - an order with no matching restaurant record disappears entirely. * **LEFT JOIN** keeps every row from the left table, filling in NULLs for any right-table columns that have no match. * **RIGHT JOIN** is the mirror of LEFT JOIN - rarely used, since you can always rewrite it as a LEFT JOIN by swapping table order. * **FULL OUTER JOIN** keeps every row from both tables, matched where possible, NULL-filled where not. * **CROSS JOIN** pairs every row in one table with every row in the other - a cartesian product, rarely intentional but important to recognise when a query accidentally produces one. ```sql -- INNER JOIN - only orders that have a matching restaurant record SELECT o.order_id, o.amount, r.name AS restaurant_name FROM orders o INNER JOIN restaurants r ON o.restaurant_id = r.restaurant_id; -- LEFT JOIN - every order, even ones whose restaurant record was deleted SELECT o.order_id, o.amount, r.name AS restaurant_name FROM orders o LEFT JOIN restaurants r ON o.restaurant_id = r.restaurant_id; ``` > 📌 **Engineering Decision:** default to LEFT JOIN when building a pipeline query, not INNER JOIN. INNER JOIN silently drops rows the moment a matching key is missing on either side, and in a pipeline that means data quietly disappears from a report with no error anywhere. Reach for INNER JOIN only when you have deliberately decided that unmatched rows are not wanted in the result - and even then, count rows before and after the join so the drop is a decision, not a surprise. > 🔴 **Common Mistake:** writing a JOIN without checking whether the row count after the join matches expectations produces silently wrong results. A join on a column that is not actually unique on one side (for example, joining orders to a restaurants table that accidentally has duplicate restaurant_id rows) multiplies rows instead of matching them one-to-one - always sanity-check row counts before trusting a joined result.
A dashboard at Zerodha shows yesterday's trade volume as zero. Support is already fielding questions. The pipeline logs ...
Before querying anything, it helps to know how the tables you are about to query came to exist. DDL (Data Definition Lan...
A primary key is the column, or set of columns, that uniquely identifies every row in a table - no two rows can share on...
Every SQL query starts from the same three clauses. SELECT names the columns you want back, FROM names the table they co...
CASE WHEN lets a query make a decision per row, the same way an if/elif/else does in Python, but expressed as an express...
Production data is never one giant table. A Razorpay-style payments system keeps payments, merchants, and paymentmethods...
A SQL query reads top to bottom, but it does not run top to bottom. The database executes clauses in a fixed logical ord...
GROUP BY collapses many rows into one row per group. A window function does something different - it performs a calculat...
A CTE (Common Table Expression) is a named, temporary result set defined with WITH, used inside a larger query. It exist...
Pipeline queries filter by date constantly - "last 30 days," "this month," "yesterday" - so handling dates correctly in ...
Where a JOIN combines tables side by side by matching columns, a set operation stacks the results of two queries on top ...
A query that returns correct results but takes four minutes against a 200-million-row table is not actually a working qu...
Before any transformation or dashboard trusts a table, someone has to check that the data actually looks right. These va...
A transaction groups multiple database operations so that they either all succeed together or all roll back together - t...
A teammate wrote this query to find Hotstar customers who watched more than 10 hours of content last month, but it is re...
This module's practice has already moved through increasing levels of challenge - basic filtering and NULL handling, the...
Task Syntax Pattern Remove duplicate rows SELECT DISTINCT col FROM table Match against a list WHERE col IN (val1, val2) ...
Using WHERE to filter on an aggregate result instead of HAVING causes a syntax error every time, because WHERE runs befo...
Aligns directly with DevOps, Site Reliability (SRE), and Platform Engineering job descriptions.