🛢️ Week 6 – SQL for Data Science · Day 32
Day 32: SQL Basics with SQLite
⏱ 2 hours📶 Beginner🎯 100% self-study
Day 32 / 60
✅ Today you will learn
- Why every data job asks for SQL
- Create a database from CSV files
- SELECT, aliases and calculated columns
- Filter with WHERE, IN, BETWEEN, LIKE, IS NULL
- Sort, limit, DISTINCT and CASE WHEN
🏢 In the real world
In a real company the data does not live in CSV files on your laptop – it lives in databases with crores of rows. Before you can use pandas you must pull the right slice with SQL. It is the #1 skill in data-analyst job descriptions in India.
SQL in one picture
We use SQLite – a full SQL database inside one file that comes with Python. The SQL you write here works almost unchanged in MySQL and PostgreSQL.
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
pd.read_csv("sales_data.csv").to_sql("sales", con, if_exists="replace", index=False)
pd.read_csv("employees.csv").to_sql("employees", con, if_exists="replace", index=False)
pd.read_csv("departments.csv").to_sql("departments", con, if_exists="replace", index=False)
def q(sql):
return pd.read_sql(sql, con)
print(q("SELECT name, type FROM sqlite_master WHERE type = 'table'")) name type
0 sales table
1 employees table
2 departments table
SELECT
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("SELECT order_id, city, product, unit_price FROM sales LIMIT 5")) order_id city product unit_price
0 BK100359 Delhi Jeans 1220
1 BK100422 Mumbai Sneakers 2327
2 BK100744 Mumbai Tea 1kg 525
3 BK100830 Mumbai Pen Set 154
4 BK100947 Bengaluru T-Shirt 515
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT order_id,
product,
quantity * unit_price AS gross,
ROUND(quantity * unit_price * (1 - discount), 2) AS revenue
FROM sales
LIMIT 5
""")) order_id product gross revenue
0 BK100359 Jeans 2440 2440.0
1 BK100422 Sneakers 2327 2094.3
2 BK100744 Tea 1kg 2625 2625.0
3 BK100830 Pen Set 154 138.6
4 BK100947 T-Shirt 515 463.5
💡 SQL style
Write keywords in CAPITALS, one clause per line, and give calculated columns a name with AS. SQL ignores case and line breaks, but your colleagues will thank you.
WHERE – filtering rows
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT order_id, city, product, unit_price, payment_mode
FROM sales
WHERE city = 'Mumbai' AND unit_price > 20000
""")) order_id city product unit_price payment_mode
0 BK100862 Mumbai Laptop 52754 Cash on Delivery
1 BK101584 Mumbai Laptop 57852 Cash on Delivery
2 BK101257 Mumbai Laptop 56481 Cash on Delivery
3 BK101778 Mumbai Laptop 59044 Card
4 BK101487 Mumbai Laptop 50851 Card
5 BK101943 Mumbai Laptop 56438 Cash on Delivery
| Operator | Example | pandas equivalent |
|---|---|---|
| = , <>, <, >= | city = 'Delhi' | df.city == "Delhi" |
| AND / OR / NOT | a > 1 AND b = 'x' | (a > 1) & (b == "x") |
| IN | city IN ('Pune', 'Delhi') | df.city.isin([...]) |
| BETWEEN | customer_age BETWEEN 25 AND 35 | between(25, 35) |
| LIKE | product LIKE '%Phone%' | str.contains("Phone") |
| IS NULL | rating IS NULL | df.rating.isna() |
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT COUNT(*) AS orders
FROM sales
WHERE city IN ('Delhi', 'Mumbai', 'Bengaluru')
AND customer_age BETWEEN 25 AND 35
AND product LIKE '%Phone%'
"""))
print(q("SELECT COUNT(*) AS missing_ratings FROM sales WHERE rating IS NULL")) orders
0 19
missing_ratings
0 35
⚠️ NULL is not equal to anything
WHERE rating = NULL returns nothing, even when ratings are missing. Always write IS NULL / IS NOT NULL.
ORDER BY, LIMIT, DISTINCT
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT order_id, city, product,
ROUND(quantity * unit_price * (1 - discount)) AS revenue
FROM sales
ORDER BY revenue DESC
LIMIT 5
"""))
print(q("SELECT DISTINCT region, city FROM sales ORDER BY region, city")) order_id city product revenue
0 BK100824 Chennai Laptop 114432.0
1 BK101257 Mumbai Laptop 112962.0
2 BK101612 Delhi Laptop 105232.0
3 BK101943 Mumbai Laptop 90301.0
4 BK101042 Delhi Laptop 88006.0
region city
0 East Kolkata
1 East Patna
2 North Delhi
3 North Jaipur
4 North Lucknow
5 South Bengaluru
6 South Chennai
7 South Hyderabad
8 West Ahmedabad
9 West Mumbai
10 West Pune
CASE WHEN – if/else inside SQL
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT order_id, unit_price,
CASE WHEN unit_price >= 20000 THEN 'Premium'
WHEN unit_price >= 2000 THEN 'Mid'
ELSE 'Budget' END AS price_band,
CASE WHEN payment_mode = 'Cash on Delivery' THEN 0 ELSE 1 END AS prepaid
FROM sales
ORDER BY unit_price DESC
LIMIT 6
""")) order_id unit_price price_band prepaid
0 BK101778 59044 Premium 1
1 BK100738 58065 Premium 1
2 BK101584 57852 Premium 0
3 BK100725 57677 Premium 1
4 BK100855 57420 Premium 1
5 BK100824 57216 Premium 1
Working with dates
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT DISTINCT order_date,
strftime('%m', order_date) AS month,
CASE strftime('%w', order_date) WHEN '0' THEN 'Weekend' WHEN '6' THEN 'Weekend' ELSE 'Weekday' END AS day_type
FROM sales
WHERE order_date BETWEEN '2024-10-01' AND '2024-10-31'
ORDER BY order_date
LIMIT 7
""")) order_date month day_type
0 2024-10-01 10 Weekday
1 2024-10-02 10 Weekday
2 2024-10-03 10 Weekday
3 2024-10-04 10 Weekday
4 2024-10-05 10 Weekend
5 2024-10-06 10 Weekend
6 2024-10-07 10 Weekday
How SQL runs a query
- 1FROM (which table)
- 2WHERE (filter rows)
- 3GROUP BY (Day 33)
- 4HAVING (Day 33)
- 5SELECT (columns)
- 6ORDER BY
- 7LIMIT
🧠 Why this matters
Because WHERE runs before SELECT, you cannot use a SELECT alias in WHERE in most databases (WHERE revenue > 5000 fails). Repeat the expression or use a sub-query/CTE (Day 34). ORDER BY runs after SELECT, so aliases work there.
🏅 Job skills unlocked today
✔ Load CSVs into a SQL database✔ Write SELECT with calculated columns✔ Filter with WHERE operators and NULL checks✔ Sort, limit and categorise with CASE
Practice tasks
🏋️ Practice – do it yourself
Task 1: List the 5 cheapest Electronics orders in the South region.
Show solution
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""SELECT order_id, city, product, unit_price FROM sales
WHERE category = 'Electronics' AND region = 'South' ORDER BY unit_price LIMIT 5""")) order_id city product unit_price
0 BK101236 Chennai Power Bank 1111
1 BK101875 Hyderabad Power Bank 1125
2 BK100129 Hyderabad Power Bank 1126
3 BK101127 Chennai Power Bank 1126
4 BK101494 Chennai Power Bank 1145
Task 2: How many employees earn between ₹60,000 and ₹1,00,000?
Show solution
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("SELECT COUNT(*) AS n FROM employees WHERE salary BETWEEN 60000 AND 100000")) n
0 14
Task 3: Show each employee’s name, salary and a band: “Senior” (≥ ₹1 lakh), “Mid” (≥ ₹60k), else “Junior”, highest salary first.
Show solution
Python
import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""SELECT name, salary, CASE WHEN salary >= 100000 THEN 'Senior' WHEN salary >= 60000 THEN 'Mid' ELSE 'Junior' END AS band
FROM employees ORDER BY salary DESC LIMIT 8""")) name salary band
0 Kunal 146000 Senior
1 Tanvi 135000 Senior
2 Neha 122000 Senior
3 Kabir 118000 Senior
4 Meera 115000 Senior
5 Manav 111000 Senior
6 Aditya 94000 Mid
7 Diya 89000 Mid
Quick quiz
🧠 Quick Quiz – test yourself
1. Which keyword filters rows?
WHERE keeps rows that match the condition.
2. product LIKE 'Smart%' matches…
% means any characters.
3. Correct way to find missing ratings?
NULL needs IS.
4. Which runs first?
FROM → WHERE → … → SELECT → ORDER BY → LIMIT.
5. pd.read_sql(query, con) returns…
SQL result straight into pandas.
Interview corner
💼 Interview Corner
What is the difference between WHERE and HAVING?
WHERE filters individual rows before grouping; HAVING filters groups after GROUP BY using aggregates (e.g. HAVING SUM(revenue) > 100000). Covered in Day 33.
Why can’t you use a column alias in WHERE?
Because of logical execution order – WHERE is evaluated before SELECT creates the alias. Repeat the expression or wrap the query in a sub-query/CTE.
Summary
- SELECT columns FROM table WHERE condition ORDER BY col LIMIT n.
- IN, BETWEEN, LIKE, IS NULL cover most filters.
- CASE WHEN creates categories; AS names columns.
- pd.read_sql brings results into pandas.
➡️ Next
Day 33: GROUP BY, HAVING and JOINs – combine and summarise tables like a pro.
Frequently Asked Questions
Which database should I learn?
The SQL is ~95% the same everywhere. Learn with SQLite here, then practise MySQL or PostgreSQL – both are free and common in Indian companies.
How do I connect pandas to MySQL?
Use SQLAlchemy: create_engine("mysql+pymysql://user:pass@host/db") and pass the engine to pd.read_sql. Keep passwords in environment variables.
