Zapytania – ćwiczenia 2
1. Podaj nazwy najczęściej używanych funkcji agregujących. W jakich klauzulach możemy ich używać?
Funkcje agregujące:
Dozwolone klauzule:
Wykonaj zapytania:
• Znajdź maksymalną spośród wszystkich pensji (wypłacanych obecnie lub kiedyś).
Odp.: 5000
• Znajdź maksymalną i minimalną spośród wszystkich pensji (wypłacanych obecnie lub kiedyś).
Odp.: 5000 i 1300
• Znajdź różnicę pomiędzy najwyższą i najniższą pensją. Weź pod uwagę wszystkie pensje – wypłacane obecnie lub kiedyś).
Odp.: 3700
• Znajdź średnią z bieżących (aktualnie wypłacanych) pensji.
Odp.: 2693,478260869565217391304347826086956522
• Znajdź średnią z bieżących (aktualnie wypłacanych) pensji. Wynik zaokrąglij do dwóch miejsc po przecinku.
Wybraną kolumnę zatytułuj „Aktualna średnia pensja”.
Odp.: 2693,48
• Znajdź aktualną maksymalną, minimalną i średnią pensję wśród pracowników zatrudnionych aktualnie na wydziale o identyfikatorze 1.
Odp.: 4000 1500 2333,333333333333333333333333333333333333
• Wskaż najwcześniejszą i najpóźniejszą z dat urodzenia. Spraw, aby daty zostały wyświetlone w formacie
„dzień (dwucyfrowo) nazwa miesiąca (małymi literami) rok (czterocyfrowo)”. Nadaj kolumnom aliasy.
Najwcześniejsza data Najpóźniejsza data
-------------------------------------- ------------------------
16 listopad 1943 12 czerwiec 1985
1 rows selected
• Podaj sumę aktualnych pensji pracowników zatrudnionych aktualnie na wydziale o identyfikatorze 2.
Odp.: 22050
• Podaj liczbę osób, których dane są przechowywane w tabeli OSOBY. Kolumnę zatytułuj LICZBA_OSÓB.
LICZBA_OSÓB
----------------------
30
1 rows selected
• Podaj liczbę kobiet.
Odp.: 18
• Oblicz ile jest różnych nazwisk.
Odp.: 28
• Oblicz ile osób urodziło się w listopadzie.
Odp.: 6
• Oblicz ile osób podało swoje drugie imię.
Odp.: 6
2. Przypomnij która klauzula służy do podziału wierszy na grupy. Czy jest ona obowiązkowa? Jak wpływa zastosowanie tej klauzuli na zawartość listy SELECT?
Klauzula:
Czy obowiązkowa:
Wykonaj zapytania:
• Oblicz aktualną minimalną i maksymalną pensję dla każdego z wydziałów. Nadaj kolumnom nagłówki:
„minimalna pensja”, „maksymalna pensja”.
ID_W minimalna pensja maksymalna pensja
---------------------- ---------------------- ----------------------
1 1500 4000
2 1900 3600
3 2000 4500
4 2000 5000
5 1400 3000
5 rows selected
• Oblicz aktualną średnią pensję oraz liczbę pracowników dla każdego z wydziałów. Nadaj kolumnom nagłówki: „ŚREDNIA PENSJA”, „LICZBA PRACOWNIKÓW”. Uporządkuj wyniki malejąco według liczby pracowników.
ID_W ŚREDNIA PENSJA LICZBA PRACOWNIKÓW
-------------- ---------------------------------------- ----------------------
2 2450
9
4 3725
4
5 2250
4
1 2333,333333333333333333333333333333333333 3
3 3000 3
5 rows selected
• Oblicz sumę aktualnych pensji na każdym ze stanowisk na wydziale o identyfikatorze 3. Uporządkuj wyniki rosnąco według identyfikatorów stanowisk. Kolumnie z sumami nadaj nagłówek „SUMA_PENSJI”.
ID_S SUMA_PENSJI
---------------------- ----------------------
1 2000
2 2500
3 4500
3 rows selected
• Oblicz aktualną średnią pensję na każdym ze stanowisk na każdym z wydziałów. Wyniki uporządkuj
rosnąco według identyfikatorów stanowisk, a następnie, w przypadku takich samych identyfikatorów stanowisk, rosnąco według identyfikatorów wydziałów. Trzeciej z kolumn nadaj odpowiedni nagłówek.
ID_S ID_W ŚREDNIA_PENSJA
---------------------- ---------------------- ----------------------
1 1 1500
1 2 1960
1 3 2000
1 5 2000
2 2 2625
2 3 2500
2 4 2000
2 5 3000
3 1 4000
3 2 3500
3 3 4500
3 4 4300
12 rows selected
• Dla każdej litery, która rozpoczyna nazwisko którejś z osób, wyświetl liczbę osób o nazwiskach rozpoczynających się od tej litery. Wyniki uporządkuj alfabetycznie według liter. Pierwszą kolumnę zatytułuj
„LITERA”, a drugą „LICZBA OSÓB”.
LITERA LICZBA OSÓB
------ ----------------------
C 1
D 4
G 2
K 6
L 2
M 1
N 3
P 3
S 2
T 2
W 4
11 rows selected
3. Przypomnij do czego służy klauzula HAVING. Czy jest ona obowiązkowa? Jaka klauzula musi jej towarzyszyć?
Zastosowanie:
Czy obowiązkowa:
Klauzula poprzedzająca:
Wykonaj zapytania:
• Znajdź identyfikatory wydziałów, w których aktualnie pracują więcej niż 4 osoby.
ID_W
----------------------
2
1 rows selected
• Znajdź identyfikatory wydziałów, dla których aktualna średnia pensja jest mniejsza bądź równa 2700.
ID_W
----------------------
1
2
5
3 rows selected
• Podaj identyfikatory wydziałów, w których aktualna maksymalna pensja wśród osób o identyfikatorze stanowiska równym 3 jest równa 3600.
ID_W
----------------------
2
1 rows selected
• Znajdź identyfikatory osób (pracujących obecnie lub nie), które występują w więcej niż jednym wierszu tabeli Zatrudnienia. Co to oznacza?
ID_OS
----------------------
2
10
12
13
15
19
21
23
8 rows selected
• Znajdź identyfikatory wydziałów, w których średnia aktualnych pensji jest większa bądź równa 1400, ale mniejsza bądź równa 2300.
ID_W
----------------------
5
1 rows selected
Do domu
• Wskaż wiek najmłodszej i najstarszej z osób. Kolumny zatytułuj NAJMŁODSZY i NAJSTARSZY.
NAJMŁODSZY NAJSTARSZY
---------------------- ----------------------
27 69
1 rows selected
Wynik na dzień 01.12.2012.
• Wskaż wiek najmłodszego i najstarszego ze mężczyzn. Kolumny zatytułuj: NAJMŁODSZY, NAJSTARSZY.
NAJMŁODSZY NAJSTARSZY
---------------------- ----------------------
27 64
1 rows selected
Wynik na dzień 01.12.2012.
• Oblicz ile osób pracuje aktualnie na stanowisku o identyfikatorze 1.
Odp.: 11
• Oblicz ile osób nie podało swojego drugiego imienia.
Odp.: 24
• Oblicz ile osób urodziło się później niż 1 czerwca 1980 roku.
Odp.: 8
• Oblicz ile jest wierszy przechowujących pensje większe od 3000.
Odp.: 10
• Oblicz ile jest różnych wartości pensji (wypłacanych obecnie lub kiedyś).
Odp:. 21
• Oblicz ile osób obchodzi swoje urodziny w drugim kwartale roku.
Wskazówka: zobacz dopuszczalne formaty daty w funkcji TO_CHAR (plik Wybrane funkcje Oracle 10) Odp:. 9
• Oblicz ile osób urodziło się w niedzielę.
Odp:. 3
Wskazówka: aby obciąć spacje należy użyć funkcji TRIM.
• Sporządź statystykę:
Id stanowiska
Minimalna pensja
Średnia pensja
Maksymalna pensja
Weź pod uwagę tylko aktualne pensje. Zaokrąglij średnią pensję do dwóch miejsc po przecinku.
Id stanowiska Minimalna pensja Średnia pensja Maksymalna pensja
--------------- -------------------- ---------------------- ----------------------
1 1400 1890,91 2500
2 2000 2550 3000
3 3400 4057,14 5000
3 rows selected
• Policz liczbę osób zatrudnionych aktualnie na każdym ze stanowisk. Wyniki uporządkuj rosnąco ze względu na liczbę pracowników. Nadaj alias kolumnie z liczbą pracowników.
ID_S Liczba pracowników
---------------------- ----------------------
2 5
3 7
1 11
3 rows selected
• Oblicz aktualną średnią pensję oraz aktualną liczbę pracowników dla każdego ze stanowisk. Weź pod uwagę tylko pracowników z wydziałów o identyfikatorach 1 i 4. Nadaj kolumnom odpowiednie aliasy.
ID_S Średnia pensja Liczba pracowników
---------------------- ---------------------- ----------------------
1 1500 2
2 2000 1
3 4225 4
3 rows selected
• Oblicz sumę aktualnych pensji na każdym z wydziałów. Nadaj kolumnie liczbowej alias SUMA PENSJI.
Uporządkuj wyniki rosnąco według sum.
ID_W SUMA PENSJI
---------------------- ----------------------
1 7000
3 9000
5 9000
4 14900
2 22050
5 rows selected
• Policz liczbę aktualnych pracowników na każdym ze stanowisk na każdym z wydziałów. Uporządkuj wyniki malejąco według liczby pracowników. Nadaj alias ostatniej kolumnie.
ID_S ID_W Liczba pracowników
---------------------- ---------------------- ----------------------
1 2 5
3 4 3
1 5 3
2 2 2
1 1 2
3 2 2
3 3 1
3 1 1
2 3 1
2 5 1
2 4 1
1 3 1
12 rows selected
• Policz aktualną średnią pensję dla poszczególnych wydziałów i poszczególnych stanowisk. Ostatniej kolumnie nadaj alias „Średnia pensja”. Uporządkuj wyniki rosnąco według identyfikatorów wydziałów, a w przypadku takich samych identyfikatorów – rosnąco według identyfikatorów stanowisk.
ID_W ID_S Średnia pensja
---------------------- ---------------------- ----------------------
1 1 1500
1 3 4000
2 1 1960
2 2 2625
2 3 3500
3 1 2000
3 2 2500
3 3 4500
4 2 2000
4 3 4300
5 1 2000
5 2 3000
12 rows selected
• Wyświetl nazwy miesięcy, w których urodziły się jakieś osoby wraz z liczbą osób urodzonych w danym
miesiącu. Uporządkuj wyniki malejąco według liczby osób. Nadaj kolumnom odpowiednie nagłówki.
MIESIAC LICZBA_OSÓB
-------------------------------------------- ----------------------
październik 7
listopad 6
czerwiec 5
lipiec 3
maj 3
grudzień 2
marzec 2
kwiecień 1
sierpień 1
9 rows selected
Wskazówka: należy pogrupować po nazwie miesiąca wyciągniętym z daty
• Podaj identyfikatory stanowisk, w których aktualna minimalna pensja jest równa 2000.
ID_S
----------------------
2
1 rows selected
• Znajdź identyfikatory stanowisk, na których aktualnie pracuje więcej niż 5 osób.
ID_S
----------------------
1
3
2 rows selected
• Znajdź identyfikatory wydziałów, które występują w dokładnie jednym wierszu tabeli Kierownicy. Co to oznacza?
ID_W
----------------------
1
2
3
5
4 rows selected
• Podaj identyfikator wydziału, w którym aktualna średnia pensja osób o identyfikatorze stanowiska 2 jest mniejsza bądź równa 2500.
ID_W
----------------------
3
4
2 rows selected
• Znajdź identyfikatory osób, które które występują w dokładnie jednym wierszu tabeli Zatrudnienia. Co to oznacza? Uporządkuj wyniki rosnąco według identyfikatorów.
ID_OS
----------------------
1
3
4
5
6
7
8
9
11
14
16
17
18
20
22
24
25
26
18 rows selected
• Znajdź te litery alfabetu, dla których liczba osób o nazwiskach rozpoczynających się od tej litery jest większa niż 4. Kolumnę zatytułuj LITERA.
LITERA
------
K
1 rows selected