text: --All tables
  -------From Rest service
  SELECT * FROM VEHICLES_VIEW;
  -------From csv file
  SELECT * FROM "TravelPolicy_Destinations";
  
  --Oracle DB tables
  SELECT * FROM AUTODATA@OracleDB;
  SELECT * FROM CUSTOMER@OracleDB;
  SELECT * FROM PROFILE@OracleDB;
  SELECT * FROM TRAVELDATA@OracleDB;
  
  --PostgreSQL DB tables
  SELECT * FROM AUTOPOLICY@PostgresDB;
  SELECT * FROM INSURANCEPOLICY@PostgresDB;
  SELECT * FROM TRAVELPOLICY@PostgresDB;
  
  -------[Dimensions]----------------------------------
  --D1: {Customers}-{DriverType}-{CoverageLevels}
  
  DROP VIEW OLAP_DIM_CUST_DT_CL;
  CREATE OR REPLACE VIEW OLAP_DIM_CUST_DT_CL AS
  SELECT p.NAME || ' ' || p.SURNAME AS FullName,
         a.TYPE AS DriverType,
         ip.COVERAGELEVEL AS CoverageLevel
  FROM CUSTOMER@OracleDB c
    INNER JOIN PROFILE@OracleDB p ON c.ID = p.CUSTOMER_ID
    INNER JOIN AUTODATA@OracleDB a ON c.ID = a.CUSTOMER_ID
    INNER JOIN INSURANCEPOLICY@PostgresDB ip ON c.ID = ip.CUSTOMER_ID;
  
  SELECT  * FROM OLAP_DIM_CUST_DT_CL;
  
  --D2: {PolicyDuration}-{Expired}-{PolicyCategory}-{Customer}
  DROP VIEW OLAP_DIM_POLICIES_CUSTOMERS;
  CREATE OR REPLACE VIEW OLAP_DIM_POLICIES_CUSTOMERS AS
  SELECT round((TO_DATE(ENDDATE) - TO_DATE(STARDATE))/365,0) AS DURATION,
         CASE	WHEN (CURRENT_DATE - TO_DATE(ENDDATE))>0 THEN 'Expired'
  			      ELSE 'Not Expired'
  		   END EXPIRATION,
         INSURANCETYPE,
         p.NAME || ' ' || p.SURNAME AS FullName
  FROM INSURANCEPOLICY@PostgresDB ip
    INNER JOIN  CUSTOMER@OracleDB c ON ip.CUSTOMER_ID = c.ID
    INNER JOIN PROFILE@OracleDB p ON c.ID = p.CUSTOMER_ID;
  
  SELECT * FROM OLAP_DIM_POLICIES_CUSTOMERS;
  
  --D3: {Customers}-{DriveExperience}-{Vehicle}
  
  DROP VIEW OLAP_CUST_EXP_CAR;
  CREATE OR REPLACE VIEW OLAP_CUST_EXP_CAR AS
  SELECT p.NAME || ' ' || p.SURNAME AS FullName,
         a.EXPRIENCE || ' '|| 'years' AS EXPERIENCE,
         vv.MANUFACTURER || ' ' || vv.MODEL || ' ' || vv.YEAR AS CAR
  FROM CUSTOMER@OracleDB c
    INNER JOIN PROFILE@OracleDB p ON c.ID = p.CUSTOMER_ID
    INNER JOIN AUTODATA@OracleDB a ON c.ID = a.CUSTOMER_ID
    INNER JOIN INSURANCEPOLICY@PostgresDB ip ON c.ID = ip.CUSTOMER_ID
    INNER JOIN VEHICLES_VIEW vv ON ip.POLICY_ID = vv.POLICYID;
  
  SELECT * FROM OLAP_CUST_EXP_CAR;
  
  --D4: {Customers}-{InsurancePolicy}-{TravelDestinations}-{TravelPolicy}
  
  DROP VIEW OLAP_CUST_IP_TD_TP;
  CREATE OR REPLACE VIEW OLAP_CUST_IP_TD_TP AS
  SELECT p.NAME || ' ' || p.SURNAME AS FullName,
        ip.COVERAGELEVEL,
         tpd.COUNTRY,
         tp.HEALTH_CATEGORY
  FROM CUSTOMER@OracleDB c
    INNER JOIN PROFILE@OracleDB p ON c.ID = p.CUSTOMER_ID
    INNER JOIN INSURANCEPOLICY@PostgresDB ip ON c.ID = ip.CUSTOMER_ID
    INNER JOIN "TravelPolicy_Destinations" tpd ON ip.POLICY_ID = tpd.POLICY_ID
    INNER JOIN TRAVELPOLICY@PostgresDB tp ON ip.POLICY_ID = tp.POLICY_ID;
  
  SELECT * FROM OLAP_CUST_IP_TD_TP;
  
  --C1: {Cars values cube}
  
  DROP VIEW CUBE_CARS_VALUES;
  CREATE OR REPLACE VIEW CUBE_CARS_VALUES AS
  SELECT MANUFACTURER,TYPE,SUM(VALUE) AS VALUE
  FROM VEHICLES_VIEW
  GROUP BY CUBE (MANUFACTURER,TYPE)
  ORDER BY MANUFACTURER,TYPE;
  
  SELECT * FROM CUBE_CARS_VALUES;
  
  --C2: {Number of countires by Names, Coverage level and Health category}
  
  DROP VIEW CUBE_COUNTRY_COUNT;
  CREATE OR REPLACE VIEW CUBE_COUNTRY_COUNT AS
  SELECT FullName,COVERAGELEVEL,HEALTH_CATEGORY,COUNT(COUNTRY) AS COUNTRIES
  FROM(SELECT p.NAME || ' ' || p.SURNAME AS FullName,
        ip.COVERAGELEVEL,
         tpd.COUNTRY,
         tp.HEALTH_CATEGORY
    FROM CUSTOMER@OracleDB c
      INNER JOIN PROFILE@OracleDB p ON c.ID = p.CUSTOMER_ID
      INNER JOIN INSURANCEPOLICY@PostgresDB ip ON c.ID = ip.CUSTOMER_ID
      INNER JOIN "TravelPolicy_Destinations" tpd ON ip.POLICY_ID = tpd.POLICY_ID
      INNER JOIN TRAVELPOLICY@PostgresDB tp ON ip.POLICY_ID = tp.POLICY_ID)
  GROUP BY CUBE (FullName,COVERAGELEVEL,HEALTH_CATEGORY)
  ORDER BY FullName,COVERAGELEVEL,HEALTH_CATEGORY;
  
  SELECT * FROM CUBE_COUNTRY_COUNT;
  
  --C3: {Number of beneficiaries by insurance type and coverage level}
  
  DROP VIEW CUBE_BENEFICIARIES_COUNT;
  CREATE OR REPLACE VIEW CUBE_BENEFICIARIES_COUNT AS
  SELECT INSURANCETYPE,COVERAGELEVEL,COUNT(BENEFICIARY_ID) AS BENEFICIARIES
  FROM(SELECT ip.INSURANCETYPE,ip.COVERAGELEVEL,ap.BENEFICIARY_ID
      FROM INSURANCEPOLICY@PostgresDB ip
        INNER JOIN AUTOPOLICY@PostgresDB ap ON ip.POLICY_ID = ap.POLICY_ID
      UNION ALL
      SELECT ip.INSURANCETYPE,ip.COVERAGELEVEL,tp.BENEFICIARY_ID
      FROM INSURANCEPOLICY@PostgresDB ip
        INNER JOIN TRAVELPOLICY@PostgresDB tp ON ip.POLICY_ID = tp.POLICY_ID)
  GROUP BY CUBE (INSURANCETYPE,COVERAGELEVEL)
  ORDER BY INSURANCETYPE,COVERAGELEVEL;
  
  SELECT * FROM CUBE_BENEFICIARIES_COUNT;
  -------------All cubes-------------------------
  
  --Dimensions
  SELECT  * FROM OLAP_DIM_CUST_DT_CL;
  SELECT * FROM OLAP_DIM_POLICIES_CUSTOMERS;
  SELECT * FROM OLAP_CUST_EXP_CAR;
  SELECT * FROM OLAP_CUST_IP_TD_TP;
  --Cubes
  SELECT * FROM CUBE_CARS_VALUES;
  SELECT * FROM CUBE_COUNTRY_COUNT;
  SELECT * FROM CUBE_BENEFICIARIES_COUNT;
  
  -----------------------------------------------
  
  ----------- [Facts] ---------------------------
  --- {CustName}-{CoverageLevel}-{CountryCount}-{HealthCategory}
  
  DROP VIEW OLAP_FACTS_COUNTRIES_COUNT;
  CREATE OR REPLACE VIEW OLAP_FACTS_COUNTRIES_COUNT AS
  SELECT p.NAME || ' ' || p.SURNAME AS FullName,
        ip.COVERAGELEVEL,
         COUNT(tpd.COUNTRY) AS COUNTRY_NUMBERS,
         tp.HEALTH_CATEGORY
  FROM CUSTOMER@OracleDB c
    INNER JOIN PROFILE@OracleDB p ON c.ID = p.CUSTOMER_ID
    INNER JOIN INSURANCEPOLICY@PostgresDB ip ON c.ID = ip.CUSTOMER_ID
    INNER JOIN "TravelPolicy_Destinations" tpd ON ip.POLICY_ID = tpd.POLICY_ID
    INNER JOIN TRAVELPOLICY@PostgresDB tp ON ip.POLICY_ID = tp.POLICY_ID
  GROUP BY p.NAME,p.SURNAME,ip.COVERAGELEVEL,tp.HEALTH_CATEGORY;
  
  SELECT * FROM OLAP_FACTS_COUNTRIES_COUNT;
  
  ------------------------------------------------
  
  ----------- [Analytical Views] -----------------
  --AV1: PIVOT {Count insurance type by months}
  
  DROP VIEW OLAP_VIEW_INS_TYPE_BY_MONTHS;
  CREATE OR REPLACE VIEW OLAP_VIEW_INS_TYPE_BY_MONTHS AS
  SELECT *
  FROM(SELECT extract(month from ip.STARDATE) AS MONTH,
         ip.INSURANCETYPE
    FROM CUSTOMER@OracleDB c
      INNER JOIN PROFILE@OracleDB p ON c.ID = p.CUSTOMER_ID
      INNER JOIN PROFILE@OracleDB p ON c.ID = p.CUSTOMER_ID
      INNER JOIN INSURANCEPOLICY@PostgresDB ip ON c.ID = ip.CUSTOMER_ID)
  PIVOT(COUNT(*) FOR(MONTH)
        IN (1 as January,2 as February,3 as March,4 as April,5 as May,6 as June,
          7 as July,8 as August,9 as September,10 as October,11 as November,12 as December));
  
  SELECT * FROM OLAP_VIEW_INS_TYPE_BY_MONTHS;
  
  --AV2: ROLLUP {Number of beneficiaries by insurance type and coverage level}
  
  SELECT INSURANCETYPE,COVERAGELEVEL,COUNT(BENEFICIARY_ID) AS BENEFICIARIES
  FROM(SELECT ip.INSURANCETYPE,ip.COVERAGELEVEL,ap.BENEFICIARY_ID
      FROM INSURANCEPOLICY@PostgresDB ip
        INNER JOIN AUTOPOLICY@PostgresDB ap ON ip.POLICY_ID = ap.POLICY_ID
      UNION ALL
      SELECT ip.INSURANCETYPE,ip.COVERAGELEVEL,tp.BENEFICIARY_ID
      FROM INSURANCEPOLICY@PostgresDB ip
        INNER JOIN TRAVELPOLICY@PostgresDB tp ON ip.POLICY_ID = tp.POLICY_ID)
  GROUP BY ROLLUP (INSURANCETYPE,COVERAGELEVEL)
  ORDER BY INSURANCETYPE,COVERAGELEVEL;
  
  --AV3: ROLLUP {Duration of insurance policy (in years) by full name and insurance type}
  
  SELECT FullName,INSURANCETYPE,SUM(DURATION_YEARS) DURATION_YEARS_TOTALS
  FROM  (SELECT p.NAME || ' ' || p.SURNAME AS FullName,
         INSURANCETYPE,
         round((TO_DATE(ENDDATE) - TO_DATE(STARDATE))/365,0) AS DURATION_YEARS
    FROM INSURANCEPOLICY@PostgresDB ip
      INNER JOIN  CUSTOMER@OracleDB c ON ip.CUSTOMER_ID = c.ID
      INNER JOIN PROFILE@OracleDB p ON c.ID = p.CUSTOMER_ID)
  GROUP BY ROLLUP (FullName,INSURANCETYPE)
  ORDER BY FullName,INSURANCETYPE;
  
  --AV4: ROLLUP/GROUPING FUNCTION {Number of policies by end date year, month}
  
  SELECT
         CASE WHEN GROUPING(YEAR) = 1 THEN 'TOTAL GENERAL'
         ELSE to_char(YEAR) END AS YEAR,
         CASE
          WHEN GROUPING(YEAR) = 1 THEN ' '
          WHEN GROUPING(MONTH) = 1 THEN 'TOTAL YEAR ' || YEAR
          ELSE to_char(MONTH) END AS MONTH,
          COUNT(*) AS POLICIES
  FROM(SELECT  extract(year from ENDDATE) AS YEAR,
         extract(month from ENDDATE) AS MONTH
    FROM INSURANCEPOLICY@PostgresDB)
  GROUP BY ROLLUP (YEAR, MONTH)
  ORDER BY YEAR, MONTH;
  
  --AV5: GROUP BY/UNION {IN_LEASING} - {MANUFACTURER} - {MODEL}
  
  SELECT IN_LEASING,MANUFACTURER,MODEL,SUM(VALUE) AS CARS_VALUE
  FROM VEHICLES_VIEW
  GROUP BY IN_LEASING,MANUFACTURER,MODEL
  UNION
  SELECT IN_LEASING,MANUFACTURER,NULL AS MODEL,SUM(VALUE) AS CARS_VALUE
  FROM VEHICLES_VIEW
  GROUP BY IN_LEASING,MANUFACTURER
  UNION
  SELECT IN_LEASING,NULL AS MANUFACTURER,NULL AS MODEL,SUM(VALUE) AS CARS_VALUE
  FROM VEHICLES_VIEW
  GROUP BY IN_LEASING
  UNION
  SELECT NULL AS IN_LEASING,NULL AS MANUFACTURER,NULL AS MODEL,SUM(VALUE) AS CARS_VALUE
  FROM VEHICLES_VIEW
  GROUP BY 1,2,3;
  
  --AV6: GROUP BY/HAVING
  
  SELECT MANUFACTURER,TYPE,SUM(VALUE) AS VALUE
  FROM VEHICLES_VIEW
  GROUP BY (MANUFACTURER,TYPE)
  HAVING MANUFACTURER = 'Volvo';
  
  --AV7: GROUPING SETS
  
  SELECT FullName,INSURANCETYPE,COVERAGELEVEL,SUM(DURATION_YEARS) DURATION_YEARS_TOTALS,
         GROUPING_ID(FullName,INSURANCETYPE,COVERAGELEVEL) AS GROUPING_ID
  FROM  (SELECT p.NAME || ' ' || p.SURNAME AS FullName,
         INSURANCETYPE,
          COVERAGELEVEL,
         round((TO_DATE(ENDDATE) - TO_DATE(STARDATE))/365,0) AS DURATION_YEARS
    FROM INSURANCEPOLICY@PostgresDB ip
      INNER JOIN  CUSTOMER@OracleDB c ON ip.CUSTOMER_ID = c.ID
      INNER JOIN PROFILE@OracleDB p ON c.ID = p.CUSTOMER_ID)
  GROUP BY GROUPING SETS((FullName,INSURANCETYPE), (FullName, COVERAGELEVEL),(INSURANCETYPE,COVERAGELEVEL))
  ORDER BY GROUPING_ID,FullName,INSURANCETYPE,COVERAGELEVEL;
  
  --AV8: GROUPING SETS
  
  SELECT FullName,
         EXPRIENCE,
         MANUFACTURER,
         MODEL,
         YEAR,
         SUM(VALUE) AS CAR_VALUES,
         GROUPING_ID(MANUFACTURER, MODEL,YEAR) AS GROUPING_ID,
         GROUP_ID() AS GROUP_ID
  FROM
    (SELECT p.NAME || ' ' || p.SURNAME AS FullName,
         a.EXPRIENCE,
         vv.MANUFACTURER,vv.MODEL,vv.YEAR, vv.VALUE
    FROM CUSTOMER@OracleDB c
      INNER JOIN PROFILE@OracleDB p ON c.ID = p.CUSTOMER_ID
      INNER JOIN AUTODATA@OracleDB a ON c.ID = a.CUSTOMER_ID
      INNER JOIN INSURANCEPOLICY@PostgresDB ip ON c.ID = ip.CUSTOMER_ID
      INNER JOIN VEHICLES_VIEW vv ON ip.POLICY_ID = vv.POLICYID)
  GROUP BY GROUPING SETS((FullName,EXPRIENCE), CUBE (MANUFACTURER, MODEL,YEAR))
  HAVING GROUP_ID() = 0
  ORDER BY GROUP_ID,GROUPING_ID,FullName,MANUFACTURER,MODEL,YEAR;