The following table shows flights from Atlanta to London, UK and Paris, France later this month. The
Question:
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)
Database Systems Design Implementation and Management
ISBN: 978-1337627900
13th edition
Authors: Carlos Coronel, Steven Morris