SQL MULTI-TABLE JOIN — সম্পূর্ণ ২ ঘন্টার লাইভ ক্লাস নোট

01

গল্প — Online Shopping

১২ মিনিট

একটি e-commerce company-এর data আলাদা table-এ থাকে।

customers

customer_id	customer_name	city
1	Rahim	Dhaka
2	Karim	Chittagong
3	Nila	Dhaka

orders

order_id	customer_id	product_id	quantity
101	1	501	2
102	2	502	1
103	1	503	3

products

product_id	product_name	category_id	price
501	Laptop	10	80000
502	Mouse	11	1500
503	Keyboard	11	2500

category_id	category_name
10	Electronics
11	Accessories

কেন এক table-এ সব রাখব না?

৮ মিনিট

একই customer ১০০ order করলে?

নাম/city ১০০বার duplicate → storage waste, inconsistency, update কঠিন।

GOOD: Customers → Orders → Products → Categories Related data আলাদা · JOIN করে প্রয়োজনে একসাথে

03

1 → 2 → 3 → 4 Table

৬ মিনিট

1 table: Customers → no JOIN 2 tables: Customers→Orders → JOIN 3 tables: + Products → Multi-table JOIN 4 tables: + Categories → longer chain

04

Big Visual Diagram

৫ মিনিট

CUSTOMER │ │ customer_id ↓ ORDERS │ │ product_id ↓ PRODUCTS │ │ category_id ↓ CATEGORIES Customer + Order + Product + Category = Complete Business Info

05

Multi-table JOIN কী?

৬ মিনিট

যখন ৩ বা তার বেশি related table JOIN করে একটি result তৈরি হয় — Multi-table JOIN।

নতুন JOIN type নয় — একাধিক table একসাথে JOIN করা।

customers JOIN orders JOIN products JOIN categories

06

আগে Relationship আঁকো

৮ মিনিট

customers.customer_id → orders.customer_id orders.product_id → products.product_id products.category_id → categories.category_id Customer → Order → Product → Category

JOIN লেখার আগে relationship বুঝতে হবে।

07

২-Table Revision

৬ মিনিট

SELECT c.customer_name, o.order_id FROM customers c JOIN orders o ON c.customer_id = o.customer_id;

c = customer · o = order · key = customer_id।

এবার একই logic দিয়ে পরের table যোগ করব।

08

৩-Table JOIN

১০ মিনিট

SELECT c.customer_name, o.order_id, p.product_name FROM customers c JOIN orders o ON c.customer_id = o.customer_id JOIN products p ON o.product_id = p.product_id;

Customer → Orders → Products memorize নয় — chain বোঝো

09

Animated Join Frames

১০ মিনিট

Step 1

Rahim · customer_id = 1

Step 2

orders.customer_id = 1 → order 101, 103

Step 3

order 101 → product_id 501

Step 4

product 501 → Laptop

Rahim → Order 101 → Laptop Rahim → Order 103 → Keyboard Karim → Order 102 → Mouse

10

৪-Table JOIN

১০ মিনিট

SELECT c.customer_name, o.order_id, p.product_name, cat.category_name FROM customers c JOIN orders o ON c.customer_id = o.customer_id JOIN products p ON o.product_id = p.product_id JOIN categories cat ON p.category_id = cat.category_id;

customer_name	order_id	product_name	category_name
Rahim	101	Laptop	Electronics
Karim	102	Mouse	Accessories
Rahim	103	Keyboard	Accessories

customers → orders → products → categories

11

কীভাবে Query পড়া/লেখা

৮ মিনিট

কী information চাই?
কোন table-এ আছে?
কীভাবে connected?
কোন JOIN type?
Final output কী?

Business Q → Columns → Tables → Relationships → JOIN types → JOIN chain → Filter → Aggregate → Report

12

INNER JOIN Chain

৮ মিনিট

FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id INNER JOIN products p ON o.product_id = p.product_id;

প্রতিটি ধাপে matching চাই। Product না থাকলে সেই order বাদ।

Customers ↓ matching Orders ↓ matching Products

13

LEFT JOIN Chain

১০ মিনিট

সব customer চাই — order না থাকলেও।

SELECT c.customer_name, o.order_id, p.product_name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id LEFT JOIN products p ON o.product_id = p.product_id;

customer	order	product
Rahim	101	Laptop
Karim	102	Mouse
Nila	NULL	NULL

14

Mixed JOIN Types

৮ মিনিট

FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id INNER JOIN products p ON o.product_id = p.product_id;

JOIN order + type result বদলায়। Business requirement আগে বুঝো। Nila (no order) এখানে INNER products-এর কারণে বাদ যেতে পারে।

15

+ WHERE

৬ মিনিট

SELECT c.customer_name, c.city, p.product_name FROM customers c JOIN orders o ON c.customer_id = o.customer_id JOIN products p ON o.product_id = p.product_id WHERE c.city = 'Dhaka';

JOIN → related dataset → WHERE → filter Dhaka

16

+ GROUP BY

৮ মিনিট

SELECT cat.category_name, COUNT(o.order_id) AS total_orders FROM categories cat JOIN products p ON cat.category_id = p.category_id JOIN orders o ON p.product_id = o.product_id GROUP BY cat.category_name;

Categories → Products → Orders → GROUP BY → COUNT

17

+ SUM Sales

৮ মিনিট

SELECT cat.category_name, SUM(p.price * o.quantity) AS total_sales FROM categories cat JOIN products p ON cat.category_id = p.category_id JOIN orders o ON p.product_id = o.product_id GROUP BY cat.category_name;

price × qty → order value → GROUP BY category → SUM

18

+ HAVING

৬ মিনিট

SELECT cat.category_name, SUM(p.price * o.quantity) AS total_sales FROM categories cat JOIN products p ON cat.category_id = p.category_id JOIN orders o ON p.product_id = o.product_id GROUP BY cat.category_name HAVING SUM(p.price * o.quantity) > 50000;

WHERE = row filter · HAVING = group filter।

19

+ ORDER BY

৫ মিনিট

SELECT cat.category_name, SUM(p.price * o.quantity) AS total_sales FROM categories cat JOIN products p ON cat.category_id = p.category_id JOIN orders o ON p.product_id = o.product_id GROUP BY cat.category_name ORDER BY total_sales DESC;

JOIN → GROUP BY → SUM → ORDER BY

20

Complete Business Report

১০ মিনিট

SELECT c.customer_name, c.city, o.order_id, p.product_name, cat.category_name, o.quantity, p.price, p.price * o.quantity AS total_amount FROM customers c JOIN orders o ON c.customer_id = o.customer_id JOIN products p ON o.product_id = p.product_id JOIN categories cat ON p.category_id = cat.category_id;

Customer → Order → Product → Category → Qty → Price → Total Amount

21

Case Study Schema

৮ মিনিট

Online shop: customers (id, name, city, country) · orders (id, customer_id, product_id, qty, order_date) · products · categories — ~২০ rows lab dataset।

customers → orders → products → categories

22

CREATE / INSERT Lab

১৫ মিনিট

CREATE DATABASE multi_join_lab; USE multi_join_lab; CREATE TABLE categories ( category_id INT PRIMARY KEY, category_name VARCHAR(80) ); CREATE TABLE customers ( customer_id INT PRIMARY KEY, customer_name VARCHAR(80), city VARCHAR(80), country VARCHAR(80) ); CREATE TABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(80), category_id INT, price DECIMAL(10,2), FOREIGN KEY (category_id) REFERENCES categories(category_id) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, product_id INT, quantity INT, order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id), FOREIGN KEY (product_id) REFERENCES products(product_id) ); INSERT INTO categories VALUES (10,'Electronics'),(11,'Accessories'),(12,'Home'); INSERT INTO customers VALUES (1,'Rahim','Dhaka','BD'), (2,'Karim','Chittagong','BD'), (3,'Nila','Dhaka','BD'), (4,'Sumi','Khulna','BD'), (5,'Hasan','Dhaka','BD'); INSERT INTO products VALUES (501,'Laptop',10,80000),(502,'Mouse',11,1500), (503,'Keyboard',11,2500),(504,'Phone',10,45000), (505,'Lamp',12,1200),(506,'Headset',11,3500); INSERT INTO orders VALUES (101,1,501,2,'2024-01-05'),(102,2,502,1,'2024-01-06'), (103,1,503,3,'2024-01-07'),(104,5,504,1,'2024-02-01'), (105,2,501,1,'2024-02-10'),(106,4,505,2,'2024-02-12'), (107,1,506,1,'2024-03-01'),(108,5,502,4,'2024-03-05'); SELECT * FROM customers; SELECT * FROM orders;

Nila-এর কোনো order নেই — LEFT JOIN practice-এর জন্য।

23

১৫ Progressive Challenges

১৫ মিনিট

Ch 1 — Customer + Order

SELECT c.customer_name, o.order_id FROM customers c JOIN orders o ON c.customer_id=o.customer_id;

Ch 2 — Order + Product

JOIN products p ON o.product_id=p.product_id

Ch 3 — Product + Category

JOIN categories cat ON p.category_id=cat.category_id

Ch 4 — Customer + Product (via orders)

c JOIN o JOIN p

Ch 5 — Customer + Product + Category

c JOIN o JOIN p JOIN cat

Ch 6 — Dhaka customers + products

… WHERE c.city='Dhaka'

Ch 7 — Category-wise order count

GROUP BY cat.category_name COUNT(o.order_id)

Ch 8 — Category-wise sales

SUM(p.price*o.quantity) GROUP BY category

Ch 9 — Customer-wise spending

SUM(p.price*o.quantity) GROUP BY c.customer_name

Ch 10 — City-wise sales

GROUP BY c.city SUM(…)

Ch 11 — Top-selling category

ORDER BY total_sales DESC LIMIT 1

Ch 12 — Top customer spending

GROUP BY customer ORDER BY spend DESC LIMIT 1

Ch 13 — Products never ordered

products p LEFT JOIN orders o … WHERE o.order_id IS NULL

Ch 14 — Customers never ordered

customers c LEFT JOIN orders o … WHERE o.order_id IS NULL

Ch 15 — Categories sales > threshold

HAVING SUM(…) > X

24

Build-the-Query Card Game

৮ মিনিট

25

JOIN Chain Visualizer (Blueprint)

৬ মিনিট

CUSTOMERS │ customer_id ↓ ORDERS │ product_id ↓ PRODUCTS │ category_id ↓ CATEGORIES RUN: highlight keys → match rows → intermediate → next JOIN → final

26

Intermediate Results

১০ মিনিট

customers JOIN orders → Intermediate #1 #1 JOIN products → Intermediate #2 #2 JOIN categories → Final Result

SQL ধাপে ধাপে related set বড় করে।

27

Common Mistakes (১৫)

১০ মিনিট

Wrong columns in ON
Forgetting ON
Wrong alias
Ambiguous column names
SELECT *
Wrong starting table
Wrong JOIN type
Broken relationship chain
Joining unrelated tables
Unexpected duplicate rows
Missing NULL (need LEFT)
Wrong WHERE
Wrong GROUP BY
Wrong HAVING
Unintentional row multiplication

Pattern

WRONG → What happened? → Why? → CORRECT → Expected

28

Duplicate / Row Growth

৮ মিনিট

Rahim (1 customer) → Order 101 → Order 103 → Order 107 = 3 result rows (one-to-many)

JOIN পর row বাড়ে — এটা bug নয় যদি relationship 1:N হয়।

29

1:1 · 1:N · M:N

১০ মিনিট

1:1

Person → Passport

1:N

Customer → Orders

M:N Students ↔ Courses via junction: students → student_courses → courses

SELECT s.name, c.course_name FROM students s JOIN student_courses sc ON s.student_id = sc.student_id JOIN courses c ON sc.course_id = c.course_id;

30

School Example

৮ মিনিট

Students → Enrollments → Courses → Teachers

SELECT s.student_name, c.course_name, t.teacher_name FROM students s JOIN enrollments e ON s.student_id = e.student_id JOIN courses c ON e.course_id = c.course_id JOIN teachers t ON c.teacher_id = t.teacher_id;

31

Hospital Example

৮ মিনিট

Patients → Appointments → Doctors → Departments

SELECT p.patient_name, d.doctor_name, dep.department_name FROM patients p JOIN appointments a ON p.patient_id = a.patient_id JOIN doctors d ON a.doctor_id = d.doctor_id JOIN departments dep ON d.department_id = dep.department_id;

32

Company HR Example

৮ মিনিট

Employee কোন department, manager, location-এ?

SELECT e.employee_name, d.department_name, m.manager_name, l.city FROM employees e JOIN departments d ON e.department_id = d.department_id JOIN managers m ON e.manager_id = m.manager_id JOIN locations l ON d.location_id = l.location_id;

33

SELF JOIN + Multi-table

৮ মিনিট

SELECT e.employee_name, m.employee_name AS manager, d.department_name FROM employees e LEFT JOIN employees m ON e.manager_id = m.employee_id JOIN departments d ON e.department_id = d.department_id;

এক query-তে Multi-table + SELF + LEFT মিশতে পারে।

34

Conceptual Execution Flow

৬ মিনিট

FROM → JOIN2 → JOIN3 → JOIN4 → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT (শিক্ষার্থীদের জন্য সরল flow; engine-এর logical order আলাদা হতে পারে)

35

Comparison Tables

১০ মিনিট

Topic	A	B
2-table vs Multi	২ table	৩+ table chain
INNER vs LEFT multi	শুধু match	বাম সব + NULL
SELF vs Multi	এক table roles	আলাদা tables
WHERE vs HAVING	row filter	group filter
JOIN vs UNION	columns পাশাপাশি	rows নিচে নিচে
PK vs FK	unique identity	অন্য table reference
1:1 / 1:N / M:N	passport / orders / junction	—

36

Industry Use

৬ মিনিট

Analyst: sales, customer, KPI
Data Scientist: feature/dataset build
Data Engineer: ETL / warehouse queries
BI: Power BI / Looker datasets
Finance / HR: revenue, employee reports

37

Performance Basics

৬ মিনিট

অনেক JOIN expensive হতে পারে
Index PK/FK / join columns
অপ্রয়োজনীয় table/column বাদ
Filter early (WHERE)
Row multiplication বুঝো
শুরুতে EXPLAIN দেখো

১০০ row খোঁজা সহজ · ১০০ million বারবার scan ব্যয়বহুল।

38

Playground UI Blueprint

৫ মিনিট

☑ Customers ☑ Orders ☑ Products ☑ Categories Relationship map + JOIN Builder: Table A → JOIN TYPE → Table B → ON colA=colB → Add JOIN…

39

Interactive Animation Blueprint

৫ মিনিট

RUN → Step1 C+O → Step2 +P → Step3 +Cat → FINAL Inspect: keys, matched, unmatched, NULL, rows before/after

40

Predict the Row Count

৮ মিনিট

Predict rows: 5 customers INNER JOIN 8 orders (all have cust)?

৮ (1:N — order count)

Predict: 5 cust LEFT JOIN orders (1 never ordered)?

৮ matched + 1 NULL = ৯ (if 8 orders)

Predict: after JOIN products, unmatched product?

INNER drops that order

Predict: Rahim 3 orders → how many Rahim rows?

৩

Predict: GROUP BY category after JOINs → rows?

category count, not order count

41

Query Builder Game

৮ মিনিট

Need: customer, product, category — which chain?

c → o → p → cat

ON for customers–orders?

c.customer_id = o.customer_id

ON for orders–products?

o.product_id = p.product_id

ON for products–categories?

p.category_id = cat.category_id

Wrong: customers JOIN products ON customer_id?

✗ no direct FK — need orders bridge

42

Debugging Game

৮ মিনিট

Debug: JOIN … missing ON

Cartesian / error — add ON

Debug: ON c.customer_id = p.product_id

Wrong relationship

Debug: SELECT name without alias

Ambiguous — use c.customer_name

Debug: INNER but need all customers

Use LEFT JOIN from customers

Debug: forgot join to categories

Add JOIN cat ON p.category_id=…

Debug: HAVING without GROUP BY

Add GROUP BY or move to WHERE

Debug: wrong table start for unmatched products

FROM products LEFT JOIN orders

43

Live Class Activities (২০)

১২ মিনিট

Act 1 — Identify tables for customer+category report

c,o,p,cat

Act 2 — Draw relationship arrows

cust_id / prod_id / cat_id

Act 3 — Mark PKs

customer_id, order_id, product_id, category_id

Act 4 — Mark FKs

orders.customer_id, orders.product_id, products.category_id

Act 5 — Build JOIN chain

c JOIN o JOIN p JOIN cat

Act 6 — Predict Rahim Laptop category

Electronics

Act 7 — Predict Nila INNER rows

0 (no orders)

Act 8 — Choose JOIN for all customers

LEFT from customers

Act 9 — Fix broken ON

Match related keys

Act 10 — Add third table products

JOIN p ON o.product_id

Act 11 — Add fourth categories

JOIN cat ON p.category_id

Act 12 — Add WHERE Dhaka

WHERE c.city='Dhaka'

Act 13 — Add GROUP BY category

GROUP BY cat.category_name

Act 14 — Add HAVING sales>X

HAVING SUM(price*qty)>X

Act 15 — Add ORDER BY sales DESC

ORDER BY total_sales DESC

Act 16 — Top customer

GROUP BY customer ORDER BY spend DESC LIMIT 1

Act 17 — Top category

same on category

Act 18 — Customers without orders

LEFT + WHERE o.order_id IS NULL

Act 19 — Products never ordered

products LEFT orders IS NULL

Act 20 — Final business report

Part 20 query

44

Mini Project — E-Commerce Analytics

১৫ মিনিট

Customer order history
Customer + Product
Customer + Product + Category
Customer total spending
Category total sales
City-wise sales
Top 10 customers
Top 10 products
Products never ordered
Customers who never ordered
Categories with sales > X
Monthly sales summary

JOIN · LEFT · WHERE · GROUP · HAVING · ORDER · LIMIT · aggregates।

45

Interview Top 30

১৫ মিনিট

IV 1. Multi-table JOIN কী?

৩+ related tables JOIN · উদা: c-o-p-cat · ভুল: new keyword · ফলো: chain · ইউজ: reports

IV 2. Normal JOIN থেকে আলাদা?

সংখ্যা/chain · ভুল: আলাদা syntax type · ফলো: 3 vs 2 · ইউজ: BI

IV 3. তিন table কীভাবে?

JOIN … ON … JOIN … ON · ভুল: এক ON · ফলো: aliases · ইউজ: e-com

IV 4. চার table?

হ্যাঁ — আরও JOIN · ভুল: max 2 · ফলো: keys · ইউজ: analytics

IV 5. Mix INNER/LEFT?

হ্যাঁ — সাবধানে · ভুল: same type always · ফলো: order · ইউজ: keep left

IV 6. JOIN chain?

ধাপে intermediate · ভুল: all at once magic · ফলো: visualize · ইউজ: debug

IV 7. Missing relationship?

wrong/NULL/drop · ভুল: ignore · ফলো: ERD · ইউজ: QA

IV 8. Row count বাড়ে কেন?

1:N · ভুল: bug always · ফলো: GROUP · ইউজ: orders

IV 9. Avoid duplicates?

keys/DISTINCT/GROUP · ভুল: SELECT * · ফলো: grain · ইউজ: reports

IV 10. One-to-many?

Customer→Orders · ভুল: always 1:1 · ফলো: row growth · ইউজ: modeling

IV 11. Many-to-many?

junction table · ভুল: direct FK both · ফলো: enroll · ইউজ: school

IV 12. Junction table?

bridge M:N · ভুল: optional always · ফলো: student_courses · ইউজ: schema

IV 13. + GROUP BY

aggregate after join · ভুল: group wrong cols · ফলো: sales · ইউজ: KPI

IV 14. + HAVING

filter groups · ভুল: WHERE SUM · ফলো: threshold · ইউজ: finance

IV 15. + WHERE

filter rows · ভুল: after HAVING · ফলো: city · ইউজ: segment

IV 16. + ORDER BY

sort result · ভুল: before GROUP wrong · ফলো: top · ইউজ: ranking

IV 17. + LIMIT

top N · ভুল: without ORDER · ফলো: top 10 · ইউজ: dashboards

IV 18. Unmatched records?

LEFT + IS NULL · ভুল: INNER · ফলো: anti-join · ইউজ: gaps

IV 19. Customers no orders?

c LEFT o WHERE o.id IS NULL · ভুল: NOT IN only · ফলো: Nila · ইউজ: CRM

IV 20. Products never ordered?

p LEFT o IS NULL · ভুল: INNER · ফলো: catalog · ইউজ: inventory

IV 21. Debug wrong output?

check ON/type/aliases · ভুল: blame data first · ফলো: sample · ইউজ: support

IV 22. Aliases help?

readability/qualify · ভুল: skip · ফলো: c/o/p · ইউজ: maintain

IV 23. Indexes?

speed join keys · ভুল: ignore · ফলো: EXPLAIN · ইউজ: DBA

IV 24. SELF in multi?

e LEFT m JOIN dept · ভুল: can't mix · ফলো: org · ইউজ: HR

IV 25. Multiple JOIN types?

yes · ভুল: illegal · ফলো: business · ইউজ: complex

IV 26. JOIN order decide?

driving table + req · ভুল: random · ফলো: LEFT start · ইউজ: optimize

IV 27. Cartesian?

missing ON · ভুল: always intentional · ফলো: fix ON · ইউজ: accidents

IV 28. Optimize?

index, fewer cols/tables, filter · ভুল: more SELECT * · ফলো: EXPLAIN · ইউজ: prod

IV 29. Analysts?

revenue/customer reports · ভুল: only ETL · ফলো: GROUP · ইউজ: BI

IV 30. Data Scientists?

feature tables · ভুল: only notebooks · ফলো: labels · ইউজ: ML

46

MCQ (২৫+)

১২ মিনিট

MCQ 1. Multi-table JOIN means? A) new keyword B) 3+ tables JOIN C) UNION D) VIEW

B

MCQ 2. Min tables typically? A) 1 B) 2 C) 3+ D) 10

C

MCQ 3. Customer→product bridge? A) categories B) orders C) city D) price

B

MCQ 4. 4-table e-com chain ends with? A) customers B) categories C) UNION D) SELF

B

MCQ 5. FK in orders to customers? A) order_id B) customer_id C) price D) city

B

MCQ 6. INNER multi drops? A) unmatched B) all C) PK D) aliases

A

MCQ 7. Keep customers without orders? A) INNER B) LEFT from c C) RIGHT only D) CROSS

B

MCQ 8. Nila with INNER c-o-p? A) appears B) missing C) NULL product only D) error

B

MCQ 9. 1 customer 3 orders rows? A) 1 B) 3 C) 0 D) 4

B

MCQ 10. M:N needs? A) VIEW B) junction C) INDEX only D) LIMIT

B

MCQ 11. Ambiguous name fix? A) SELECT * B) qualify alias C) DROP D) UNION

B

MCQ 12. Filter Dhaka? A) HAVING B) WHERE c.city C) GROUP D) LIMIT

B

MCQ 13. Filter groups sales>X? A) WHERE B) HAVING C) ON D) FROM

B

MCQ 14. Category sales use? A) COUNT only B) SUM(price*qty) C) AVG city D) DISTINCT *

B

MCQ 15. Products never ordered? A) INNER B) LEFT p→o IS NULL C) RIGHT only D) SELF

B

MCQ 16. Intermediate result? A) after each JOIN B) only final C) WHERE only D) PDF

A

MCQ 17. Mixed JOIN careful? A) never allowed B) order/type matter C) always same D) no ON

B

MCQ 18. Performance tip? A) index join cols B) SELECT * C) no WHERE D) more tables

A

MCQ 19. SELF + multi? A) illegal B) possible C) only MySQL 9 D) needs UNION

B

MCQ 20. JOIN vs UNION? A) same B) side-by-side vs stack rows C) both drop PK D) both NULL

B

MCQ 21. PK role? A) duplicate all B) identify row C) always FK D) city

B

MCQ 22. Wrong ON risk? A) wrong matches/Cartesian B) faster C) auto fix D) prettier

A

MCQ 23. School chain? A) s→e→c→t B) only students C) UNION teachers D) SELF only

A

MCQ 24. Conceptual flow first? A) ORDER BY B) FROM/JOINs C) LIMIT D) HAVING

B

MCQ 25. Mental model? A) fat one table B) related pieces JOIN C) no keys D) only 2 tables

B

MCQ 26. Alias cat for categories why?

Avoid keyword clash / clarity

47

Viva

১০ মিনিট

Viva 1. Multi-table JOIN কী?

৩+ related tables JOIN

Viva 2. নতুন JOIN type?

না

Viva 3. কেন দরকার?

data আলাদা tables-এ

Viva 4. Relationship আগে কেন?

সঠিক ON

Viva 5. ৩-table উদাহরণ?

c-o-p

Viva 6. ৪-table?

c-o-p-cat

Viva 7. Intermediate?

প্রতি JOIN পর

Viva 8. LEFT multi কখন?

সব বাম রাখতে

Viva 9. Mixed JOIN?

সম্ভব — সাবধানে

Viva 10. Row বাড়ে কেন?

1:N

Viva 11. M:N?

junction

Viva 12. Unmatched customers?

LEFT + IS NULL

Viva 13. Never ordered products?

p LEFT o IS NULL

Viva 14. GROUP BY role?

aggregate per group

Viva 15. HAVING vs WHERE?

group vs row

Viva 16. Sales formula?

price*qty then SUM

Viva 17. Aliases?

qualify + read

Viva 18. Cartesian?

missing/wrong ON

Viva 19. SELF + multi?

হ্যাঁ

Viva 20. Hospital chain?

patient→appt→doc→dept

Viva 21. Performance?

index join keys

Viva 22. SELECT * সমস্যা?

ambiguous/heavy

Viva 23. Start table?

business keep-side

Viva 24. BI use?

dashboards

Viva 25. DS use?

feature tables

Viva 26. Cheat memory?

Customer→Order→Product→Category

48

Homework

বাড়ি

প্রশ্ন (২০):

2-table customer-order
Order-product
Product-category
3-table customer-product
4-table + category
Dhaka filter
Category order counts
Category sales
Customer spending
City sales
HAVING sales>X
ORDER BY top categories
LIMIT top 3 customers
Customers no orders
Products never ordered
School 4-table
Hospital 4-table
SELF+dept report
Mixed LEFT/INNER explain
Full business report

উত্তর ১–৫

c JOIN o; o JOIN p; p JOIN cat; c-o-p; c-o-p-cat

উত্তর ৬

WHERE c.city='Dhaka'

উত্তর ৭–৮

COUNT / SUM(price*qty) GROUP BY category

উত্তর ৯–১০

GROUP BY customer / city

উত্তর ১১–১৩

HAVING; ORDER BY DESC; LIMIT 3

উত্তর ১৪–১৫

LEFT + IS NULL patterns

উত্তর ১৬–১৮

domain chains + e LEFT m JOIN d

উত্তর ১৯–২০

explain mix; Part 20 SELECT

49

Final Cheat Sheet

৫ মিনিট

MULTI-TABLE JOIN
3+ RELATED TABLES → IDENTIFY RELATIONSHIPS → JOIN CHAIN
→ FILTER → GROUP → SUMMARIZE → SORT → REPORT

SELECT c.customer_name, p.product_name, cat.category_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN products p ON o.product_id = p.product_id
JOIN categories cat ON p.category_id = cat.category_id;

Memory: Customer → Order → Product → Category

দরকারি information আলাদা tables-এ → relationship ধরে JOIN করে এক জায়গায় আনো।

বিষয়সূচী

গল্প — Online Shopping

কেন এক table-এ সব রাখব না?

1 → 2 → 3 → 4 Table

Big Visual Diagram

Multi-table JOIN কী?

আগে Relationship আঁকো

২-Table Revision

৩-Table JOIN

Animated Join Frames

৪-Table JOIN

কীভাবে Query পড়া/লেখা

INNER JOIN Chain

LEFT JOIN Chain

Mixed JOIN Types

+ WHERE

+ GROUP BY

+ SUM Sales

+ HAVING

+ ORDER BY

Complete Business Report

Case Study Schema

CREATE / INSERT Lab

১৫ Progressive Challenges

Build-the-Query Card Game

JOIN Chain Visualizer (Blueprint)

Intermediate Results

Common Mistakes (১৫)

Duplicate / Row Growth

1:1 · 1:N · M:N

1:1

1:N

School Example

Hospital Example

Company HR Example

SELF JOIN + Multi-table

Conceptual Execution Flow

Comparison Tables

Industry Use

Performance Basics

Playground UI Blueprint

Interactive Animation Blueprint

Predict the Row Count

Query Builder Game

Debugging Game

Live Class Activities (২০)

Mini Project — E-Commerce Analytics

Interview Top 30

MCQ (২৫+)

Viva

Homework

Final Cheat Sheet