Select only the evidence you need

Estimated study time: 45–60 minutes. Read, predict, run, explain, then attempt the independent work.

The manager first wants to see which customers exist. We do not need a total yet; we need a trustworthy sample. SQL's SELECT clause chooses columns, and FROM identifies the source. A comma separates expressions in the output. A semicolon ends the statement. SQL keywords are conventionally uppercase here to make the structure visible; unquoted PostgreSQL identifiers are folded to lowercase.

A query can rename an output with AS. In the first example, customer_name AS name makes the result heading shorter without renaming the stored column. An alias belongs to that query result. It does not add a field to the database. Similarly, selecting customer_id and city does not delete the other customer fields. Think of SELECT as choosing a view of the existing evidence.

A sample needs a stable order

Tables have no guaranteed presentation order. Even when rows appear in insertion order today, a different plan or maintenance operation can change what appears first. ORDER BY customer_id makes the result predictable. ASC is ascending and is the default; DESC puts larger values first. When two rows can tie, add a unique identifier as a final ordering key. A price ordering with product_id as a tie-breaker gives a repeatable top two even when prices match.

LIMIT takes the first specified number of rows after ordering. It helps inspect a sample; it does not prove that omitted records are clean. It also does not guarantee that the database will avoid expensive work earlier in the query. A result with LIMIT 10 can still require a large join or sort. Performance and accuracy are separate questions.

SELECT * is convenient when first inspecting a small table because the star requests all columns. For a saved analysis, list the intended columns. This documents the contract, reduces accidental exposure of unnecessary fields, and avoids a report silently changing when a new column is added. In this course the names are synthetic, but the habit matters when real data contains addresses or other private information.

Discover categories without deleting records

DISTINCT removes duplicate result rows. If you select only city, all Pune occurrences become one city label. If you select city and customer_id together, each different pair remains. DISTINCT therefore works on the full selected row, not one secretly special column. It is useful for examining categories, but it is not a general repair for duplicated business events.

NULL represents an unknown or missing value. In the city list it remains a separate result value. NULLS LAST makes its display position explicit. You have not yet decided why the city is missing or what business action is appropriate; you have only made the absence visible. Module 3 will show why filtering a missing value needs special treatment.

A good first inspection ends with a sentence: “This result contains one row per customer, sorted by customer_id, and limited to three rows.” That sentence states grain and selection. Practice writing it before running the examples. If the result's columns or number of rows cannot support the sentence, revise the query or the claim.

Worked example 02 select

Read the statement from the source table to the chosen columns, then the ordering and the limit. Predict the three identifiers before running it. The alias changes only the heading.

SELECT customer_id, customer_name AS name, city
FROM aabha_sql.customers
ORDER BY customer_id
LIMIT 3;

Expected result from the bundled dataset:

The runnable file is sql/examples/02_select.sql.

Worked example 02 distinct

This answers “Which city labels appear?” rather than “How many customers live in each city?” The missing city remains visible.

SELECT DISTINCT city FROM aabha_sql.customers ORDER BY city NULLS LAST;

Expected result from the bundled dataset:

The runnable file is sql/examples/02_distinct.sql.

Independent practice

02a

Return the two most expensive products with product_id, product_name and list_price. Sort descending by list_price, then ascending by product_id.

Hint: A stable tie-breaker belongs after the main ordering expression.

02b

List each distinct sales channel once in alphabetical order.

Hint: Choose only channel before applying DISTINCT.

Check before moving on

Explain why an unordered LIMIT is not a reliable top-N report, and why DISTINCT city and DISTINCT city, customer_id have different meanings.