Lesson 16 · Data analytics zero to hero

SQL Fundamentals for Data Analysts | Data Analytics #16

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…

⬇ Download notebook ⬇ Notebook + data (.zip)Open in Colab ↗

CourseData analytics zero to hero
Lesson16 of 30
Video12 min
FormatJupyter notebook · 10 code cells
Data1 dataset

SQLite pandas

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.csv715.5 KB

📓 Full notebook

Download .ipynb

Data 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')

Real data loaded into the sales table

result = pd.read_sql('SELECT * FROM sales LIMIT 5', conn)
print(result)

   Order Date Region       State         Category Sub-Category    Segment  \
0   11/8/2016  South    Kentucky        Furniture    Bookcases   Consumer   
1   11/8/2016  South    Kentucky        Furniture       Chairs   Consumer   
2   6/12/2016   West  California  Office Supplies       Labels  Corporate   
3  10/11/2015  South     Florida        Furniture       Tables   Consumer   
4  10/11/2015  South     Florida  Office Supplies      Storage   Consumer   

      Sales    Profit  Quantity  
0  261.9600   41.9136         2  
1  731.9400  219.5820         3  
2   14.6200    6.8714         2  
3  957.5775 -383.0310         5  
4   22.3680    2.5164         2

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)

  Order Date Region         Category    Sales
0  6/12/2016   West  Office Supplies   14.620
1   6/9/2014   West        Furniture   48.860
2   6/9/2014   West  Office Supplies    7.280
3   6/9/2014   West       Technology  907.152
4   6/9/2014   West  Office Supplies   18.504

query = '''
SELECT * FROM sales
WHERE Sales > 500 AND Category = 'Technology'
LIMIT 5
'''
result = pd.read_sql(query, conn)
print(result[['Region', 'Category', 'Sales', 'Profit']])

    Region    Category     Sales    Profit
0     West  Technology   907.152   90.7152
1     West  Technology   911.424   68.3568
2  Central  Technology  1097.544  123.4737
3     East  Technology  1029.950  298.6855
4  Central  Technology   944.930  236.2325

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)

  Sub-Category      Sales
0     Machines  22638.480
1      Copiers  17499.950
2      Copiers  13999.960
3      Copiers  11199.968
4      Copiers  10499.970

query = 'SELECT DISTINCT Region FROM sales'
result = pd.read_sql(query, conn)
print(result)

    Region
0    South
1     West
2  Central
3     East

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)

   num_orders   total_sales    avg_sale  min_sale  max_sale
0        9994  2.297201e+06  229.858001     0.444  22638.48

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)

          Category  num_orders  total_sales
0       Technology        1847  836154.0330
1        Furniture        2121  741999.7953
2  Office Supplies        6026  719047.0320

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)

   avg_profit
0    8.699327
8.699327109853845

conn.close()
print('Connection closed')

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.

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.

☕ Buy me a coffee Other ways to help