# SQL & Database Explorer — 30 query workshops

DiscoveryVIP · October 9, 2026

Uses SQLite syntax and fictional records. Prices and revenues are integer cents. Other SQL engines differ. The lab runs one read-only query at a time; every run starts from the same dataset.

## 1. Read your first table

A relational table stores rows with named columns. SELECT describes the result you want, while FROM identifies the source. SQL keywords are not case-sensitive here. Row order is not guaranteed unless you request it.

Task: Return all customer columns, ordered by id.

Hint: Use SELECT * FROM customers, then ORDER BY id.

```sql
SELECT * FROM customers ORDER BY id;
```

## 2. Choose useful columns

Selecting explicit columns makes the result contract clear and avoids transferring fields you do not need. Column order follows your SELECT list. This matters to reports and client code that expect a particular shape.

Task: Return name and city from customers, ordered by id.

Hint: List name before city.

```sql
SELECT name, city FROM customers ORDER BY id;
```

## 3. Name an output column

An alias gives a result column a readable name without renaming the stored field. AS makes the intent explicit. Use stable aliases in reports instead of relying on engine-generated expression names.

Task: Return customer name as customer_name, ordered by id.

Hint: AS changes the output label only.

```sql
SELECT name AS customer_name FROM customers ORDER BY id;
```

## 4. Filter a known value

WHERE filters rows before they become results. Strings use single quotes in standard SQL. A filter expresses a condition; it does not modify or delete source records. Compare exact stored values in this dataset.

Task: Return id and name for Toronto customers, ordered by id.

Hint: Put the city condition in WHERE.

```sql
SELECT id, name FROM customers WHERE city = 'Toronto' ORDER BY id;
```

## 5. Sort and limit

ORDER BY defines ordering; DESC reverses it. LIMIT restricts the number of result rows. Add a tie-breaker when equal values are possible so repeated runs remain predictable. A limit without ordering does not mean top-ranked.

Task: Return name and price_cents for the three most expensive products, with id as ascending tie-breaker.

Hint: Order by price descending before applying LIMIT.

```sql
SELECT name, price_cents FROM products ORDER BY price_cents DESC, id ASC LIMIT 3;
```

## 6. Combine conditions

AND requires both conditions. OR accepts either and has lower precedence than AND. Parentheses communicate the intended grouping and prevent subtle changes when a condition is extended later.

Task: Return name and price_cents for Books priced at least 3000 cents, ordered by id.

Hint: Combine category and price with AND.

```sql
SELECT name, price_cents FROM products WHERE category = 'Books' AND price_cents >= 3000 ORDER BY id;
```

## 7. Match a list of choices

IN is a readable way to test against several values. It replaces a chain of equality comparisons connected by OR. Null values require separate thought because SQL uses three-valued logic, not just true and false.

Task: Return id and name for customers in London or Berlin, ordered by id.

Hint: Put the permitted cities inside IN (...).

```sql
SELECT id, name FROM customers WHERE city IN ('London','Berlin') ORDER BY id;
```

## 8. Use an inclusive range

BETWEEN includes both endpoints. For timestamps, a half-open range is often clearer because the final day may contain many times. This exercise uses integer prices so the endpoint behavior is easy to observe.

Task: Return name and price_cents for prices from 1200 through 2500 inclusive, ordered by price_cents then id.

Hint: BETWEEN lower AND upper includes both bounds.

```sql
SELECT name, price_cents FROM products WHERE price_cents BETWEEN 1200 AND 2500 ORDER BY price_cents, id;
```

## 9. Find a text pattern

LIKE uses % for any run of characters and _ for a single character. Case behavior varies between database engines and collations. SQLite’s default LIKE behavior is case-insensitive for ASCII; do not assume identical Unicode matching everywhere.

Task: Return id and name for products ending in book, ordered by id.

Hint: The % wildcard goes before book.

```sql
SELECT id, name FROM products WHERE name LIKE '%book' ORDER BY id;
```

## 10. Understand missing values

NULL represents missing or unknown information. Equality with NULL does not work like equality with a normal value. Use IS NULL or IS NOT NULL. Missing email is different from an empty string or a known address.

Task: Return id and name for customers with no email, ordered by id.

Hint: Use IS NULL, not = NULL.

```sql
SELECT id, name FROM customers WHERE email IS NULL ORDER BY id;
```

## 11. List distinct categories

DISTINCT removes duplicate result rows after the selected expressions are evaluated. It operates on the whole selected row, not one field independently. It is useful for a category list, but can hide a mistaken join if added without understanding duplicates.

Task: Return distinct product categories in alphabetical order.

Hint: Use DISTINCT with only category in the SELECT list.

```sql
SELECT DISTINCT category FROM products ORDER BY category;
```

## 12. Count rows and known values

COUNT(*) counts rows, while COUNT(column) counts non-NULL values. Neither automatically means unique people or transactions. Name the counts so readers know the denominator and what has been excluded.

Task: Return total_customers and known_emails for the customer table.

Hint: COUNT(email) excludes the two NULL values.

```sql
SELECT COUNT(*) AS total_customers, COUNT(email) AS known_emails FROM customers;
```

## 13. Calculate a line total

Expressions can derive a value without changing the stored data. This dataset stores prices in integer cents. Multiplying price by quantity produces cents too. State units in the column alias so a later chart does not mislabel the amount.

Task: Return product_id, quantity and quantity * 800 as sample_total_cents for order 101, ordered by product_id.

Hint: This is an arithmetic demonstration, not the real total for differently priced products.

```sql
SELECT product_id, quantity, quantity * 800 AS sample_total_cents FROM order_items WHERE order_id = 101 ORDER BY product_id;
```

## 14. Group before aggregating

GROUP BY forms groups for aggregate calculations. Every selected non-aggregate field should describe the grouping. SQLite permits some bare-column queries that other databases reject; avoid relying on that permissive behavior.

Task: Return category and product_count for every category, ordered by category.

Hint: Group by the category you also select.

```sql
SELECT category, COUNT(*) AS product_count FROM products GROUP BY category ORDER BY category;
```

## 15. Filter groups with HAVING

WHERE filters source rows before grouping. HAVING filters aggregated groups afterward. Keeping these stages separate prevents confusing a per-record condition with a condition on a count or sum.

Task: Return customer_id and order_count for customers with at least two orders, ordered by customer_id.

Hint: Apply the count condition in HAVING.

```sql
SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id HAVING COUNT(*) >= 2 ORDER BY customer_id;
```

## 16. Join related records

An inner join combines matching rows from two sources. The ON condition defines the relationship. A customer with several orders appears several times, because the result grain is one row per matching order, not one row per customer.

Task: Return order_id and customer_name for all orders, ordered by order id.

Hint: Join customer primary key to order customer_id.

```sql
SELECT o.id AS order_id, c.name AS customer_name FROM orders AS o JOIN customers AS c ON c.id = o.customer_id ORDER BY o.id;
```

## 17. Keep unmatched customers

LEFT JOIN preserves every row from the left table and fills right-side columns with NULL when there is no match. Moving a right-table filter into WHERE can remove those unmatched rows. Decide deliberately which population to preserve.

Task: Return customer_name and order_id for all customers, including those without orders; order by customer id then order id.

Hint: Put customers on the left side.

```sql
SELECT c.name AS customer_name, o.id AS order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id ORDER BY c.id, o.id;
```

## 18. Find records with no match

A left join followed by a NULL check on the right primary key can find missing relationships. Check a field that cannot be NULL in a real matching row. This is a useful pattern for customers with no orders or projects with no tasks.

Task: Return customer id and name for customers with no orders, ordered by customer id.

Hint: Filter on the missing order primary key.

```sql
SELECT c.id, c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL ORDER BY c.id;
```

## 19. Calculate real order totals

Joining item rows to product prices produces one priced row per item. Aggregate those lines by order ID. Watch the grain of each join so a later one-to-many relationship does not accidentally multiply the amounts.

Task: Return order_id and total_cents for every order, ordered by order_id.

Hint: Use each product’s price, not a fixed number.

```sql
SELECT i.order_id, SUM(i.quantity * p.price_cents) AS total_cents FROM order_items i JOIN products p ON p.id=i.product_id GROUP BY i.order_id ORDER BY i.order_id;
```

## 20. Keep zero-order customers in counts

After a left join, COUNT(*) includes the preserved unmatched row. COUNT(right.id) counts only real matches. This difference is essential when presenting zeroes for customers who have not ordered.

Task: Return customer_name and order_count for every customer, ordered by customer id.

Hint: Count o.id rather than every joined row.

```sql
SELECT c.name AS customer_name, COUNT(o.id) AS order_count FROM customers c LEFT JOIN orders o ON o.customer_id=c.id GROUP BY c.id,c.name ORDER BY c.id;
```

## 21. Label values with CASE

CASE maps conditions into output values. Evaluate the most specific condition first if ranges overlap. The ELSE branch defines the remaining behavior. This is useful for report labels but should not obscure the original numeric data.

Task: Return name and price_band (Premium for prices >=2500, otherwise Standard), ordered by product id.

Hint: End the CASE expression with END and give it an alias.

```sql
SELECT name, CASE WHEN price_cents >= 2500 THEN 'Premium' ELSE 'Standard' END AS price_band FROM products ORDER BY id;
```

## 22. Display a fallback with COALESCE

COALESCE returns the first non-NULL argument. It is useful for display values, but replacing missing data with a label does not make it known. Preserve the distinction when doing analysis or exporting typed data.

Task: Return name and contact, replacing NULL emails with Not provided, ordered by id.

Hint: Use COALESCE(email, fallback).

```sql
SELECT name, COALESCE(email,'Not provided') AS contact FROM customers ORDER BY id;
```

## 23. Compare with an aggregate subquery

A scalar subquery can calculate a threshold for the outer query. AVG returns an average over non-NULL inputs. The inner query describes a separate calculation, not a value that should be hardcoded into your answer.

Task: Return name and price_cents for products priced above the average, ordered by price_cents then id.

Hint: Compute AVG in a parenthesized SELECT.

```sql
SELECT name, price_cents FROM products WHERE price_cents > (SELECT AVG(price_cents) FROM products) ORDER BY price_cents,id;
```

## 24. Use EXISTS for presence

EXISTS asks whether a subquery returns at least one row. A correlated subquery can refer to the outer row. This expresses a presence test without creating duplicate outer rows through a join.

Task: Return customer id and name for customers with at least one paid order, ordered by id.

Hint: Correlate the subquery using o.customer_id=c.id.

```sql
SELECT c.id,c.name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id=c.id AND o.status='paid') ORDER BY c.id;
```

## 25. Name an intermediate result with a CTE

A common table expression gives a query result a name within one statement. It can make multi-step logic easier to read. It is not automatically a persisted table or a performance improvement; query planning depends on the engine and query.

Task: Use a CTE to return order_id and total_cents for orders totaling at least 5000, ordered by order_id.

Hint: Build the per-order totals first, then filter them.

```sql
WITH totals AS (SELECT i.order_id,SUM(i.quantity*p.price_cents) AS total_cents FROM order_items i JOIN products p ON p.id=i.product_id GROUP BY i.order_id) SELECT order_id,total_cents FROM totals WHERE total_cents>=5000 ORDER BY order_id;
```

## 26. Query an ISO date interval

Text dates in a consistent YYYY-MM-DD form sort chronologically. A half-open interval includes the start and excludes the next period’s start. Real time-zone and timestamp handling is engine-specific, so confirm the stored representation.

Task: Return id and ordered_at for September 2026 orders, ordered by id.

Hint: Use September’s start and October’s start as boundaries.

```sql
SELECT id,ordered_at FROM orders WHERE ordered_at>='2026-09-01' AND ordered_at<'2026-10-01' ORDER BY id;
```

## 27. Rank within a category

A window function calculates across related rows without collapsing them into one group row. ROW_NUMBER assigns a position in the specified order. Add a tie-breaker to make ranking deterministic. The outer ORDER BY controls display order separately.

Task: Return name, category and position ranked by descending price within category, breaking ties by id; display category then position.

Hint: PARTITION BY resets numbering per category.

```sql
SELECT name,category,ROW_NUMBER() OVER (PARTITION BY category ORDER BY price_cents DESC,id) AS position FROM products ORDER BY category,position;
```

## 28. Measure paid revenue

A useful business report needs a population rule. Here only paid orders count as revenue, and values remain in cents. Join the required tables, filter status, then group. Do not silently include pending or cancelled purchases.

Task: Return category and revenue_cents from paid orders only, ordered by category.

Hint: Apply the paid filter before aggregation.

```sql
SELECT p.category,SUM(i.quantity*p.price_cents) AS revenue_cents FROM orders o JOIN order_items i ON i.order_id=o.id JOIN products p ON p.id=i.product_id WHERE o.status='paid' GROUP BY p.category ORDER BY p.category;
```

## 29. Build a customer spending report

Preserving customers with zero eligible orders requires care about filter placement. Put the paid condition into the join so unmatched customers remain. SUM over no matching values is NULL, so use COALESCE for the report’s explicit zero.

Task: Return customer_name and paid_total_cents for every customer, ordered by descending total then customer name.

Hint: Keep the status condition in ON, not WHERE.

```sql
SELECT c.name AS customer_name,COALESCE(SUM(i.quantity*p.price_cents),0) AS paid_total_cents FROM customers c LEFT JOIN orders o ON o.customer_id=c.id AND o.status='paid' LEFT JOIN order_items i ON i.order_id=o.id LEFT JOIN products p ON p.id=i.product_id GROUP BY c.id,c.name ORDER BY paid_total_cents DESC,customer_name;
```

## 30. Capstone: inventory demand report

Combine a full product population with a carefully scoped aggregate. A CTE isolates the paid quantity per product; a left join adds zeroes for products with no paid demand. This pattern avoids confusing absence of matching records with absence from the product catalog.

Task: Return product_name and paid_units for every product, sorted by paid_units descending then product_name.

Hint: Aggregate paid item quantities in a CTE, then left join the complete product table.

```sql
WITH paid AS (SELECT i.product_id,SUM(i.quantity) AS units FROM order_items i JOIN orders o ON o.id=i.order_id WHERE o.status='paid' GROUP BY i.product_id) SELECT p.name AS product_name,COALESCE(paid.units,0) AS paid_units FROM products p LEFT JOIN paid ON paid.product_id=p.id ORDER BY paid_units DESC,product_name;
```