Your First Query — SELECT & FROM

SELECT, FROM, WHERE, JOIN

SELECT
  first_name,
  last_name,

  first_name,
  last_name,

SELECT ...
FROM ...
WHERE ...

unit_price - cost_price  AS margin

FROM customers;

SELECT * retrieves all columns from the specified table in their defined order. Selecting specific columns — SELECT col1, col2, col3 — retrieves only the named columns in the order you specify. The data returned is a subset of what SELECT * would return, and the columns appear in your chosen order regardless of their order in the table definition.

SELECT * is appropriate for exploration — when you want to see what a table contains or are debugging a data issue. In production code it has three significant problems. First, it retrieves unnecessary data — if your application needs 2 columns but SELECT * fetches 15, you waste network bandwidth and memory on every execution. Second, it breaks silently when schema changes — if a column is added or removed, code that expects specific columns at specific positions will produce incorrect results with no error. Third, it prevents covering index optimisation — the database cannot serve the query from an index alone if you ask for all columns.

The professional rule: use SELECT * only in interactive exploration and debugging. Every query that runs in an application, a scheduled job, a dashboard, or a data pipeline should name its columns explicitly.

The written order of a SQL query (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT) does not match the execution order. The database executes clauses in this sequence: FROM first (identify the source table or tables), WHERE second (filter individual rows), GROUP BY third (group the filtered rows), HAVING fourth (filter the groups), SELECT fifth (compute the output columns from the remaining rows), ORDER BY sixth (sort the result), LIMIT last (cut to the specified number of rows).

This execution order has practical consequences. It explains why you cannot use a SELECT alias in a WHERE clause — WHERE runs before SELECT, so the alias does not exist yet when WHERE is evaluated. It explains why you can use a SELECT alias in ORDER BY — ORDER BY runs after SELECT. It explains why aggregate functions like COUNT() and SUM() appear in SELECT and HAVING but not in WHERE — WHERE runs before grouping, so aggregate values do not exist yet at that point.

Understanding execution order also helps with query optimisation. WHERE filtering happens early (before SELECT projection), so effective WHERE conditions reduce the number of rows processed by all subsequent steps. Applying filters as early as possible — through WHERE conditions on indexed columns — is one of the most impactful query performance improvements you can make.

SELECT DISTINCT returns only unique rows — it eliminates duplicate rows from the result set. When you list multiple columns, DISTINCT applies to the combination: every unique combination of the listed columns appears exactly once in the output. SELECT DISTINCT city returns each city once. SELECT DISTINCT city, loyalty_tier returns each unique city-tier pair once.

DISTINCT is appropriate when you genuinely need unique values — finding all distinct cities a company has customers in, all distinct product categories, all distinct payment methods used. It is also useful for data profiling: DISTINCT shows you what values actually exist in a column, which is valuable when exploring a new database.

DISTINCT should be avoided when you do not actually need unique values, because it carries a performance cost — the database must sort or hash all rows to identify and eliminate duplicates. On large tables this can be significantly slower than a plain SELECT. A common mistake is using SELECT DISTINCT to work around a query that returns more rows than expected — this hides an underlying JOIN or filter bug rather than fixing it. The correct fix is to identify why duplicates are appearing (usually a many-to-one JOIN) and address the root cause.

Yes. SELECT can perform arithmetic, string operations, date calculations, and function calls on column values, and return the computed result as a new column in the output. The original table is never modified — calculations happen during query execution and exist only in the result set.

Arithmetic examples: unit_price - cost_price computes the profit margin per product. total_amount * 1.18 applies 18% GST to an order total. quantity * unit_price recalculates the line total. These use the standard arithmetic operators: + (add), - (subtract), * (multiply), / (divide), % (modulo). Division between integers produces an integer in some databases — use CAST or decimal literals if you need decimal division.

String operation example: first_name || ' ' || last_name concatenates first and last name with a space (PostgreSQL/DuckDB syntax). MySQL uses CONCAT(first_name, ' ', last_name). Date example: delivery_date - order_date returns the number of days between two dates in PostgreSQL. Built-in function example: ROUND(price * 0.18, 2) rounds a calculated tax value to 2 decimal places. All computed columns can be given a readable name using AS: unit_price - cost_price AS profit. Without AS, the column header in the result shows the raw expression.

SQL comments are text in a query that the database engine ignores completely — they exist only for humans reading the query. There are two comment syntaxes. Single-line comments start with two hyphens (--) and everything after them on that line is ignored. Multi-line comments start with /* and end with */ and can span any number of lines.

Comments serve four practical purposes in professional SQL work. First, explanation: a non-obvious calculation or filter needs an explanation of the business rule behind it — WHY you are multiplying by 1.18, not just WHAT the multiplication does. Second, attribution: who wrote the query and when, especially in shared query libraries. Third, toggling: commenting out individual SELECT columns or WHERE conditions is easier than deleting and retyping them when debugging. Fourth, documentation: queries in a data pipeline or scheduled job should have a comment explaining what business question they answer and which team owns them.

The mark of a professional SQL writer is queries that read like documentation — the structure tells you what, the comments tell you why. A query you write today at 10 AM should be fully understandable to a colleague at 3 PM without your explanation. This becomes increasingly important as companies grow and queries are shared across teams and maintained over years.

Cause: The column name in your SELECT does not exactly match any column name in the table. Here 'firstname' was typed instead of 'first_name' (missing the underscore). SQL column names must match exactly — a typo in any character causes this error. This also happens when you try to select a column from the wrong table, or when a column name was changed and your query was not updated.

Fix: Check the exact column names available: SELECT * FROM customers LIMIT 1; — this shows you every column header. In DBeaver or TablePlus you can expand the table in the schema panel to see column names. Once you find the right name, update your query. If you are on a new database and do not know the columns, run: SELECT column_name FROM information_schema.columns WHERE table_name = 'customers' ORDER BY ordinal_position; to get the full list.

Cause: The table name in your FROM clause does not match any table in the current database. Here 'customer' was typed instead of 'customers' (missing the s). Like column names, table names must match exactly. This also happens when you are connected to the wrong database, when the table exists in a different schema, or when the table simply has not been created yet.

Fix: List all tables in the current database: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'; (PostgreSQL) or SHOW TABLES; (MySQL). Find the correct table name and update your FROM clause. If the table is missing entirely, it may need to be created or you may be connected to the wrong database.

Cause: A keyword is misspelled — 'FORM' instead of 'FROM'. The database parser reached 'FORM', did not recognise it as any valid SQL keyword, and stopped with a syntax error. The error message tells you exactly where in the query the parser got confused — 'at or near FORM' — so you can find the typo quickly. SQL keywords must be spelled correctly but are not case-sensitive.

Fix: Read the error message — it tells you which word it did not recognise. Correct the spelling: FROM not FORM. To avoid keyword typos, use a SQL editor with autocomplete and syntax highlighting (VS Code with SQLTools, DBeaver, or DataGrip) — unrecognised keywords are highlighted in a different colour before you even run the query.

Cause: You are using a table alias (c.first_name) but you never defined the alias. The prefix 'c.' tells the database you want the first_name column from a table aliased as 'c' — but no table in your FROM clause has that alias. This happens when you start writing a JOIN query with aliases and forget to actually add the alias to the FROM clause.

Fix: Either remove the table prefix (SELECT first_name FROM customers;) or add the alias definition to FROM: SELECT c.first_name FROM customers c; — the alias is defined by placing it after the table name. Aliases become necessary and important in JOIN queries (Module 30 onwards) where the same column name might exist in multiple tables and you need to specify which table's version you want.

Cause: The denominator in your division calculation evaluated to zero for at least one row. Here, if unit_price equals cost_price (zero margin product), the expression unit_price - cost_price evaluates to 0, and dividing by 0 is mathematically undefined. SQL databases raise an error and stop the entire query when division by zero occurs, even if only one row in a million triggers it.

Fix: Use NULLIF to safely handle potential zero denominators: SELECT unit_price / NULLIF(unit_price - cost_price, 0) FROM products; — NULLIF(expression, 0) returns NULL instead of 0, and dividing by NULL returns NULL instead of raising an error. The affected rows will show NULL in the result, which is usually the correct behaviour (indicating the calculation is not applicable for that row). You can then use COALESCE to replace NULL with a default: COALESCE(unit_price / NULLIF(unit_price - cost_price, 0), 0).

FreshMart wants a product listing for their website. Write a SELECT query that returns: the product name, category, brand, price (formatted as unit_price with an alias 'price'), and a calculated column called 'gst_price' which is the unit_price multiplied by 1.18 (adding 18% GST), rounded to 2 decimal places. Only return products that are in stock. Order by price ascending.