SQL Foundations: SELECT, WHERE, and ORDER BY

Concept

SQL is the language used to query relational data. A table stores rows of observations and columns of attributes. In an analytics schema, customers has one row per customer and orders has one row per order. A primary key, such as customer_id or order_id, uniquely identifies a row.

Why It Matters

Data Scientists regularly use SQL to inspect source data, create analysis-ready extracts, and calculate business metrics before moving to Python or Pandas. It lets the database return only the relevant columns and records instead of exporting every table.

SELECT and LIMIT

SELECT
  customer_id,
  name,
  region
FROM customers
LIMIT 5;

This returns a small sample of three named columns. SELECT * is useful for a quick inspection, but selecting explicit columns is safer in reusable work because it documents what is needed. AS gives a result column a readable alias, and DISTINCT returns unique values.

SELECT DISTINCT
  region AS customer_region
FROM customers
ORDER BY customer_region;

WHERE: Filter Rows

SELECT
  order_id,
  customer_id,
  revenue
FROM orders
WHERE status = 'completed'
  AND revenue > 500
ORDER BY revenue DESC
LIMIT 10;

The expected result is the ten highest-value completed orders above 500. WHERE evaluates conditions for individual rows. Use comparisons, AND, OR, NOT, IN, BETWEEN, and LIKE to express a question. Parentheses make mixed AND and OR logic clear.

SELECT
  customer_id,
  name
FROM customers
WHERE region IN ('West', 'North')
  AND signup_date BETWEEN '2025-01-01' AND '2025-12-31';

Use IS NULL or IS NOT NULL for missing values; = NULL is not the correct SQL test. ORDER BY revenue DESC, order_date ASC sorts by revenue first and resolves ties by date. ASC is the default direction.

Intuition and SQL Execution

Think: choose a table, keep qualifying rows, select useful columns, then present them in order. Databases may optimize the physical work differently, but this mental model is enough for writing clear beginner queries. Filtering rows with WHERE revenue > 500 differs from filtering a summary such as total revenue; summaries are filtered later with HAVING.

Best Practices

Start with LIMIT, inspect column meanings, and select explicit fields. Use aliases that communicate the metric. Keep SQL portable where possible; date functions and some syntax differ across PostgreSQL, MySQL, SQL Server, and SQLite.

Data Science Perspective

The common workflow is SQL to extract or aggregate data, then Python/Pandas for deeper analysis or modeling. SQL filters can also define a reproducible cohort for an experiment or model dataset.

Interview Perspective

Question: Why use ORDER BY with LIMIT? Answer: LIMIT alone returns an arbitrary subset; sorting defines which records are top or most recent. Follow-up: explain why WHERE cannot filter SUM(revenue).

Practice Questions

1. Write a query for customers in the West region.
2. Return the five highest-revenue completed orders.
3. Find orders whose status is missing.

Quick Quiz

1. What does DISTINCT do? Answer: returns unique values or combinations.
2. How do you test for missing SQL values? Answer: IS NULL.
3. What does DESC mean? Answer: descending order.

A Practical Analytical Workflow

For a new table, work in small, reviewable steps: first inspect a few rows with SELECT * ... LIMIT 10; then select only relevant fields; filter the business cohort; sort the result to expose useful records; and keep the output limited while exploring. Rows are individual observations, columns are their attributes, and a stable primary key lets you refer to one row reliably. Prefer customer_id over a name, which may repeat or change.

NULL means an unknown or absent value. It is not the number 0, and it is not an empty string. SQL uses three-valued logic around unknown values, so column = NULL does not evaluate to true. Use IS NULL or IS NOT NULL explicitly. LIKE 'West%' supports a text pattern, while IN is clearer than several OR conditions for a known list.

Improved Interview Perspective

Question: Why avoid SELECT * in an analytical extract? Answer: explicit columns reduce data transfer, protect the query from unrelated schema changes, and document the intended dataset. What this tests: whether you write maintainable analysis, not only syntax. Follow-up: explain why LIMIT without ORDER BY is not a top-N query.

Next Lesson

Next, calculate metrics with aggregations, groups, and HAVING.