ADS_8_OLAP

ORACLE

CREATE TABLE gadgets (
    item VARCHAR2(40),
    brand VARCHAR2(20),
    category VARCHAR2(20),
    stock INT
);

DROP TABLE gadgets;
INSERT INTO gadgets VALUES ('Phone', 'Apple', 'Smartphone', 50);
INSERT INTO gadgets VALUES ('Phone', 'Samsung', 'Smartphone', 40);
INSERT INTO gadgets VALUES ('Tablet', 'Apple', 'Tablet', 30);
INSERT INTO gadgets VALUES ('Laptop', 'Dell', 'Laptop', 25);
INSERT INTO gadgets VALUES ('Phone', 'OnePlus', 'Smartphone', 20);
INSERT INTO gadgets VALUES ('Laptop', 'HP', 'Laptop', 15);

SELECT * FROM gadgets;


SELECT item, brand, category, SUM(stock) AS total_stock
FROM gadgets
GROUP BY ROLLUP(item, brand, category);

SELECT item, brand, category, SUM(stock) AS total_stock
FROM gadgets
GROUP BY CUBE(item, brand, category);

SELECT item, brand, stock
FROM gadgets
WHERE category = 'Smartphone';  -- Drill down into the Smartphone category


SELECT item, brand, stock
FROM gadgets
WHERE category = 'Smartphone';  -- Slice by category = 'Smartphone'


SELECT item, brand, stock
FROM gadgets
WHERE category IN ('Smartphone', 'Tablet') AND brand IN ('Apple', 'Samsung');  -- Dice for specific categories and brands