drop database if exists aeroporiki;

create database aeroporiki;
use aeroporiki;

create table airplane (
    Airname varchar(10),
    oikthe integer,
    prthe integer,
    primary key (Airname)
);

create table dromologio (
    Code varchar(10),
    afet varchar(10),
    proo varchar(10),
    timeanax date,
    timeaf date,
    apost float,
    oikthe integer,
    prthe integer,
    primary key (Code)
);

create table im_pra (
    Co varchar(10),
    de char(1),
    tr char(1),
    te char(1),
    pe char(1),
    pa char(1),
    sa char(1),
    ki char(1),
    constraint foreign key (Co) references dromologio(Code) on delete cascade
);

create table pelates (
    Onpe varchar(20),
    city varchar(15),
    street varchar(15),
    ari integer,
    primary key (Onpe)
);

create table employee (
    AT varchar(15),
    hmgen date,
    salary float,
    onep varchar(20),
    city varchar(15),
    street varchar(15),
    ari integer,
    onre varchar(20),
    rela varchar(10),
    tel integer,
    primary key (AT)
);

create table pilotos (
    ATpil varchar(15),
    ores integer,
    primary key (ATpil),
    constraint foreign key(ATpil)references employee (AT)
);

create table aerosinodos (
    ATaer varchar(15),
    years integer,
    primary key (ATaer),
    constraint foreign key(ATaer)references employee (AT)
);

create table dioikitikos (
    ATdi varchar(15),
    years integer,
    know varchar(20),
    primary key (ATdi),
    constraint foreign key(ATdi)references employee (AT)
);

create table praktoras (
    Prcode integer,
    ATpr varchar(15),
    onpra varchar(20),
    dieuthinsi varchar(20),
    primary key (Prcode , ATpr)
);

create table aerometafora (
    Airn varchar(15),
    Ima date,
    primary key (Airn , Ima),
    constraint foreign key(Airn)references airplane (Airname)
);

create table pilotos_aero (
    Airn varchar(15),
    Im date,
    ATpil1 varchar(15),
    ATpil2 varchar(15),
    primary key (Airn, Im), 
    constraint foreign key(Airn,Im)references aerometafora (Airn,Ima)
);

create table aerosinodos_aero (
    Airn varchar(15),
    Im date,
    ATaer varchar(15),
    primary key (Airn, Im), 
    constraint foreign key(Airn,Im)references aerometafora (Airn,Ima)

);

create table dioikitikos_aero (
    Airn varchar(15),
    Im date,
    ATdi varchar(15),
    primary key (Airn, Im), 
    constraint foreign key(Airn,Im)references aerometafora (Airn,Ima)
    
);

create table kratisi_pelati (
    Airn varchar(15),
    Im date,
    tikra date,
    tiak date,
    oikthe integer,
    prthe integer,
    primary key (Airn,Im), 
    constraint foreign key(Airn,Im)references aerometafora (Airn,Ima)
);

create table kratisi_praktora (
    Airn varchar(15),
    Im date,
    onpel varchar(20),
    oikthe integer,
    prthe integer,
    primary key (Airn, Im), 
    constraint foreign key(Airn,Im)references aerometafora (Airn,Ima)
);

alter table pilotos_aero add constraint foreign key(ATpil1)references pilotos (ATpil);
alter table pilotos_aero add constraint foreign key(ATpil2)references pilotos (ATpil);
alter table aerosinodos_aero add constraint foreign key(ATaer)references aerosinodos (ATaer);
alter table dioikitikos_aero add constraint foreign key(ATdi)references dioikitikos (ATdi);


insert into airplane values ('plane1',50,15);
insert into airplane values ('plane2',20,19);
insert into airplane values ('plane3',80,5);
insert into airplane values ('plane4',53,22);
insert into airplane values ('plane5',15,50);


insert into dromologio values ('p112','ath','zak','2014-04-11','2014-04-12',150.45,50,15);
insert into dromologio values ('p125','ira','ath','2014-07-01','2014-05-01',150.45,20,19);
insert into dromologio values ('p222','ioa','the','2014-05-11','2014-05-11',350.15,80,5);
insert into dromologio values ('p002','the','ira','2014-05-17','2014-05-17',580.49,50,15);
insert into dromologio values ('p190','cha','ale','2014-04-20','2014-04-21',650.22,53,22);
insert into dromologio values ('p182','zak','rod','2014-04-01','2014-04-01',580.49,15,50);
insert into dromologio values ('p099','rod','ioa','2014-05-11','2014-05-11',210.00,53,22);


insert into im_pra values ('p112','1',NUll,NUll,NUll,NUll,NUll,NUll);
insert into im_pra values ('p125',NUll,'1',NUll,NUll,NUll,NUll,NUll);
insert into im_pra values ('p222',NUll,NUll,'1',NUll,NUll,NUll,NUll);
insert into im_pra values ('p002',NUll,NUll,NUll,'1',NUll,NUll,NUll);
insert into im_pra values ('p190',NUll,NUll,NUll,NUll,'1',NUll,NUll);
insert into im_pra values ('p182',NUll,NUll,NUll,NUll,NUll,'1',NUll);
insert into im_pra values ('p099',NUll,NUll,NUll,NUll,NUll,NUll,'1');


insert into pelates values ('m_papadakis','irakleio','dim',12);
insert into pelates values ('k_miaoulis','thessaloniki','mok',99);
insert into pelates values ('g_goalstoglou','zakinthos','ele',105);
insert into pelates values ('p_anastasiadis','ioannina','kal',58);
insert into pelates values ('n_papadopoulos','patra','ali',4);


insert into employee values ('ab1234567','1970-07-07',3112.58,'kostas_m','irakleio','dim',10,'maria_m','kori',210999111);
insert into employee values ('gd1234567','1960-07-07',3522.58,'giannis_m','irakleio','ali',22,'eleni_m','kori',210555111);
insert into employee values ('ez1234567','1980-07-07',2112.58,'giorgos_m','irakleio','kom',11,'dora_m','kori',210999222);
insert into employee values ('ht1234567','1990-07-07',1112.58,'mixalis_m','irakleio','kal',88,'viky_m','kori',210777111);
insert into employee values ('ik1234567','1979-07-07',2802.58,'maria_m','irakleio','mok',75,'anna_m','kori',210998444);

insert into pilotos values ('ab1234567',1235);
insert into pilotos values ('gd1234567',1578);
insert into pilotos values ('ez1234567',2189);


insert into aerosinodos values ('ht1234567',4);


insert into dioikitikos values ('ik1234567',10,'metaptixiako');


insert into praktoras values (123,'a1234567','nikos','kapou');
insert into praktoras values (456,'b1234567','alex','pouthena');
insert into praktoras values (789,'c1234567','niki','pantou');


insert into aerometafora values ('plane1','2014-04-11');
insert into aerometafora values ('plane2','2014-05-11');
insert into aerometafora values ('plane3','2014-05-01');
insert into aerometafora values ('plane4','2014-05-17');
insert into aerometafora values ('plane5','2014-04-20');

insert into pilotos_aero values ('plane1','2014-04-11','ez1234567','ab1234567');
insert into pilotos_aero values ('plane2','2014-05-11','ez1234567','gd1234567');

insert into aerosinodos_aero values ('plane1','2014-04-11','ht1234567');
insert into aerosinodos_aero values ('plane2','2014-05-11','ht1234567');

insert into dioikitikos_aero values ('plane1','2014-04-11','ik1234567');
insert into dioikitikos_aero values ('plane2','2014-05-11','ik1234567');


insert into kratisi_pelati values ('plane1','2014-04-11','2014-02-12','2014-03-12',0,1);
insert into kratisi_pelati values ('plane2','2014-05-11','2014-03-12','2014-04-12',1,0);


insert into kratisi_praktora values ('plane1','2014-04-11','n_papadopoulos',0,1);
insert into kratisi_praktora values ('plane2','2014-05-11','m_papadakis',1,0);




update employee inner join pilotos on ATpil=AT and ores>2000 set salary=salary*0.1+salary;




delete from dromologio where afet='zak';