The SELECT Statement

SQL is how data analysts talk to databases. The SELECT statement is the first sentence you learn — and the one you'll write thousands of times. Every analysis starts here.

Learning Objectives

• Understand the anatomy of a SQL query
• Retrieve specific columns from a table
• Use column aliases to rename output
• Write readable, well-formatted SQL
• Understand why SELECT * is a production anti-pattern

---

The Anatomy of a SQL Query

Every SQL query follows this basic structure:

SELECT column1, column2, column3
FROM table_name;

Two clauses, one semicolon. That's the minimum viable query.

The order SQL executes vs. the order you write it

This trips up beginners constantly. You write SELECT first, but SQL evaluates FROM first.

Logical execution order: 1. FROM — which table? 2. WHERE — which rows? 3. GROUP BY — which groups? 4. HAVING — filter groups? 5. SELECT — which columns? 6. ORDER BY — what order? 7. LIMIT — how many rows?

Understanding this explains why you can't use a column alias in a WHERE clause — the alias doesn't exist yet when WHERE runs. It also explains why HAVING exists separately from WHERE.

Why this order matters in practice

If you try SELECT quantity * unit_price AS order_total FROM orders WHERE order_total > 100, you get an error in PostgreSQL (and silent wrong results in some MySQL modes). The alias order_total is defined in step 5 — WHERE runs at step 2 and can't see it yet. Write WHERE quantity * unit_price > 100 instead.

---

The Sample Schema

All examples in this module use these four tables. Familiarise yourself with the structure before writing queries — understanding the data model is step zero.

CREATE TABLE customers (
    customer_id   VARCHAR(10)   PRIMARY KEY,
    first_name    VARCHAR(50),
    last_name     VARCHAR(50),
    email         VARCHAR(100),
    city          VARCHAR(50),
    country       VARCHAR(50),
    signup_date   DATE,
    segment       VARCHAR(20)   -- 'VIP', 'Regular', 'New'
);

CREATE TABLE products (
    product_id    VARCHAR(10)   PRIMARY KEY,
    product_name  VARCHAR(100),
    category      VARCHAR(50),
    unit_price    DECIMAL(10,2),
    cost_price    DECIMAL(10,2),
    stock_qty     INT
);

CREATE TABLE orders (
    order_id      VARCHAR(10)   PRIMARY KEY,
    customer_id   VARCHAR(10),  -- references customers.customer_id
    product_id    VARCHAR(10),  -- references products.product_id
    order_date    DATE,
    quantity      INT,
    unit_price    DECIMAL(10,2),
    discount_pct  DECIMAL(5,2), -- NULL when no discount applied
    status        VARCHAR(20),  -- 'completed', 'pending', 'cancelled', 'refunded'
    delivery_date DATE          -- NULL when not yet delivered
);

CREATE TABLE order_items (
    item_id       INT           PRIMARY KEY AUTO_INCREMENT,
    order_id      VARCHAR(10),
    product_id    VARCHAR(10),
    quantity      INT,
    unit_price    DECIMAL(10,2)
);

Relationships: - Each order belongs to one customer (via customer_id) - Each order is for one product (via product_id) - order_items records individual line items for multi-product orders

---

Selecting Specific Columns

Retrieve everything

SELECT *
FROM orders;

Sample output:

order_id	customer_id	product_id	order_date	quantity	unit_price	discount_pct	status	delivery_date
O001	C001	P001	2024-01-05	2	149.00	NULL	completed	2024-01-08
O002	C003	P004	2024-01-07	1	389.00	10.00	completed	2024-01-12
O003	C002	P003	2024-01-09	3	49.99	NULL	pending	NULL

Avoid SELECT * in production

SELECT * returns every column. On a table with 50 columns and 10 million rows this wastes memory and query time. If someone adds or removes a column, your query silently changes behaviour. Always name the columns you actually need.

Retrieve specific columns

SELECT
    order_id,
    customer_id,
    order_date,
    unit_price
FROM orders;

Sample output:

order_id	customer_id	order_date	unit_price
O001	C001	2024-01-05	149.00
O002	C003	2024-01-07	389.00
O003	C002	2024-01-09	49.99

---

Column Aliases

Aliases rename a column in the output — useful for clarity, readability, or when the column is a calculation.

SELECT
    order_id        AS "Order ID",
    customer_id     AS customer,
    unit_price      AS price_usd
FROM orders;

Calculated columns

Business question: "What is the gross total for each order?"

SELECT
    order_id,
    customer_id,
    quantity,
    unit_price,
    quantity * unit_price   AS gross_total
FROM orders;

Sample output:

order_id	customer_id	quantity	unit_price	gross_total
O001	C001	2	149.00	298.00
O002	C003	1	389.00	389.00
O003	C002	3	49.99	149.97

For mid-level analysts

Calculated columns in SELECT are not stored — they're computed at query time. If you need the same calculation across many queries, consider a view or a generated/computed column (MySQL 5.7+, PostgreSQL 12+). Generated columns store the result physically and can be indexed.

Handling NULL in calculations

If discount_pct is NULL, quantity * unit_price * (1 - discount_pct / 100) evaluates to NULL. Use COALESCE to treat NULL as zero:

SELECT
    order_id,
    customer_id,
    quantity * unit_price                                           AS gross_total,
    quantity * unit_price * (1 - COALESCE(discount_pct, 0) / 100)  AS net_total
FROM orders;

Sample output:

order_id	customer_id	gross_total	net_total
O001	C001	298.00	298.00
O002	C003	389.00	350.10
O003	C002	149.97	149.97

COALESCE(value, default)

Returns the first non-NULL argument. COALESCE(discount_pct, 0) means: "use discount_pct if it has a value; otherwise use 0." This prevents NULL from propagating through arithmetic.

---

String Concatenation

Combining first and last name is a very common operation. Syntax differs between databases.

MySQL / MariaDB:

SELECT
    customer_id,
    CONCAT(first_name, ' ', last_name)  AS full_name,
    email
FROM customers;

PostgreSQL / ANSI SQL:

SELECT
    customer_id,
    first_name || ' ' || last_name      AS full_name,
    email
FROM customers;

Sample output:

customer_id	full_name	email
C001	Priya Sharma	priya@gmail.com
C002	Arjun Mehta	arjun@company.com
C003	Sarah Johnson	sarah@gmail.com

MySQL vs PostgreSQL — concatenation

The || operator works in PostgreSQL and ANSI SQL but NOT in MySQL by default (MySQL uses it as a logical OR). Always use CONCAT() in MySQL. PostgreSQL supports both.

---

Formatting Your SQL

-- Good: uppercase keywords, one clause per line, aligned columns
SELECT
    order_id,
    customer_id,
    order_date,
    quantity * unit_price   AS gross_total
FROM orders
WHERE status = 'completed'
ORDER BY gross_total DESC;

-- Bad: everything on one line, no alignment
select order_id,customer_id,order_date,quantity*unit_price as gross_total from orders where status='completed' order by gross_total desc;

Both return identical results. The first one is readable by a colleague at 2am during an incident.

Formatting conventions to follow

• SQL keywords in UPPERCASE
• One clause per line (SELECT, FROM, WHERE, ORDER BY)
• Column list indented, one per line for queries with more than two columns
• Align AS keywords using spaces
• Semicolon at the end of the statement

---

Common Mistakes

Trailing comma on the last column

SELECT
    order_id,
    customer_id,   -- ← this comma causes a syntax error
FROM orders;

Remove the comma after the last column before FROM. This is one of the most common beginner errors.

Column names with spaces

Wrap them in backticks (MySQL) or double quotes (PostgreSQL):

SELECT `Order Date` FROM orders;      -- MySQL
SELECT "Order Date" FROM orders;      -- PostgreSQL

Avoid column names with spaces in any schema you design — they cause problems everywhere.

Using a SELECT alias in WHERE

-- WRONG — alias 'gross_total' does not exist when WHERE runs
SELECT quantity * unit_price AS gross_total
FROM orders
WHERE gross_total > 100;

-- CORRECT — repeat the expression
SELECT quantity * unit_price AS gross_total
FROM orders
WHERE quantity * unit_price > 100;

---

Practice Exercises

Use the customers table: customer_id, first_name, last_name, email, city, country, signup_date, segment.

Warm-up 1. Retrieve all columns from customers. 2. Retrieve only customer_id, first_name, last_name, and email. 3. Alias first_name as "First Name" and last_name as "Last Name".

Main 4. Return customer names as a single full_name column (first name + space + last name). Use the syntax for your database. 5. Retrieve customer_id, and a calculated days_since_signup — assume today is '2024-03-01'. In MySQL: DATEDIFF('2024-03-01', signup_date). In PostgreSQL: '2024-03-01'::date - signup_date. 6. Add a calculated gross_total column (quantity * unit_price) and net_total (accounting for discount) to a query on the orders table.

Stretch 7. Format customer info for a mailing label: combine first_name, last_name, city, and country into a single mailing_label column. Format: "Priya Sharma, Mumbai, India". 8. From the orders table, calculate the gross margin per order: (unit_price - cost_price) * quantity. You'll need to join products to get cost_price. Write the query with a table alias for each table.

---

Interview Questions

[Beginner] What does SELECT * do, and when should you avoid it?

Show answer

SELECT * retrieves every column in the table. It is fine for ad-hoc exploration (always pair it with LIMIT) but problematic in production code because:

• It transfers unnecessary data, wasting network bandwidth and memory
• If a column is added or removed from the table, the query output changes silently — reports break without an obvious error
• Query optimisers cannot use covering indexes when all columns are selected

In production queries and dashboards, always name the specific columns you need.

---

[Beginner] What is a column alias and how do you create one?

Show answer

An alias gives a column a different name in the query output. Create one with the AS keyword:

SELECT
    unit_price          AS price,
    quantity * unit_price   AS gross_total
FROM orders;

The AS keyword is optional in most databases (quantity * unit_price gross_total also works), but including it makes the query more readable. Aliases are available in ORDER BY but not in WHERE or GROUP BY in most databases.

---

[Mid-level] Explain the logical execution order of a SQL query. Why does it matter that FROM runs before SELECT?

Show answer

SQL processes clauses in this logical order — not the order you write them:

1. FROM — identify the table and load rows
2. WHERE — filter rows
3. GROUP BY — group rows
4. HAVING — filter groups
5. SELECT — compute columns and assign aliases
6. ORDER BY — sort output
7. LIMIT — return N rows

This order matters because:

• Aliases in WHERE don't work: The alias is defined at step 5, but WHERE runs at step 2. Use the expression directly: WHERE quantity * unit_price > 100, not WHERE gross_total > 100.
• Aliases in ORDER BY do work: ORDER BY runs at step 6, after SELECT, so aliases are visible.
• HAVING requires GROUP BY first: Aggregate functions in HAVING are computed during step 3-4. Using WHERE COUNT(*) > 5 is a syntax error because rows haven't been grouped yet.

---

[Mid-level] Can you use a column alias in the WHERE clause? Why or why not?

Show answer

No — not in standard SQL or PostgreSQL. The alias defined in SELECT is not available in WHERE because WHERE runs before SELECT in the logical execution order.

-- ERROR in PostgreSQL, silently wrong in some MySQL modes
SELECT quantity * unit_price AS gross_total
FROM orders
WHERE gross_total > 100;

-- CORRECT: repeat the expression
SELECT quantity * unit_price AS gross_total
FROM orders
WHERE quantity * unit_price > 100;

MySQL in some configurations does allow alias references in HAVING but not in WHERE. PostgreSQL is strict about both. Always use the expression directly in WHERE.

---

[Senior] A junior analyst writes SELECT * FROM large_table in a production dashboard that refreshes every minute on a 200-column, 50-million-row table. What are the problems, and how would you fix them?

Show answer

Problems:

1. Data volume: 200 columns × 50M rows × avg 20 bytes/column = ~200GB of data scanned per query. Refreshing every minute means ~288TB of reads per day.
2. Network transfer: Only 10–15 columns are probably displayed on the dashboard. Fetching all 200 wastes network bandwidth on every refresh.
3. Memory pressure: The dashboard tool must load the full 200-column result into memory before rendering.
4. Schema fragility: If the table schema changes (column added, renamed, dropped), the dashboard breaks silently or behaves unexpectedly.
5. Index incompatibility: SELECT * prevents use of covering indexes — the database must access the heap for every row.

How to fix it: 1. Replace SELECT * with explicit column names for only the 10–15 columns the dashboard renders. 2. Add a WHERE clause to filter to the relevant date range (last 30 days, current month, etc.). 3. Consider materialising the query result into a summary table refreshed every 5 minutes via a scheduled job — the dashboard queries the small summary, not the raw 50M-row table. 4. Add indexes on the WHERE and ORDER BY columns if they don't already exist.

---

Previous: 00-agenda | Next: 02-where-clause