48011727 INFS2200 A2 Report 1.1 SELECT S2. stop_name AS station_name, SUM(T.charge) AS revenue FROM STOPS S JOIN TAPS T ON S.stop_id = T.stop_id JOIN STOPS S2 ON S.parent_station = S2.stop_id GROUP BY S2. stop_name ORDER BY revenue DESC FETCH FIRST 10 ROWS ONLY; Output STATION_NAME Central station 7587800 Varsity Lakes station 874950 Helensvale station 634700 Fortitude Valley station 622100 South Brisbane station 564100 Coomera station 520150 Redland Bay marina 473000 UQ Lakes station 411050 South Bank station 391500 Bowen Hills station 389500 10 rows selected. 1.2 REVENUE SELECT S2. stop_name AS station_name, SUM(T.charge) AS revenue FROM STOPS S JOIN TAPS T ON S.stop_id = T.stop_id JOIN CUSTOMERS C ON T. customer_id = C.customer_id JOIN STOPS S2 ON S.parent_station = S2.stop_id WHERE C. "class" = 'Concession' AND T.charge > 200 GROUP BY S2. stop_name ORDER BY revenue DESC FETCH FIRST 10 ROWS ONLY; STATION_NAME REVENUE Fortitude Valley station 546100 South Bank station 391500 Coomera station 295250 Varsity Lakes station 259350 Helensvale station 224100 Eumundi station 222200 Eight Mile Plains station 164150 UQ Lakes station 155050 King George Square bus station 80850 Upper Mt Gravatt station 58800 10 rows selected.
1.3 CREATE OR REPLACE VIEW V_STATION_BOARDINGS AS SELECT S2. stop_name AS station_name, C. "class", TO_CHAR(T. "timestamp", 'DAY') AS day_of_week, COUNT(*) AS boardings FROM STOPS S JOIN TAPS T ON S.stop_id = T.stop_id JOIN CUSTOMERS C ON T.customer_id = C.customer_id JOIN STOPS S2 ON S.parent_station = S2.stop_id WHERE T.charge = 0 GROUP BY S2. stop_name, C. "class", TO_CHAR(T. "timestamp", 'DAY'), TO_CHAR(T. "timestamp", 'D') ORDER BY S2. stop_name, TO_NUMBER(TO_CHAR(T. "timestamp", 'D')), C. "class"; TOP 11: STATION_NAME Albion station Albion station Albion station Albion station Albion station Adult Albion station Adult Albion station Alderley station Adult Alderley station Adult Alderley station Alderley station BOTTOM 11: Wooloowin station Wooloowin station Wooloowin station STATION_NAME Zillmere station Zillmere station Zillmere station Zillmere station Zillmere station Zillmere station Adult Zillmere station 590 rows selected. 1.4 class Adult Adult Adult Adult Adult Adult Adult WEDNESDAY Adult Adult Adult class Adult Adult Adult Adult Adult Adult CREATE MATERIALIZED VIEW MV_STATION_BOARDINGS AS DAY_OF_WE BOARDINGS SUNDAY 3 MONDAY 26 TUESDAY 43 WEDNESDAY 40 THURSDAY 37 FRIDAY 34 SATURDAY SUNDAY 8 2 MONDAY 27 TUESDAY 40 41 THURSDAY FRIDAY SATURDAY 43 29 12 DAY_OF_WE BOARDINGS SUNDAY MONDAY 31 4 TUESDAY 43 WEDNESDAY 37 THURSDAY 44 FRIDAY 29 SATURDAY 6 SELECT S2. stop_name AS station_name, C. "class", TO_CHAR(T. "timestamp", 'D') AS day_of_week, COUNT(*) AS boardings FROM STOPS S JOIN TAPS T ON S.stop_id = T.stop_id JOIN CUSTOMERS C ON T. customer_id = C.customer_id JOIN STOPS S2 ON S.parent_station = S2.stop_id
WHERE T.charge = 0 GROUP BY S2. stop_name, C. "class", TO_CHAR(T. "timestamp", 'DAY'), TO_CHAR(T. "timestamp", 'D') ORDER BY S2. stop_name, TO_NUMBER(TO_CHAR(T. "timestamp", 'D')), C. "class"; TOP 11: STATION_NAME class D BOARDINGS Albion station Albion station Albion station Albion station Albion station Albion station Albion station Alderley station Alderley station Alderley station Alderley station BOTTOM 11: Wooloowin station Adult 4 Wooloowin station Wooloowin station Wooloowin station STATION_NAME Adult 1 Adult 2 26 Adult 3 Adult 4