Oracle 13.04 class 6

--Assignment 1, 2
SELECT P.NAME, COUNT(B.ISBN) COUNT,
    RANK() OVER(ORDER BY COUNT(B.ISBN) DESC) RANK
    DENSE_RANK() OVER (ORDER BY COUNT(B.ISBN) DESC) DENSE_RANK
FROM BOOKS B JOIN PUBLISHER P ON B.PUBID=P.PUBID
GROUP BY P.NAME
ORDER BY 2 DESC;

--Assignment 3
SELECT O.SHIPCITY, SUM(OI.QUANTITY) QUANTITY,
       NTILE(4) OVER (ORDER BY SUM(OI.QUANTITY) DESC) NTILE
FROM ORDERS O JOIN ORDERITEMS OI ON O.ORDER#=OI.ORDER#
GROUP BY O.SHIPCITY;

--Assignment 4
SELECT P.NAME, TO_CHAR(O.ORDERDATE, 'YYYY-MM') TIME,
       SUM(OI.QUANTITY*(B.RETAIL-B.COST)) PROFIT,
       SUM(SUM(OI.QUANTITY*(B.RETAIL-B.COST)))
       OVER(PARTITION BY P.NAME
            ORDER BY TO_CHAR(O.ORDERDATE, 'YYYY-MM')
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CUM_SUM
FROM ORDERS O JOIN ORDERITEMS OI ON O.ORDER#=OI.ORDER#
        JOIN BOOKS B ON OI.ISBN=B.ISBN JOIN PUBLISHER P
        ON B.PUBID=P.PUBID
GROUP BY P.NAME, TO_CHAR(O.ORDERDATE, 'YYYY-MM')
ORDER BY 1,2;

--Assignment 5
SELECT P.NAME, TO_CHAR(O.ORDERDATE, 'YYYY-MM') TIME,
       SUM(OI.QUANTITY*(B.RETAIL-B.COST)) PROFIT,
       ROUND(AVG(SUM(OI.QUANTITY*(B.RETAIL-B.COST)))
       OVER(PARTITION BY P.NAME
            ORDER BY TO_CHAR(O.ORDERDATE, 'YYYY-MM')
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),2) MOV_AVG
FROM ORDERS O JOIN ORDERITEMS OI ON O.ORDER#=OI.ORDER#
        JOIN BOOKS B ON OI.ISBN=B.ISBN JOIN PUBLISHER P
        ON B.PUBID=P.PUBID
GROUP BY P.NAME, TO_CHAR(O.ORDERDATE, 'YYYY-MM')
ORDER BY 1,2;

--Assignment 6
SELECT P.NAME, TO_CHAR(O.ORDERDATE, 'YYYY-MM') TIME,
       SUM(OI.QUANTITY*(B.RETAIL-B.COST)) PROFIT,
       ROUND(AVG(SUM(OI.QUANTITY*(B.RETAIL-B.COST)))
       OVER(PARTITION BY P.NAME
            ORDER BY TO_CHAR(O.ORDERDATE, 'YYYY-MM')
            ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING),2) CEN_AVG
FROM ORDERS O JOIN ORDERITEMS OI ON O.ORDER#=OI.ORDER#
        JOIN BOOKS B ON OI.ISBN=B.ISBN JOIN PUBLISHER P
        ON B.PUBID=P.PUBID
GROUP BY P.NAME, TO_CHAR(O.ORDERDATE, 'YYYY-MM')
ORDER BY 1,2;

--Assignment 7
SELECT B.CATEGORY, EXTRACT(MONTH FROM O.ORDERDATE) MONTH,
       SUM(OI.QUANTITY) QUANTITY,
       ROUND(AVG(SUM(OI.QUANTITY))
       OVER (PARTITION BY EXTRACT(MONTH FROM O.ORDERDATE)),2) AVG
FROM ORDERS O JOIN ORDERITEMS OI ON O.ORDER#=OI.ORDER#
        JOIN BOOKS B ON OI.ISBN=B.ISBN
WHERE EXTRACT(YEAR FROM O.ORDERDATE) = 2005
GROUP BY B.CATEGORY, EXTRACT(MONTH FROM O.ORDERDATE)
ORDER BY 2;

--Assignment 8
SELECT P.NAME,
       SUM(OI.QUANTITY*(B.RETAIL-B.COST)) PROFIT,
       SUM(OI.QUANTITY*(B.RETAIL-B.COST)) -
       FIRST_VALUE(SUM(OI.QUANTITY*(B.RETAIL-B.COST))) OVER() FIRST,
       SUM(OI.QUANTITY*(B.RETAIL-B.COST)) -
       LAST_VALUE(SUM(OI.QUANTITY*(B.RETAIL-B.COST))) OVER() LAST
FROM ORDERITEMS OI
JOIN BOOKS B ON OI.ISBN=B.ISBN
JOIN PUBLISHER P ON B.PUBID=P.PUBID
GROUP BY P.NAME
ORDER BY 2 DESC;

--Assignment 9
SELECT B.CATEGORY,EXTRACT(MONTH FROM O.ORDERDATE) MONTH,
       SUM(OI.QUANTITY*(B.RETAIL-B.COST)) PROFIT,
       ROUND(RATIO_TO_REPORT(SUM(OI.QUANTITY*(B.RETAIL-B.COST)))
       OVER(PARTITION BY B.CATEGORY),3)*100 PERCENT
FROM ORDERS O JOIN ORDERITEMS OI ON O.ORDER#=OI.ORDER#
        JOIN BOOKS B ON OI.ISBN=B.ISBN
WHERE EXTRACT(YEAR FROM O.ORDERDATE) = 2005
GROUP BY B.CATEGORY, EXTRACT(MONTH FROM O.ORDERDATE);

--Assignment 10
SELECT *
FROM (
SELECT B.CATEGORY,
       DECODE(EXTRACT(MONTH FROM O.ORDERDATE), NULL, 'SUMMARY',
       EXTRACT(MONTH FROM O.ORDERDATE)) MONTH,
       SUM(OI.QUANTITY*B.RETAIL) SALE
FROM ORDERS O JOIN ORDERITEMS OI ON O.ORDER#=OI.ORDER#
        JOIN BOOKS B ON OI.ISBN=B.ISBN
WHERE EXTRACT(YEAR FROM O.ORDERDATE) = 2005
GROUP BY CUBE(B.CATEGORY, EXTRACT(MONTH FROM O.ORDERDATE))
) PIVOT (SUM(SALE) FOR MONTH IN (3,4, 'SUMMARY'));