Exercicio de sql

[pic 1][pic 2][pic 3]

DCC060 – BANCO DE DADOS                                                                                                        Prof. Tarcísio de Souza Lima^[1]

Exercícios Revisionais de SQL

Considere o banco de dados da empresa de transporte aéreo mostrado abaixo, em que as chaves primárias estão sublinhadas.

1. Implemente em SQL as tabelas abaixo, considerando as restrições de chave primária e de chave estrangeira; inclua também restrições de domínio adequadas.
2. Escreva expressões em SQL para exibir ou modificar dados nesse esquema.

OBS.: TODAS AS SOLUÇÕES DEVERÃO SER IMPLEMENTADAS E TESTADAS UTILIZANDO O BD ORACLE (o professor poderá solicitar a execução em sala de aula).

Piloto (codigo_piloto, nome_piloto, salario, gratificacao, companhia, pais)

Voo (codigo_voo, aeroporto_origem, aeroporto_destino, hora)

Escala (codigo_voo, data_voo, piloto, aviao)

Aeroporto (codigo_aeroporto, nome_aeroporto, cidade, pais)

a) CREATE TABLE PILOTO (Codigo_Piloto int PRIMARY KEY, Nome_piloto varchar (255), Salario number, Gratificacao number, Companhia varchar (30), Pais varchar (30) ); CREATE TABLE VOO (codigo_voo int PRMARY KEY, aeroporto_origem int, aeroporto_destino int, hora CONSTRAINT Frkaeroportoorig FOREIGN KEY (aeroporto_origem) REFERENCES AEROPORTO (codigo) CONSTRAINT Frkaeroportodest FOREIGN KEY (aeroporto_destino) REFERENCES AEROPORTO (codigo) CREATE TABLE ESCALA codigo_voo int, data_voo date, piloto int, aviao varchar (30) CONSTRAINT FrkEscalaVoo FOREIGN KEY (codigo_voo) REFERENCES VOO (codigo_voo) CONSTRAINT FrkEscalaPiloto FOREIGN KEY (piloto) REFERENCES PILOTO (codigo_piloto) ), CREATE TABLE AEROPORTO codigo_aeroporto int PRIMARY KEY, nome varchar (30), cidade varchar(30), pais varchar (30) ),

Básico (Select, Order By, Like): Resolva pelo menos 10 dessas questões, cobrindo essas cláusulas SQL.

1. Os dados de todos os pilotos de companhias brasileiras.

SELECT * FROM Piloto WHERE Pais = 'Brasil'

1. Nome de todos os pilotos da Gol.

SELECT Nome FROM Piloto WHERE companhia = 'Gol'

1. Nome de todos os pilotos, junto com seu salário e gratificação.

SELECT Nome, Salario, Gratificaçao FROM Piloto

1. Nome de todos os pilotos, junto com o total de seus vencimentos, para todos os pilotos que voam há pelo menos 5 anos.

1. Nome da cidade e país de destino do voo JJ230.

SELECT Cidade, Pais

FROM Voo, Aeroporto WHERE Voo.aeroporto_destino = Aeroporto.codigo AND Voo.codigo = JJ230

1. A companhia dos voos escalados para 21/05/2011.

SELECT Piloto.companhia FROM Escala, Piloto WHERE Escala.piloto = Piloto.codigo_piloto AND Escala.data_voo = 21/05/2011

1. Código de todos os voos, nome dos pilotos escalados para os mesmos, e respectivos tipo de avião e companhia.

SELECT Voo.Codigo_voo, Piloto.nome_piloto, Escala.aviao, P.companhia FROM Voo, Piloto, Escala WHERE Voo.codigo_voo = Escal.codigo_voo AND (Escala.codigo_piloto = Piloto.codigo_piloto)

1. Código de todos os voos e hora de saída, junto com o nome dos pilotos escalados para os mesmos, e respectivos tipo de avião e companhia, para todos os voos de companhias estrangeiras.

1. Código de todos os voos para a Alemanha ou Itália, com as respectivas data e hora de saída.

SELECT Voo.codigo_voo, Escala.data, Escala.hora FROM Escala, Voo, Aeroporto WHERE Voo.codigo_voo = Escala.codigo_voo AND Voo.aeroporto_destino = codigo_aeroporto AND (Aeroporto.pais = 'Alemanha' OR Aeroporto = 'Italia'

1. A companhia dos pilotos que voam para a Italia.

1. Destino (nome do aeroporto, cidade e país) de todos os voos da TAM.

SELECT Aeroporto. nome_aeroporto, Aeroporto

1. Nome dos aeroportos de origem e de destino de todos os voos marcados para o dia 02/11.

1. Os aeroportos de destino (nome, cidade) para todos os voos da Gol em Boeing 737.

1. Código e horário dos voos internos de todos os países.

1. Código de todos os voos internacionais da TAM. Dica: Todas as companhias só podem operar a partir de seu próprio país, ou retornar a este.

1. Nome de todos os aeroportos onde a Ocean Air opera.

1. Código de todos os voos internacionais que as companhias fazem a partir de aeroportos em seus próprios países.

1. Nome dos pilotos que voam de MD11 para o seu próprio país.

1. Encontre os nomes dos pilotos que começam com A.

1. Encontre os nomes de todos os pilotos cujo nome não comece pela letra A.

1. Encontre os nomes dos pilotos que sejam parentes da família Silva, ou seja, contenham Silva no nome.

1. Encontre os nomes dos pilotos que terminam O.

1. Encontre os nomes dos pilotos que tenham no nome uma segunda letra A.

1. Encontre os nomes dos pilotos que não tem Júnior no nome.

1. Encontre os nomes dos aeroportos que tenham 'POR' ou 'ALE' no nome.

1. Encontre os nomes dos aeroportos que tenha a penúltima letra do nome R.

1. Código de todos os voos que iniciam por RG ou JJ, junto com a respectiva companhia.

1. Uma listagem dos nomes dos pilotos ordenados pelo salário.

1. Uma listagem dos nomes dos pilotos escalados em voos para o Brasil ordenados pelo nome.

1. Uma listagem dos nomes dos aeroportos ordenados pela cidade.

1. Uma listagem da localização (pais, cidade) dos aeroportos, ordenados por pais e cidade.

Funções Agregadas (Max, Min, Count, Sum, Distinct, Group By, Having): Resolva pelo menos 10 dessas questões, cobrindo essas cláusulas SQL.

1. O salário do piloto mais bem pago da Gol.

1. O menor, maior, e média dos salários dos pilotos de companhias brasileiras.

1. O total da folha de pagamento da Gol.

1. O número de escalas previstas para o voo JJ230.

1. O número de pessoas envolvidas em escalas do voo JJ230.

1. O número de aeroportos no Brasil.

1. Para as quais se destinam os voos partindo de GRU.

1. O número de aeroportos diferentes de onde os pilotos da Gol partem.

1. O número de voos diários que a TAM oferece para São Paulo.

1. O número de aeroportos diferentes que os pilotos da Varig usam (para chegada ou partida).

1. Para cada companhia, o menor, maior salário, bem como a média.

1. Para cada cidade do Brasil, o número de aeroportos que possui.

1. Para cada piloto, o número de escalas programadas para o dia 01/06/2011.

1. Para cada dia e hora, o número de voos que partem pela manhã.

1. Para cada país, o número de aeroportos e de países distintos para onde os voos de suas companhias se dirigem.

1. Para cada faixa de salário de cada companhia brasileira, o número de pilotos recebendo este salário, o total dos salários pagos na faixa, e o número de escalas vinculadas a estes pilotos.

1. Para cada piloto de companhia estrangeira, o número de voos que ele faz de ou para o Brasil.

1. Para cada pais, o número de companhias que fazem voos estrangeiros.

1. Para cada pais encontre o número de pilotos que ganham mais de R$18.000,00.

1. Para cada companhia encontre a quantidade de aeroportos de onde saem os voos e quantidade de aeroportos de onde partem os seus voos.

1. As companhias que pagam como salário mais alto a seus pilotos menos que R$12.000,00.

1. As companhias brasileiras que pagam como salário mais alto a seus pilotos menos que R$12.000,00.

1. As companhias estrangeiras que possuem pelo menos 2 pilotos.

1. As companhias que possuem pelo menos 2 pilotos escalados em voos para o Canadá.

1. Para cada país estrangeiro que possui pelo menos 2 companhias aéreas distintas, o salário do piloto mais bem pago e do mais mal pago, a média salarial do país, a folha de pagamento, e o número total de pilotos

1. O número de escalas previstas em MD11 para cada piloto da Varig que possui pelo menos 2 escalas neste tipo de avião.

1. O código de voo para cada voo previsto com destino a São Paulo que envolve dois (e somente 2) tipos de aeronaves.

1. Os pilotos que trabalham só em uma companhia.

1. As companhias que só usam um tipo de avião.

1. Os voos cujas escalas são todas em aviões diferentes.

Subconsultas (In, Exist): Resolva pelo menos 8 dessas questões, cobrindo essas cláusulas SQL.

1. As companhias que voam de MD11.

1. As companhias que não voam de MD11.

1. As companhias que só voam de MD11.

1. Nome dos pilotos que voam de MD11 ou de Boeing 737.

1. Nome dos pilotos que voam de MD11 e de Boeing 737.

1. Nome dos pilotos que voam de MD11 e não voam de Boeing 737.

1. Nome das companhia que empregam pelo menos dois pilotos.

1. O nome dos pilotos que trabalham para companhias que empregam pelo menos três pilotos.

1. Para toda companhia que voa com aviões MD11, o código dos voos que são escalados neste tipo de avião.

1. Para toda companhia que voa com aviões MD11, o código de todos os voos que ela oferece

1. A média salarial dos pilotos.

1. Nome dos pilotos que ganham menos que a média salarial.

1. A(s) companhia(s) que paga(m) o maior salário.

1. O número total das escalas e nome dos pilotos, para cada piloto da Varig que voa de MD11.

1. As companhias que oferecem mais voos para o Canadá.

1. Para cada piloto da Varig que voa de MD11, o nome do piloto e o número total das escalas programadas neste tipo de avião.

1. Para cada piloto da Varig que voa de MD11, o nome do piloto e o número total das escalas programadas para este piloto, independentemente do avião.

1. Nome dos pilotos que voam a partir de aeroportos no Brasil ou na Alemanha.

1. Nome dos pilotos que voam a partir de aeroportos no Brasil e na Alemanha.

1. Nome dos pilotos que voam a partir de aeroportos no Brasil e não voam a partir de aeroportos na Alemanha.

1. Nome dos pilotos que fizeram pelo menos duas escalas de MD11, e o total de diferentes voos (código) fizeram nestes aviões.

1. Nome dos pilotos que fizeram pelo menos duas escalas de MD11, e o total de diferentes voos (código) para São Paulo que eles fizeram (em qualquer tipo de avião).

Insert, Delete, Update: Resolva pelo menos 5 dessas questões, cobrindo essas cláusulas SQL.

...