ITM 315 Fall 2017 Assignment 5 Chapter 10 & Overall SQL
Due Friday, December 1, 3pm XX points possible
Create and save the following queries in the provided database: airline chapter 10. Rename the database as airline chapter10lastname and submit via blackboard by the due date/time. As on past assignments, name the queries Query 1 through Query XX. While some of the queries come from Chapter 10, others are from previous chapters covered earlier in the semester.
Rename this file ch10gradesheet_lastname and submit this via blackboard. Unlike previous assignments, there will be no diagrams to include in the word document.
Query1: Add a manufacturer table to the database with the following columns: manufacturer_name, CEO first name, CEO last name, Headquarters city. Make sure to designate the primary key.
Query 2: Add a column to the appropriate table to record the state the manufacturer is in.
Query 3: change the name of FAAcontact Sue Battles to Susan Jones.
query 4: delete AID 146-200 from the database.
query 5: Add the following aircraft to the database: AID 1578, manufacturer Westwind, cruising range 32000m comment: freighter and reliability score 7.5.
Query 6: outerjoin
Create a query that shows all of the employees and any certifications for those employees. The result should include all employees, even those employees that do not have any certifications. Show the eid, emplast, empfirst and aid in the result.
Query 7. How many trouble reports has each FAA contact (by name) handled? List the number of reports for any FAA contact that has handled less than 4 reports.
Query 8: What is the average cruising range for each aircraft manufacturer?
Query 9: Which employees have a last name starting with the letter B?
Query 10: Which FAA contact(s) (by name) have not been associated with trouble reports?
Query 11: List the names of the FAA Contacts along with the name of each person's supervisor.
Query 12: Which aircraft (by AID) has the highest reliability score?
Do this query last!!!! Query 13: drop the comments column from the aircraft table.
Assignment 5 grade sheet Fall 2017
YES NO Points
Grade sheet (0.5 pts) ____ ____ _____
Appropriately named files (1 pts) ____ ____ _____
Queries points total
Query 1 ____ ____ _____
Query 2 ____ ____ _____
Query 3 ____ ____ _____
Query 4 ____ ____ _____
Query 5 ____ ____ _____
Query 6 ____ ____ _____
Query 7 ____ ____ _____
Query 8 ____ ____ _____
Query 9 ____ ____ _____
Query 10 ____ ____ _____
Query 11 ____ ____ _____
Query 12 ____ ____ _____
Query 13 ____ ____ _____