Question

The following tables form part of a database (Flights Database) held in a relational DBMS: employee (empNo, empName, empSalary, empPosition) aircraft (aircraftNo, acName, acModel, acFlyingRange) flight (flightNo, aircraftNo, fromAirport, toAirport, flightDistance, departTime, arriveTime) certified (empNo, aircraftNo) Where:  employee contains details of all employees (pilots and non-pilots) and empNo is the primary key;  aircraft contains details of aircraft and C is the primary key.  flight contains details of flights and (flightNo, aircraftNo) form the primary key.  certified contains details of the staff who are certified to fly an aircraft and (empNo, aircraftNo) form the primary key. Create tables (12 points)

1. Using the CREATE TABLE statement, create each of the above tables (create primary keys and foreign keys, where appropriate). (Already Done)

Now answer the questions below using SQL.

List the pilot number and names of all pilots who have executed flights where the distance covered is greater than 1000 Km. List the aircrafts who have never executed a flight to the destination ‘London’. List the flights where the flight distance equals to the flying range of the aircraft who executed it?

1. List the number of flights with each aircraft. 2. List the number of flights with each Boeing aircraft. 3. What is the number of pilots certified for each type of aircraft? 4. What are the most commonly destined airports? 5. What is the number of aircrafts certified for each pilot?

Answer 1

Please do up vote thank you