Query a Database with SQL
Ask questions of data using SELECT, WHERE, JOINs and GROUP BY, right in your browser.
You will learn to
- Create an in-memory SQLite database using Python
- Write SQL SELECT statements to retrieve specific columns
- Filter data using the WHERE clause and logical operators
- Aggregate data using GROUP BY, SUM, COUNT, and AVG
- Use the HAVING clause to filter aggregated results
Before you start
- No experience needed; basic spreadsheets help
Tools needed
- The in-browser Jupyter Sandbox with sqlite3 (embedded below)
Your workspace
Practise right here. Work through the steps below in this environment.
The notebook runs entirely in your browser (JupyterLite). The first load downloads the Python runtime, give it a minute on slow connections, then it is cached. Use the upload arrow in the file panel to add datasets.
Step-by-step
Tick each task as you finish it, your progress saves on this device.
1. Create a database in memory
We will use Python’s built-in `sqlite3` library to run a real SQL engine entirely in RAM. Run `import sqlite3` and `import pandas as pd`. Then establish a connection: `con = sqlite3.connect(":memory:")`. This creates an instant, temporary database.
Done when: A SQLite connection object is successfully established2. Load the sample data into SQL
Read the sales dataset with pandas (`df = pd.read_csv("teki-sales-sample.csv")`). Then write this dataframe directly into a SQL table named "sales" using `df.to_sql("sales", con, index=False)`. The data is now ready to be queried with SQL.
Done when: A "sales" table is populated in the SQLite database3. Your first SELECT query
SQL commands are strings. We can execute them and view the results nicely using pandas. Run: `pd.read_sql("SELECT date, product, revenue FROM sales LIMIT 5", con)`. This selects only 3 specific columns and limits the output to the first 5 rows.
Done when: A table with exactly three columns and five rows is displayed4. Filter rows with WHERE
Let's find all sales for a specific product. Use the WHERE clause: `pd.read_sql("SELECT * FROM sales WHERE product = 'Laptop'", con)`. Notice we use single quotes for SQL strings. Try another query finding sales where revenue is greater than 1000.
Done when: Only rows matching your filter conditions are returned5. Aggregate data with GROUP BY
Let's generate a report of total units sold per product. Use the SUM function and GROUP BY: `pd.read_sql("SELECT product, SUM(units_sold) AS total_units FROM sales GROUP BY product ORDER BY total_units DESC", con)`. `AS` aliases the column to a friendly name, and `ORDER BY ... DESC` sorts highest to lowest.
Done when: A summary table showing total units per product, sorted descending6. Filter aggregated data with HAVING
You cannot use WHERE to filter aggregated functions like SUM. Instead, use HAVING. Modify your previous query to only show products that sold more than 50 total units: `... GROUP BY product HAVING SUM(units_sold) > 50 ORDER BY ...`
Done when: The summary table updates to exclude low-volume products
Finished every step?
Mark the lab complete to record it on this device.
Reflect
If you can answer these in your own words, the lab stuck.
- What is the order of execution between WHERE, GROUP BY, and HAVING?
- Why do we need the LIMIT clause in large databases?
- How does the AS keyword make SQL output more readable?