←Back to the bookChapter 154 · Notebook 1 · SQL Foundations
▶ Open in Colab⬇ Download notebookView on GitHub
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.

In [1]:
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']
Out[1]:
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.

In [2]:
q("SELECT product, category, unit_price FROM products LIMIT 5")
Out[2]:
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.

In [3]:
q("""SELECT product, category, unit_price
       FROM products
       WHERE category = 'Equipment' AND unit_price > 30
       ORDER BY unit_price DESC""")
Out[3]:
product category unit_price
0 Grinder Electric Equipment 119
1 Cold Brew Kit Equipment 39
2 Grinder Manual Equipment 34
In [4]:
q("""SELECT product, unit_price FROM products
       WHERE unit_price BETWEEN 10 AND 30
       ORDER BY unit_price""")
Out[4]:
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.

In [5]:
q("""SELECT product, unit_price
       FROM products
       ORDER BY unit_price DESC
       LIMIT 3""")
Out[5]:
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.

In [6]:
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']
Out[6]:
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.

In [7]:
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""")
Out[7]:
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.

In [8]:
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""")
Out[8]:
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