Consultas SQL - Passageiros Voos e Companhias Aéreas no Brasil

Elabore as seguintes consultas SQL 1 Listar o nome completo primeiro nome último nome a idade e a cidade de todos os passageiros do sexo feminino sexw com mais de 40 anos residentes no país BRAZIL resposta sugerida 141 linhas 2 Listar o nome da companhia aérea o identificador da aeronave o nome do tipo de aeronave e o número de todos os voos operados por essa companhia aérea independentemente de a aeronave ser de sua propriedade que saem e chegam em aeroportos localizados no país BRAZIL resposta sugerida 8 linhas valor corrigido 3 Listar o número do voo o nome do aeroporto de saída e o nome do aeroporto de destino o nome completo primeiro e último nome e o assento de cada passageiro para todos os voos que partem no dia do seu aniversário neste ano caso a consulta não retorne nenhuma linha faça par o mês subsequente até encontrar uma data que retorne alguma linha resposta sugerida 106 linhas para o dia 25032023 4 Listar o nome da companhia aérea bem como a data e hora de saída de todos os voos que chegam para a cidade de NEW YORK que partem às terças quartas ou quintasfeiras no mês do seu aniversário caso a consulta não retorne nenhuma linha faça para o mês subsequente até encontrar um mês que retorne alguma linha resposta sugerida 1 linha para o mês de março de 2023 5 Crie uma consulta que seja resolvida adequadamente com um access hash em um cluster com pelo menos duas tabelas A consulta deve utilizar todas as tabelas do cluster e pelo menos outra tabela fora dele Dica 1 Nunca utilize funções ou outros modificadores no lado esquerdo das condições ou seja lá devem estar somente COLUNAS caso contrário índices existentes sobre as colunas não serão utilizados Veja o seguinte exemplo 1 SELECT 2 FROM airpassengersdetails 3 WHERE 4 TRUNCMONTHSBETWEENSYSDATEbirthdate120 40 Crie um índice sobre a coluna birthdate e veja se ele será utilizado Agora execute a mesma consulta mas isolando a coluna no lado esquerdo da condição 1 SELECT FROM airpassengersdetails 2 WHERE 3 birthdate ADDMONTHS SYSDATE 4012 Dica 2 Como definir o período de um dia inteiro compreendido entre as 000000 e as 235959 desse dia Faça o seguinte 1 SELECT BETWEEN TRUNCSYSDATE AND TRUNCSYSDATE11 246060 Por que isso funciona Porque ao operar com valores do tipo DATE 1 1 dia ou seja 24 horas ou 1440 minutos 24 horas x 60 minutos ou 86400 segundos 24 horas x 60 minutos x 60 segundos logo 1246060 1 segundo Dica 3 Como calcular o tamanho das tabelas 1 SELECT 2 segmentname 3 SUMbytes1024 kB 4 FROM 5 usersegments 6 WHERE 7 segmenttypeTABLE 8 AND segmentname LIKE AIR 9 GROUP BY 10 segmentname 11 ORDER BY 12 2 DESC Dica 4 Reduzir o tamanho da tabela AIRBOOKINGS para conseguir criar eventuais clusters se necessário 1 DELETE FROM airbookings 2 WHERE bookingid 370888114 3 COMMIT Etapa 3 Sintonia de desempenho SQL tunning Execute cada uma das consultas sem criar nenhuma constraint ou estrutura de acesso otimizado os seguintes passos 1 Execute a consulta e confira a resposta 2 Capture o plano de execução Etapa 4 Sintonia de desempenho SQL tunning Para cada uma das consultas faça os testes necessários buscando a geração do melhor plano de execução possível Lembrese entretanto que o espaço de armazenamento de vocês é limitado e duplicar tabelas grandes pode extrapolálo gerando um erro ao criar tabelas clusters índices ou outras estruturas A seguir 1 Crie todas as estruturas de acesso otimizadas necessárias para que a consulta seja executada da forma mais otimizada possível Constraints de chave primária primary key geram índices únicos implementados como BTree Constraints de chave alternativa unique geram índices únicos implementados como BTree Índices não únicos implementados como BTree Podemdevem ser implementados em colunas que frequentemente aparecem em condições da cláusula WHERE Podem e normalmente devem ser criados nas constraints de chave estrangeira foreign key Clusters de tabelas com acesso via Índice BTree Clusters de tabelas com acesso via hash 2 Liste e capture a imagem do plano de execução sugerido pelo Oracle Material a ser entregue Entregar relatório em PDF contendo para cada consulta 1 Comando SQL DQL SELECT que implementa a consulta 2 Captura de tela do Plano de Execução do SQL Developer anterior à sintonia de desempenho 3 Comandos SQL DDL Data Definition Language para criação das estruturas de acesso sugeridas e 4 Captura de tela do Plano de Execução do SQL Developer anterior à sintonia de desempenho BANCO DE DADOS 10 de outubro de 2023 Data Definition Language Comandos General criteria for correcting each of the 5 queries 1 The query must be correct 33 2 The created structures must be necessary and accurate 33 3 The optimized execution plan should have lower costs and cardinalities than the original plan 33 1 Retrieve the name age country and city of all female passengers sexw over 40 years old residing in the Brazil country 1 SELECT pfirstname plastname AS passengername TRUNCMONTHSBETWEENSYSDATE pdbirthdate 12 0 AS age pdcity FROM airpassengers p INNER JOIN airpassengersdetails pd ON pdpassengerid ppassengerid WHERE pdsex w AND pdbirthdate ADDMONTHSSYSDATE 40 12 AND pdcountry BRAZIL Suggested answer 143 rows Final cost 202 Final cardinality 143 1 Create a hash cluster including airairports and airairportsgeo 2 Create primary keys and foreign keys 3 Create indexes on foreign keys 4 Create an index on airpassengersdetailscountry or Create an index on airpassengersdetailsbirthdate or Create an index on airpassengerdetailsh1country sex birthdate 2 2 Retrieve the airline name airplane identifier airplane type name and flight numbers for flights departing from airports located in the Brazil country SELECT alairlinename aplairplaneid aptname fflightno FROM airairlines al INNER JOIN airflights f ON fairlineid alairlineid INNER JOIN airairplanes apl ON aplairplaneid fairplaneid INNER JOIN airairplanetypes apt ON aptairplanetypeid aplairplanetypeid INNER JOIN airairportsgeo apgo ON apgoairportid ffromairportid INNER JOIN airairportsgeo apgd ON apgdairportid ftoairportid WHERE apgocountry BRAZIL AND apgdcountry BRAZIL Suggested answer 8 rows Final cost 65 Final cardinality 1 1 Create a hash cluster including airairports and airairportsgeo 2 Create primary keys and foreign keys 3 3 Create indexes on foreign keys 4 Create an index on airairportsgeocountry 3 Retrieve the flight number departure airport name destination airport name full name first and last name and seat of each passenger for flights departing on a specific date SELECT fflightno fapname AS fromairportname tapname AS toairportname pfirstname plastname AS passengername bseat FROM airflights f INNER JOIN airairports fap ON fapairportid ffromairportid INNER JOIN airairports tap ON tapairportid ftoairportid INNER JOIN airbookings b ON bflightid fflightid INNER JOIN airpassengers p ON ppassengerid bpassengerid WHERE fdeparture BETWEEN TRUNCTODATE25032023 ddmmyyyy AND TRUNCTODATE26032023 ddmmyyyy 1 24 60 60 Suggested answer 106 rows for March 25 3023 Final cost 233 Final cardinality 357 4 1 Create a hash cluster including airflights and airbookings 2 Create primary keys and foreign keys 3 Create indexes on foreign keys 4 Create an index on airflightsdeparture 4 Retrieve the airline name departure date and time of flights arriving in New York on Tuesdays Wednesdays or Thursdays one month from now SELECT DISTINCT alairlinename fflightno fdeparture tapgcity FROM airairlines al INNER JOIN airflights f ON fairlineid alairlineid INNER JOIN airflightsschedules fs ON fsflightno fflightno INNER JOIN airairportsgeo tapg ON tapgairportid ftoairportid INNER JOIN airbookings b ON bflightid fflightid INNER JOIN airpassengers p ON ppassengerid bpassengerid WHERE tapgcity NEW YORK AND fstuesday 1 OR fswednesday 1 OR fsthursday 1 AND fdeparture BETWEEN TODATE01032023 ddmmyyyy AND ADDMONTHSTODATE01042023 ddmmyyyy 1 1 5 Suggested answer 1 row for departures in March 2023 Final cost 237 Final cardinality 136 No need to create a hash cluster or index cluster 1 Create primary keys and foreign keys 2 Create indexes on foreign keys 3 Create an index on airflightsdeparture 4 Create an index on airairportsgeocity 5 Create a bitmap index on airflightsschedulesweekdays 5 Create a query that can be efficiently resolved with hash access in a cluster containing at least two tables The query should use all the tables in the cluster and at least one other table outside of it Evaluate the cluster and the query PRIMARY KEYS ALTER TABLE airairlines ADD CONSTRAINT pkairlineid PRIMARY KEY airlineid ALTER TABLE airairplanetypes ADD CONSTRAINT pkairplanetypeid PRIMARY KEY airplanetypeid ALTER TABLE airairplanes ADD CONSTRAINT pkairplaneid PRIMARY KEY airplaneid ALTER TABLE airairports ADD CONSTRAINT pkairportid PRIMARY KEY airportid ALTER TABLE airairportsgeo ADD CONSTRAINT pkairportsgeo PRIMARY KEY airportid ALTER TABLE airflights ADD CONSTRAINT pkflightid PRIMARY KEY flightid 6 ALTER TABLE airflightsschedules ADD CONSTRAINT pkflightsschedules PRIMARY KEY flightno ALTER TABLE airpassengers ADD CONSTRAINT pkpassengerid PRIMARY KEY passengerid ALTER TABLE airpassengersdetails ADD CONSTRAINT pkpassengersdetails PRIMARY KEY passengerid ALTER TABLE airbookings ADD CONSTRAINT pkbooking id PRIMARY KEY bookingid FOREIGN KEYS ALTER TABLE airairplanes ADD CONSTRAINT fkairplanesairplanetypes FOREIGN KEY airplanetypeid REFERENCES airairplanetypesairplanetypeid ALTER TABLE airairportsgeo ADD CONSTRAINT fkairportsgeo FOREIGN KEY airportid REFERENCES airairportsairportid ALTER TABLE airflights ADD CONSTRAINT fkflightsschedules FOREIGN KEY flightno REFERENCES airflightsschedulesflightno ALTER TABLE airflights ADD CONSTRAINT fkflightsairlines FOREIGN KEY airlineid REFERENCES airairlinesairlineid ALTER TABLE airflights ADD CONSTRAINT fkflightsfromairports FOREIGN KEY fromairportid REFERENCES airairportsairportid ALTER TABLE airflights ADD CONSTRAINT fkflightstoairports FOREIGN KEY toairportid REFERENCES airairportsairportid ALTER TABLE airflights ADD CONSTRAINT fkflightsairplanes FOREIGN KEY airplaneid REFERENCES airairplanesairplaneid ALTER TABLE airpassengersdetails ADD CONSTRAINT fkpassengersdetails FOREIGN KEY passengerid REFERENCES airpassengerspassengerid ALTER TABLE airbookings ADD CONSTRAINT fkflightsbookings FOREIGN KEY flightid REFERENCES airflightsflightid ALTER TABLE airbookings ADD CONSTRAINT fkbookingspassengers FOREIGN KEY passengerid REFERENCES airpassengerspassengerid 7 ALTERNATE KEYS ALTER TABLE airairlines ADD CONSTRAINT akairairlinesiata UNIQUE iata ALTER TABLE airairports ADD CONSTRAINT akairairportsicao UNIQUE icao ALTER TABLE airpassengers ADD CONSTRAINT akairpassengerspassportno UNIQUE passportno CREATE UNIQUE INDEX akairbookingsflightidseat ON airbookings CASE WHEN seat IS NOT NULL THEN flightid ELSE NULL END CASE WHEN seat IS NOT NULL THEN seat ELSE NULL END BTREE INDEXES REQUIRED CREATE INDEX idxairportsgeocountry ON airairportsgeocountry CREATE INDEX idxairportsgeocity ON airairportsgeocity CREATE INDEX idxflightsdeparture ON airflightsdeparture CREATE INDEX idxflightsarrival ON airflightsarrival 8 CREATE INDEX idxflightsnumber ON airflightsflightno CREATE INDEX idxpassengersdetailscountry ON airpassengersdetailscountry CREATE INDEX idxpassengersdetailsbirth ON airpassengersdetailsbirthdate CREATE INDEX idxpassengersdetailssex ON airpassengersdetailssex INCORRECT CREATE INDEX idxflightsschedulestuesday ON airflightsschedulestuesday CREATE INDEX idxflightsscheduleswednes ON airflightsscheduleswednesday CREATE INDEX idxflightsschedulesthursday ON airflightsschedulesthursday OPTIONAL CREATE INDEX idxairairlinesbaseairportid ON airairlinesbaseairportid CREATE INDEX idxairairplanesairlineid ON airairplanesairlineid CREATE INDEX idxairairplanesairplanetype ON airairplanesairplanetypeid CREATE INDEX idxairairflightsairlineid ON airflightsairlineid CREATE INDEX idxairairflightsairplaneid ON airflightsairplaneid CREATE INDEX idxairairflightsfromid ON airflightsfromairportid CREATE INDEX idxairairflightstoid ON airflightstoairportid CREATE INDEX idxairflightsschedairline ON airflightsschedulesairlineid CREATE INDEX idxairflightsschedfrom ON airflightsschedulesfromairportid CREATE INDEX idxairflightsschedto ON airflightsschedulestoairportid CREATE INDEX idxairflightsschdeparture ON airflightsschedulesdeparture CREATE INDEX idxairflightsscharrival ON airflightsschedulesarrival CREATE INDEX idxairbookingspassid ON airbookingspassengerid 9 CREATE INDEX idxairbookingsflightid ON airbookingsflightid CLUSTERS RECOMMENDED FLIGHTS CREATE CLUSTER airclflightsflightid NUMERIC10 INDEX STORAGE INITIAL 8192K NEXT 512K CREATE INDEX idxclairflights ON CLUSTER airclflights RENAME airflights TO airflightstemp RENAME airbookings TO airbookingstemp CREATE TABLE airflights flightid NUMERIC10 NOT NULL flightno CHAR8 NOT NULL airlineid NUMERIC5 NOT NULL fromairportid NUMBER5 NOT NULL toairportid NUMBER5 NOT NULL airplaneid NUMERIC6 NOT NULL departure TIMESTAMP NOT NULL arrival TIMESTAMP NOT NULL 10 CLUSTER airclflightsbookingsflightid CREATE TABLE airbookings bookingid NUMERIC 20 NOT NULL passengerid NUMERIC 12 NOT NULL flightid NUMERIC 10 NOT NULL seat CHAR 4 price DECIMAL 102 NOT NULL CLUSTER airclflightsbookingsflightid INSERT INTO airflights SELECT FROM airflightstemp INSERT INTO airbookings SELECT FROM airbookingstemp DROP TABLE airflightstemp CASCADE CONSTRAINTS DROP TABLE airbookingstemp CASCADE CONSTRAINTS PASSENGERS CREATE CLUSTER airclpassengerspassengerid NUMERIC12 INDEX STORAGE INITIAL 8192K NEXT 512K CREATE INDEX airidxclpassengers ON CLUSTER airclpassengers RENAME airpassengers TO airpassengerstemp RENAME airpassengersdetails TO airpassengersdetailstemp CREATE TABLE airpassengers passengerid NUMERIC12 NOT NULL 11 passengerno CHAR9 NOT NULL firstname VARCHAR100 NOT NULL lastname VARCHAR100 NOT NULL CLUSTER airclpassengersdetailspassengerid CREATE TABLE airpassengersdetails passengerid NUMERIC12 NOT NULL birthdate DATE NOT NULL sex CHAR1 NOT NULL street VARCHAR100 city VARCHAR100 zip NUMBER5 NOT NULL country VARCHAR50 emailaddress VARCHAR120 telephoneno VARCHAR30 CLUSTER airclpassengersdetailspassengerid INSERT INTO airpassengers SELECT FROM airpassengerstemp INSERT INTO airpassengersdetails SELECT FROM airpassengersdetailstemp DROP TABLE airpassengerstemp CASCADE CONSTRAINTS DROP TABLE airpassengersdetailstemp CASCADE CONSTRAINTS AIRPORTS CREATE CLUSTER airclairportsairportid NUMERIC5 HASHKEYS 128 STORAGE INITIAL 1024K 12 NEXT 128K RENAME airairports TO airairportstemp RENAME airairportsgeo TO airairportsgeotemp CREATE TABLE airairports airportid NUMERIC5 NOT NULL 13