exam_imp.sql

CREATE DATABASE IF NOT EXISTS retail_store;
USE retail_store;

CREATE TABLE Customers (
    cust_id INT PRIMARY KEY,
    cust_name VARCHAR(50) NOT NULL,
    city VARCHAR(50) NOT NULL
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    cust_id INT,
    order_date DATE NOT NULL,
    total_amount INT,
    FOREIGN KEY (cust_id) REFERENCES Customers(cust_id)
);

CREATE TABLE Products (
    prod_id INT PRIMARY KEY,
    prod_name VARCHAR(50) NOT NULL,
    price INT NOT NULL
);

CREATE TABLE OrderDetails (
    orderdetails_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    prod_id INT,
    quantity INT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (prod_id) REFERENCES Products(prod_id)
);

INSERT INTO Customers VALUES 
(1, 'Nivida Khanal', 'Buddha'),
(2, 'Sita Dhakal', 'Chowk'),
(3, 'Kritika Shrestha', 'Bazar'),
(4, 'Achal Gupta', 'Milan');

INSERT INTO Products VALUES
(1, 'Pen', 20),
(2, 'Book', 50),
(3, 'Bag', 100),
(4, 'Phone', 1000),
(5, 'Notebook', 30);

INSERT INTO Orders VALUES
(1, 1, CURDATE() - INTERVAL 2 MONTH, 0),
(2, 1, CURDATE() - INTERVAL 1 MONTH, 0),
(3, 2, CURDATE() - INTERVAL 3 MONTH, 0),
(4, 3, CURDATE() - INTERVAL 5 MONTH, 0),
(5, 4, CURDATE() - INTERVAL 7 MONTH, 0); 

INSERT INTO OrderDetails (order_id, prod_id, quantity) VALUES
(1, 1, 2),
(1, 2, 1),
(1, 3, 1),
(2, 4, 1),
(2, 5, 2),
(3, 1, 1),
(3, 3, 1),
(3, 2, 1),
(4, 5, 2),
(5, 1, 2);

SELECT 
    c.cust_name,
    c.city,
    SUM(p.price * od.quantity) AS total_spending
FROM 
    Customers c
JOIN 
    Orders o ON c.cust_id = o.cust_id
JOIN 
    OrderDetails od ON o.order_id = od.order_id
JOIN 
    Prozducts p ON od.prod_id = p.prod_id
WHERE 
    o.order_date >= CURDATE() - INTERVAL 6 MONTH
GROUP BY 
    c.cust_id, c.cust_name, c.city
HAVING 
    COUNT(DISTINCT od.prod_id) >= 3
ORDER BY 
    total_spending DESC
LIMIT 3;