Advertisement
vvccs

ADS_8_OLAP

Oct 15th, 2024 (edited)
215
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 1.17 KB | None | 0 0
  1. ORACLE
  2.  
  3. CREATE TABLE gadgets (
  4.     item VARCHAR2(40),
  5.     brand VARCHAR2(20),
  6.     category VARCHAR2(20),
  7.     stock INT
  8. );
  9.  
  10. DROP TABLE gadgets;
  11. INSERT INTO gadgets VALUES ('Phone', 'Apple', 'Smartphone', 50);
  12. INSERT INTO gadgets VALUES ('Phone', 'Samsung', 'Smartphone', 40);
  13. INSERT INTO gadgets VALUES ('Tablet', 'Apple', 'Tablet', 30);
  14. INSERT INTO gadgets VALUES ('Laptop', 'Dell', 'Laptop', 25);
  15. INSERT INTO gadgets VALUES ('Phone', 'OnePlus', 'Smartphone', 20);
  16. INSERT INTO gadgets VALUES ('Laptop', 'HP', 'Laptop', 15);
  17.  
  18. SELECT * FROM gadgets;
  19.  
  20.  
  21. SELECT item, brand, category, SUM(stock) AS total_stock
  22. FROM gadgets
  23. GROUP BY ROLLUP(item, brand, category);
  24.  
  25. SELECT item, brand, category, SUM(stock) AS total_stock
  26. FROM gadgets
  27. GROUP BY CUBE(item, brand, category);
  28.  
  29. SELECT item, brand, stock
  30. FROM gadgets
  31. WHERE category = 'Smartphone';  -- Drill down into the Smartphone category
  32.  
  33.  
  34. SELECT item, brand, stock
  35. FROM gadgets
  36. WHERE category = 'Smartphone';  -- Slice by category = 'Smartphone'
  37.  
  38.  
  39. SELECT item, brand, stock
  40. FROM gadgets
  41. WHERE category IN ('Smartphone', 'Tablet') AND brand IN ('Apple', 'Samsung');  -- Dice for specific categories and brands
  42.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement