Ficha de trabalho de SQL

Crie uma base de dados com o nome “aviacao”.

Entre na base de dados e cole o SQL desta folha.

Responda às questões criando vistas. Exemplo: P1

CREATE TABLE aeroportos (

  idAeroporto int PRIMARY KEY,

  designacao varchar(100),

  cidade varchar(100),

  pais varchar(100)

);

CREATE TABLE modelos (

        idModelo int PRIMARY KEY,

        fabricante varchar(100),

        versao varchar(100),

        motores int

);

CREATE TABLE avioes (

        idAviao int PRIMARY KEY,

        designacao varchar(30),

        idModelo int REFERENCES modelos

);

CREATE TABLE voos (

        idVoo int PRIMARY KEY,

        idAeroportoOrigem int REFERENCES aeroportos,

        idAeroportoDestino int REFERENCES aeroportos,

        companhia varchar(100),

        duracao int,

        idAviao int REFERENCES avioes

);

INSERT INTO aeroportos  VALUES  (1,'Sa Carneiro','Porto','Portugal');

INSERT INTO aeroportos  VALUES  (3,'Portela','Lisboa','Portugal');

INSERT INTO aeroportos  VALUES  (5,'Faro','Faro','Portugal');

INSERT INTO aeroportos  VALUES  (2,'Madeira','Funchal','Portugal');

INSERT INTO aeroportos  VALUES  (4,'Ponta Delgada','S. Miguel','Portugal');

INSERT INTO aeroportos  VALUES  (9,'Orly','Paris','France');

INSERT INTO aeroportos  VALUES  (8,'Charles de Gaule','Paris','France');

INSERT INTO aeroportos  VALUES  (11,'Heathrow','Londres','United Kingdom');

INSERT INTO aeroportos  VALUES  (12,'Gatwick','Londres','United Kingdom');

INSERT INTO modelos  VALUES  (1,'Douglas','DC-10','3');

INSERT INTO modelos  VALUES  (2,'Boeing','737','2');

INSERT INTO modelos  VALUES  (3,'Boeing','747','4');

INSERT INTO modelos  VALUES  (4,'Airbus','A300','2');

INSERT INTO modelos  VALUES  (5,'Airbus','A340','4');

INSERT INTO avioes  VALUES  (1,'Scott Adams',1);

INSERT INTO avioes  VALUES  (2,'Milo Manara',1);

INSERT INTO avioes  VALUES  (4,'Henki Bilal',3);

INSERT INTO avioes  VALUES  (5,'Gary Larson',4);

INSERT INTO avioes  VALUES  (6,'Bill Waterson',4);

INSERT INTO avioes  VALUES  (7,'J R R Tolkien',3);

INSERT INTO avioes  VALUES  (8,'Franquin',3);

INSERT INTO avioes  VALUES  (9,'Douglas Adams',1);

INSERT INTO avioes  VALUES  (3,'Serpieri',5);

INSERT INTO voos VALUES  (1001,1,2,'TAP',2,1);

INSERT INTO voos VALUES  (1002,2,3,'TAP',1,2);

INSERT INTO voos VALUES  (1010,12,4,'BA',3,3);

INSERT INTO voos VALUES  (1008,3,12,'Portugalia',3,4);

INSERT INTO voos VALUES  (1007,5,1,'TAP',1,5);

INSERT INTO voos VALUES  (1009,1,3,'Portugalia',1,2);

INSERT INTO voos VALUES  (1005,9,2,'AirFrance',2,3);

INSERT INTO voos VALUES  (1003,2,12,'BA',2,5);

INSERT INTO voos VALUES  (1006,8,11,'BA',1,5);

INSERT INTO voos VALUES  (1004,4,3,'SATA',3,6);

INSERT INTO voos VALUES  (1111,1,3,'TAP',2,3);

-- 1) Qual é a designação e a cidade de todos os aeroportos de Portugal? (designacao, cidade)

-- 2) Quais são as designações dos aviões cuja versao é 'DC-10'? (designacao)

-- 3) Quantos motores tem cada avião? (designacao, motores)

-- 4) Quantos voos com duração de duas ou três horas estão registados na base de dados? (total)

-- 5) Quais são os aviões cuja versão do modelo começa com 'A3'? (idModelo, versao)

-- 6) Qual é o código e a duração de todos os voos? Ordene a resposta dos voos com maior duração para menor duração. Se os vooos tiverem igual duração ordene-os do código mais baixo para o mais alto.(idVoo, duracao).

-- 7) Sabendo que não existem voos diretos do Porto para Londres, quais são os voos de ligação que podem ser usados para viajar entre os 2 aeroportos. Nota: Use os códigos dos aeroportos (1 and 12) ao invés de usar a designação do aeroporto.

-- 8) Quantos aeroportos existem em cada pais? Ordene a resposta de forma ascendente. (pais, motores)?

-- 9) Qual é o id do Voo, cidade de origem e destino de cada voo na base de dados? Ordene pelo idVoo (idVoo, origin, destination)

-- 10) Quais são os idVoo de todos os voos do Porto para Lisboa? (idVoo) Nota: Use a cidade ao invés do id dos aeroportos.

-- 11) Quantos aeroportos tem cada pais quando cada pais tiver mais de 2 aeroportos? (pais, total);

-- 12) Qual é o paaís que tem mais aeroportos e quantos tem? (pais, total)

-- 13) Quantos aviões existem para cada modelo? Ordene o resultado de modo a  aparecerem primeiro os que existem em maior  quantidade (fabricante, versao, total). Nota: Não é preciso mostrar os modelos que não têm aviões associados.

-- 14) Quantos aviões existem para cada modelo? Ordene o resultado de modo a  aparecerem primeiro os que existem em menor  quantidade (fabricante, versao, total). Nota: É preciso mostrar os modelos que não têm aviões associados.