CODE:
CREATE TABLE DimStore (
Store_id SERIAL PRIMARY KEY,
Store_name VARCHAR(100),
City VARCHAR(50),
State VARCHAR(50),
Region VARCHAR(50)
);
CREATE TABLE DimDrug (
Drug_id SERIAL PRIMARY KEY,
Drug_name VARCHAR(100),
Drug_type VARCHAR(50),
Manufacturer VARCHAR(100),
Expiry_date DATE
);
CREATE TABLE DimCustomer (
Customer_id SERIAL PRIMARY KEY,
Customer_name VARCHAR(100),
Age INT,
Gender VARCHAR(10),
Loyalty_card BOOLEAN
);
CREATE TABLE DimDate (
Date_id SERIAL PRIMARY KEY,
Day INT,
Month INT,
Year INT,
Quarter INT
);
CREATE TABLE FactSales (
Fact_id SERIAL PRIMARY KEY,
Store_id INT REFERENCES DimStore(Store_id),
Customer_id INT REFERENCES DimCustomer(Customer_id),
Drug_id INT REFERENCES DimDrug(Drug_id),
Date_id INT REFERENCES DimDate(Date_id),
Quantity_sold INT CHECK (Quantity_sold >= 0),
Total_amount NUMERIC(10,2) CHECK (Total_amount >= 0)
);
INSERT INTO DimStore (Store_name, City, State, Region) VALUES
('z', 'Mumbai', 'Maharashtra', 'West'),
('y', 'Pune', 'Maharashtra', 'West'),
('x', 'Bangalore', 'Karnataka', 'South'),
('w', 'Hyderabad', 'Telangana', 'South'),
('v', 'Delhi', 'Delhi', 'North');
INSERT INTO DimDrug (Drug_name, Drug_type, Manufacturer, Expiry_date) VALUES
('Paracetamol', 'Tablet', 'Cipla', '2026-05-01'),
('Amoxicillin', 'Capsule', 'Sun Pharma', '2025-12-15'),
('Ibuprofen', 'Tablet', 'Dr. Reddy', '2026-03-20'),
('Cetirizine', 'Syrup', 'Zydus', '2025-11-11'),
('Azithromycin', 'Tablet', 'Cipla', '2026-08-30');
INSERT INTO DimCustomer (Customer_name, Age, Gender, Loyalty_card) VALUES
('A', 29, 'Male', TRUE),
('B', 35, 'Female', TRUE),
('C', 42, 'Male', FALSE),
('D', 24, 'Female', TRUE),
('E', 51, 'Male', FALSE);
INSERT INTO DimDate (Day, Month, Year, Quarter) VALUES
(15, 1, 2024, 1),
(20, 3, 2024, 1),
(5, 6, 2024, 2),
(10, 9, 2024, 3),
(18, 11, 2024, 4),
(2, 1, 2025, 1);
INSERT INTO FactSales (Store_id, Customer_id, Drug_id, Date_id, Quantity_sold, Total_amount)
VALUES
(1, 1, 1, 1, 3, 90.00), -- Mumbai, Paracetamol
(1, 2, 2, 2, 2, 120.00), -- Mumbai, Amoxicillin
(2, 3, 3, 3, 1, 45.00), -- Pune, Ibuprofen
(2, 1, 1, 4, 4, 120.00), -- Pune, Paracetamol
(3, 2, 4, 1, 2, 80.00), -- Bangalore, Cetirizine
(3, 4, 5, 2, 3, 150.00), -- Bangalore, Azithromycin
(4, 5, 3, 3, 1, 50.00), -- Hyderabad, Ibuprofen
(4, 3, 1, 4, 2, 60.00), -- Hyderabad, Paracetamol
(5, 5, 2, 5, 2, 110.00), -- Delhi, Amoxicillin
(5, 4, 4, 6, 1, 45.00), -- Delhi, Cetirizine
(1, 2, 5, 3, 2, 140.00), -- Mumbai, Azithromycin
(2, 1, 3, 5, 3, 130.00); -- Pune, Ibuprofen
SELECT * FROM DimStore;
SELECT * FROM DimDrug;
SELECT * FROM DimCustomer;
SELECT * FROM DimDate;
SELECT * FROM FactSales;
ROLL-UP:
SELECT
ds.Region,
SUM(fs.Total_amount) AS Total_Sales
FROM FactSales fs
JOIN DimStore ds ON fs.Store_id = ds.Store_id
GROUP BY ROLLUP(ds.Region)
ORDER BY ds.Region;
DRILL-DOWN:
SELECT dd.Year, dd.Quarter, dd.Month, SUM(fs.Total_amount) AS Total_Sales
FROM FactSales fs
JOIN DimDate dd ON fs.Date_id = dd.Date_id
GROUP BY dd.Year, dd.Quarter, dd.Month
ORDER BY dd.Year, dd.Quarter, dd.Month;
SLICE:
SELECT fs.*
FROM FactSales fs
JOIN DimStore ds ON fs.Store_id = ds.Store_id
WHERE ds.City = 'Pune';
DICE:
SELECT fs.*
FROM FactSales fs
JOIN DimStore ds ON fs.Store_id = ds.Store_id
JOIN DimDrug dd ON fs.Drug_id = dd.Drug_id
JOIN DimDate ddate ON fs.Date_id = ddate.Date_id
WHERE ds.Region = 'West'
AND dd.Drug_type = 'Tablet'
AND ddate.Quarter IN (1, 2);
