ISYS114 Introduction to Database Design and Management

Task Descriptions

The following tasks will require SQL code to be written. Test the code using your own database setup / connection. The code you submit will be run on a database residing on the ASH server (which you have been using in labs), so be sure to test your code on there before submitting. The file your group is submitting is an SQL file. The answers to the tasks should be written in SQL (you can include comments – and are expected to).

Task 1

Create all the tables detailed on the previous pages and insert the data provided to you.

Task 2

Write a query to print the ‘City’ name from the hangar location. Only include hangars with storage capacity less than 16 or above 30.

Task 3

Write a query to print the details of any aircraft (id and purchase date) that have more than 150 seats. Only include aircraft that have been purchased in either October of any
year or in any months in the year of 2014 or 2016. Sort the results by number of seats in descending order.

Task 4

Write a query to print the id of any aircraft that are of type ‘Airbus’ISYS114 2019-Assignment

Task 5

Write a query to print the details of the aircraft (id and name of the aircraft type) if the aircraft has been serviced at ‘H4’. Sort the results by the name of the aircraft type. Also
remove any duplicate results, if there are any

Task 6

Write a query to print all details of the service if the aircraft has been serviced at any hangar in NSW in the third quarter of 2019. You have to use a subquery to derive at your
answer.

Task 7

Rewrite Task 6 in join format.

Task 8

Write a query to print all the ids of all Airbus 220 or Boeing 737 aircrafts that are to be serviced on 20th of December, 2019 at a hangar with a storage capacity of either less than
10 or over 20 by a team whose seniority level is 5. Sort the results by id of the aircraft in descending order

Task 9

Write a query to print the total number of aircraft that have been serviced. Only include aircraft that were purchased after 2017 or if the seating capacity is not equal to 104.

Task 10

Write a query to print the team number, their seniority level and number of services they have performed. Only include teams that have a seniority level of either 1 or 3 and have less than 4 services performed. Sort the results by the number of services in descending order. Please note that if a particular team has not serviced at all, you have to include them in the results as well.

Task 11

Write a query to print the aircraft ids and the number of times they have been serviced, if they have more than average number of services.