-- 1️⃣ Product টেবিল তৈরি
CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    Category VARCHAR(50),
    Price DECIMAL(10,2) NOT NULL,
    Stock INT NOT NULL
);
 
-- 2️⃣ Sell টেবিল তৈরি
CREATE TABLE Sell (
    SellID INT PRIMARY KEY,
    ProductID INT,
    Quantity INT NOT NULL,
    SellDate DATE NOT NULL,
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);
 
-- 3️⃣ Product ডাটা ইনসার্ট
INSERT INTO Product (ProductID, ProductName, Category, Price, Stock)
VALUES
(1, 'Smart T-Shirt', 'Clothing', 450.00, 50),
(2, 'Smart Jeans', 'Clothing', 1200.00, 30),
(3, 'Sneakers', 'Footwear', 2500.00, 20),
(4, 'Cap', 'Accessories', 300.00, 100);
 
-- 4️⃣ Sell ডাটা ইনসার্ট
INSERT INTO Sell (SellID, ProductID, Quantity, SellDate)
VALUES
(101, 1, 20, '2025-08-01'),
(102, 1, 15, '2025-08-03'),
(103, 2, 5, '2025-08-04'),
(104, 3, 10, '2025-08-05'),
(105, 4, 12, '2025-08-06'),
(106, 4, 25, '2025-08-07');
 
-------------------------------------------------
-- প্রশ্ন 2: Retrieve sales details for products with 'Smart' in their name
-------------------------------------------------
SELECT 
    s.SellID,
    p.ProductName,
    s.Quantity,
    s.SellDate,
    (s.Quantity * p.Price) AS TotalPrice
FROM Sell s
JOIN Product p ON s.ProductID = p.ProductID
WHERE p.ProductName LIKE '%Smart%';
 
-------------------------------------------------
-- প্রশ্ন 3: Retrieve product name and total sales revenue for each product
-------------------------------------------------
SELECT 
    p.ProductName,
    SUM(s.Quantity * p.Price) AS TotalRevenue
FROM Product p
JOIN Sell s ON p.ProductID = s.ProductID
GROUP BY p.ProductName;
 
-------------------------------------------------
-- প্রশ্ন 4: Categorize sales as High, Medium, or Low
-------------------------------------------------
SELECT 
    s.SellID,
    p.ProductName,
    (s.Quantity * p.Price) AS TotalPrice,
    CASE
        WHEN (s.Quantity * p.Price) > 200 THEN 'High'
        WHEN (s.Quantity * p.Price) BETWEEN 100 AND 200 THEN 'Medium'
        ELSE 'Low'
    END AS SaleCategory
FROM Sell s
JOIN Product p ON s.ProductID = p.ProductID;
 
-------------------------------------------------
-- প্রশ্ন 5: Calculate the number of days between current date and sale date
-------------------------------------------------
SELECT 
    s.SellID,
    p.ProductName,
    s.SellDate,
    DATEDIFF(CURRENT_DATE, s.SellDate) AS DaysSinceSale
FROM Sell s
JOIN Product p ON s.ProductID = p.ProductID;

LS0gMe+4j+KDoyBQcm9kdWN0IOCmn+Cnh+CmrOCmv+CmsiDgpqTgp4jgprDgpr8KQ1JFQVRFIFRBQkxFIFByb2R1Y3QgKAogICAgUHJvZHVjdElEIElOVCBQUklNQVJZIEtFWSwKICAgIFByb2R1Y3ROYW1lIFZBUkNIQVIoMTAwKSBOT1QgTlVMTCwKICAgIENhdGVnb3J5IFZBUkNIQVIoNTApLAogICAgUHJpY2UgREVDSU1BTCgxMCwyKSBOT1QgTlVMTCwKICAgIFN0b2NrIElOVCBOT1QgTlVMTAopOwoKLS0gMu+4j+KDoyBTZWxsIOCmn+Cnh+CmrOCmv+CmsiDgpqTgp4jgprDgpr8KQ1JFQVRFIFRBQkxFIFNlbGwgKAogICAgU2VsbElEIElOVCBQUklNQVJZIEtFWSwKICAgIFByb2R1Y3RJRCBJTlQsCiAgICBRdWFudGl0eSBJTlQgTk9UIE5VTEwsCiAgICBTZWxsRGF0ZSBEQVRFIE5PVCBOVUxMLAogICAgRk9SRUlHTiBLRVkgKFByb2R1Y3RJRCkgUkVGRVJFTkNFUyBQcm9kdWN0KFByb2R1Y3RJRCkKKTsKCi0tIDPvuI/ig6MgUHJvZHVjdCDgpqHgpr7gpp/gpr4g4KaH4Kao4Ka44Ka+4Kaw4KeN4KafCklOU0VSVCBJTlRPIFByb2R1Y3QgKFByb2R1Y3RJRCwgUHJvZHVjdE5hbWUsIENhdGVnb3J5LCBQcmljZSwgU3RvY2spClZBTFVFUwooMSwgJ1NtYXJ0IFQtU2hpcnQnLCAnQ2xvdGhpbmcnLCA0NTAuMDAsIDUwKSwKKDIsICdTbWFydCBKZWFucycsICdDbG90aGluZycsIDEyMDAuMDAsIDMwKSwKKDMsICdTbmVha2VycycsICdGb290d2VhcicsIDI1MDAuMDAsIDIwKSwKKDQsICdDYXAnLCAnQWNjZXNzb3JpZXMnLCAzMDAuMDAsIDEwMCk7CgotLSA077iP4oOjIFNlbGwg4Kah4Ka+4Kaf4Ka+IOCmh+CmqOCmuOCmvuCmsOCnjeCmnwpJTlNFUlQgSU5UTyBTZWxsIChTZWxsSUQsIFByb2R1Y3RJRCwgUXVhbnRpdHksIFNlbGxEYXRlKQpWQUxVRVMKKDEwMSwgMSwgMjAsICcyMDI1LTA4LTAxJyksCigxMDIsIDEsIDE1LCAnMjAyNS0wOC0wMycpLAooMTAzLCAyLCA1LCAnMjAyNS0wOC0wNCcpLAooMTA0LCAzLCAxMCwgJzIwMjUtMDgtMDUnKSwKKDEwNSwgNCwgMTIsICcyMDI1LTA4LTA2JyksCigxMDYsIDQsIDI1LCAnMjAyNS0wOC0wNycpOwoKLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQotLSDgpqrgp43gprDgprbgp43gpqggMjogUmV0cmlldmUgc2FsZXMgZGV0YWlscyBmb3IgcHJvZHVjdHMgd2l0aCAnU21hcnQnIGluIHRoZWlyIG5hbWUKLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQpTRUxFQ1QgCiAgICBzLlNlbGxJRCwKICAgIHAuUHJvZHVjdE5hbWUsCiAgICBzLlF1YW50aXR5LAogICAgcy5TZWxsRGF0ZSwKICAgIChzLlF1YW50aXR5ICogcC5QcmljZSkgQVMgVG90YWxQcmljZQpGUk9NIFNlbGwgcwpKT0lOIFByb2R1Y3QgcCBPTiBzLlByb2R1Y3RJRCA9IHAuUHJvZHVjdElECldIRVJFIHAuUHJvZHVjdE5hbWUgTElLRSAnJVNtYXJ0JSc7CgotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCi0tIOCmquCnjeCmsOCmtuCnjeCmqCAzOiBSZXRyaWV2ZSBwcm9kdWN0IG5hbWUgYW5kIHRvdGFsIHNhbGVzIHJldmVudWUgZm9yIGVhY2ggcHJvZHVjdAotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tClNFTEVDVCAKICAgIHAuUHJvZHVjdE5hbWUsCiAgICBTVU0ocy5RdWFudGl0eSAqIHAuUHJpY2UpIEFTIFRvdGFsUmV2ZW51ZQpGUk9NIFByb2R1Y3QgcApKT0lOIFNlbGwgcyBPTiBwLlByb2R1Y3RJRCA9IHMuUHJvZHVjdElECkdST1VQIEJZIHAuUHJvZHVjdE5hbWU7CgotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCi0tIOCmquCnjeCmsOCmtuCnjeCmqCA0OiBDYXRlZ29yaXplIHNhbGVzIGFzIEhpZ2gsIE1lZGl1bSwgb3IgTG93Ci0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KU0VMRUNUIAogICAgcy5TZWxsSUQsCiAgICBwLlByb2R1Y3ROYW1lLAogICAgKHMuUXVhbnRpdHkgKiBwLlByaWNlKSBBUyBUb3RhbFByaWNlLAogICAgQ0FTRQogICAgICAgIFdIRU4gKHMuUXVhbnRpdHkgKiBwLlByaWNlKSA+IDIwMCBUSEVOICdIaWdoJwogICAgICAgIFdIRU4gKHMuUXVhbnRpdHkgKiBwLlByaWNlKSBCRVRXRUVOIDEwMCBBTkQgMjAwIFRIRU4gJ01lZGl1bScKICAgICAgICBFTFNFICdMb3cnCiAgICBFTkQgQVMgU2FsZUNhdGVnb3J5CkZST00gU2VsbCBzCkpPSU4gUHJvZHVjdCBwIE9OIHMuUHJvZHVjdElEID0gcC5Qcm9kdWN0SUQ7CgotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCi0tIOCmquCnjeCmsOCmtuCnjeCmqCA1OiBDYWxjdWxhdGUgdGhlIG51bWJlciBvZiBkYXlzIGJldHdlZW4gY3VycmVudCBkYXRlIGFuZCBzYWxlIGRhdGUKLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQpTRUxFQ1QgCiAgICBzLlNlbGxJRCwKICAgIHAuUHJvZHVjdE5hbWUsCiAgICBzLlNlbGxEYXRlLAogICAgREFURURJRkYoQ1VSUkVOVF9EQVRFLCBzLlNlbGxEYXRlKSBBUyBEYXlzU2luY2VTYWxlCkZST00gU2VsbCBzCkpPSU4gUHJvZHVjdCBwIE9OIHMuUHJvZHVjdElEID0gcC5Qcm9kdWN0SUQ7