←Back to the bookChapter 154 · Notebook 1 · SQL Foundations

Chapter 154 · Tools & Workflow · Notebook 1 of 5

SQL Foundations: SELECT, WHERE, ORDER BY

SQL is how you ask a database questions. Nearly every query is a variation on one sentence: SELECT these columns FROM this table WHERE some condition holds, ORDER BY something. This notebook builds that sentence one clause at a time.

Every query below runs for real against a small shop database loaded into SQLite. Read the SQL, read the result, then change a value and rerun.

import pandas as pd, sqlite3
BASE = "https://raw.githubusercontent.com/johnfisher-ai/Statistics-Data-Science-AI-Visual-Book/main/data/"
fn = "sql-and-databases--shop.xlsx"
try:
    xls = pd.ExcelFile("../../data/" + fn)
except FileNotFoundError:
    xls = pd.ExcelFile(BASE + fn)
# Load the four sheets into an in-memory SQLite database, then query it with real SQL.
con = sqlite3.connect(":memory:")
for table in ["customers", "products", "orders", "order_items"]:
    pd.read_excel(xls, sheet_name=table).to_sql(table, con, index=False, if_exists="replace")

def q(sql):
    """Run a SQL query against the shop database and return the result as a DataFrame."""
    return pd.read_sql_query(sql, con)

print("tables:", [r[0] for r in con.execute("SELECT name FROM sqlite_master WHERE type='table'")])
q("SELECT * FROM products LIMIT 3")

tables: ['customers', 'products', 'orders', 'order_items']

	product_id	product	category	unit_price
0	1	Cold Brew Kit	Equipment	39
1	2	Pour-Over Set	Equipment	28
2	3	Espresso Beans 1kg	Beans	22

DEMO 1 · SELECT columns FROM a table

The two words at the heart of SQL. SELECT names the columns you want; FROM names the table. A star means every column, and LIMIT caps how many rows come back, which you always want while exploring.

q("SELECT product, category, unit_price FROM products LIMIT 5")

	product	category	unit_price
0	Cold Brew Kit	Equipment	39
1	Pour-Over Set	Equipment	28
2	Espresso Beans 1kg	Beans	22
3	Ceramic Mug	Accessories	12
4	Travel Tumbler	Accessories	19

DEMO 2 · WHERE filters the rows

WHERE keeps only rows that satisfy a condition. You can compare with =, >, <, use BETWEEN for a range, IN for a set, and LIKE for text patterns (% is a wildcard). Combine conditions with AND and OR.

q("""SELECT product, category, unit_price
       FROM products
       WHERE category = 'Equipment' AND unit_price > 30
       ORDER BY unit_price DESC""")

	product	category	unit_price
0	Grinder Electric	Equipment	119
1	Cold Brew Kit	Equipment	39
2	Grinder Manual	Equipment	34

q("""SELECT product, unit_price FROM products
       WHERE unit_price BETWEEN 10 AND 30
       ORDER BY unit_price""")

	product	unit_price
0	Ceramic Mug	12
1	Travel Tumbler	19
2	Espresso Beans 1kg	22
3	Milk Frother	24
4	Gift Card	25
5	Pour-Over Set	28

DEMO 3 · ORDER BY and LIMIT: the top-N query

Sorting plus a limit answers ‘what are the biggest?’ questions. ORDER BY takes DESC for descending, and LIMIT returns just the first rows after sorting.

q("""SELECT product, unit_price
       FROM products
       ORDER BY unit_price DESC
       LIMIT 3""")

	product	unit_price
0	Grinder Electric	119
1	Subscription Box	45
2	Cold Brew Kit	39

DEMO 4 · DISTINCT: the unique values

DISTINCT collapses duplicate rows to the unique ones, the quickest way to see the categories, regions, or channels present in a column.

print("categories:", q("SELECT DISTINCT category FROM products").category.tolist())
print("regions:   ", q("SELECT DISTINCT region FROM customers ORDER BY region").region.tolist())
q("SELECT DISTINCT channel FROM orders")

categories: ['Equipment', 'Beans', 'Accessories', 'Other']
regions:    ['Central', 'East', 'North', 'South', 'West']

	channel
0	web
1	mobile
2	store

DEMO 5 · Computed columns and aliases

You can calculate new columns right in the SELECT and name them with AS. Here we turn a list price into a sale price, all in SQL.

q("""SELECT product,
              unit_price,
              ROUND(unit_price * 0.9, 2) AS sale_price_10pct_off
       FROM products
       ORDER BY unit_price DESC
       LIMIT 5""")

	product	unit_price	sale_price_10pct_off
0	Grinder Electric	119	107.1
1	Subscription Box	45	40.5
2	Cold Brew Kit	39	35.1
3	Grinder Manual	34	30.6
4	Pour-Over Set	28	25.2

DEMO 6 · Putting it together on the big table

The order_items table has one row per line item. Filter it to the large, full-price lines and sort them, the same four clauses, now doing real work.

q("""SELECT order_id, product_id, quantity, unit_price, revenue
       FROM order_items
       WHERE quantity >= 4 AND discount_pct = 0
       ORDER BY revenue DESC
       LIMIT 6""")

	order_id	product_id	quantity	unit_price	revenue
0	1013	8	5	119	595.0
1	1016	8	5	119	595.0
2	1139	8	5	119	595.0
3	1143	8	5	119	595.0
4	1527	8	5	119	595.0
5	1592	8	5	119	595.0

Wrap-up

One sentence, five clauses: SELECT columns, FROM a table, WHERE a condition holds, ORDER BY a sort key, LIMIT the rows. That pattern answers a surprising share of real questions. Next we add the verbs that summarize many rows into one.

←Back to the bookChapter 154 · Notebook 1 · SQL Foundations