SQL – Zaawansowane operacje – ćwiczenia cz.2

W poprzednim temacie zapoznaliśmy się z podstawowymi operacjami bazo-danowymi. Teraz już czas zmierzyć się z bardziej zaawansowaną składnią poleceń umożliwiającą głębszą manipulację zagregowanymi w bazie danych wartościami.

Ćwiczenie 1: Zakładając, że urzędnikom odciąga się 25% pensji na podatek dochodowy, oblicz wartość tego podatku i wyświetl wraz z kolumną ZAROBEK.

Pokaż kod

SELECT ZAROBEK,(ZAROBEK * 0.25) FROM "PRACOWNICY" WHERE STANOWISKO='URZĘDNIK';

Ćwiczenie 2. Wylicz dochody za cały rok pracowników obejmujących stanowisko sprzedawcy. Dane te wyświetl wraz z ID_Pracownika, Imie, Nazwisko, Stanowisko, Zarobek.

Pokaż kod

SELECT ID_PRACOWNIKA,IMIE,NAZWISKO,ZAROBEK,12*ZAROBEK FROM "PRACOWNICY" WHERE STANOWISKO='SPRZEDAWCA';

Ćwiczenie 3: Wyświetl średnią pensję sprzedawców

W tym celu musimy już sięgnąć do funkcji SQL, czyli znać ich nazwę, funkcję i sposób użycia. Tak więc dla tego zadania wyliczenie średniej wymaga posiadania informacji o sumie i o ilości.

SELECT ROUND(SUM(ZAROBEK)/COUNT(ZAROBEK),2) FROM "PRACOWNICY" WHERE STANOWISKO='SPRZEDAWCA';

SELECT AVG(ZAROBEK) FROM PRACOWNICY WHERE STANOWISKO='SPRZEDAWCA';

//lub

SELECT ROUND(AVG(ZAROBEK),2) FROM PRACOWNICY GROUP BY STANOWISKO HAVING STANOWISKO = 'SPRZEDAWCA';

Ćwiczenie 4: Do tabeli PRACOWNICY dodaj kolumnę PROWIZJA, która będzie przechowywać wartości tego samego typu co ZAROBEK.

[ppwp passwords=”P4$$4″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]

ALTER TABLE PRACOWNICY ADD PROWIZJA FLOAT;

[/ppwp]

Ćwiczenie 5: Dla pracowników obejmujących stanowiska sprzedawców, wypełnij kolumnę PROWIZJA wartością 300.

[ppwp passwords=”P4$$5″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]

UPDATE PRACOWNICY SET PROWIZJA=300 WHERE STANOWISKO='SPRZEDAWCA';

[/ppwp]

Ćwiczenie 6: Wyświetl roczny dochód sprzedawców (dochód obejmuje także prowizję). Kolumna zawierająca wartości dochodu musi mieć nazwę. Wyświetl także kolumny: Imie, Nazwisko, Stanowisko, Zarobek.

Ćwiczenie wymaga znajomości słowa AS do nadawania nazw aliasów kolumn.

[ppwp passwords=”P4$$6″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]

Ćwiczenie można zrozumieć dwojako – wyświetl roczny dochód sprzedawców (wszystkich) można np. zapisać kodem jak poniżej:

SELECT 12*(SUM(ZAROBEK)+SUM(PROWIZJA)) AS "Roczny Dochód" FROM PRACOWNICY WHERE STANOWISKO='SPRZEDAWCA';

Można też wylistować kolejno sprzedawców i każdemu wyliczyć wartości

SELECT IMIE, NAZWISKO, STANOWISKO,ZAROBEK,12*(ZAROBEK+PROWIZJA) "ROCZNY DOCHÓD" FROM PRACOWNICY WHERE STANOWISKO='SPRZEDAWCA';

Jak widać wyliczana wartość kolumny ma nazwę „ROCZNY DOCHÓD”. Jeśli byłoby to jedno słowo, to nie trzeba obramować zapisu „”, ale wtedy z automatu nazwa zostanie podniesiona do dużych liter. Tak samo słowo AS może zostać pominięte.

[/ppwp]

Ćwiczenie 7: Wyświetl pensję sprzedawców (z uwzględnieniem prowizji) jako kolumnę o nazwie DOCHOD_MIESIACA oraz Imie, Nazwisko, Stanowisko, Zarobek.

[ppwp passwords=”P4$$7″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]

SELECT IMIE, NAZWISKO, STANOWISKO,ZAROBEK AS PENSJA,(ZAROBEK+PROWIZJA) AS DOCHOD_MIESIACA FROM PRACOWNICY WHERE STANOWISKO='SPRZEDAWCA';

[/ppwp]

Ćwiczenie 8: Wyświetl pracowników, których wartość prowizji jest większa niż 20% pensji.

[ppwp passwords=”P4$$8″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]

SELECT IMIE, NAZWISKO, STANOWISKO,ZAROBEK AS PENSJA FROM PRACOWNICY WHERE PROWIZJA>ZAROBEK*0.20;

[/ppwp]

Ćwiczenie 9: Wyświetl dane dotyczące sprzedawców: ID_Pracownika, Imie, Zarobek, Prowizja, różnica między pensją a wartością prowizji. Uporządkuj wyniki według tej różnicy.

[ppwp passwords=”P4$$9″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]

SELECT ID_PRACOWNIKA, IMIE, ZAROBEK, PROWIZJA, ZAROBEK-PROWIZJA AS ROZNICA FROM PRACOWNICY WHERE STANOWISKO='SPRZEDAWCA' ORDER BY ROZNICA;

[/ppwp]

Ćwiczenie 10: Uporządkuj dane z poprzedniego zapytania według Nazwiska, nie używając we właściwym poleceniu nazwy kolumny.

[ppwp passwords=”P4$$10″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]

SELECT ID_PRACOWNIKA, IMIE, NAZWISKO, ZAROBEK, PROWIZJA,ZAROBEK-PROWIZJA as ROZNICA FROM PRACOWNICY WHERE STANOWISKO='SPRZEDAWCA' ORDER BY 3;

lub w pełni poprawnie za pomocą join co daje nam przecięcie po obu zbiorach i wyciągnięcie z nich części wspólnej.

SELECT * FROM PRACOWNICY JOIN (SELECT ID_PRACOWNIKA,ZAROBEK - PROWIZJA AS ROZNICA FROM PRACOWNICY WHERE STANOWISKO='SPRZEDAWCA') USING(ID_PRACOWNIKA) ORDER BY 3;

[/ppwp]

Ćwiczenie 11: Pogrupuj pracowników według numeru działu. Wyświetl te dane wraz z liczebnością grup.

[ppwp passwords=”P4$$11″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]

SELECT NR_DZIALU, COUNT(NR_DZIALU) "LICZEBNOSC" FROM PRACOWNICY GROUP BY NR_DZIALU;

[/ppwp]

Ćwiczenie 12: Wyświetl nazwę stanowiska oraz liczbę osób na danym stanowisku

[ppwp passwords=”P4$$12″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]

SELECT STANOWISKO, COUNT(STANOWISKO) "Liczba Osób" FROM PRACOWNICY GROUP BY STANOWISKO;

[/ppwp]

Ćwiczenie 13: Ile wynosi średnia pensja w każdym dziale. Wyniki uporządkuj według numeru działu.

[ppwp passwords=”P4$$13″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]

SELECT NR_DZIALU, AVG(ZAROBEK) FROM PRACOWNICY GROUP BY NR_DZIALU ORDER BY NR_DZIALU;

//lub

SELECT D.NR_DZIALU, D.NAZWA_DZIALU, ROUND(AVG(P.ZAROBEK),2) AS SREDNI_ZAROBEK_DZIALU FROM DZIAL D INNER JOIN PRACOWNICY P ON (D.NR_DZIALU=P.NR_DZIALU) GROUP BY D.NAZWA_DZIALU,D.NR_DZIALU ORDER BY D.NR_DZIALU;

[/ppwp]

Ćwiczenie 14: Wyświetl stanowiska występujące w dziale numer 30 oraz liczbę pracowników obejmujących te stanowiska. (Pogrupuj pracowników, którzy pracują w dziale numer 30, według numeru działu i stanowiska. Wyświetl te dane wraz z liczebnością grup.)

[ppwp passwords=”P4$$14″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]

SELECT STANOWISKO, COUNT(NR_DZIALU) "w Dziale Handlowym pracuje:" FROM PRACOWNICY GROUP BY NR_DZIALU, STANOWISKO HAVING NR_DZIALU = 30;

lub:

SELECT STANOWISKO, COUNT(NR_DZIALU) "w Dziale Handlowym pracuje:" FROM PRACOWNICY WHERE NR_DZIALU = 30 GROUP BY NR_DZIALU, STANOWISKO;

Możemy tez posortować wyniki według liczebności grup z wykorzystaniem nazwy aliasowej:

SELECT STANOWISKO, COUNT(NR_DZIALU) "w Dziale Handlowym pracuje:" FROM PRACOWNICY WHERE NR_DZIALU = 30 GROUP BY NR_DZIALU, STANOWISKO ORDER BY "w Dziale Handlowym pracuje:";

lub z wykorzystaniem INNER JOIN:

SELECT STANOWISKO,COUNT(*) AS LICZBA_PRACOWNIKÓW FROM DZIAL D INNER JOIN PRACOWNICY P ON (D.NR_DZIALU=P.NR_DZIALU) WHERE D.NR_DZIALU=30 GROUP BY NAZWA_DZIALU,STANOWISKO ORDER BY LICZBA_PRACOWNIKÓW;

[/ppwp]

Ćwiczenie 15: Wyświetl wszystkie stanowiska z wyjątkiem kierownika. Określ ile osób pracuje na danym stanowisku i jaka jest średnia pensja na danym stanowisku.

[ppwp passwords=”P4$$15″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]

SELECT STANOWISKO, COUNT(STANOWISKO), AVG(ZAROBEK) FROM PRACOWNICY WHERE STANOWISKO!='KIEROWNIK' GROUP BY STANOWISKO;

//lub

SELECT STANOWISKO, COUNT(STANOWISKO), AVG(ZAROBEK) FROM PRACOWNICY WHERE STANOWISKO<>'KIEROWNIK' GROUP BY STANOWISKO;

lub bardziej dopracować odpowiedź, używając nazw aliasowych i sortując od największego zarobku do najmniejszego:

SELECT STANOWISKO, COUNT(STANOWISKO) "Pracuje osób:", AVG(ZAROBEK) "Średnia pensja" FROM PRACOWNICY WHERE STANOWISKO!='KIEROWNIK' GROUP BY STANOWISKO ORDER BY "Średnia pensja" DESC;

Odnosząc się do kolejności sortowania, można jeszcze pamiętać, że do ASC¹w kolejności rosnącej, standardowe ustawienie i nie trzeba go podawać, DESC²w kolejności malejącej są także dodatkowe parametry takie jak NULLS FIRST lub NULLS LAST.

SELECT STANOWISKO, COUNT(STANOWISKO) "Pracuje osób:", AVG(ZAROBEK) "Średnia pensja" FROM PRACOWNICY WHERE STANOWISKO!='KIEROWNIK' GROUP BY STANOWISKO ORDER BY "Średnia pensja" DESC NULLS LAST;

Inne możliwe zapytania mogą przybierać postać:

SELECT STANOWISKO, COUNT(STANOWISKO), AVG(ZAROBEK) FROM PRACOWNICY GROUP BY STANOWISKO HAVING STANOWISKO != 'KIEROWNIK';

//lub

SELECT STANOWISKO, COUNT(ID_PRACOWNIKA), AVG(ZAROBEK) FROM PRACOWNICY WHERE NOT STANOWISKO = 'KIEROWNIK' GROUP BY STANOWISKO;

[/ppwp]

Zadanie: Z przygotowanych wyżej ćwiczeń sporządź raport w postaci dokumentu pdf i załącz go w Moodle. Raport powinien zawierać numer ćwiczenia, odpowiadający mu kod SQL i krótki opis wykonywanej czynności.