Mathew K Analytics

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…

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.

📓 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.

🧠 Test yourself

Pick an answer to check it. Your best score is saved in this browser.

  1. Which query returns only the sales in the West region?

  2. What does SELECT DISTINCT Region FROM sales return?

  3. Which query shows the 5 largest sales?

  4. Why do column names like "Order Date" need double quotes in the query?

  5. What does this query return?

    sql
    SELECT Category, SUM(Sales) AS total_sales
    FROM sales GROUP BY Category
    

💻 Practice exercises

Try each one in the notebook or in your own editor before opening the solution.

  1. Create an in-memory SQLite database with a sales table (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()
    
  2. 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()
    
  3. 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.