Oracle_homework3

-- Assignment 1
SELECT
    DECODE(GROUPING(CITY.COUNTRY), 1, 'Summary-total:', CITY.COUNTRY) AS VISITED_COUNTRY,
    DECODE(GROUPING(CITY.COUNTRY), 0,
           DECODE(GROUPING(CITY.NAME), 1, 'Summary-country:', CITY.NAME),
           NULL) AS CITY,
    DECODE(GROUPING(CITY.COUNTRY), 0,
           DECODE(GROUPING(CITY.NAME), 0,
                  DECODE(GROUPING(ACCOMMODATIONTYPE.NAME), 1, 'Summary-city:', ACCOMMODATIONTYPE.NAME),
                  NULL),
           NULL) AS TYPE,
    SUM(VISIT.NUMBER_OF_VISITORS) AS VISITORS,
    SUM(VISIT.PROFIT) AS PROFIT
FROM
    VISIT
    JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
    JOIN ACCOMMODATIONTYPE ON VISIT.ID_TYPE = ACCOMMODATIONTYPE.ID_TYPE
GROUP BY
    ROLLUP(CITY.COUNTRY, CITY.NAME, ACCOMMODATIONTYPE.NAME)
ORDER BY
    VISITED_COUNTRY, CITY, TYPE;


--Assignment 2
SELECT CITY.COUNTRY VISITED_COUNTRY,
       TIME.YEAR YEAR,
       TIME.MONTH MONTH,
       SUM(VISIT.NUMBER_OF_VISITORS) VISITORS,
       ROUND(AVG(SUM(VISIT.NUMBER_OF_VISITORS))
            OVER (PARTITION BY CITY.COUNTRY
                  ORDER BY TIME.YEAR, TIME.MONTH
                  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),2) TREND
FROM VISIT
       JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
       JOIN TIME ON VISIT.ID_TIME = TIME.ID_TIME
WHERE  CONTINENT='South America'
GROUP BY CITY.COUNTRY, TIME.YEAR, TIME.MONTH
ORDER BY VISITED_COUNTRY, YEAR, MONTH;



--Assignment 3
SELECT NVL(AGE.NAME, 'SUMMARY') AGE,
       TIME.YEAR YEAR,
       TIME.MONTH MONTH,
       LPAD('$'||SUM(VISIT.PROFIT), 10) PROFIT,
       LPAD('$'||SUM(SUM(VISIT.PROFIT))
       OVER (PARTITION BY AGE.NAME
             ORDER BY TIME.YEAR, TIME.MONTH), 10) CUMULATIVE,
       ROUND(RATIO_TO_REPORT(SUM(VISIT.PROFIT)) OVER (PARTITION BY AGE.NAME), 3)*100 PERCENT
FROM VISIT
     JOIN AGE ON VISIT.ID_AGE = AGE.ID_AGE
     JOIN TIME ON VISIT.ID_TIME = TIME.ID_TIME
GROUP BY GROUPING SETS ((AGE.NAME, TIME.YEAR, TIME.MONTH), ())
ORDER BY AGE.NAME, YEAR, MONTH;

--Assignment 4
--Ranking for row 4 and 5?
SELECT
    CITY.COUNTRY COUNTRY,
    CITY.NAME CITY,
    RANK() OVER (PARTITION BY CITY.COUNTRY ORDER BY NVL(SUM(VISIT.NUMBER_OF_VISITORS), 0) DESC) RANKING_R1,
    RANK() OVER (PARTITION BY CITY.COUNTRY ORDER BY NVL(SUM(VISIT.PROFIT), 0) DESC) RANKING_R2
FROM CITY
    LEFT OUTER JOIN VISIT ON VISIT.ID_CITY = CITY.ID_CITY
WHERE CITY.CONTINENT = 'South America'
GROUP BY CITY.COUNTRY, CITY.NAME
ORDER BY CITY.COUNTRY, RANKING_R1;


SELECT
    CITY.COUNTRY COUNTRY,
    CITY.NAME CITY,
    NVL(SUM(VISIT.NUMBER_OF_VISITORS), 0) VISITORS,
    NVL(SUM(VISIT.PROFIT), 0) PROFIT
FROM CITY
    LEFT OUTER JOIN VISIT ON VISIT.ID_CITY = CITY.ID_CITY
WHERE CITY.CONTINENT = 'South America'
GROUP BY CITY.COUNTRY, CITY.NAME
ORDER BY CITY.COUNTRY, VISITORS DESC;


--Assignment 5
SELECT *
FROM (
    SELECT NVL(CITY.COUNTRY, '--TOTAL--') COUNTRY,
           NVL(AGE.NAME, 'TOTAL') AGE,
           SUM(VISIT.NUMBER_OF_VISITS) VISITS
    FROM VISIT
         JOIN AGE ON VISIT.ID_AGE = AGE.ID_AGE
         JOIN TIME ON VISIT.ID_TIME = TIME.ID_TIME
         JOIN CITY ON VISIT.ID_CITY = CITY.ID_CITY
    WHERE CITY.CONTINENT = 'Europe'
    GROUP BY CUBE(CITY.COUNTRY, AGE.NAME)
)
PIVOT (SUM(VISITS) FOR AGE IN ('child','teenager','young adult', 'adult',
                                  'middle age', 'senior', 'TOTAL'))
ORDER BY COUNTRY DESC NULLS LAST;