use Examples;

Create Table Aircraft (
   tailNumber int          not null,
   callName    varchar(20) not null,
 Primary Key (tailNumber)
);

Create Table Blimps (
   tailNumber   int not null,
   basketSizeM3 int not null,
 Primary Key (tailNumber),
 Foreign Key (tailNumber) references Aircraft(tailNumber)
);

Create Table Jets (
   tailNumber int not null,
   wingCount  int not null,
 Primary Key (tailNumber),
 Foreign Key (tailNumber) references Aircraft(tailNumber)
);


/* (General / "Essential") Aircraft data */
insert into Aircraft (tailNumber, callName)
values (1, 'Hong Kong');

insert into Aircraft (tailNumber, callName)
values (2, 'Xian');

insert into Aircraft (tailNumber, callName)
values (3, 'Beijing');

insert into Aircraft (tailNumber, callName)
values (4, 'Shanghai');


/* (Specific / (Accidental for Aircraft) but (Essential for Blimp-ness) Blimp data */
insert into Blimps (tailNumber, basketSizeM3)
values (2, 4500);

insert into Blimps (tailNumber, basketSizeM3)
values (4, 7500);


/* (Specific / (Accidental for Aircraft) but (Essential for Jet-ness) Jet data */
insert into Jets (tailNumber, wingCount)
values (1, 4);

insert into Jets (tailNumber, wingCount)
values (3, 2);


/* General Aircraft Data */
select *
from Aircraft;


/* Specific Blimp Data */
select *
from Aircraft a, Blimps b
where a.tailNumber = b.tailNumber;

/* Specific Jet Data */
select *
from Aircraft a, Jets j
where a.tailNumber = j.tailNumber;

/* All subtypes combined for Aircraft overview */
select a.tailNumber, a.callName, b.basketSizeM3 as 'BLIMP: Basket Size', j.wingCount as 'JET: Wing Count'
from Aircraft a left outer join Blimps b on a.tailNumber = b.tailNumber
                left outer join Jets j on a.tailNumber = j.tailNumber
order by b.basketSizeM3