Sample SQL Statements - Aviation Database

Aviation Reports and Queries AirLine Reports and Queries Fee Based Services Database Description

Sample SQL Statements

If you are looking for a non-technical way to generate reports against the database, see our free reports and queries.

This page contains a variety of SQL statements that can be made against the database. They illustrate some of the types of information that can be extracted. Most of them are "Data Mining" type queries, but two, Aircraft and one of the Service Difficulty Report (SDR) queries, are examples of transactional type query. Obviously, this is just a small sample of the types of queries that can be made.

Feel free to try them.

NTSB Accident related SQL statements
FAA Accident/Incident related SQL statements
Shows how to use the NARRATIVE_WORDS table to efficiently search the NARRATIVES table by keyword
Aircraft related SQL statements
Service Difficulty Report (SDR) related SQL statements
Airman related SQL statements
Airport related SQL statements

NTSB Accident SQL Statements top

Select the reasons for accidents that have occurred during a missed approach. The phase of flight values can be found by querying the PHASE_OF_FLIGHT_CODES table.

  SELECT DESCRIPTION, C.OCCURRENCE_CODE, COUNT(*)
  FROM OCCURRENCES O, OCCURRENCE_CODES C
  WHERE O.OCCURRENCE_CODE = C.OCCURRENCE_CODE 
    AND PHASE_OF_FLIGHT = '569'
  GROUP BY DESCRIPTION, C.OCCURRENCE_CODE

This query costs about $0.01 to run.

Using the above query, select the NARRATIVES for Accidents that occurred during a missed approach due to "AIRFRAME/COMPONENT/SYSTEM FAILURE/MALFUNCTION (incld inflt brkup)".

  SELECT N.EV_ID, N.NARRATIVE_TYPE, N.TEXT
  FROM NARRATIVES N, OCCURRENCES O, OCCURRENCE_CODES C
  WHERE N.EV_ID = O.EV_ID
    AND O.OCCURRENCE_CODE = C.OCCURRENCE_CODE 
    AND PHASE_OF_FLIGHT = '569'
    AND C.OCCURRENCE_CODE = '130'
  ORDER BY 1, 2

This query costs about $0.01 to run.

Using the above query, show some event, aircraft information, and engine information.

  SELECT V.EV_ID, V.EV_DATE, 
    (V.INJ_TOT_F + V.INJ_TOT_M + V.INJ_TOT_S) AS INJURIES, 
    A.ACFT_MAKE, A.ACFT_MODEL, E.ENG_TYPE, E.ENG_MFGR, E.ENG_MODEL
  FROM EVENTS V, AIRCRAFT A, ENGINES E, OCCURRENCES O, OCCURRENCE_CODES C
  WHERE V.EV_ID = A.EV_ID
    AND A.EV_ID = E.EV_ID
    AND A.AIRCRAFT_KEY = E.AIRCRAFT_KEY
    AND V.EV_ID = O.EV_ID
    AND O.OCCURRENCE_CODE = C.OCCURRENCE_CODE 
    AND PHASE_OF_FLIGHT = '569'
    AND C.OCCURRENCE_CODE = '130'
  ORDER BY 2

This query costs about $0.01 to run.

FAA Accident/Incident SQL Statements top

Select the causes of accidents, most common first. This query will take about 45 seconds to complete.

  SELECT COALESCE(C.LONG_DESCRIPTION,'NOT SPECIFIED'), A.COUNT
  FROM (SELECT GENERAL_CAUSE_CATEGORY_CODE, COUNT(*) AS COUNT
        FROM AID_INCIDENT
        GROUP BY GENERAL_CAUSE_CATEGORY_CODE) AS A 
        LEFT JOIN AID_GENERAL_CAUSE_CAT_CODES C ON
            C.GENERAL_CAUSE_CAT_CODE = A.GENERAL_CAUSE_CATEGORY_CODE
  ORDER BY 2 DESC

This query costs about $0.20 to run.

Key Word Searching top

These examples show how to select Accident related information using the NARRATIVE_WORDS keyword table.

Select all narrative text that contains the keyword HANDPROP

  SELECT N.EV_ID, N.TEXT
  FROM NARRATIVES N, NARRATIVE_KEYWORDS W, KEYWORDS K
  WHERE N.KEY = W.NARRATIVE_KEY
    AND W.KEYWORD_KEY = K.KEY
    AND K.KEYWORD = 'HANDPROP'
  ORDER BY 1

This query costs about $0.01 to run.

Select all narrative text that contains a string starting with HANDPROP. Note that if the string starts with the variable part of the selection, the query will take quite awhile as it will not use an index.

  SELECT DISTINCT N.EV_ID, N.TEXT
  FROM NARRATIVES N, NARRATIVE_KEYWORDS W, KEYWORDS K
  WHERE N.KEY = W.NARRATIVE_KEY
    AND W.KEYWORD_KEY = K.KEY
    AND K.KEYWORD LIKE 'HANDPROP%'
  ORDER BY 1

This query costs about $0.01 to run.

Select all narrative text that contains a string starting with HANDPROP and also the keyword PASSENGER.

  SELECT N.EV_ID, TEXT 
  FROM NARRATIVES N, NARRATIVE_KEYWORDS W1, KEYWORDS K1 
  WHERE N.KEY = W1.NARRATIVE_KEY 
    AND W1.KEYWORD_KEY = K1.KEY 
    AND K1.KEYWORD LIKE 'HANDPROP%' 
    AND EXISTS 
    (SELECT W2.NARRATIVE_KEY 
     FROM NARRATIVE_KEYWORDS W2, KEYWORDS K2 
     WHERE W1.NARRATIVE_KEY = W2.NARRATIVE_KEY 
       AND W2.KEYWORD_KEY = K2.KEY 
       AND K2.KEYWORD = 'PASSENGER') 
  ORDER BY 1

This query costs about $0.01 to run.

Select all narrative text that contains a string starting with HANDPROP and not the keyword PASSENGER.

  SELECT N.EV_ID, TEXT 
  FROM NARRATIVES N, NARRATIVE_KEYWORDS W1, KEYWORDS K1 
  WHERE N.KEY = W1.NARRATIVE_KEY 
    AND W1.KEYWORD_KEY = K1.KEY 
    AND K1.KEYWORD LIKE 'HANDPROP%' 
    AND NOT EXISTS 
    (SELECT W2.NARRATIVE_KEY 
     FROM NARRATIVE_KEYWORDS W2, KEYWORDS K2 
     WHERE W1.NARRATIVE_KEY = W2.NARRATIVE_KEY 
       AND W2.KEYWORD_KEY = K2.KEY 
       AND K2.KEYWORD = 'PASSENGER') 
  ORDER BY 1

This query costs about $0.01 to run. Aircraft SQL Statements top

Get all the information available from the Aircraft subject area for N Number N10560. The outer join is to allow for the possibility of no engine or aircraft information for the N Number being queried. Note that the "N_NUMBER" column does not include the "N".

  SELECT * 
  FROM MASTER M LEFT JOIN ACFTREF A ON M.AIRCRAFT_MFR_MODEL = A.MANUFACTURER_MODEL_SERIES 
                LEFT JOIN ENGINE E ON M.ENGINE_MFR_MODEL = E.ENGINE_CODE
  WHERE M.N_NUMBER = '10560'

This query costs about $0.01 to run.

Find the number of rotor craft by manufacturer and registered in the US which are not amateur built. Since the manufacturers' names are not consistently spelled, only the first 4 characters of the name are used. The TYPE_AIRCRAFT and AMATEUR_CERTIFICATION code values come from the manuals. This query will take about 20 seconds to execute.

  SELECT SUBSTR(MANUFACTURER_NAME,1, 4) AS MANUFACTURER, COUNT(*) 
  FROM MASTER M, ACFTREF A
  WHERE M.AIRCRAFT_MFR_MODEL = A.MANUFACTURER_MODEL_SERIES
    AND TYPE_AIRCRAFT = '6'
    AND AMATEUR_CERTIFICATION = '0'
  GROUP BY SUBSTR(MANUFACTURER_NAME,1, 4)
  HAVING COUNT(*) > 5
  ORDER BY 2 DESC

This query costs about $0.10 to run.

This query is a modification to the previous one in that it also groups by the manufacturer's model. As the model names are also not consistent, any "-"(s) are removed. Additionally the data is displayed as a spreadsheet rather than HTML.

  SELECT SUBSTR(MANUFACTURER_NAME,1, 4) AS MANUFACTURER,
    REPLACE(MODEL_NAME,'-','') AS MODEL, COUNT(*) 
  FROM MASTER M, ACFTREF A
  WHERE M.AIRCRAFT_MFR_MODEL = A.MANUFACTURER_MODEL_SERIES
    AND TYPE_AIRCRAFT = '6'
    AND AMATEUR_CERTIFICATION = '0'
  GROUP BY SUBSTR(MANUFACTURER_NAME,1, 4), REPLACE(MODEL_NAME,'-','')
  HAVING COUNT(*) > 5
  ORDER BY 3 DESC

This query costs about $0.10 to run.

Service Difficulty Report SQL Statements top

Find all the JASC codes which codify the types of service difficulties.

  SELECT * FROM JASC_CODES
  ORDER BY 1

This query costs about $0.11 to run.

Find all the SDR's for Cessna 310 models that have had troubles with the AC Inverter. All relevant information is returned. Note that even though 1 report is found, 2 rows are returned, one for each condition.

  SELECT S.*, R.REMARKS, A.LONG_DESCRIPTION AS ALERT, 
    SG.LONG_DESCRIPTION AS SEGMENT, SE.LONG_DESCRIPTION AS SEVERITY, 
    ST.LONG_DESCRIPTION AS STAGE, PC.LONG_DESCRIPTION AS PROCEDURE, 
    CC.LONG_DESCRIPTION AS CONDITION  
  FROM SDR S 
           JOIN JASC_CODES JC ON S.AIR_TRANSPORT_ASSOCIATION_CODE = JC.JASC_CODE
           LEFT JOIN SDR_REMARKS R ON S.DATE_OF_REPORT = R.DATE_OF_REPORT
                                  AND S.UNIQUE_SEQUENTIAL_NUMBER = R.UNIQUE_SEQUENTIAL_NUMBER
           LEFT JOIN SDR_PROCEDURES SP ON S.DATE_OF_REPORT = SP.DATE_OF_REPORT
                                      AND S.UNIQUE_SEQUENTIAL_NUMBER = SP.UNIQUE_SEQUENTIAL_NUMBER
           LEFT JOIN PROCEDURE_CODES PC ON SP.PRECAUTIONARY_PROCEDURES_CODE = PC.PROCEDURE_CODE 
           LEFT JOIN SDR_CONDITIONS SDC ON S.DATE_OF_REPORT = SDC.DATE_OF_REPORT
                                       AND S.UNIQUE_SEQUENTIAL_NUMBER = SDC.UNIQUE_SEQUENTIAL_NUMBER
           LEFT JOIN CONDITION_CODES CC ON SDC.NATURE_OF_CONDITION_CODE = CC.CONDITION_CODE 
           LEFT JOIN ALERT_CODES A ON S.ALERT_CODE = A.ALERT_CODE
           LEFT JOIN SEGMENT_CODES SG ON S.SEGMENT_CODE = SG.SEGMENT_CODE
           LEFT JOIN SEVERITY_CODES SE ON S.SEVERITY_FACTOR_CODE = SE.SEVERITY_CODE
           LEFT JOIN STAGE_CODES ST ON S.STAGE_OF_OPERATION_CODE = ST.STAGE_CODE
  WHERE S.AIRCRAFT_MANUFACTURER_NAME = 'CESSNA'
    AND S.AIRCRAFT_GROUP_CODE = '310'
    AND JC.SHORT_DESCRIPTION = 'AC Inverter';

This query costs about $0.01 to run.

Select all SDR's filed for the aircraft with N Number '6193X'. Note the predicates on columns AIRCRAFT_MANUFACTURER_NAME and AIRCRAFT_GROUP_CODE to allow efficient use of an existing index.

  SELECT S.*, JC.LONG_DESCRIPTION AS DIFFICULTY, R.REMARKS,
    A.LONG_DESCRIPTION AS ALERT, SG.LONG_DESCRIPTION AS SEGMENT,
    SE.LONG_DESCRIPTION AS SEVERITY, ST.LONG_DESCRIPTION AS STAGE,
    PC.LONG_DESCRIPTION AS PROCEDURE,CC.LONG_DESCRIPTION AS CONDITION 
  FROM SDR S 
           LEFT JOIN JASC_CODES JC ON S.AIR_TRANSPORT_ASSOCIATION_CODE = JC.JASC_CODE
           LEFT JOIN SDR_REMARKS R ON S.DATE_OF_REPORT = R.DATE_OF_REPORT
                                  AND S.UNIQUE_SEQUENTIAL_NUMBER = R.UNIQUE_SEQUENTIAL_NUMBER
           LEFT JOIN SDR_PROCEDURES SP ON S.DATE_OF_REPORT = SP.DATE_OF_REPORT
                                      AND S.UNIQUE_SEQUENTIAL_NUMBER = SP.UNIQUE_SEQUENTIAL_NUMBER
           LEFT JOIN PROCEDURE_CODES PC ON SP.PRECAUTIONARY_PROCEDURES_CODE = PC.PROCEDURE_CODE 
           LEFT JOIN SDR_CONDITIONS SDC ON S.DATE_OF_REPORT = SDC.DATE_OF_REPORT
                                       AND S.UNIQUE_SEQUENTIAL_NUMBER = SDC.UNIQUE_SEQUENTIAL_NUMBER
           LEFT JOIN CONDITION_CODES CC ON SDC.NATURE_OF_CONDITION_CODE = CC.CONDITION_CODE 
           LEFT JOIN ALERT_CODES A ON S.ALERT_CODE = A.ALERT_CODE
           LEFT JOIN SEGMENT_CODES SG ON S.SEGMENT_CODE = SG.SEGMENT_CODE
           LEFT JOIN SEVERITY_CODES SE ON S.SEVERITY_FACTOR_CODE = SE.SEVERITY_CODE
           LEFT JOIN STAGE_CODES ST ON S.STAGE_OF_OPERATION_CODE = ST.STAGE_CODE
  WHERE S.AIRCRAFT_MANUFACTURER_NAME = 'CESSNA'
    AND S.AIRCRAFT_GROUP_CODE = '310'
	  AND S.AIRCRAFT_REGISTRATION = '6193X'
  ORDER BY 1, 2

This query costs about $0.01 to run.

Airman SQL Statements top

Show the number of Glider instructors by country and state that have a current instructor's license. One could also select their names, and addresses. The description of the values of CERTIFICATE_TYPE and RATING can be found by querying the CERTIFICATE_TYPES and CERTIFICATE_RATINGS tables. The "COALESCE" function is because the COUNTRY_NAME column is not populated if the country is UNITED STATES. This query will take about 70 seconds to complete.

  SELECT COALESCE(A.COUNTRY_NAME,'UNITED STATES'), A.STATE, 
    COUNT(DISTINCT A.KEY) AS INSTRUCTORS
  FROM AIRMAN A, AIRMAN_RATING R
  WHERE A.KEY = R.AIRMAN_KEY
    AND R.CERTIFICATE_TYPE = 'F'
    AND R.RATING LIKE 'GL%'
    AND R.CERTIFICATE_EXPIRE_DATE > CURRENT_DATE
  GROUP BY COALESCE(A.COUNTRY_NAME,'UNITED STATES'), A.STATE
  ORDER BY 1, 2

This query costs about $0.25 to run.

Airport SQL Statements top

Select all the airports in California that have one or more runways with a length greater than 6000 feet.

  SELECT DISTINCT OFFICIAL_FACILITY_NAME, ASSOCIATED_CITY, ASSOCIATED_COUNTY
  FROM AIRPORT_FACILITY_DATA A, FACILITY_RUNWAY_DATA R
  WHERE R.AIRPORT_IDENTIFIER = A.LANDING_FACILITY_SITE_NUMBER  
    AND A.ASSOCIATED_STATE = 'CA'
    AND R.RUNWAY_LENGTH > 6000
  ORDER BY 3, 2, 1

This query costs about $0.01 to run.

Legal