In this post we are going to cover the SQL fundamentals that trip people up: the order SQL really runs a SELECT, WHERE vs HAVING, the three kinds of COUNT, NULLs, UNION vs UNION ALL and DELETE vs TRUNCATE vs DROP. These are 7 real SQL interview questions, asked the way they come up on the job, and the last one is a scenario where a report is one customer short because of a NULL.
These look like beginner questions, and that is exactly why interviewers ask them. Most wrong numbers in real reports come from these basics: an alias used too early, a filter in the wrong clause, a count of the wrong thing, or a NULL that quietly drops a row. A senior answer explains why, not only what.
Every query below is complete, so you can copy it, run it on the sample data and get exactly the output shown under it.
Part 1 of the SQL Server RealTime Questions Answers series on TechBrothersIT.
- The short answer
- Let's create some sample data
- Question 1: A developer calculates a 10 percent tax in the SELECT list with the alias tax, filters WHERE tax greater than 50, and gets the error 'column tax does not exist'. In what order does SQL actually run a SELECT, and how do you fix the query?
- Question 2: Your manager wants the customers who spent more than 1,000, not counting cancelled orders. A teammate wrote WHERE SUM(amount) greater than 1000 and got an error. When do you use WHERE, and when do you use HAVING?
- Question 3: Three reports count customers and get 7, 6 and 2. What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column), and which one answers the business question?
- Question 4: A query with WHERE region = NULL returns no rows, and an order balance (amount minus paid amount) comes back NULL. How do NULLs behave in comparisons and arithmetic, and how do you fix both?
- Question 5: You need one list of the customers who ordered in January or February. Should you use UNION or UNION ALL, and what is the difference?
- Question 6: A teammate needs to empty a staging table before tonight's load. Should they use DELETE, TRUNCATE or DROP, and what does each one really do?
- Scenario 7: The report's count of customers outside the East region is one short of the CRM. The query filters region not equal to 'East'. Why is a customer missing, and how do you fix it?
- The full SQL script
The short answer
SQL runs a SELECT in the order FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, which is why aliases work in ORDER BY but not in WHERE. NULL means unknown: comparisons with it are never true, COUNT(column) skips it, and arithmetic with it returns NULL, so test with IS NULL and default with COALESCE.
Keep that in mind. Most questions below come back to one of those two ideas.
Let's create some sample data
We use a small shop database called TB Shop: departments, employees, customers, products, orders, order items and payments, plus orders_stage, a staging copy of orders that we can safely empty in question 6. The script is written for PostgreSQL, and it runs on SQL Server, MySQL 8 or Snowflake with very small changes.
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
INSERT INTO departments VALUES (10, 'Sales'), (20, 'Engineering'), (30, 'HR'), (40, 'Finance');
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT, -- NULL = not assigned yet
manager_id INT, -- NULL = top of the company
salary INT,
hire_date DATE
);
INSERT INTO employees VALUES
(1, 'Aisha', NULL, NULL, 150000, '2018-03-01'),
(2, 'Omar', 20, 1, 120000, '2019-06-15'),
(3, 'Lina', 20, 2, 90000, '2021-01-10'),
(4, 'Ravi', 20, 2, 90000, '2022-04-05'),
(5, 'Mei', 20, 2, 75000, '2024-09-01'),
(6, 'Sara', 10, 1, 95000, '2020-02-20'),
(7, 'Daniel', 10, 6, 105000, '2021-07-01'),
(8, 'Fatima', 10, 6, 60000, '2023-11-12'),
(9, 'John', 30, 1, 70000, '2019-09-30'),
(10,'Zara', NULL, 9, 55000, '2026-01-05');
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(20),
city VARCHAR(20),
region VARCHAR(10), -- NULL = unknown
email VARCHAR(40)
);
INSERT INTO customers VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@mail.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@mail.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@mail.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@mail.com'),
(5, 'Ravi Patel', 'Austin', NULL, 'ravi@mail.com'),
(6, 'Chen Wu', 'Denver', 'West', 'chen@mail.com'),
(7, 'Sara K.', 'Toronto', 'East', 'sara@mail.com');
CREATE TABLE products (product_id INT PRIMARY KEY, product VARCHAR(20), category VARCHAR(15), price DECIMAL(10,2));
INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 1200.00),
(2, 'Monitor', 'Electronics', 300.00),
(3, 'Keyboard', 'Accessories', 50.00),
(4, 'Mouse', 'Accessories', 25.00),
(5, 'Desk', 'Furniture', 400.00),
(6, 'Chair', 'Furniture', 250.00);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
status VARCHAR(10),
amount DECIMAL(10,2)
);
INSERT INTO orders VALUES
(1001, 1, '2026-01-05', 'Delivered', 1250.00),
(1002, 2, '2026-01-18', 'Delivered', 300.00),
(1003, 4, '2026-02-02', 'Delivered', 650.00),
(1004, 1, '2026-02-14', 'Cancelled', 50.00),
(1005, 3, '2026-02-20', 'Delivered', 1200.00),
(1006, 4, '2026-03-03', 'Shipped', 275.00),
(1007, 5, '2026-03-15', 'Delivered', 400.00),
(1008, 2, '2026-04-01', 'Delivered', 75.00),
(1009, 4, '2026-04-22', 'Delivered', 250.00),
(1010, 1, '2026-05-09', 'Shipped', 300.00),
(1011, 3, '2026-05-30', 'Cancelled', 400.00),
(1012, 7, '2026-06-11', 'Delivered', 50.00);
CREATE TABLE order_items (order_id INT, product_id INT, qty INT, unit_price DECIMAL(10,2));
INSERT INTO order_items VALUES
(1001, 1, 1, 1200.00), (1001, 3, 1, 50.00),
(1002, 2, 1, 300.00),
(1003, 5, 1, 400.00), (1003, 6, 1, 250.00),
(1004, 3, 1, 50.00),
(1005, 1, 1, 1200.00),
(1006, 6, 1, 250.00), (1006, 4, 1, 25.00),
(1007, 5, 1, 400.00),
(1008, 4, 3, 25.00),
(1009, 6, 1, 250.00),
(1010, 2, 1, 300.00),
(1011, 5, 1, 400.00),
(1012, 3, 1, 50.00);
CREATE TABLE payments (payment_id INT PRIMARY KEY, order_id INT, paid_amount DECIMAL(10,2));
INSERT INTO payments VALUES
(1, 1001, 1250.00), (2, 1002, 300.00), (3, 1003, 400.00), (4, 1003, 250.00), (5, 1005, 1200.00);
DROP TABLE IF EXISTS orders_stage;
-- a staging copy of orders, safe to delete from
CREATE TABLE orders_stage (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
status VARCHAR(10),
amount DECIMAL(10,2)
);
INSERT INTO orders_stage SELECT order_id, customer_id, order_date, status, amount FROM orders;Here are the customers. Notice two things we will use later: Ravi Patel has a NULL region, and Sara Khan and Sara K. share the same email, sara@mail.com.
| customer_id | name | city | region | |
|---|---|---|---|---|
| 1 | Sara Khan | Toronto | East | sara@mail.com |
| 2 | Omar Ali | Dallas | West | omar@mail.com |
| 3 | Lina Park | Seattle | West | lina@mail.com |
| 4 | Mei Chen | Boston | East | mei@mail.com |
| 5 | Ravi Patel | Austin | NULL | ravi@mail.com |
| 6 | Chen Wu | Denver | West | chen@mail.com |
| 7 | Sara K. | Toronto | East | sara@mail.com |
And here are the orders. Two of them are Cancelled, 1004 and 1011, and customer 1 ordered in both January and February.
| order_id | customer_id | order_date | status | amount |
|---|---|---|---|---|
| 1001 | 1 | 2026-01-05 | Delivered | 1250.00 |
| 1002 | 2 | 2026-01-18 | Delivered | 300.00 |
| 1003 | 4 | 2026-02-02 | Delivered | 650.00 |
| 1004 | 1 | 2026-02-14 | Cancelled | 50.00 |
| 1005 | 3 | 2026-02-20 | Delivered | 1200.00 |
| 1006 | 4 | 2026-03-03 | Shipped | 275.00 |
| 1007 | 5 | 2026-03-15 | Delivered | 400.00 |
| 1008 | 2 | 2026-04-01 | Delivered | 75.00 |
| 1009 | 4 | 2026-04-22 | Delivered | 250.00 |
| 1010 | 1 | 2026-05-09 | Shipped | 300.00 |
| 1011 | 3 | 2026-05-30 | Cancelled | 400.00 |
| 1012 | 7 | 2026-06-11 | Delivered | 50.00 |
Question 1: A developer calculates a 10 percent tax in the SELECT list with the alias tax, filters WHERE tax greater than 50, and gets the error 'column tax does not exist'. In what order does SQL actually run a SELECT, and how do you fix the query?
Not in the order we write it. Logically, the database runs the clauses in this order:
- FROM, and the joins
- WHERE, which filters rows
- GROUP BY, then HAVING, which filters groups
- SELECT, where the aliases are born
- DISTINCT, then ORDER BY, which can use the aliases, and any row limit comes last
Let's run the developer's query as it was written.
SELECT order_id,
CAST(amount * 0.10 AS DECIMAL(10,2)) AS tax
FROM orders
WHERE tax > 50
ORDER BY tax DESC;column "tax" does not exist
LINE 4: WHERE tax > 50The database says column "tax" does not exist. WHERE runs before SELECT, so when WHERE is evaluated the alias does not exist yet.
The fix is to repeat the expression in WHERE, or wrap the query in a subquery or a CTE. Let's repeat the expression and keep the alias in ORDER BY.
SELECT order_id,
CAST(amount * 0.10 AS DECIMAL(10,2)) AS tax
FROM orders
WHERE amount * 0.10 > 50
ORDER BY tax DESC;| order_id | tax |
|---|---|
| 1001 | 125.00 |
| 1005 | 120.00 |
| 1003 | 65.00 |
Three orders have a tax above 50: 1001 with 125.00, 1005 with 120.00 and 1003 with 65.00. Notice that ORDER BY tax works, because ORDER BY runs after SELECT.
PostgreSQL and MySQL also accept an alias in GROUP BY, and Snowflake even accepts one in WHERE, but SQL Server accepts neither. If the code has to be portable, follow the execution order.
Question 2: Your manager wants the customers who spent more than 1,000, not counting cancelled orders. A teammate wrote WHERE SUM(amount) greater than 1000 and got an error. When do you use WHERE, and when do you use HAVING?
WHERE filters rows, before they are grouped. HAVING filters groups, after the aggregates are calculated. It follows straight from the execution order in question 1.
So you cannot put an aggregate in WHERE. Here is the teammate's query:
SELECT customer_id, SUM(amount) AS revenue
FROM orders
WHERE SUM(amount) > 1000
GROUP BY customer_id;aggregate functions are not allowed in WHERE
LINE 3: WHERE SUM(amount) > 1000The error is aggregate functions are not allowed in WHERE. At the WHERE step there is no group yet, so there is no SUM to test.
Now the right way. The status test is about a single row, so it goes in WHERE. The total is about the group, so it goes in HAVING.
SELECT customer_id, COUNT(*) AS order_count,
SUM(amount) AS revenue
FROM orders
WHERE status <> 'Cancelled'
GROUP BY customer_id
HAVING SUM(amount) > 1000
ORDER BY customer_id;| customer_id | order_count | revenue |
|---|---|---|
| 1 | 2 | 1550.00 |
| 3 | 1 | 1200.00 |
| 4 | 3 | 1175.00 |
Customers 1, 3 and 4 qualify, with 1550.00, 1200.00 and 1175.00. Customer 1 has 2 orders left after the cancelled one is removed, and customer 4 has 3.
Filter as early as you can. A condition on a grouping column, like the customer, belongs in WHERE even when HAVING would accept it: it reads clearer, and it throws rows away before the grouping step.
Question 3: Three reports count customers and get 7, 6 and 2. What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column), and which one answers the business question?
COUNT(*)counts every row.COUNT(col)counts only the rows where that column is not NULL.COUNT(DISTINCT col)counts the different non-NULL values.
Let's run all of them on the customers table in one query.
SELECT COUNT(*) AS all_rows,
COUNT(region) AS with_region,
COUNT(DISTINCT region) AS distinct_regions,
COUNT(DISTINCT email) AS distinct_emails
FROM customers;| all_rows | with_region | distinct_regions | distinct_emails |
|---|---|---|---|
| 7 | 6 | 2 | 6 |
COUNT(*) is 7. COUNT(region) is 6, because Ravi's region is NULL. COUNT(DISTINCT region) is 2, just East and West, because NULL is not counted as a value. That is the 7, 6 and 2 from the three reports.
And COUNT(DISTINCT email) is 6, not 7, because Sara Khan and Sara K. share the same email. Same person, two customer records. That is a data quality finding, not just a number.
In a real project, when two reports disagree on a count, this is the first thing I check: one is counting rows, the other is counting non-NULL or distinct values. So always ask which count the business means.
Question 4: A query with WHERE region = NULL returns no rows, and an order balance (amount minus paid amount) comes back NULL. How do NULLs behave in comparisons and arithmetic, and how do you fix both?
NULL means unknown. Comparing anything to NULL, even NULL itself, gives unknown, not true, and WHERE keeps only the rows where the condition is true.
SELECT customer_id, name
FROM customers
WHERE region = NULL
ORDER BY customer_id;| customer_id | name |
|---|
No rows at all, and no error. That is what makes it dangerous. To test for NULL, use IS NULL or IS NOT NULL.
SELECT customer_id, name
FROM customers
WHERE region IS NULL
ORDER BY customer_id;| customer_id | name |
|---|---|
| 5 | Ravi Patel |
Now we get Ravi Patel, customer 5, the one with no region.
NULL also spreads through calculations. Here is a real balance calculation, amount minus paid amount, with a LEFT JOIN to payments.
SELECT o.order_id, o.amount, p.paid_amount,
o.amount - p.paid_amount AS balance,
o.amount - COALESCE(p.paid_amount, 0) AS balance_fixed
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE o.order_id IN (1002, 1006, 1007)
ORDER BY o.order_id;| order_id | amount | paid_amount | balance | balance_fixed |
|---|---|---|---|---|
| 1002 | 300.00 | 300.00 | 0.00 | 0.00 |
| 1006 | 275.00 | NULL | NULL | 275.00 |
| 1007 | 400.00 | NULL | NULL | 400.00 |
Orders 1006 and 1007 have no payment row, so paid_amount is NULL and the balance is NULL too, not the full amount. In most databases, concatenating a NULL string gives NULL as well.
COALESCE fixes it. It returns the first value that is not NULL, so a missing payment counts as 0. In balance_fixed the balances are 275.00 and 400.00, and order 1002, paid in full, shows 0.00.
Question 5: You need one list of the customers who ordered in January or February. Should you use UNION or UNION ALL, and what is the difference?
Both stack the results of two queries. UNION removes duplicate rows, UNION ALL keeps every row. Both queries must return the same number of columns, with compatible types, and the column names come from the first query.
Let's list the January customers and the February customers with UNION first.
SELECT customer_id FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31'
UNION
SELECT customer_id FROM orders
WHERE order_date BETWEEN '2026-02-01' AND '2026-02-28'
ORDER BY customer_id;| customer_id |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
Four customers: 1, 2, 3 and 4. Now the same query with UNION ALL.
SELECT customer_id FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31'
UNION ALL
SELECT customer_id FROM orders
WHERE order_date BETWEEN '2026-02-01' AND '2026-02-28'
ORDER BY customer_id;| customer_id |
|---|
| 1 |
| 1 |
| 2 |
| 3 |
| 4 |
Five rows, because customer 1 ordered in both months, so it appears twice. Notice that one ORDER BY at the end sorts the whole combined result.
Which one should you use? UNION has to sort or hash everything to remove the duplicates, so it costs more. In a real pipeline, use UNION ALL whenever the sources cannot overlap, like monthly partitions, and use UNION only when you really want duplicates removed.
Question 6: A teammate needs to empty a staging table before tonight's load. Should they use DELETE, TRUNCATE or DROP, and what does each one really do?
DELETE removes the rows you choose with a WHERE clause. It works row by row, it is fully logged, and it fires triggers. TRUNCATE removes all rows at once, with no WHERE, by deallocating the data pages, so it is much faster on big tables. DROP removes the table itself.
| DELETE | TRUNCATE | DROP | |
|---|---|---|---|
| REMOVES | the rows you choose (WHERE) | all rows, no WHERE | the table itself |
| HOW | row by row, logged, fires triggers | deallocates pages, very fast | data, structure, indexes, permissions |
| ROLLBACK | yes, in a transaction | PostgreSQL, SQL Server: yes; MySQL, Oracle: no | PostgreSQL, SQL Server: yes |
Let's try DELETE on our staging copy of orders and remove the cancelled orders.
DELETE FROM orders_stage
WHERE status = 'Cancelled';The statement runs and changes the data. The check query below shows the result.
SELECT COUNT(*) AS rows_left FROM orders_stage;| rows_left |
|---|
| 10 |
The two cancelled orders are gone and 10 rows are left. Now TRUNCATE.
TRUNCATE TABLE orders_stage;The statement runs and changes the data. The check query below shows the result.
SELECT COUNT(*) AS rows_left FROM orders_stage;| rows_left |
|---|
| 0 |
Zero rows, but the table is still there, with its columns, ready for the next load. That is why staging loads usually truncate and reload.
Two follow-ups interviewers like. Can you roll back a TRUNCATE? In PostgreSQL and SQL Server, yes, inside a transaction. In MySQL and Oracle, it commits immediately.
TRUNCATE resets the IDENTITY counter, and it is blocked when a foreign key references the table.
Scenario 7: The report's count of customers outside the East region is one short of the CRM. The query filters region not equal to 'East'. Why is a customer missing, and how do you fix it?
Here is how this comes up in a real project. A manager says the CRM has 4 customers outside the East region, but our report shows one less. Let's run the report query.
SELECT customer_id, name, region
FROM customers
WHERE region <> 'East'
ORDER BY customer_id;| customer_id | name | region |
|---|---|---|
| 2 | Omar Ali | West |
| 3 | Lina Park | West |
| 6 | Chen Wu | West |
Three customers, all from the West: Omar Ali, Lina Park and Chen Wu. But Ravi Patel has a NULL region. NULL <> 'East' is unknown, not true, so WHERE silently drops him. No error, just a missing row, and that is the one customer the CRM counts and we don't.
The fix is to say what you mean about NULLs:
SELECT customer_id, name, region
FROM customers
WHERE region <> 'East' OR region IS NULL
ORDER BY customer_id;| customer_id | name | region |
|---|---|---|
| 2 | Omar Ali | West |
| 3 | Lina Park | West |
| 5 | Ravi Patel | NULL |
| 6 | Chen Wu | West |
Now we get four customers, including Ravi Patel with his NULL region, and the report matches the CRM.
- A shorter way is
WHERE region IS DISTINCT FROM 'East', which treats NULL as a normal value. It works in PostgreSQL, Snowflake and SQL Server 2022. - The long-term fix is in the data: load unknown regions as a real value, like Unknown, so every report handles them the same way.
Key points to remember
- Logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY
- WHERE filters rows, HAVING filters groups
- COUNT(col) skips NULLs; COUNT(DISTINCT) collapses duplicates
- NULL is unknown: test with IS NULL, default with COALESCE
- UNION ALL unless you need duplicates removed; TRUNCATE for full reloads
The full SQL script
Here is everything in one script: the sample data and every query from this post, in order. Copy it and run it top to bottom.
Show the full SQL script (sample data and every query)
-- ========================================================================
-- SQL Interview Q&A #1 - SQL fundamentals that trip people up
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16 (and cross-checked on DuckDB). Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA sql_q01; SET search_path TO sql_q01;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
INSERT INTO departments VALUES (10, 'Sales'), (20, 'Engineering'), (30, 'HR'), (40, 'Finance');
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(20),
dept_id INT, -- NULL = not assigned yet
manager_id INT, -- NULL = top of the company
salary INT,
hire_date DATE
);
INSERT INTO employees VALUES
(1, 'Aisha', NULL, NULL, 150000, '2018-03-01'),
(2, 'Omar', 20, 1, 120000, '2019-06-15'),
(3, 'Lina', 20, 2, 90000, '2021-01-10'),
(4, 'Ravi', 20, 2, 90000, '2022-04-05'),
(5, 'Mei', 20, 2, 75000, '2024-09-01'),
(6, 'Sara', 10, 1, 95000, '2020-02-20'),
(7, 'Daniel', 10, 6, 105000, '2021-07-01'),
(8, 'Fatima', 10, 6, 60000, '2023-11-12'),
(9, 'John', 30, 1, 70000, '2019-09-30'),
(10,'Zara', NULL, 9, 55000, '2026-01-05');
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(20),
city VARCHAR(20),
region VARCHAR(10), -- NULL = unknown
email VARCHAR(40)
);
INSERT INTO customers VALUES
(1, 'Sara Khan', 'Toronto', 'East', 'sara@mail.com'),
(2, 'Omar Ali', 'Dallas', 'West', 'omar@mail.com'),
(3, 'Lina Park', 'Seattle', 'West', 'lina@mail.com'),
(4, 'Mei Chen', 'Boston', 'East', 'mei@mail.com'),
(5, 'Ravi Patel', 'Austin', NULL, 'ravi@mail.com'),
(6, 'Chen Wu', 'Denver', 'West', 'chen@mail.com'),
(7, 'Sara K.', 'Toronto', 'East', 'sara@mail.com');
CREATE TABLE products (product_id INT PRIMARY KEY, product VARCHAR(20), category VARCHAR(15), price DECIMAL(10,2));
INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 1200.00),
(2, 'Monitor', 'Electronics', 300.00),
(3, 'Keyboard', 'Accessories', 50.00),
(4, 'Mouse', 'Accessories', 25.00),
(5, 'Desk', 'Furniture', 400.00),
(6, 'Chair', 'Furniture', 250.00);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
status VARCHAR(10),
amount DECIMAL(10,2)
);
INSERT INTO orders VALUES
(1001, 1, '2026-01-05', 'Delivered', 1250.00),
(1002, 2, '2026-01-18', 'Delivered', 300.00),
(1003, 4, '2026-02-02', 'Delivered', 650.00),
(1004, 1, '2026-02-14', 'Cancelled', 50.00),
(1005, 3, '2026-02-20', 'Delivered', 1200.00),
(1006, 4, '2026-03-03', 'Shipped', 275.00),
(1007, 5, '2026-03-15', 'Delivered', 400.00),
(1008, 2, '2026-04-01', 'Delivered', 75.00),
(1009, 4, '2026-04-22', 'Delivered', 250.00),
(1010, 1, '2026-05-09', 'Shipped', 300.00),
(1011, 3, '2026-05-30', 'Cancelled', 400.00),
(1012, 7, '2026-06-11', 'Delivered', 50.00);
CREATE TABLE order_items (order_id INT, product_id INT, qty INT, unit_price DECIMAL(10,2));
INSERT INTO order_items VALUES
(1001, 1, 1, 1200.00), (1001, 3, 1, 50.00),
(1002, 2, 1, 300.00),
(1003, 5, 1, 400.00), (1003, 6, 1, 250.00),
(1004, 3, 1, 50.00),
(1005, 1, 1, 1200.00),
(1006, 6, 1, 250.00), (1006, 4, 1, 25.00),
(1007, 5, 1, 400.00),
(1008, 4, 3, 25.00),
(1009, 6, 1, 250.00),
(1010, 2, 1, 300.00),
(1011, 5, 1, 400.00),
(1012, 3, 1, 50.00);
CREATE TABLE payments (payment_id INT PRIMARY KEY, order_id INT, paid_amount DECIMAL(10,2));
INSERT INTO payments VALUES
(1, 1001, 1250.00), (2, 1002, 300.00), (3, 1003, 400.00), (4, 1003, 250.00), (5, 1005, 1200.00);
DROP TABLE IF EXISTS orders_stage;
-- a staging copy of orders, safe to delete from
CREATE TABLE orders_stage (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
status VARCHAR(10),
amount DECIMAL(10,2)
);
INSERT INTO orders_stage SELECT order_id, customer_id, order_date, status, amount FROM orders;
-- Step 1: An alias used in WHERE fails
-- This statement is EXPECTED TO FAIL: it shows the constraint protecting the data.
-- Remove the leading -- to run it and see the error.
-- SELECT order_id,
-- CAST(amount * 0.10 AS DECIMAL(10,2)) AS tax
-- FROM orders
-- WHERE tax > 50
-- ORDER BY tax DESC;
-- Step 2: Repeat the expression in WHERE; the alias works in ORDER BY
SELECT order_id,
CAST(amount * 0.10 AS DECIMAL(10,2)) AS tax
FROM orders
WHERE amount * 0.10 > 50
ORDER BY tax DESC;
-- Step 3: An aggregate in WHERE fails
-- This statement is EXPECTED TO FAIL: it shows the constraint protecting the data.
-- Remove the leading -- to run it and see the error.
-- SELECT customer_id, SUM(amount) AS revenue
-- FROM orders
-- WHERE SUM(amount) > 1000
-- GROUP BY customer_id;
-- Step 4: Row filter in WHERE, group filter in HAVING
SELECT customer_id, COUNT(*) AS order_count,
SUM(amount) AS revenue
FROM orders
WHERE status <> 'Cancelled'
GROUP BY customer_id
HAVING SUM(amount) > 1000
ORDER BY customer_id;
-- Step 5: COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column)
SELECT COUNT(*) AS all_rows,
COUNT(region) AS with_region,
COUNT(DISTINCT region) AS distinct_regions,
COUNT(DISTINCT email) AS distinct_emails
FROM customers;
-- Step 6: = NULL returns nothing (no error)
SELECT customer_id, name
FROM customers
WHERE region = NULL
ORDER BY customer_id;
-- Step 7: IS NULL finds the missing region
SELECT customer_id, name
FROM customers
WHERE region IS NULL
ORDER BY customer_id;
-- Step 8: NULL in arithmetic, and COALESCE
SELECT o.order_id, o.amount, p.paid_amount,
o.amount - p.paid_amount AS balance,
o.amount - COALESCE(p.paid_amount, 0) AS balance_fixed
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE o.order_id IN (1002, 1006, 1007)
ORDER BY o.order_id;
-- Step 9: UNION removes duplicates
SELECT customer_id FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31'
UNION
SELECT customer_id FROM orders
WHERE order_date BETWEEN '2026-02-01' AND '2026-02-28'
ORDER BY customer_id;
-- Step 10: UNION ALL keeps every row
SELECT customer_id FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31'
UNION ALL
SELECT customer_id FROM orders
WHERE order_date BETWEEN '2026-02-01' AND '2026-02-28'
ORDER BY customer_id;
-- Step 11: DELETE removes only the rows you choose
DELETE FROM orders_stage
WHERE status = 'Cancelled';
-- check the result
SELECT COUNT(*) AS rows_left FROM orders_stage;
-- Step 12: TRUNCATE empties the table, the table stays
TRUNCATE TABLE orders_stage;
-- check the result
SELECT COUNT(*) AS rows_left FROM orders_stage;
-- Step 13: The report: region <> East (drops the NULL region)
SELECT customer_id, name, region
FROM customers
WHERE region <> 'East'
ORDER BY customer_id;
-- Step 14: The fix: say what you mean about NULL
SELECT customer_id, name, region
FROM customers
WHERE region <> 'East' OR region IS NULL
ORDER BY customer_id;
Part 2: Joins in Real-Time Scenarios
The sample data on this page is invented for teaching. Every output table is the real result of the query above it, run on PostgreSQL 16 over that sample data, and the portable queries were also checked on a second engine. Where SQL Server syntax is different, the post says so.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.