🛢️ Week 6 – Data Science के लिए SQL · दिन 33

दिन 33: SQL Joins, GROUP BY और HAVING

⏱ 2.5 hours📶 Intermediate🎯 100% self-study

दिन 33 / 60

✅ आज आप सीखेंगे

Aggregate functions और GROUP BY
HAVING vs WHERE
INNER, LEFT, RIGHT/FULL joins
Self-join, UNION और sub-queries
SQL और pandas के बीच अनुवाद

🏢 असली दुनिया में

HR head पूछते हैं: “हर department और office location का head-count और औसत salary दिखाओ – और जो किसी department से नहीं जुड़े, उनकी list।” Finance पूछता है: “किन शहरों ने prepaid revenue में ₹6 lakh पार किया?” दोनों GROUP BY + JOIN सवाल हैं – हर SQL interview का रोज़ का खाना।

Python – setup

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)
print([r[0] for r in con.execute("SELECT name FROM sqlite_master WHERE type = 'table'")])

['sales', 'employees', 'departments']

Aggregates और GROUP BY

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,
       COUNT(rating)            AS rated_orders,
       COUNT(DISTINCT city)     AS cities,
       ROUND(SUM(quantity * unit_price * (1 - discount))) AS revenue,
       ROUND(AVG(rating), 2)    AS avg_rating,
       MIN(order_date) AS first_day, MAX(order_date) AS last_day
FROM sales
"""))

orders rated_orders cities revenue avg_rating first_day last_day 0 2000 1965 11 6879783.0 4.06 2024-01-01 2024-12-31

Python

import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT region,
       COUNT(*) AS orders,
       ROUND(SUM(quantity * unit_price * (1 - discount)) / 100000.0, 2) AS revenue_lakh,
       ROUND(AVG(quantity * unit_price * (1 - discount))) AS aov
FROM sales
GROUP BY region
ORDER BY revenue_lakh DESC
"""))

region orders revenue_lakh aov 0 South 654 20.82 3183.0 1 West 519 18.98 3656.0 2 North 508 16.01 3151.0 3 East 319 13.00 4075.0

🧠 GROUP BY का नियम

SELECT का हर column या तो GROUP BY में हो या aggregate (SUM, AVG, COUNT…) के अंदर। SELECT city, region, SUM(...) ... GROUP BY city ज़्यादातर databases में error है।

HAVING – groups filter करना

Python

import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT city,
       ROUND(SUM(quantity * unit_price * (1 - discount)) / 100000.0, 2) AS revenue_lakh
FROM sales
WHERE payment_mode <> 'Cash on Delivery'        -- row filter (before grouping)
GROUP BY city
HAVING SUM(quantity * unit_price * (1 - discount)) > 600000   -- group filter
ORDER BY revenue_lakh DESC
"""))

city revenue_lakh 0 Delhi 7.98 1 Bengaluru 7.48 2 Kolkata 6.09

JOINs

INNER JOIN

LEFT JOIN (सभी employees)

FULL OUTER JOIN

employees.dept_id → departments.dept_id

Python

import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT e.name, e.salary, d.dept_name, d.location
FROM employees AS e
INNER JOIN departments AS d ON e.dept_id = d.dept_id
ORDER BY e.salary DESC
LIMIT 5
"""))
print(q("SELECT COUNT(*) AS inner_rows FROM employees e JOIN departments d ON e.dept_id = d.dept_id"))

name salary dept_name location 0 Kunal 146000 Data Science Bengaluru 1 Tanvi 135000 Data Science Bengaluru 2 Neha 122000 Data Science Bengaluru 3 Kabir 118000 Data Science Bengaluru 4 Meera 115000 Data Science Bengaluru inner_rows 0 28

Python

import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT e.emp_id, e.name, e.dept_id, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL            -- employees with no matching department
"""))

emp_id name dept_id dept_name 0 129 Nikhil 60 None 1 130 Priya 60 None

Python

import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT COALESCE(d.dept_name, 'Unassigned') AS department,
       COALESCE(d.location, '-')           AS location,
       COUNT(e.emp_id)                     AS headcount,
       ROUND(AVG(e.salary))                AS avg_salary
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
GROUP BY department, location
ORDER BY headcount DESC
"""))

department location headcount avg_salary 0 Data Science Bengaluru 11 105818.0 1 Sales Mumbai 10 57500.0 2 HR Delhi 5 52800.0 3 Unassigned - 2 71500.0 4 Finance Mumbai 1 61000.0 5 Marketing Pune 1 79000.0

Python

import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT d.dept_name, COUNT(e.emp_id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_name
UNION ALL
SELECT 'No department', COUNT(*) FROM employees
WHERE dept_id NOT IN (SELECT dept_id FROM departments)
"""))

dept_name headcount 0 Data Science 11 1 Finance 1 2 HR 5 3 Marketing 1 4 Sales 10 5 No department 2

💡 FULL OUTER JOIN

PostgreSQL और SQLite 3.39+ सीधे FULL OUTER JOIN support करते हैं। MySQL नहीं – ऊपर जैसा LEFT JOIN और RIGHT JOIN को UNION से जोड़ें।

Self-join और sub-queries

Python

import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""
SELECT a.name AS employee, b.name AS colleague, a.city
FROM employees a
JOIN employees b ON a.city = b.city AND a.emp_id < b.emp_id AND a.dept_id = b.dept_id
WHERE a.city = 'Pune'
"""))

employee colleague city 0 Kabir Aditya Pune 1 Ananya Isha Pune 2 Ananya Pooja Pune 3 Ananya Nisha Pune 4 Isha Pooja Pune 5 Isha Nisha Pune 6 Pooja Nisha Pune 7 Siddharth Harsh Pune

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, dept_id
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees x WHERE x.dept_id = e.dept_id)   -- correlated sub-query
ORDER BY dept_id, salary DESC
LIMIT 8
"""))

name salary dept_id 0 Vihaan 78000 10 1 Yash 74000 10 2 Pooja 65000 10 3 Isha 61000 10 4 Kunal 146000 20 5 Tanvi 135000 20 6 Neha 122000 20 7 Kabir 118000 20

SQL ↔ pandas cheat sheet

SQL	pandas
SELECT a, b FROM t	df[["a", "b"]]
WHERE city = 'Pune'	df[df.city == "Pune"]
GROUP BY city … SUM(x)	df.groupby("city")["x"].sum()
HAVING SUM(x) > 10	.loc[lambda s: s > 10] after groupby
ORDER BY x DESC LIMIT 5	df.nlargest(5, "x")
LEFT JOIN … ON k	df.merge(other, on="k", how="left")
UNION ALL	pd.concat([a, b])
COALESCE(x, 0)	df.x.fillna(0)

🏅 आज के Job Skills

✔ GROUP BY और aggregates से summary✔ HAVING से groups filter✔ सही JOIN चुनना और लिखना✔ Sub-queries, self-joins और UNION

Practice tasks

🏋️ Practice – ख़ुद करें

Task 1: हर category और payment mode का revenue और orders, सिर्फ़ वे जोड़ियाँ जिनमें कम से कम 100 orders।

Solution देखें

Python

import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""SELECT category, payment_mode, COUNT(*) AS orders, ROUND(SUM(quantity*unit_price*(1-discount))) AS revenue
FROM sales GROUP BY category, payment_mode HAVING COUNT(*) >= 100 ORDER BY revenue DESC"""))

category payment_mode orders revenue 0 Electronics UPI 205 1516089.0 1 Electronics Card 115 1047969.0 2 Fashion UPI 235 703723.0 3 Fashion Card 128 406734.0 4 Grocery UPI 271 405319.0 5 Home & Kitchen UPI 146 192784.0 6 Grocery Card 113 180109.0 7 Grocery Cash on Delivery 105 140767.0 8 Books & Stationery UPI 104 137491.0

Task 2: हर department location का कुल salary ख़र्च (JOIN लें)।

Solution देखें

Python

import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""SELECT d.location, SUM(e.salary) AS cost, COUNT(*) AS people FROM employees e
JOIN departments d ON e.dept_id = d.dept_id GROUP BY d.location ORDER BY cost DESC"""))

location cost people 0 Bengaluru 1164000 11 1 Mumbai 636000 11 2 Delhi 264000 5 3 Pune 79000 1

Task 3: Sub-query से हर department का सबसे ज़्यादा salary वाला employee निकालें।

Solution देखें

Python

import sqlite3
import pandas as pd
con = sqlite3.connect("bazaar.db")
q = lambda sql: pd.read_sql(sql, con)
print(q("""SELECT d.dept_name, e.name, e.salary FROM employees e JOIN departments d ON e.dept_id = d.dept_id
WHERE e.salary = (SELECT MAX(salary) FROM employees x WHERE x.dept_id = e.dept_id) ORDER BY e.salary DESC"""))

dept_name name salary 0 Data Science Kunal 146000 1 Marketing Rohan 79000 2 Sales Vihaan 78000 3 HR Zoya 62000 4 Finance Aarav 61000

Quick quiz

🧠 Quick Quiz – ख़ुद को जाँचें

1. HAVING क्या filter करता है?

Rows के लिए WHERE।

2. COUNT(rating) vs COUNT(*)?

COUNT(*) rows गिनता है।

3. बिना department वाले employees किसमें दिखेंगे?

NULL department columns के साथ।

4. UNION vs UNION ALL?

UNION ALL तेज़ है।

5. COALESCE(x, "Unknown") क्या लौटाता है?

पहली non-NULL value।

Interview corner

💼 Interview Corner

SQL joins के प्रकार समझाइए।

INNER दोनों tables की matching rows देता है; LEFT left table की सभी rows और matches (वरना NULL); RIGHT उसका उल्टा; FULL OUTER दोनों की सभी rows; CROSS हर combination; self-join table को ख़ुद से जोड़ता है।

5 से ज़्यादा employees वाले departments ढूँढें।

SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id HAVING COUNT(*) > 5; – नाम के लिए departments join करें।

सारांश

GROUP BY + aggregates summary देते हैं; HAVING groups filter करता है।
INNER = matches, LEFT = left की सभी rows, FULL = सब कुछ।
COALESCE NULLs भरता है; UNION ALL results stack करता है।
Sub-queries row की उसके group से तुलना करती हैं।

➡️ आगे

Day 34: window functions और CTEs – SQL में rankings, running totals और month-over-month growth।

अक्सर पूछे जाने वाले प्रश्न (FAQ)

Sub-query तेज़ है या JOIN?

नए databases अक्सर दोनों को एक ही plan में optimise करते हैं। जो साफ़ हो वही लिखें; performance ज़रूरी हो तो EXPLAIN देखें।

मेरे JOIN ने उम्मीद से ज़्यादा rows क्यों दीं?

Key एक तरफ़ unique नहीं – हर match rows को गुणा करता है। SELECT key, COUNT(*) … GROUP BY key HAVING COUNT(*) > 1 से जाँचें।

← Data Science (60 Days)

दिन 33: SQL Joins, GROUP BY और HAVING

✅ आज आप सीखेंगे

🏢 असली दुनिया में

Aggregates और GROUP BY

HAVING – groups filter करना

JOINs

Self-join और sub-queries

SQL ↔ pandas cheat sheet

🏅 आज के Job Skills

Practice tasks

🏋️ Practice – ख़ुद करें

Quick quiz

🧠 Quick Quiz – ख़ुद को जाँचें

Interview corner

💼 Interview Corner

सारांश

अक्सर पूछे जाने वाले प्रश्न (FAQ)

यह lesson कैसा लगा?

पढ़ने वालों के reviews

Video से सीखें – आसान Hindi में