W poprzednim temacie zapoznaliśmy się z pierwszą częścią operacji zaawansowanych bazo-danowymi. Teraz już czas zająć się drugą częścią ćwiczeń. Dalej pracujemy na BD stworzonej na wzór poniższych tabel.
Ćwiczenie 16: Wyświetl ile osób zajmuje poszczególne stanowiska, z wyjątkiem stanowiska prezesa. W poleceniu SELECT zastosuj kolejność kolumn: ‘średnia pensja=’, średnia pensja, liczba pracowników na stanowisku, ‘pracownik(ów) na stanowisku’, stanowisko.
[ppwp passwords=”P416″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]
SELECT 'Średnia_pensja=', ROUND(AVG(ZAROBEK),2) AS ŚREDNIA_PENSJA, COUNT(STANOWISKO) AS " ", 'pracowników na stanowisku', STANOWISKO FROM PRACOWNICY WHERE STANOWISKO!='PREZES' GROUP BY STANOWISKO;
[/ppwp]
Ćwiczenie 17: Pokaż na dwa sposoby instrukcję wyświetlającą pracowników pracujących w dziale 10 lub w dziale 30.
[ppwp passwords=”P417″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]
SELECT * FROM PRACOWNICY WHERE NR_DZIALU=10 OR NR_DZIALU=30;
//lub
SELECT * FROM PRACOWNICY WHERE (NR_DZIALU BETWEEN 10 AND 30) AND NR_DZIALU!=20;
//lub
SELECT * FROM PRACOWNICY WHERE NR_DZIALU IN ('10', '30');
//lub
SELECT * FROM PRACOWNICY WHERE NR_DZIALU LIKE '10' OR NR_DZIALU LIKE '30';
[/ppwp]
Ćwiczenie 18: Wyświetl pracowników, których pensje są równe prowizji.
[ppwp passwords=”P418″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]
SELECT * FROM PRACOWNICY WHERE ZAROBEK = PROWIZJA;
[/ppwp]
Ćwiczenie 19: Pokaż na dwa sposoby instrukcję wyświetlającą pracowników, których imiona to Marcin lub Wojtek lub Adam. Wiersze uporządkuj według imienia, nie wykorzystując nazwy tej kolumny.
[ppwp passwords=”P419″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]
SELECT * FROM PRACOWNICY WHERE IMIE='MARCIN' OR IMIE='WOJTEK' OR IMIE='ADAM' ORDER BY 2;
//lub
SELECT * FROM PRACOWNICY WHERE IMIE IN ('MARCIN', 'WOJTEK','ADAM') ORDER BY 2;
//lub
SELECT * FROM PRACOWNICY JOIN (SELECT * FROM PRACOWNICY WHERE IMIE='MARCIN' OR IMIE='WOJTEK' OR IMIE='ADAM') USING(ID_PRACOWNIKA) ORDER BY 2;
[/ppwp]
Ćwiczenie 20: Pokaż na dwa sposoby instrukcję wyświetlającą pracowników, których pensja jest w przedziale od 2000 do 3000.
[ppwp passwords=”P420″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]
SELECT * FROM PRACOWNICY WHERE ZAROBEK BETWEEN 2000 AND 3000; //lub SELECT * FROM PRACOWNICY WHERE ZAROBEK >= 2000 AND ZAROBEK <= 3000;
[/ppwp]
Ćwiczenie 21: Pokaż na dwa sposoby instrukcję wyświetlającą pracowników, którzy pracują w dziale 30 lub 10 i których nazwisko kończy się na ”er”.
[ppwp passwords=”P421″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]
SELECT * FROM PRACOWNICY WHERE (NR_DZIALU=10 OR NR_DZIALU=30) AND NAZWISKO LIKE '%ER%';
To zapytanie powyżej nie jest idealne, ponieważ znajduje wszystkich pracowników zawierających w nazwisku ciąg „er”. Nie można użyć like '%%ER'; bowiem typ danych char ma stałą długość i wypełniany jest spacjami do pełnego, zarezerwowanego wymiaru (tutaj: 60 bajtów).
SELECT * FROM PRACOWNICY WHERE (NR_DZIALU=10 OR NR_DZIALU=30) AND NAZWISKO LIKE '%ER %'; //lub SELECT * FROM PRACOWNICY WHERE (NR_DZIALU=10 OR NR_DZIALU=30) AND RTRIM(NAZWISKO) like '%ER'; //lub SELECT ID_PRACOWNIKA, IMIE, NAZWISKO, STANOWISKO, NR_DZIALU FROM PRACOWNICY WHERE REGEXP_LIKE(NAZWISKO, '[A-Z]ER *') AND NR_DZIALU = 10 UNION SELECT ID_PRACOWNIKA, IMIE, NAZWISKO, STANOWISKO, NR_DZIALU FROM PRACOWNICY WHERE REGEXP_LIKE(NAZWISKO, '[A-Z]ER *') AND NR_DZIALU = 30;
[/ppwp]
Ćwiczenie 22: Wyświetl pracowników, których ID zaczyna się od 77.
[ppwp passwords=”P422″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]
SELECT * FROM PRACOWNICY WHERE ID_PRACOWNIKA LIKE '77%';
[/ppwp]
Ćwiczenie 23: Wypisz nazwy działów, w których pracują urzędnicy.
[ppwp passwords=”P423″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]
SELECT DISTINCT(NAZWA_DZIALU) FROM DZIAL LEFT JOIN PRACOWNICY ON DZIAL.NR_DZIALU=PRACOWNICY.NR_DZIALU WHERE STANOWISKO='URZĘDNIK'; //lub SELECT DISTINCT(NAZWA_DZIALU) FROM DZIAL INNER JOIN PRACOWNICY ON DZIAL.NR_DZIALU = PRACOWNICY.NR_DZIALU WHERE STANOWISKO = 'URZĘDNIK'; //lub SELECT DISTINCT(D.NAZWA_DZIALU) , P.STANOWISKO FROM PRACOWNICY P, DZIAL D WHERE P.NR_DZIALU = D.NR_DZIALU AND P.STANOWISKO = 'URZĘDNIK'; //lub SELECT NAZWA_DZIALU FROM DZIAL D FULL JOIN PRACOWNICY P ON (D.NR_DZIALU=P.NR_DZIALU) WHERE P.STANOWISKO='URZĘDNIK' GROUP BY D.NAZWA_DZIALU;
[/ppwp]
Ćwiczenie 24: Wypisz listę pracowników dołączając informacje o ich działach. Wyniki posortuj wg numeru działu.
[ppwp passwords=”P424″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]
SELECT ID_PRACOWNIKA, IMIE, NAZWISKO, STANOWISKO, ZAROBEK, PROWIZJA, DZIAL.NR_DZIALU, DZIAL.NAZWA_DZIALU, DZIAL.LOKALIZACJA FROM DZIAL LEFT JOIN PRACOWNICY ON DZIAL.NR_DZIALU=PRACOWNICY.NR_DZIALU ORDER BY NR_DZIALU; //lub SELECT IMIE,NAZWISKO,DZIAL.* FROM PRACOWNICY JOIN DZIAL ON PRACOWNICY.NR_DZIALU=DZIAL.NR_DZIALU ORDER BY DZIAL.NR_DZIALU; //lub SELECT * FROM PRACOWNICY P LEFT OUTER JOIN DZIAL D ON P.NR_DZIALU=D.NR_DZIALU ORDER BY P.NR_DZIALU; //lub SELECT P.ID_PRACOWNIKA,P.NR_DZIALU,P.IMIE,P.NAZWISKO,P.STANOWISKO,P.ZAROBEK,D.NAZWA_DZIALU,D.LOKALIZACJA FROM PRACOWNICY P FULL JOIN DZIAL D ON (P.NR_DZIALU=D.NR_DZIALU) ORDER BY D.NR_DZIALU; //lub SELECT * FROM PRACOWNICY INNER JOIN DZIAL ON DZIAL.NR_DZIALU = PRACOWNICY.NR_DZIALU ORDER BY PRACOWNICY.NR_DZIALU; //lub SELECT p.IMIE,p.NAZWISKO,d.NR_DZIALU,d.NAZWA_DZIALU,d.LOKALIZACJA FROM PRACOWNICY p, DZIAL d WHERE p.nr_dzialu = d.nr_dzialu ORDER BY d.NR_DZIALU;
[/ppwp]
Ćwiczenie 25: Wypisz nazwy wszystkich działów oraz nazwiska ich kierowników.
[ppwp passwords=”P425″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]
SELECT NAZWA_DZIALU, NAZWISKO FROM DZIAL LEFT JOIN PRACOWNICY ON DZIAL.NR_DZIALU=PRACOWNICY.NR_DZIALU WHERE STANOWISKO='KIEROWNIK' OR NAZWA_DZIALU='OPERACJE'; //lub SELECT D.NAZWA_DZIALU, A.NAZWISKO FROM (SELECT NR_DZIALU,NAZWISKO FROM PRACOWNICY WHERE STANOWISKO='KIEROWNIK') A FULL OUTER JOIN DZIAL D ON A.NR_DZIALU=D.NR_DZIALU;
[/ppwp]
Ćwiczenie 26: Którzy pracownicy zarabiają mniej niż wynosi średnia pensja?
[ppwp passwords=”P426″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]
SELECT * FROM PRACOWNICY WHERE ZAROBEK < (SELECT AVG(ZAROBEK) FROM PRACOWNICY);
[/ppwp]
Ćwiczenie 27: Wskaż pracowników, którzy pracują w Warszawie
[ppwp passwords=”P427″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]
SELECT * FROM PRACOWNICY LEFT JOIN DZIAL ON PRACOWNICY.NR_DZIALU=DZIAL.NR_DZIALU WHERE LOKALIZACJA='WARSZAWA'; //lub SELECT ID_PRACOWNIKA,IMIE,NAZWISKO FROM DZIAL INNER JOIN PRACOWNICY ON DZIAL.NR_DZIALU=PRACOWNICY.NR_DZIALU WHERE LOKALIZACJA='WARSZAWA';
[/ppwp]
Ćwiczenie 28: Podaj nazwiska pracowników zarabiających więcej niż wynosi średnia pensja.
[ppwp passwords=”P416″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]
SELECT NAZWISKO FROM PRACOWNICY WHERE ZAROBEK > (SELECT AVG(ZAROBEK) FROM PRACOWNICY);
[/ppwp]
Ćwiczenie 29: Podaj sumę pensji wszystkich pracowników pracujących w Katowicach
[ppwp passwords=”P416″ description=”” label=”Rozwiązanie” headline=”” button=”Odblokuj” error_msg=”nieprawidłowy kod…”]
SELECT SUM(ZAROBEK) AS SUMA_PENSJI FROM PRACOWNICY LEFT JOIN DZIAL ON PRACOWNICY.NR_DZIALU=DZIAL.NR_DZIALU WHERE LOKALIZACJA='KATOWICE';
[/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.