Lesson 16 · Data analytics zero to hero
SQL Fundamentals for Data Analysts | Data Analytics #16
And the start of a SQL block: SELECT, WHERE, ORDER BY, and aggregate functions. Entirely in Python, using the built-in sqlite3 module, no separate database…
- CourseData analytics zero to hero
- Lesson16 of 30
- Video12 min
- FormatJupyter notebook · 10 code cells
- Data1 dataset
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- superstore_sales.csv preview715.5 KB
📓 Full notebook
Download .ipynbData Analytics Zero to Hero, Video 16: SQL Fundamentals for Data Analysts#
- Video sixteen of the 30-part series, and the start of a SQL block: SELECT, WHERE, ORDER BY, and aggregate functions.
- Entirely in Python, using the built-in sqlite3 module, no separate database software to install.
- We're loading the real Sample Superstore dataset from video fifteen into a genuine SQL database.
- Let's jump straight in.
Before You Start#
- Open a new Jupyter Notebook in VS Code and select your Python interpreter as the kernel.
- Place superstore_sales.csv in the same folder as this notebook.
- sqlite3 ships with Python; no installation needed.
Part 1: Loading Real Data into SQLite#
import sqlite3
import pandas as pd
conn = sqlite3.connect(':memory:')
df = pd.read_csv('superstore_sales.csv')
df.to_sql('sales', conn, if_exists='replace', index=False)
print('Real data loaded into the sales table')
result = pd.read_sql('SELECT * FROM sales LIMIT 5', conn)
print(result)
Part 2: SELECT and WHERE#
query = '''
SELECT "Order Date", Region, Category, Sales
FROM sales
WHERE Region = 'West'
LIMIT 5
'''
result = pd.read_sql(query, conn)
print(result)
query = '''
SELECT * FROM sales
WHERE Sales > 500 AND Category = 'Technology'
LIMIT 5
'''
result = pd.read_sql(query, conn)
print(result[['Region', 'Category', 'Sales', 'Profit']])
Part 3: ORDER BY and DISTINCT#
query = '''
SELECT "Sub-Category", Sales
FROM sales
ORDER BY Sales DESC
LIMIT 5
'''
result = pd.read_sql(query, conn)
print(result)
query = 'SELECT DISTINCT Region FROM sales'
result = pd.read_sql(query, conn)
print(result)
Part 4: Aggregate Functions#
query = '''
SELECT
COUNT(*) AS num_orders,
SUM(Sales) AS total_sales,
AVG(Sales) AS avg_sale,
MIN(Sales) AS min_sale,
MAX(Sales) AS max_sale
FROM sales
'''
result = pd.read_sql(query, conn)
print(result)
query = '''
SELECT Category, COUNT(*) AS num_orders, SUM(Sales) AS total_sales
FROM sales
GROUP BY Category
ORDER BY total_sales DESC
'''
result = pd.read_sql(query, conn)
print(result)
Part 5: The Same Question, Two Ways#
sql_answer = pd.read_sql("SELECT AVG(Profit) AS avg_profit FROM sales WHERE Category = 'Furniture'", conn)
pandas_answer = df[df['Category'] == 'Furniture']['Profit'].mean()
print(sql_answer)
print(pandas_answer)
conn.close()
print('Connection closed')
Wrap-Up: What You Learned#
- Loading a real DataFrame into a genuine SQL database with to_sql, and querying it back with read_sql.
- SELECT and WHERE, for choosing columns and filtering rows.
- ORDER BY and DISTINCT, for sorting and deduplicating results.
- Aggregate functions: COUNT, SUM, AVG, MIN, and MAX, plus a first look at GROUP BY.
- Answering the same real question in both SQL and pandas, landing on the same real answer.
- All of it run against the real Sample Superstore dataset, loaded into a genuine SQLite database. Video seventeen goes deep on SQL joins and aggregations, connecting multiple real tables the way video eleven did in pandas. Subscribe so it lands automatically see you there.
🧠 Test yourself
Pick an answer to check it. Your best score is saved in this browser.
Which query returns only the sales in the West region?
WHERE filters rows before they are returned. HAVING is for filtering groups after aggregation.
What does
SELECT DISTINCT Region FROM salesreturn?DISTINCT removes duplicates from the result.
Which query shows the 5 largest sales?
ORDER BY ... DESC sorts from largest to smallest, and LIMIT 5 keeps the first 5.
Why do column names like
"Order Date"need double quotes in the query?Names with spaces or special characters must be quoted so SQL treats them as one identifier.
What does this query return?
sql SELECT Category, SUM(Sales) AS total_sales FROM sales GROUP BY Category
GROUP BY makes one group per Category and SUM adds sales in each.
💻 Practice exercises
Try each one in the notebook or in your own editor before opening the solution.
Create an in-memory SQLite database with a
salestable (Region, Category, Sales) from a small DataFrame and select the rows where Region is 'West'.💡 Hint
Use df.to_sql('sales', conn, index=False) and pd.read_sql.
✅ Show solution
import sqlite3 import pandas as pd conn = sqlite3.connect(':memory:') df = pd.DataFrame({ 'Region': ['West', 'East', 'West', 'South'], 'Category': ['Technology', 'Furniture', 'Furniture', 'Technology'], 'Sales': [700.0, 120.5, 300.0, 90.0] }) df.to_sql('sales', conn, if_exists='replace', index=False) print(pd.read_sql("SELECT * FROM sales WHERE Region = 'West'", conn)) conn.close()
Using the same style of table, write queries for the top 2 sales (ORDER BY ... DESC LIMIT) and for the distinct categories.
💡 Hint
ORDER BY Sales DESC LIMIT 2, and SELECT DISTINCT Category.
✅ Show solution
import sqlite3 import pandas as pd conn = sqlite3.connect(':memory:') pd.DataFrame({ 'Region': ['West', 'East', 'West', 'South'], 'Category': ['Technology', 'Furniture', 'Furniture', 'Technology'], 'Sales': [700.0, 120.5, 300.0, 90.0] }).to_sql('sales', conn, index=False) print(pd.read_sql('SELECT Region, Sales FROM sales ORDER BY Sales DESC LIMIT 2', conn)) print(pd.read_sql('SELECT DISTINCT Category FROM sales', conn)) conn.close()
Write one SQL query giving per Category the number of orders, total and average Sales, sorted by total descending, then compute the average Sales for 'Furniture' in pandas and compare it with SQL.
💡 Hint
Use COUNT(*), SUM, AVG with GROUP BY; for pandas filter then .mean().
✅ Show solution
import sqlite3 import pandas as pd conn = sqlite3.connect(':memory:') df = pd.DataFrame({ 'Category': ['Technology', 'Furniture', 'Furniture', 'Technology', 'Office'], 'Sales': [700.0, 120.5, 300.0, 90.0, 40.0] }) df.to_sql('sales', conn, index=False) query = ''' SELECT Category, COUNT(*) AS num_orders, SUM(Sales) AS total_sales, AVG(Sales) AS avg_sale FROM sales GROUP BY Category ORDER BY total_sales DESC ''' print(pd.read_sql(query, conn)) sql_avg = pd.read_sql("SELECT AVG(Sales) AS a FROM sales WHERE Category = 'Furniture'", conn)['a'][0] pandas_avg = df[df['Category'] == 'Furniture']['Sales'].mean() print(sql_avg, pandas_avg) conn.close()
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



