ïż GRUPA 1


1. Zapisz polecenie SQL pokaujące typy statków powietrznych (SP) przechowywanych w tabeli atc.flight bez powtórzeń wartości.

select distinct arc_type from atc.flight;



2. Zapisz polecenia SQL ustawiające bieżący schemat sesji na ATC oraz format daty dający wyniki w postaci 2002-12-31 23:59.

alter session set current_schema=atc;
alter session set nls_date_format='YYYY-MM-DD HH24:MI';



3. Zapisz polecenie SQL pokaujące dane dla lotów z wybranego dnia, objętego zakresem danych, które zakończyły się lub rozpoczęły na lotnisku EPKT. Projekcja powinna zawierać następujące dane: klucz główny, znak wywoławczy, lotnisko startu, lotnisko lądowania, typ samolotu oraz długość trasy.

select cid, fl_id, dep_drom, dest_drom, arc_type, tot_fp_dist
from flight where dof='02-09-2002'
and (dep_drom='EPKT') OR (dest_drom='EPKT');



4. Zapisz polecenie SQL pokazujące loty wykonane na wysokościach przelotowych poniżej średniej dla wybranego dnia objętego zakresem danych. Projekcja powinna zawierać następujące dane: klucz główny, znak wywoławczy, lotnisko startu, lotnisko lądowania, typ samolotu oraz wysokość przelotową przeliczoną na kilometry i zaokrągloną do liczby całkowitej.

select cid, fl_id, dep_drom, dest_drom, arc_type,
round(cru_alti) AS KM
from flight where cru_alti < (select avg(cru_alti) from flight)
and dof='02-09-2002';

select cid, fl_id, dep_drom, dest_drom, arc_type,
round(tot_fp_dist*1.852) as km from flight
where dof=('02-09-01')
and cru_alti<(select AVG(cru_alti) from flight);



5. Zapisz polecenie SQL pokazujące segmenty trasy lotów krajowych (w FIR EPWW) z wybranego dnia, objętego zakresem danych wykonane przez operatora lotniczego o desygnatorze IATA LOT. Projekcja powinna zawierać następujące dane ze złączonych tabel flight i flight_segment: znak wywoławczy, punkt początkowy segmentu trasy, punkt końcowy segmentu trasy, prędkość przelotową oraz cosinus kursu magnetycznego. Wyniki wyszukiwania posortować według numeru rejsu i czasu na końcu segmentu.

select f.fl_id, true_speed,
s.start_point, s.end_point, cos(ground_heading*3.1416/180)
from flight f, flight_segment s
where s.id_flight=f.id_flight
and f.dof='02-09-2002'
and oper = 'LOT'
order by f.fl_id, s.actual_t_point;

uwaga:/and not (oper) = 'LOT' -> "nieLOT"/



6. Zapisz polecenie SQL pokazujące maksymalny, średni i minimalny pułap przelotowy dla poszczególnych typów samolotów, którymi wykonano conajmniej 50 operacji lotniczych w poszczególne dni dla całego zakresu danych. Projekcja powinna poza wyliczanymi wartościami zawierać kolumny które zostały wykorzystane do grupowania danych. Wartości średnie należy zaokrąglić do liczby całkowitej. Kolumny zawierające wyliczone wartości nazwać kolejno: MAX, AVG i MIN.

select dof, oper, arc_type, max(cru_alti) as MAX,
round(avg(cru_alti)) as AVG,
min(cru_alti) as MIN from flight
group by dof, arc_type, oper having count(*) > 50
order by dof, oper;



7. Zapisz polecenie SQL tworzące tabelę opisującą drogi lotnicze zawierającą: desygnator drogi, minimalny poziom lotu, maksymalny poziom lotu, kierunek magnetyczny drogi. Należy zapewnić klucz główny oraz sprawdzenie poprawności zależności pomiędzy granicami pionowymi dróg i poprawności kierunku magnetycznego.

create table AWY (
id number not null primary key,
route varchar2(255),
min_fl number not null,
max_fl number not null,
mag_hdg number not null
constraint mag_hdg_chk check (mag_hdg >0 and
mag_hdg <=360));




8. Zapisz polecenie SQL tworzące tabelę opisującą poszczególne segmenty dróg lotniczych zawierającą: nazwę punktu początkowego i końcowego segmentu, minimalny poziom lotu, maksymalny poziom lotu. Należy zapewnić klucz główny oraz klucz obcy tworzący powiązanie segmentów dróg lotniczych z drogami lotniczymi (z tabeli z zad.7)





GRUPA 2


1. Zapisz polecenie SQL pokazujące desygnatory ICAO operatorów lotniczych przechowywanych w tabeli atc.flight bez powtórzen wartości.

select distinct oper from atc.flight;



2. Zapisz polecenia SQL ustawiające bieżący schemat sesji na AMC oraz format daty dający wyniki w postaci 31/12/02 23:59.

alter session set nls_date_format='DD/MM/YY HH24:MI';



3. Zapisz polecenie SQL pokaujące dane dla lotów z wybranego dnia, objętego zakresem danych, wykonanych pomiędzy lotniskami EPWA i EPPO. Projekcja powinna zawierać następujące dane: klucz główny, znak wywoławczy, lotnisko startu, lotnisko lądowania, typ samolotu oraz długość trasy.

select cid, fl_id, dep_drom, dest_drom, arc_type, tot_fp_dist
from flight where dof='02-09-2002'
and (
(dep_drom='EPWA' and dest_drom='EPPO')
or (dep_drom='EPPO' and dest_drom='EPWA')
);



4. Zapisz polecenie SQL pokazujące loty wykonane z prędkościami przelotowymi powyżej średniej dla wybranego dnia objętego zakresem danych. Projekcja powinna zawierać następujące dane: klucz główny, znak wywoławczy, lotnisko startu, lotnisko lądowania, typ samolotu oraz prędkość przelotową przeliczoną na km/h i zaokrągloną do liczby całkowitej.

select cid, fl_id, dep_drom, dest_drom, arc_type,
round(true_speed) as km
from flight where
true_speed > (select avg(true_speed) from flight)
and dof='02-09-2002';


5. Zapisz polecenie SQL pokazujące segmenty trasy lotów tranzytowych (bez lądowania w FIR EPWW) z wybranego dnia, objętego zakresem danych wykonane przez operatora lotniczego o desygnatorze IATA innym niż LOT. Projekcja powinna zawierać następujące dane ze złączonych tabel flight i flight_segment: znak wywoławczy, punkt początkowy segmentu trasy, punkt końcowy segmentu trasy, prędkość przelotową oraz oraz sinus kursu magnetycznego. Wyniki wyszukiwania posortować według numeru rejsu i czasu na końcu segmentu.

select f.fl_id, s.start_point, s.end_point,
true_speed, sin(ground_heading*3.1416/180)
from flight f, flight_segment s
where s.id_flight=f.id_flight
and f.dof='02-09-2002'
and not (oper) = 'LOT'
order by f.fl_id, s.actual_t_point;



6. Zapisz polecenie SQL pokazujące średnią długość trasy oraz sumę długości tras dla poszczególnych operatorów lotniczych, którzy wykonali conajmniej 50 operacji lotniczych w poszczególne dni dla całego zakresu danych. Projekcja powinna poza wyliczanymi wartościami zawierać kolumny które zostały wykorzystane do grupowania danych. Wartości średnie należy zaokrąglić do liczby całkowitej. Kolumny zawierające wyliczone wartości nazwać kolejno: AVG i SUM.

select oper, dof, round(avg(tot_fp_dist)) as AVG,
sum(tot_fp_dist) as SUM
from flight
group by dof, oper having count(*) > 50
order by oper;



7. Zapisz polecenie SQL tworzące tabelę opisującą elastyczne elementy przestrzeni powietrznej (podlegające rezerwacji) zawierającą: desygnator, rodzaj elementu (TSA, TRA, etc.), minimalną i maksymalną wysokość. Należy zapewnić klucz główny tabeli oraz sprawdzenie poprawności zależności pomiędzy wysokościami.

8. Zapisz polecenie SQL tworzące tabelę opisującą rezerwacje elastycznych elementów przestrzeni powietrznej zawierającą: czas początku i końca rezerwacji oraz minimalną i maksymalną wysokość. Należy zapewnić: sprawdzenie zależności pomiędzy czasami rezerwacji, klucz główny oraz klucz obcy tabeli tworzący powiązanie rezerwacji z elementami przestrzeni powietrznej (z tabeli z zad. 7).