SELECT, WHERE, and Sorting Data

The overview traced the path from a business question to a result someone can trust, and that path begins with data extraction. Before we can clean records, explore patterns, or train a model, we need a reliable way to pull the right records out of the place where they are stored.

In most organizations, that place is a relational database: sales, orders, customers, inventory, and support tickets usually live in tables inside one. Structured Query Language (SQL) is the language those databases understand, so it is how an analyst asks a database for exactly the rows and columns a question needs, without copying entire tables by hand or waiting on someone else to export them.

That is why SQL comes first. Now let's see how SQL answers everyday business questions.

Which products need restocking now, and which category does each product belong to? A products table can answer that question when it contains rows such as a Wireless Mouse with stock_on_hand of 7 and a USB-C Cable with stock_on_hand of 24.

A table stores related records as rows with named columns. In products, one row describes one product, while columns such as product_name, category, and stock_on_hand describe properties of that product. The product_id column is a primary key, meaning every value in that column uniquely identifies one row. If product_id 103 identifies the Wireless Mouse, no other product row can use 103.

SQL answers a question by returning a result table. Before running a query, predict the rows the query should return. For the low-stock question, products with stock_on_hand of 7, 3, and 9 belong in the result. Products with 10 or more do not.

Filter and sort the products we need

To return only useful columns, SELECT names the columns that belong in the result. WHERE filters rows before SQL produces that result, and ORDER BY sorts the surviving rows. For restocking, sorting from the smallest stock count makes the most urgent product appear first.

The query also uses AS to create an alias, a temporary output name for a selected column. The stored column remains stock_on_hand; the result table can label it units_remaining so the report reads clearly. An alias changes neither the table nor its stored column name.

• Lines 1–7: The code defines the products table with five columns. INT stores whole numbers (product_id, stock_on_hand), TEXT stores strings (product_name, category), and DECIMAL(10, 2) stores exact prices with up to 10 digits, 2 of them after the decimal point. PRIMARY KEY on product_id gives each product row a unique identifier that cannot be NULL, and NOT NULL on every other column requires each row to have a value there.

• Lines 9–17: The code inserts eight products with varied stock_on_hand values, including 7, 3, and 9 so the low-stock filter has matching rows.

• Lines 19–25: The query selects only product_name, category, and an aliased stock_on_hand AS units_remaining, filters to stock_on_hand < 10, then sorts the remaining rows from lowest stock to highest with ORDER BY stock_on_hand ASC. Using DESC instead would reverse this, listing the items with the most remaining stock first.

This query returns only products below 10 units, then orders them from the lowest stock count upward. The output label units_remaining improves readability, but later filters in the same query still use the stored name stock_on_hand.

Predict rows: Read the WHERE condition before reading the selected columns. It determines which product rows can appear at all.

Explicit columns also make a question checkable. A restocking query should show product_name, category, and the stock count because each column helps someone decide what to replenish. price may exist in products, but it does not answer this particular question.

Connect products to sales events

The inventory table describes products, but it cannot show when a product sold or how many units a customer bought. A sales table records those events. Each sales row has a sale_id, a date, a quantity, and a product_id that identifies the product sold.

The sales.product_id column is a foreign key, meaning its values point to the primary-key values in another table. Here, sales.product_id points to products.product_id. A sales row with product_id 103 refers to the same Wireless Mouse product row whose primary key is 103.

• Lines 1–6: The code defines a products table whose product_id primary key the sales table can reference.

• Lines 8–14: sales.product_id is declared as a foreign key referencing products.product_id, enforcing the entity→event link so each sale row must point at a valid product.

• Lines 16–21: The code inserts five products so every product_id used in sales has a matching product row.

• Lines 23–33: The 10 sales rows deliberately reuse product IDs (for example, 103 appears multiple times) to reflect that one product entity can have many sale events.

• Lines 35–42: The query avoids SELECT * and filters sales on or after 2026-01-01, returning only sale_id, product_id, quantity, and sale_date (ordered by date, then ID) so the foreign-key references are easy to verify.

This result returns sales on or after 2026-01-01. The filter works the same way as the inventory filter, but the rows represent events rather than products. One product can appear in many sales rows because a product can be sold more than once.

The distinction matters when we ask larger questions later. products describes entities we stock. sales describes events that happened to those entities. The shared product_id makes the relationship unambiguous and gives SQL a reliable way to connect the tables.

Keep result tables easy to verify

Because each query should answer one visible question, avoid starting with SELECT *. The asterisk requests every column in a table. It can hide the columns that matter among unrelated details, and the result becomes harder to inspect when tables gain more columns.

For example, a query about recent quantities sold should request sale_id, product_id, sale_date, and quantity. That column list makes each returned row understandable. We can then verify the result with simple checks:

• Count the returned rows and compare the count with the dates that meet the filter.

• Scan sale_id values for unexpected duplicates.

• Scan columns that should contain information for unexpected NULL values.

• Confirm the first and last rows match the requested sort order when ORDER BY is present.

The key distinction is what each part changes. WHERE chooses rows, ORDER BY rearranges selected rows, and AS changes only a result-column label. Primary keys identify rows within a table, while foreign keys connect a row to a related table.

A SELECT query shown here tells us exactly which rows meet a condition and in what order they appear. It does not summarize many rows into one answer. The next lesson uses GROUP BY and HAVING when row-level results are too detailed for the question.