Grupa A (LATARNIE) Imię i nazwisko: Numer albumu: Nazwisko prowadzącego:

1:

2:

3:

4:

11:

12:

Suma:

5:

6:

7:

8:

Ocena:

Zad. 1 (10 pkt)

Dana jest relacja T. Podaj wynik poniższego zapytania (podaj i opisz również wyniki pośrednie). A 1 2 5 null 3

B 1 null 2 2 null

SELECT NVL(Z.A,0)+NVL(Z.B,0) AS SUMA, COUNT(*) FROM T Y RIGHT OUTER JOIN T Z ON (Y.A = Z.B) WHERE Y.A+NVL(Y.B,0)+NVL(Z.A,0)+Z.B=4 OR Z.B IS NULL GROUP BY NVL(Z.A,0)+NVL(Z.B,0) ORDER BY AVG(NVL(Y.B,2)) DESC

Etap 1: Klauzula:

Etap 2: Klauzula:

Etap 3: Klauzula:

Etap 4: Klauzula:

Etap 5: Klauzula:

9:

10:

Dany jest następujący schemat bazy danych: KRAJE

LATARNIE

MORZA

# ID_KRAJU * NAZWA * KONTYNENT * POWIERZCHNIA

# ID_KRAJU # ID_MORZA # NAZWA * DATA_BUDOWY * WYSOKOŚĆ * CZY_ISTNIEJE o CZY_FUNKCJONUJE

# ID_MORZA * NAZWA o POWIERZCHNIA

Objaśnienia: # – kolumna wchodzi w skład klucza podstawowego relacji * – kolumna obowiązkowa o – kolumna opcjonalna podkreślenie – kolumna wchodzi w skład klucza obcego relacji (klucze obce w relacji LATARNIE: ID_KRAJU do kolumny ID_KRAJU w relacji KRAJE, ID_MORZA do kolumny ID_MORZA w relacji MORZA). Uwaga! Kolumny CZY_ISTNIEJE i CZY_FUNKCJONUJE zawierają jednoliterowy ciąg znaków ('T','N'). Zad. 2 (6 pkt) Podaj polecenie, które utworzy relacje LATARNIE. Relacja ma posiadać następujące własności: •

•

• • • •

kolumna ID_KRAJU jest liczba całkowita o maksymalnej wartości 999 i elementem klucza podstawowego o nazwie LAT_PK oraz kluczem obcym o nazwie LAT_KR_FK, wskazującym na kolumnę ID_KRAJU w relacji KRAJE, - kolumna ID_MORZA jest liczba całkowita o maksymalnej wartosci 999 i elementem klucza podstawowego o nazwie LAT_PK oraz kluczem obcym o nazwie LAT_MR_FK, wskazującym na kolumnę ID_ MORZA w relacji MORZA, kolumna DATA_BUDOWY ma przechowywać daty kolumna WYSOKOŚĆ ma przechowywać liczby całkowite dodatnie o maksymalnej wartości 999 kolumny CZY_FUNKCJONUJE i CZY_ISTNIEJE mają zawierać litery 'T' lub 'N' nie może znajdować się w bazie danych latarnia funkcjonująca a jednocześnie nieistniejąca

Uwaga! Podczas tworzenia relacji weź pod uwagę również informacje pokazane na rysunku!

Zad. 3 (6 pkt) Napisz zapytanie, które dla każdego kontynentu, wyświetli jego nazwę, liczbę znajdujących się w nim krajów, oraz sumaryczną ich powierzchnię. Analizę ogranicz tylko do tych krajów, które znajdują się w bazie danych. Posortuj kontynenty zgodnie z malejącą liczbą znajdujących się w nim krajów.

Zad. 4 (7 pkt) Napisz zapytanie, które dla każdej latarni wyświetli jej nazwę, nazwę kraju, w którym się znajduje, nazwę morza, nad którym się znajduje, datę jej budowy i wysokość. Wynik zapytania ogranicz tylko do tych latarni, które posiadają ponad 25 metrów wysokości i znajdują się w krajach znajdujących się w Europie.

Zad. 5 (7 pkt) Napisz zapytanie, które dla każdego kraju wyświetli jego nazwę, liczbę latarni, jakie znajdują się na jego terytorium, liczbę mórz, nad którymi znajdują się te latarnie i sumaryczną wysokość owych latarń. Analizę ogranicz tylko do tych latarń, które posiadają powyżej 20 metrów wysokości. Wyniki analizy ogranicz tylko do tych krajów, które posiadają mniej niż 50 tego typu latarni. Pamiętaj o wyświetleniu również tych krajów, które nie posiadają takich latarni.

Zad. 6 (8 pkt) Napisz zapytanie, które dla każdego kraju wyświetli jego nazwę, oraz nazwę i wysokość najwyższej latarni, jaka znajduje się na jego terytorium.

Zad. 7 (8 pkt) Napisz zapytanie, które dla kraju, w którym znajduje się największa liczba funkcjonujących latarni, wyświetli jego nazwę oraz stosunek latarni funkcjonujących w danym kraju do wszystkich latarni istniejących w danym kraju.

Zad. 8 (8 pkt) Podaj polecenie, które zmieni atrybut POWIERZCHNIA w relacji MORZA z opcjonalnego na obowiązkowy. Napisz polecenie, doda do powierzchni każdego z mórz wartość wynikającą z liczby krajów posiadających nad danym morzem swoje latarnie, zgodnie z następującym wzorem: nowa powierzchnia = stara powierzchnia + 10 * liczba krajów posiadających nad danym morzem latarnie

Zad. 9 (6 pkt) Usuń informacje o tych latarniach, które znajdują się w Polsce, zostały zbudowane po roku 1956 a przed 1970 i które już nie istnieją.

Zad. 10 (10 pkt) Napisz polecenie tworzące perspektywę LATARNIE_KRAJÓW wyświetlającą dla każdego kraju jego nazwę, kontynent oraz kolumnę LATARNIE. Kolumna LATARNIE powinna zawierać analizę dotyczącą latarni znajdujących się na terytorium danego kraju. Analiza powinna składać się z czterech liczb A; B; C; D; zawierających odpowiednio liczby latarni o wysokościach: 0-15; 16-30; 31-45; powyżej 45 metrów. Jeśli istnieje taka potrzeba, napisz polecenie tworzące odpowiednią funkcję, która następnie zostanie wykorzystana podczas polecenia tworzącego perspektywę. Przykładowy wynik zwracany przez perspektywę zaprezentowany został poniżej. NAZWA POLSKA NIEMCY

KONTYNENT EUROPA EUROPA

LATARNIE 0; 8; 17; 2; 1; 16; 23; 10;

Zad. 11 (12 pkt) Napisz polecenie tworzące wyzwalacz, który modyfikacji atrybutu POWIERZCHNIA w relacji KRAJ, będzie automatycznie aktualizował atrybut POWIERZCHNIA w relacji MORZA. Każda zmiana w relacji KRAJE musi proporcjonalnie modyfikować MORZA, nad którymi znajdują się latarnie danego KRAJU. Przykładowo, jeżeli powierzchnia Anglii zostanie zmniejszona o 2 metry i jeżeli jej latarnie znajdują się nad 3 morzami, to utracona powierzchnia musi spowodować wzrost powierzchni wszystkich trzech mórz o 2/3. Wyzwalacz musi być zdefiniowany na "poziomie krotki" i musi umożliwiać operacje obejmujące swoim zakresem wiele krotek w relacji KRAJE. Wyzwalacz musi uwzględniać tylko modyfikacje powierzchni w relacji KRAJE.

Zad. 12 (12 pkt) W bazie danych, w której istnieje relacja T (z zad. 1) i identyczna z nią relacja U, za pomocą poniższych poleceń zdefiniowano pakiet P i wyzwalacz T_TRIG. create or replace package P as w number; procedure PR(v number); function DAJ_W return number; end; create or replace package body P as procedure PR(v number) is begin update U set A = w where B = v; w := w + SQL%ROWCOUNT; end; function DAJ_W return number is begin return w; end; begin w := nvl(w,0) + 3; end;

create trigger T_TRIG before update on T for each row when (NEW.A 2) begin P.PR(:NEW.B-:OLD.B); end;

Podaj wyniki poniższych zapytań: 1. SELECT * FROM U; 2. SELECT P.DAJ_W FROM DUAL;

jeżeli wcześniej wykonano polecenie: UPDATE T SET B = B+B WHERE A is null or A < 3;

Podaj pośrednie wartości zmiennej W.