Sample Data
customers
| cust_id | name | city |
|---|---|---|
| 1 | Ravi | Delhi |
| 2 | Sita | Chennai |
| 3 | John | Mumbai |
| 4 | Asha | Pune |
orders
| order_id | cust_id | amount |
|---|---|---|
| 101 | 1 | 5000 |
| 102 | 1 | 3000 |
| 103 | 2 | 7000 |
| 104 | 3 | 2000 |
payments
| pay_id | order_id | status |
|---|---|---|
| 1 | 101 | Success |
| 2 | 102 | Failed |
| 3 | 103 | Success |
Scenario 1: Show all customers with their orders (even if no order)
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id;
LEFT JOIN keeps all customers.
Asha has no order → shows NULL for order columns.
Others show matching orders.
Asha has no order → shows NULL for order columns.
Others show matching orders.
Scenario 2: Show only customers who placed orders
SELECT c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON c.cust_id = o.cust_id;
INNER JOIN returns only matching rows.
Asha is not returned because she has no order.
Asha is not returned because she has no order.
Scenario 3: Find customers who have not placed any order
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id
WHERE o.order_id IS NULL;
LEFT JOIN + NULL filter gives customers without orders.
Result = Asha.
Result = Asha.
Scenario 4: Show orders with payment status
SELECT o.order_id, o.amount, p.status
FROM orders o
LEFT JOIN payments p ON o.order_id = p.order_id;
Order 101 → Success
Order 102 → Failed
Order 103 → Success
Order 104 → NULL (no payment record)
Order 102 → Failed
Order 103 → Success
Order 104 → NULL (no payment record)
Scenario 5: Find orders where payment failed
SELECT o.order_id, o.amount
FROM orders o
JOIN payments p ON o.order_id = p.order_id
WHERE p.status = 'Failed';
Order 102 has Failed payment.
So only order 102 is returned.
So only order 102 is returned.
Scenario 6: Find customers who made successful payments
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
JOIN payments p ON o.order_id = p.order_id
WHERE p.status = 'Success';
Ravi (order 101) and Sita (order 103) have Success payments.
John’s order has no payment entry.
John’s order has no payment entry.
Scenario 7: Find customers with orders but no payment done
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
LEFT JOIN payments p ON o.order_id = p.order_id
WHERE p.order_id IS NULL;
John has order 104 but no payment record.
So John is returned.
So John is returned.
Scenario 8: Show total order amount per customer
SELECT c.name, SUM(o.amount) AS total_amount
FROM customers c
JOIN orders o ON c.cust_id = o.cust_id
GROUP BY c.name;
Ravi → 5000 + 3000 = 8000
Sita → 7000
John → 2000
Sita → 7000
John → 2000
Scenario 9: Self JOIN – Find customers from same city
SELECT c1.name AS customer1, c2.name AS customer2, c1.city
FROM customers c1
JOIN customers c2
ON c1.city = c2.city AND c1.cust_id < c2.cust_id;
Compares customers table with itself.
Finds pairs of customers in same city.
Finds pairs of customers in same city.
Scenario 10: Multi JOIN – Customer, Order and Payment report
SELECT c.name, o.order_id, o.amount, p.status
FROM customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id
LEFT JOIN payments p ON o.order_id = p.order_id;
Shows full report: customer → order → payment.
If order or payment missing, shows NULL.
If order or payment missing, shows NULL.
Source: sureshtechlabs.com