retail store database with subquery

Consider a retail store database that includes the following tables:

Shoppers (ShopperID, Name, City)
Purchases (PurchaseID, ShopperID, PurchaseDate)
Items (ItemID, ItemName, Price)
PurchaseDetails (PurchaseID, ItemID, Quantity)

Using subqueries, write SQL statements to answer the following questions. Also provide a brief explanation or justification for each result:

  • Determine the shopper(s) who purchased the most expensive item.

  • Retrieve the names of shoppers whose total purchase amounts exceed the average purchase amount.

  • List the items bought by shoppers who are from Chittagong.

  • Identify the city that has the highest number of shoppers.

  • Find the shoppers who have purchased every item available in the Items table.

SQL Queries Using Subqueries – Retail Store Database (With Example Data)

Sample Data

Shoppers

ShopperID | Name    | City
--------------------------------
1         | Rahim   | Dhaka
2         | Karim   | Chittagong
3         | Ayesha  | Chittagong
4         | Nabila  | Khulna

Items

ItemID | ItemName | Price
------------------------------
1      | Laptop   | 80000
2      | Mobile   | 25000
3      | Headset  | 2000
4      | Keyboard | 1500

Purchases

PurchaseID | ShopperID | PurchaseDate
---------------------------------------
101        | 1         | 2026-02-01
102        | 2         | 2026-02-03
103        | 3         | 2026-02-05
104        | 2         | 2026-02-10
105        | 1         | 2026-02-12

PurchaseDetails

PurchaseID | ItemID | Quantity
--------------------------------
101        | 1      | 1
101        | 3      | 2
102        | 2      | 1
103        | 3      | 3
104        | 1      | 1
104        | 4      | 1
105        | 2      | 1
105        | 4      | 2

1. Shopper(s) Who Bought the Most Expensive Item

SELECT DISTINCT s.Name
FROM Shoppers s
JOIN Purchases p ON s.ShopperID = p.ShopperID
JOIN PurchaseDetails pd ON p.PurchaseID = pd.PurchaseID
JOIN Items i ON pd.ItemID = i.ItemID
WHERE i.Price = (SELECT MAX(Price) FROM Items);

Result from Sample Data: Rahim, Karim

Explanation:
Laptop (80,000) is the most expensive item. Both Rahim (Purchase 101) and Karim (Purchase 104) bought a Laptop.

2. Shoppers Whose Total Spending is Greater Than Average

SELECT s.Name
FROM Shoppers s
WHERE s.ShopperID IN (
    SELECT p.ShopperID
    FROM Purchases p
    JOIN PurchaseDetails pd ON p.PurchaseID = pd.PurchaseID
    JOIN Items i ON pd.ItemID = i.ItemID
    GROUP BY p.ShopperID
    HAVING SUM(pd.Quantity * i.Price) >
           (SELECT AVG(total_amount)
            FROM (
                SELECT SUM(pd2.Quantity * i2.Price) AS total_amount
                FROM Purchases p2
                JOIN PurchaseDetails pd2 ON p2.PurchaseID = pd2.PurchaseID
                JOIN Items i2 ON pd2.ItemID = i2.ItemID
                GROUP BY p2.ShopperID
            ))
);

Calculation from Data:

  • Rahim = 80000 + (2×2000) + 25000 + (2×1500) = 112000
  • Karim = 25000 + 80000 + 1500 = 106500
  • Ayesha = 3×2000 = 6000
  • Nabila = 0

Average spending ≈ 56125

Result: Rahim, Karim

3. Items Purchased by Shoppers from Chittagong

SELECT DISTINCT i.ItemName
FROM Items i
WHERE i.ItemID IN (
    SELECT pd.ItemID
    FROM PurchaseDetails pd
    JOIN Purchases p ON pd.PurchaseID = p.PurchaseID
    WHERE p.ShopperID IN (
        SELECT ShopperID
        FROM Shoppers
        WHERE City = 'Chittagong'
    )
);

Shoppers from Chittagong: Karim, Ayesha

Result: Laptop, Mobile, Headset, Keyboard

Explanation:
Karim bought Laptop, Mobile, Keyboard.
Ayesha bought Headset.
So combined result includes all four items.

4. City with Maximum Number of Shoppers

SELECT City
FROM Shoppers
GROUP BY City
HAVING COUNT(*) = (
    SELECT MAX(city_count)
    FROM (
        SELECT COUNT(*) AS city_count
        FROM Shoppers
        GROUP BY City
    )
);

Shopper Count by City:

  • Dhaka = 1
  • Chittagong = 2
  • Khulna = 1

Result: Chittagong

5. Shoppers Who Purchased All Items

SELECT s.Name
FROM Shoppers s
WHERE NOT EXISTS (
    SELECT i.ItemID
    FROM Items i
    WHERE NOT EXISTS (
        SELECT *
        FROM Purchases p
        JOIN PurchaseDetails pd ON p.PurchaseID = pd.PurchaseID
        WHERE p.ShopperID = s.ShopperID
        AND pd.ItemID = i.ItemID
    )
);

Analysis from Data:

  • Rahim did not buy Laptop? (Yes)
  • Rahim did not buy Headset? (Yes)
  • Rahim did not buy Keyboard? (Yes)
  • Rahim did not buy Mobile? (Yes)

Rahim bought all 4 items.

Result: Rahim

Final Summary

This example demonstrates how subqueries work with MAX, AVG, IN, NOT EXISTS, and nested aggregation to solve complex relational database queries using realistic retail store data.

কোন মন্তব্য নেই:

একটি মন্তব্য পোস্ট করুন

এতে সদস্যতা: মন্তব্যসমূহ (Atom)