The following table shows flights from Atlanta to London, UK and Paris, France later this month. The logical data model to record information about flights (e.g., flight number, departure date and time, and destination airport), aircraft, and captains for the table is shown below. As well as the columns in the table, you need to record captains' first names, last names and hire dates.  For aircraft, record tail number, as well as manufacturer (e.g., Airbus) and model (e.g., A330-200). 

 

 

 

Flight Num	Aircraft	Tail #	Departure date	Departure time	Destination Airport	Captain
BA 226	Boeing 777-300ER	G-BNWA	2022-12-23	9:45 pm	LHR	Brown
BA 226	Boeing 767-300	G-ZZZB	2022-12-24	9:45 pm	LHR	Kregel
BA 226	Boeing 767-300ER	G-BNWA	2022-12-25	9:45 pm	LHR	Brown
AF 681	Airbus A350-900	F-HTYF	2022-12-23	4:50 pm	CDG	Halsell
VS 1044	Airbus A330-900	G-VWND	2022-12-24	6:15 pm	LHR	Wilcutt
DL 82	Airbus A330-200	N-904DE	2022-12-23	5:45 pm	CDG	Duffy
DL 82	Airbus A330-300	N-903DL	2022-12-24	5:45 pm	CDG	Mansell

 

1. Create the database designed for the logical data model. Create the table in SQL Server  and copy and paste the table creation script here.

 

2. Populate the database created in answer to question 4. Populate the table in SQL Server and copy and paste the table insertion script here. Your data should match that provided in the table above. You will need to make up data for attributes that are listed but have no data provided.

 

 

3. Write five SQL queries from the above data demonstrating the following skills. I just need to see the SQL scripts, not the results. 

 

 a. Use of the Group By Statement

 

 b. Use of multiple tables in a query 

 

 c. Use of the Having statement

 

 d. Use of the Order By statement

 

 e. Use of a subquery (IN or EXISTS)

Aircraft aircraft_tailNumber Flight aircraft manufacturer aircraft model flight_num flight departureDate flight departure Time flight_destAirport captain_id (FK) aircraft_tailNumber (FK) flies / [flown by + pilots/ piloted by L. Captain captain_id captain_firstName captain_lastName captain_hireDate