SQL QUERIES - FOR ANALYSIS

COMPARE 2 PRODUCTS

SELECT S_I.YEAR,
S_I.QUANTITY IPHONE_QUANT,
S_S.QUANTITY SAM_QUANT,
S_I.PRICE IPHONE_PRICE,
S_S.PRICE SAM_PRICE
FROM PRODUCTS P_I,
SALES S_I,
PRODUCTS P_S,
SALES S_S
WHERE P_I.PRODUCT_ID = S_I.PRODUCT_ID
AND P_S.PRODUCT_ID = S_S.PRODUCT_ID
AND P_I.PRODUCT_NAME = 'IPhone'
AND P_S.PRODUCT_NAME = 'Samsung'
AND S_I.YEAR = S_S.YEAR

SHOWING ROWS DATA IN COLUMNS

THIS QUERY REVEALS WHAT IS THE STRUCTURE OF THE TABLE.

DESCRIBE SALES;

THIS QUERY REVEALS WHAT IS THE TOTAL SALES PER YEAR.

SELECT A.YEAR ,SUM( A.QUANTITY*A.PRICE) AS SALES FROM SALES A GROUP BY A.YEAR;

THIS QUERY IS USED TO SHOW THE SALES PER YEAR COLUMN WISE.

SELECT

SUM(DECODE(S.YEAR,2010, S.QUANTITY*PRICE)) SALES_2010,

SUM(DECODE(S.YEAR,2011, S.QUANTITY*PRICE)) SALES_2011,

SUM(DECODE(S.YEAR,2012, S.QUANTITY*PRICE)) SALES_2012

FROM PRODUCTS P, SALES S

WHERE (P.PRODUCT_ID = S.PRODUCT_ID)

GROUP BY P.PRODUCT_NAME;

PIVOT TABLES IN ORACLE

HOW TO KNOW WHICH TABLES ARE AVAILABLE IN CURRENT DATABASE

--------------------------------------------------------------

-------------------------------------------------------------

----------------------------

CREATING VIEW IN ORACLE

HOW TO GET TOTAL SALES VOLUME PRODUCT WISE

BELOW QUERY FETCHES THE TOTAL SALES VOLUMES WITH RESPECT TO PRODUCT ID

SELECT PRODUCT_ID ,
YEAR, QUANTITY,
SUM(QUANTITY) OVER (PARTITION BY PRODUCT_ID ORDER BY PRODUCT_ID) TOTAL_SALES_PRODID
FROM SALES;

BELOW QUERY FETCHES THE TOTAL SALES VOLUMES WITH RESPECT TO YEAR

SELECT YEAR,PRODUCT_ID ,
QUANTITY,
SUM(QUANTITY) OVER (PARTITION BY YEAR ORDER BY YEAR) TOTAL_SALES_PRODID

FROM SALES;