/*

Student ID: Name
Student ID: Name
Student ID: Name
Student ID: Name
Student ID: Name

Class Number:
Group Number:
Tutor's Name:

*/




/******* TASK 1: AFTER THIS COMMENT, INSERT YOUR CODE TO CREATE & POPULATE THE FIVE TABLES BELOW (PUT THEM IN THE CORRECT ORDER OF EXECUTION) ***********/

-- Create table aircrafttype
create table aircrafttype(
aircrafttypeid char(2), -- A unique	identifier for the type of aircraft
aircrafttypename varchar(20), -- The name of the type of aircraft
primary key(aircrafttypeid)); -- Setting aircrafttypeid as the primary key

-- Insert statements for aircrafttype
Insert into aircrafttype values ('22','Airbus 220');
Insert into aircrafttype values ('31','Airbus 310');
Insert into aircrafttype values ('B3','Boeing 737');
Insert into aircrafttype values ('B6','Boeing 767');
Insert into aircrafttype values ('B7','Boeing 777');
Insert into aircrafttype values ('B8','Boeing 787');

-- Create table aircraft
create table aircraft(
aircraftid char(2), -- A unique	identifier for the aircraft
aircraftpurdate date, -- The date of purchase of an	aircraft
aircraftseatcap numeric(3), -- Seating capacity of an aircraft
aircrafttypeid char(2), -- The id of the type of aircraft [FK] referencing aircrafttypeid in aircrafttype table; refers	to the type	of aircraft
primary key (aircraftid), -- Setting aircraftid as the primary key
foreign key (aircrafttypeid) references aircrafttype(aircrafttypeid)); -- Setting aircrafttypeid as the foreign key from the table aircrafttype

-- Insert statements for aircraft
Insert into aircraft values ('A1','2012-06-19','140','B3');
Insert into aircraft values ('A2','2013-08-19','129','B6');
Insert into aircraft values ('A3','2013-05-19','104','B3');
Insert into aircraft values ('A4','2017-04-19','296','B7');
Insert into aircraft values ('A5','2018-03-19','120','B6');
Insert into aircraft values ('A6','2014-10-19','191','31');
Insert into aircraft values ('A7','2015-10-19','198','31');
Insert into aircraft values ('A8','2016-12-19','204','22');
Insert into aircraft values ('A9','2017-01-19','173','22');

-- Create table hangar
create table hangar(
hangarid char(2), -- A unique identifier for the hangar
hangarlocation varchar(20), -- The location of the hangar
hangarstoragecap numeric(2), -- The storage capacity of the hangar
primary key(hangarid)); -- Setting hangarid as the primary key

-- Insert statements for hangar
Insert into hangar values ('H1','Sydney, NSW','7');
Insert into hangar values ('H2','Melbourne, VIC','22');
Insert into hangar values ('H3','Sydney, NSW','25');
Insert into hangar values ('H4','Brisbane, QLD','8');
Insert into hangar values ('H5','Launceston, TAS','14');

-- Create table service
create table service(
serviceid char(3), -- A	unique identifier for the service
servicedate date, -- The date for a service
hangarid char(2), -- The id of the hangar [FK] referencing hangarid in hangar table; refers to the hangar the service happens at
aircraftid char(2), -- The id of the aircraft [FK] referencing aircraftid in aircraft table; refers to the aircraft being serviced
teamid char(2), -- The id of the serviceteam [FK] referencing teamid in serviceteam table
primary key(serviceid), -- Setting serviceid as the primary key
foreign key(hangarid) references hangar(hangarid), -- Setting hangarid as the foreign key from the table hangar
foreign key(aircraftid) references aircraft(aircraftid), -- Setting aircrafttypeid as the foreign key from the table aircraft
foreign key(teamid) references serviceteam(teamid)); -- Setting teamid as the foreign key from the table serviceteam

-- Insert statements for service
Insert into service values ('S1','2019-09-25','H3','A3','T1');
Insert into service values ('S2','2019-08-27','H2','A7','T2');
Insert into service values ('S3','2019-09-22','H5','A7','T4');
Insert into service values ('S4','2019-05-13','H5','A4','T4');
Insert into service values ('S5','2019-01-08','H1','A4','T1');
Insert into service values ('S6','2019-09-07','H4','A9','T3');
Insert into service values ('S7','2019-12-20','H3','A9','T1');
Insert into service values ('S8','2019-12-20','H4','A3','T3');
Insert into service values ('S9','2019-05-18','H4','A2','T3');
Insert into service values ('S10','2019-05-14','H3','A3','T1');
Insert into service values ('S11','2019-05-27','H3','A3','T1');
Insert into service values ('S12','2019-08-11','H3','A9','T1');
Insert into service values ('S13','2019-08-17','H4','A2','T3');
Insert into service values ('S14','2019-12-14','H4','A4','T3');
Insert into service values ('S15','2025-01-25','H5','A1','T4');

-- Create table serviceteam
create table serviceteam(
teamid char(2), -- A unique identifier for the team
teamname varchar(20), -- The name of the hangar
teamlevel numeric(1), -- The seniority level of the team
primary key (teamid)); -- Setting teamid as the primary key

-- Insert statements for serviceteam
Insert into serviceteam values ('T1','Sydney Rabbitohs','5');
Insert into serviceteam values ('T2','Melbourne Rebels','3');
Insert into serviceteam values ('T3','Queensland Reds','5');
Insert into serviceteam values ('T4','Team TRU','4');





/******* TASK 2: AFTER THIS COMMENT, INSERT YOUR QUERY BELOW ***********/
select hangarlocation
from hangar
where hangarstoragecap <16
or hangarstoragecap >30;



/******* TASK 3: AFTER THIS COMMENT, INSERT YOUR QUERY BELOW ***********/

select aircraftid, aircraftpurdate
from aircraft
where aircraftseatcap > 150
and month(aircraftpurdate) = 10
or year(aircraftpurdate) in (2014,2016)
order by aircraftseatcap desc;


/******* TASK 4: AFTER THIS COMMENT, INSERT YOUR QUERY BELOW ***********/

select a.aircraftid 'AircraftID'
from aircraft a, aircrafttype aa
where aa.aircrafttypeid=a.aircrafttypeid
and aa.aircrafttypename like 'Airbus%'
order by aircraftid;



/******* TASK 5: AFTER THIS COMMENT, INSERT YOUR QUERY BELOW ***********/

select distinct a.aircraftid, aa.aircrafttypename
from aircraft a, aircrafttype aa, service s
where aa.aircrafttypeid = a.aircrafttypeid
and a.aircraftid = s.aircraftid
and s.hangarid = 'H4';



/******* TASK 6: AFTER THIS COMMENT, INSERT YOUR QUERY BELOW ***********/

select *
from service
where (hangarid = 'H1'
or hangarid = 'H3')
and servicedate in
				(select servicedate
                from service
                where month(servicedate) between 07 and 09);

select *
from service
where month(servicedate) between 07 and 09
and hangarid in
				(select hangarid
                from hangar
                where hangarid = 'H1'
				or hangarid = 'H3');
-- This one
select *
from service
where month(servicedate) between 07 and 09
and hangarid in
				(select hangarid
                from hangar
                where hangarlocation like '%NSW');

/******* TASK 7: AFTER THIS COMMENT, INSERT YOUR QUERY BELOW ***********/

select distinct s.*
from service s join hangar h
on s.hangarid=h.hangarid
where month(s.servicedate) between 07 and 09
and h.hangarlocation like  '%NSW';



/******* TASK 8: AFTER THIS COMMENT, INSERT YOUR QUERY BELOW ***********/

select distinct s.aircraftid
from aircraft a, service s, hangar h, serviceteam st
where a.aircraftid = s.aircraftid
and h.hangarid = s.hangarid
and st.teamid = s.teamid

and (a.aircrafttypeid = '22' -- like % airbus?
or a.aircrafttypeid = 'B3') -- like % airbus?

and s.servicedate = '2019-12-20'

and (h.hangarstoragecap < 10
or h.hangarstoragecap > 20)

and st.teamlevel = 5

order by s.aircraftid desc;
-- need subqueries?

-- 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: AFTER THIS COMMENT, INSERT YOUR QUERY BELOW ***********/

select distinct count(s.aircraftid)
from service s join aircraft a
on s.aircraftid = a.aircraftid
where year(aircraftpurdate) > 2017
or aircraftseatcap != 104; -- this one


/******* TASK 10: AFTER THIS COMMENT, INSERT YOUR QUERY BELOW ***********/

select st.teamid, st.teamlevel, count(s.teamid)
from serviceteam st, service s
where st.teamid=s.teamid
and (st.teamlevel = 1
or st.teamlevel = 3)

-- and count(s.teamid) < 4
group by s.teamid
order by s.teamid desc;
-- 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: AFTER THIS COMMENT, INSERT YOUR QUERY BELOW ***********/

-- 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.