Task: You are to write five queries on a flight incident database.
The database you will deal with is from the Air Traffic Administration (DATA) of the Bureau of Air and Space Events (BASE). The database contains information about aircraft accidents and incidents. Each of these events is characterised by a unique incremental id, the type of event (either accident or incident), the date and local time it happened, the precise latitude and longitude where the event took place, the level of experience of the pilot (measured in flight hours), the aircraft manufacturer and model (e.g. Boeing 777), the code of the airline operator to which the affected aircraft belongs, and an identifier of the airport where the event took place (if at an airport). Additionally to this identifier, airports are described by a name, city and country.
For a better understanding of the possible causes of an event, the number of engines of an aircraft must be available, as well as the aforementioned manufacturer and model. Airline operators are identified by a code, and also have a name. They also have information about their starting date of operation and the number of people they employ.
Finally, each operator has a number of key people. Each of them has a name, a last name, one or more positions within the company (CEO, President, Chief Pilot, etc.) and a city where he/she is based.
event(id:integer, type:text, date:date, local_time:text, latitude:numeric, longitude:numeric, pilot_hours:integer, aircraft_manufacturer:text, aircraft_model:text, operator_code:text, airport_id:text)
aircraft(manufacturer:text, model:text, no_engines: integer)
operator(code:text, name:text, issue_date:text, no_employees:integer)
person(id:integer, firstname:text, lastname:text, city:text, operator_code:text)
airport(id:integer, name:text, city:text, county:text) position(id:integer, description:text)
event(aircraft_manufacturer, aircraft_model) –> aircraft(manufacturer, model)
event(airport_id) –> airport(id)
event(operator_code) –> operator(code)
person(operator_code) –> operator(code)
person(id) person_positon(position_id) –> position(id)
a. Count all events that happened between 1.1.2005 and 1.1.2006.
b. Count all events for operators whose name contains an “S”.
c. Count all events for operators whose owner’s name contains a “T”.
d. Count all events where the pilot’s flight hours are between 1000 and 2000 of hours.
e. Count all events that happened to an aircraft coming from an airport located in Greece.