text:                                                     --FIT5195 ASSIGNMENT 1 PART B
                                                      --ACCIDENT RECORDS CASE STUDY
                                                      --MADE BY SOVAN SINHA ROY (STUDENT ID: 30201152)
  
  --TASK 3
  
  SELECT * FROM ACCIDENT.ACCIDENT;
  SELECT * FROM ACCIDENT.OWNERS;
  SELECT * FROM ACCIDENT.DRIVER;
  SELECT * FROM ACCIDENT.VEHICLE;
  SELECT * FROM ACCIDENT.VEHICLE_DRIVER;
  SELECT * FROM ACCIDENT.LICENCE;
  SELECT * FROM ACCIDENT.ACCIDENT_RECORD;
  SELECT * FROM ACCIDENT.POLICE_OFFICER;
  
  --DESC ACCIDENT.ACCIDENT;
  --DESC ACCIDENT.VEHICLE;
  
  
  --CREATION OF ALL THE DIMENSIONS
  
  CREATE TABLE ACCIDENTDIM AS SELECT A.ACCIDENT_NO, A.ACCIDENT_EVENT, 
  1/COUNT(AR.VEHICLE_NO) AS WEIGHTFACTOR,
  LISTAGG(AR.VEHICLE_NO, ',') WITHIN GROUP (ORDER BY AR.VEHICLE_NO) AS VEHICLEGROUPLIST
  FROM ACCIDENT.ACCIDENT A, ACCIDENT.ACCIDENT_RECORD AR
  WHERE A.ACCIDENT_NO = AR.ACCIDENT_NO
  GROUP BY A.ACCIDENT_NO, A.ACCIDENT_EVENT;
  
  SELECT * FROM ACCIDENTDIM;
   
  --DROP TABLE ACCIDENTDIM;
  
  
  CREATE TABLE LOCATIONDIM AS SELECT DISTINCT ACCIDENT_LOCATION_CODE, ACCIDENT_SUBURB FROM ACCIDENT.ACCIDENT;
  SELECT * FROM LOCATIONDIM;
  
  
  
  CREATE TABLE LIGHTINGPERIODDIM AS SELECT DISTINCT TO_CHAR(ACCIDENT_DATE_TIME, 'HH24:MI') AS ACCIDENT_TIME FROM ACCIDENT.ACCIDENT;
  
  ALTER TABLE LIGHTINGPERIODDIM ADD
  LIGHTING_PERIOD_TYPE VARCHAR(15);
  
  UPDATE LIGHTINGPERIODDIM SET
  LIGHTING_PERIOD_TYPE = 'DAYTIME'
  WHERE ACCIDENT_TIME >= '06:00' AND ACCIDENT_TIME <= '17:59';
  UPDATE LIGHTINGPERIODDIM SET
  LIGHTING_PERIOD_TYPE = 'NIGHTTIME'
  WHERE ACCIDENT_TIME >= '18:00' OR ACCIDENT_TIME <= '05:59';
  
  SELECT * FROM LIGHTINGPERIODDIM;
  
  
  CREATE TABLE VEHICLEDIM AS SELECT VEHICLE_NO, VEHICLE_MODEL, VEHICLE_YEAR 
  FROM ACCIDENT.VEHICLE
  GROUP BY VEHICLE_NO, VEHICLE_MODEL, VEHICLE_YEAR;
  
  SELECT * FROM VEHICLEDIM;
  
  
  CREATE TABLE ACCIDENTVEHICLEBRIDGE AS SELECT * FROM ACCIDENT.ACCIDENT_RECORD;
  
  SELECT * FROM ACCIDENTVEHICLEBRIDGE;
  
  
  
  CREATE TABLE POLOFFRBRANCHDIM AS SELECT DISTINCT OFFICER_BRANCH FROM ACCIDENT.POLICE_OFFICER;
  
  SELECT * FROM POLOFFRBRANCHDIM;
  
  
  
  --THE TEMP FACT TABLE
  
  CREATE TABLE TEMP_FACT_AR AS SELECT A.ACCIDENT_LOCATION_CODE, A.ACCIDENT_NO,
  TO_CHAR(A.ACCIDENT_DATE_TIME, 'HH24:MI') AS ACCIDENT_TIME, P.OFFICER_BRANCH
  FROM ACCIDENT.ACCIDENT A, ACCIDENT.ACCIDENT_RECORD AR, ACCIDENT.POLICE_OFFICER P 
  WHERE A. ACCIDENT_NO = AR.ACCIDENT_NO
  AND P.OFFICER_ID = A.OFFICER_ID
  GROUP BY A.ACCIDENT_LOCATION_CODE, A.ACCIDENT_NO, TO_CHAR(A.ACCIDENT_DATE_TIME, 'HH24:MI'), P.OFFICER_BRANCH;
  
  ALTER TABLE TEMP_FACT_AR ADD
  LIGHTING_PERIOD_TYPE VARCHAR(15);
  
  UPDATE TEMP_FACT_AR SET
  LIGHTING_PERIOD_TYPE = 'DAYTIME'
  WHERE ACCIDENT_TIME >= '06:00' AND ACCIDENT_TIME <= '17:59';
  UPDATE TEMP_FACT_AR SET
  LIGHTING_PERIOD_TYPE = 'NIGHTTIME'
  WHERE ACCIDENT_TIME >= '18:00' OR ACCIDENT_TIME <= '05:59';
  
  SELECT * FROM TEMP_FACT_AR;
  
  --DROP TABLE TEMP_FACT_AR;
  
  
  
  --THE FACT TABLE
  
  CREATE TABLE ACCIDENTRECORDFACT AS SELECT A.ACCIDENT_LOCATION_CODE AS "LOCATION CODE",
  A.ACCIDENT_NO AS "ACCIDENT NUMBER", T.LIGHTING_PERIOD_TYPE AS "LIGHTING PERIOD TYPE",
  P.OFFICER_BRANCH AS "OFFICER BRANCH",
  COUNT(AR.ACCIDENT_NO) AS "TOTAL ACCIDENTS",
  COUNT(AR.VEHICLE_NO) AS "TOTAL VEHICLES"
  FROM ACCIDENT.ACCIDENT A, ACCIDENT.ACCIDENT_RECORD AR, TEMP_FACT_AR T, ACCIDENT.POLICE_OFFICER P
  WHERE A.ACCIDENT_NO = AR.ACCIDENT_NO AND AR.ACCIDENT_NO = T.ACCIDENT_NO AND
  P.OFFICER_ID = A.OFFICER_ID
  GROUP BY A.ACCIDENT_LOCATION_CODE, A. ACCIDENT_NO, T.LIGHTING_PERIOD_TYPE, P.OFFICER_BRANCH;
  
  SELECT * FROM ACCIDENTRECORDFACT;
  
  --DROP TABLE ACCIDENTRECORDFACT;
  
  
  
  --TASK 4
  
  --Q1)
  
  SELECT SUM("TOTAL ACCIDENTS") AS "TOTAL ACCIDENTS", "LOCATION CODE", "LIGHTING PERIOD TYPE" FROM ACCIDENTRECORDFACT
  GROUP BY "LOCATION CODE", "LIGHTING PERIOD TYPE" ORDER BY "TOTAL ACCIDENTS" DESC;
  
  --Q2)
  
  SELECT VEHICLE_MODEL, SUM("TOTAL ACCIDENTS") AS "TOTAL ACCIDENTS" FROM ACCIDENTRECORDFACT A, ACCIDENTVEHICLEBRIDGE AV, VEHICLEDIM V
  WHERE A."ACCIDENT NUMBER" = AV.ACCIDENT_NO AND AV.VEHICLE_NO = V.VEHICLE_NO
  GROUP BY VEHICLE_MODEL ORDER BY "TOTAL ACCIDENTS" DESC;
  
  
  --Q3)
  
  SELECT "LOCATION CODE", ACCIDENT_EVENT AS "ACCIDENT EVENT", SUM("TOTAL VEHICLES") AS "NUMBER OF VEHICLES INVOLVED"
  FROM ACCIDENTRECORDFACT AR, ACCIDENTDIM A WHERE AR."ACCIDENT NUMBER" = A.ACCIDENT_NO
  GROUP BY "LOCATION CODE", ACCIDENT_EVENT ORDER BY "NUMBER OF VEHICLES INVOLVED" DESC;
  
  --Q4)
  
  SELECT "OFFICER BRANCH", SUM("TOTAL ACCIDENTS") AS "TOTAL ACCIDENTS" FROM ACCIDENTRECORDFACT A
  GROUP BY "OFFICER BRANCH" ORDER BY "TOTAL ACCIDENTS";
  
  
  -- TASK 5
  
  --Q1)
  
  SELECT "LIGHTING PERIOD TYPE", SUM("TOTAL VEHICLES") AS "NUMBER OF VEHICLES INVOLVED" FROM ACCIDENTRECORDFACT 
  GROUP BY "LIGHTING PERIOD TYPE" ORDER BY "NUMBER OF VEHICLES INVOLVED" DESC;
  
  --Q2)
  
  SELECT A.ACCIDENT_EVENT AS "ACCIDENT EVENT", SUM("TOTAL ACCIDENTS") AS "NUMBER OF ACCIDENTS DURING NIGHTTIME" 
  FROM ACCIDENTRECORDFACT AR, ACCIDENTDIM A WHERE AR."ACCIDENT NUMBER" = A.ACCIDENT_NO AND
  "LIGHTING PERIOD TYPE" = 'NIGHTTIME'
  GROUP BY ACCIDENT_EVENT ORDER BY "NUMBER OF ACCIDENTS DURING NIGHTTIME"  DESC;
  
  
  