The following tables represent some of the data which the Fire Department tracks for reporting on emergency incidents.
INC - Incident table (Gives information about an emergency incident.)
INC_IDY (Unique incident number) SIT* (Situation Type) ADATE (Alarm Date) ATIME (Alarm Time) FIRST_DUE_STN# (The station district in which the incident occurred)
9000188 TAT 1/3/2009 14:20:22 ST05
9000770 TA 1/11/2009 17:25:31 ST02
9002128 MED 1/30/2009 8:39:15 ST11
9002322 SFIR 2/1/2009 15:55:25 ST01
9002516 MED 2/3/2009 13:19:16 ST08
* TA = Traffic accident, TAT = Traffic accident, person trapped, MED = Medical emergency, and SFIR = Structure fire.
# ST01 = Fire station 1, ST02 = Fire station 2, etc.
CMP_INC_RSP - Company response table (Gives information as to the units dispatched to an incident.)
INC_IDY (Unique incident number) CMP_IDY$ (Unit dispatched) DSP_TME (Time unit dispatched) RSP_TME (Time unit responded) ARR_TME (Time unit arrived) INS_TME (Time unit is available for another assignment)
9000188 E3 14:20:43 14:22:06 14:23:19 14:28:39
9000188 T1 14:20:43 14:22:38 14:24:54
9000770 E2 17:25:49 17:26:47 17:28:03 18:10:16
9000770 T8 17:25:49 17:26:55 17:30:25 18:08:24
9002128 E11 8:39:38 8:40:35 8:43:07 8:53:08
9002322 E1 15:56:56 15:57:09 15:59:04 16:05:48
9002322 E2 15:56:56 15:57:25 16:00:04 16:06:30
9002322 T1 15:56:56 15:57:15 15:59:49 16:01:21
9002516 E8 13:19:31 13:19:40 13:19:42 13:37:50
$ E1 = Engine at station 1, T1 = Truck at station 1, E2 = Engine at station 2, etc.
Using the table and column data given above as a guide, provide SQL queries that you would execute against the full data set - approximately 50,000 incidents per year - for the following questions:
a) To how many traffic accidents did the Fire Department send two or more companies in 2009?
b) For each incident, what is the first arriving company?
c) What is the delay between the time the alarm was received and the time each unit arrived at the scene?
d) How many times has each unit responded to incidents outside of their primary running district? (The FIRST_DUE_STN in the INC table indicates in which station's primary running district the incident occurred.)