BD

use basadados;
create table MARCA(
IdMarca int Primary Key,
NomeMarca varchar(50) NOT NULL
);
create table AVIAO(
MatriculaAviao varchar(5) Primary Key,
CorAviao varchar(30) NOT NULL,
Autonomia int NOT NULL,
IdMarca int NOT NULL,
FOREIGN KEY (IdMarca) REFERENCES MARCA(IdMarca)
);
create table PILOTO(
IdPiloto int Primary Key,
Nome varchar(50) NOT NULL,
Salario float NOT NULL,
DataNasc date NOT NULL
);
create table _VOO(
NumVoo int Primary Key,
DataVoo date NOT NULL,
OrigemVoo varchar(50) NOT NULL,
DestinoVoo varchar(50) NOT NULL,
MatriculaAviao varchar(5) NOT NULL,
IdPiloto int NOT NULL,
FOREIGN KEY (MatriculaAviao) REFERENCES AVIAO(MatriculaAviao),
FOREIGN KEY (IdPiloto) REFERENCES PILOTO(IdPiloto)
);
Insert into MARCA(IdMarca,NomeMarca) values(1,'M1');
Insert into MARCA(IdMarca,NomeMarca) values(2,'M2');
Insert into MARCA(IdMarca,NomeMarca) values(3,'M3');
Insert into MARCA(IdMarca,NomeMarca) values(4,'M4');
Insert into MARCA(IdMarca,NomeMarca) values(5,'M5');
Insert into AVIAO(MatriculaAviao,CorAviao,Autonomia,IdMarca) values('CS-A','C1',3000,1);
Insert into AVIAO(MatriculaAviao,CorAviao,Autonomia,IdMarca) values('CS-B','C2',5000,2);
Insert into AVIAO(MatriculaAviao,CorAviao,Autonomia,IdMarca) values('CS-C','C3',1000,3);
Insert into AVIAO(MatriculaAviao,CorAviao,Autonomia,IdMarca) values('CS-D','C4',5000,2);
Insert into AVIAO(MatriculaAviao,CorAviao,Autonomia,IdMarca) values('CS-E','C5',2000,5);
Insert into PILOTO(IdPiloto,Nome,Salario,DataNasc) values(1,'Piloto1',3000,'1980-03-03');
Insert into PILOTO(IdPiloto,Nome,Salario,DataNasc) values(2,'Piloto2',5000,'1970-05-05');
Insert into PILOTO(IdPiloto,Nome,Salario,DataNasc) values(3,'Piloto3',1000,'1990-01-01');
Insert into PILOTO(IdPiloto,Nome,Salario,DataNasc) values(4,'Piloto4',5000,'1971-05-05');
Insert into PILOTO(IdPiloto,Nome,Salario,DataNasc) values(5,'Piloto5',2000,'1985-02-02');
Insert into _VOO(NumVoo,DataVoo,OrigemVoo,DestinoVoo,MatriculaAviao,IdPiloto) values(1,'2000-01-01','Origem1','Destino1','CS-A',1);
Insert into _VOO(NumVoo,DataVoo,OrigemVoo,DestinoVoo,MatriculaAviao,IdPiloto) values(2,'2010-01-01','Origem2','Destino2','CS-B',2);
Insert into _VOO(NumVoo,DataVoo,OrigemVoo,DestinoVoo,MatriculaAviao,IdPiloto) values(3,'2017-06-09','Origem3','Destino3','CS-B',2);
Insert into _VOO(NumVoo,DataVoo,OrigemVoo,DestinoVoo,MatriculaAviao,IdPiloto) values(4,'2018-05-05','Origem4','Destino4','CS-A',1);
Insert into _VOO(NumVoo,DataVoo,OrigemVoo,DestinoVoo,MatriculaAviao,IdPiloto) values(5,'2018-05-09','Origem5','Destino6','CS-C',3);

Insert into AVIAO(MatriculaAviao,CorAviao,Autonomia,IdMarca) values('CS-G','C1',4000,1);



/*a)*/
select MatriculaAviao from AVIAO where Autonomia >=5000;

/*b)*/
select MARCA.NomeMarca, count(AVIAO.MatriculaAviao) as QuantidadedeAvioes
from AVIAO right join MARCA
on MARCA.idMarca=AVIAO.idMarca
group by NomeMarca;

/*c)*/
select Nome
from PILOTO
where DataNasc like "%05-08";

/*d)*/
select Nome from PILOTO
where datanasc=(select min(datanasc) from piloto);

/*e)*/
select avg(Autonomia) from AVIAO
group by CorAviao;
/*f)*/
SET SQL_SAFE_UPDATES = 0;
UPDATE Piloto
SET Salario=(Salario+Salario*.1) where Salario<2000;
SELECT * FROM PILOTO;

/*G)*/
SET SQL_SAFE_UPDATES = 0;
UPDATE PILOTO
SET Salario=(Salario-Salario*.1) Where Piloto.IdPiloto Not IN (SELECT idPiloto from _Voo);

SELECT * FROM PILOTO;


/*2)*/
create view nova as
select * from _VOO order by DataVoo desc with check option;
select * from nova;

/*3)*/

delimiter $$
create procedure atu_salario()
Begin
	select (salario+salario*.3)
    from PILOTO;
End$$
delimiter ;

call atu_salario();