Kurs SQL. Zadanie 11. Pokaż wszystkich pracowników których bezpośrednim przełożonym jest Leopold Banko.

Czego się dowiesz czytając ten wpis:

  • poznasz polecenie JOIN,
  • poznasz wyrażenie IS NULL,
  • powtórzysz wiedzę nt. ALIASÓW i WHERE.

Zadanie 11.

Pokaż wszystkich pracowników których bezpośrednim przełożonym jest Leopold Banko. Wyniki przedstaw w konwencji imię, nazwisko w jednej kolumnie i imię i nazwisko przełożonego w drugiej kolumnie.

Rozwiązanie:

                Jedna z pierwszych czynności jest określenie źródła danych, czyli:

FROM
   Pracownicy

    Zastanówmy się w jaki sposób uzyskać informację na temat kto jest szefem danego pracownika i odpowiednio je przefiltrować. Informacje kto jest szefem kogo znajduje się w kolumnie „ID_przelozonego”. Najszybszą metodą jest przefiltrowanie tabeli pracownicy po identyfikatorze/ID Pana Bańko i z głowy. Ale jak zrobić, żeby skorzystać z jego nazwiska? Tutaj z pomocą przyjdzie nam polecenie JOIN i taka fajna sztuczka. Otóż istnieje możliwość „jakby zdublowania” tabeli „Pracownicy” i połączenie obu tabel.  Dla rozróżnienia obu tabel nadajmy im aliasy. Pierwszej nadajmy alias „P” a drugiej alias „SZEF”. Czyli reasumując mamy teraz dwie tabele „Pracownicy” o różnych nazwach i teraz będziemy chcieli je połączyć ale chcemy je połączyć w taki sposób żeby do każdego pracownia przyporządkować jego szefa. W dalszej części będę się już posługiwać aliasami tabel. W tabeli „P” istnieje kolumna „ID_przelozonego”. Jest to po prostu wartość „ID_pracownika” szefa danej osoby. Żeby uzyskać zadowalający nas efekt musimy połączyć/powiązać odpowiednie „ID_przełozonego” z tabeli „P” z „ID_pracownika” z tabeli „SZEF”. I tutaj skorzystamy z polecenia JOIN, opis poniżej.

Tabela1 JOIN Tabela2 ON (warunek złączenia)

W naszym przypadku będzie to wyglądało tak:

FROM
Pracownicy AS P JOIN Pracownicy AS SZEF ON P.ID_przelozonego = SZEF.ID_pracownika

Żeby lepiej zobrazować co się dzieje podczas złączenia prezentuję poniższy schemat.

kurs sql sql course

Polecenie JOIN znajdzie nam część wspólną obu tabel, czyli znajdzie wszystkie rekordy które po obu stronach (w obu tabelach) będą miały wartości i to wartości odpowiadające warunkowi złączenia. Innymi słowy takie rekordy gdzie dla wartości „ID_przełozonego”, z tabeli „P”, będzie istniała odpowiednia wartość „ID_pracownika”, z tabeli „SZEF”.

Teraz określimy jakie informacje z naszych tabel, z jakich kolumn, będziemy wyświetlać. Po klauzuli SELECT określamy (posługując się aliasami) jakie kolumny z jakich tabel chcemy prezentować. Pamiętajmy, że wyniki mamy prezentować w konwencji imię, nazwisko w jednej kolumnie i imię i nazwisko przełożonego w drugiej kolumnie.

SELECT
       P.imie+' '+P.nazwisko AS [Imię i Nazwisko]
      ,SZEF.imie+' '+SZEF.nazwisko AS [Imię i Nazwisko Przełożonego]

Nasze zapytanie w tym momencie wygląda tak.

SELECT
        P.imie+' '+P.nazwisko AS [Imię i Nazwisko]
       ,SZEF.imie+' '+SZEF.nazwisko AS [Imię i Nazwisk Przełożonego]
FROM
      Pracownicy AS P JOIN Pracownicy AS SZEF
      ON (P.ID_przelozonego = SZEF.ID_pracownika)

Gdybyśmy jednak uruchomili zapytanie w takiej postaci nie uzyskamy jeszcze zadowalającego nas efektu ponieważ w wynikach zobaczymy wszystkich pracowników a my chcemy tylko tych których bezpośrednim przełożonym jest Leopold Banko. Żeby osiągnąć nasz cel w klauzuli WHERE musimy wpisać jeszcze warunek filtrowania, czyli wyświetlić tylko te rekordy gdzie szefem jest Pan Banko.

     WHERE
            SZEF.nazwisko = 'Banko'

W tym momencie nasze zapytanie przybierze formę.

SELECT
        P.imie+' '+P.nazwisko AS [Imię i Nazwisko]
       ,SZEF.imie+' '+SZEF.nazwisko AS [Imię i Nazwisk Przełożonego]
FROM
      Pracownicy AS P JOIN Pracownicy AS SZEF ON P.ID_przelozonego = SZEF.ID_pracownika
WHERE
      SZEF.nazwisko = 'Banko'

Uruchamiamy zapytanie i otrzymujemy 4 rekordy z imieniem i nazwiskiem szefa Leopold Banko. Wydaje się, że wszystko jest już w porządku i otrzymujemy prawidłową liczbę rekordów. Specjalnie wybrałem taką sytuację żeby pokazać jeszcze jedną pułapkę. Przeanalizujmy tabelę „Pracownicy” i kolumnę „dataKoniecZatrudnienia” która przechowuje daty zakończenia stosunku pracy z pracownikiem. Jeżeli przy pracowniku w tej kolumnie znajduje się jakaś data, czyli dany pracownik już nie pracuje w banku, po co nam on w wynikach naszego zapytania. Na poniższym rysunku możecie zobaczyć dwóch pracowników z których jeden już nie pracuje w banku z uwagi na to, że w kolumnie „dataKoniecZatrudnienia” jest wprowadzona wartość w jego rekordzie.

kurs sql sql course Czyli widzimy, że Pan Julian Zdziwiony nie pracuje już w banku od dnia 21 maja 1990 r. Idąc dalej w naszych rozważaniach, czyli żeby uzyskać listę aktualnie pracujących osób w banku musimy szukać pustych wartości (wartości NULL) w kolumnie „dataKoniecZatrudnienia”. Do naszej klauzuli WHERE musimy dopisać jeszcze jeden warunek filtrowania.

WHERE
      SZEF.nazwisko = 'Banko'
      AND P.dataKoniecZatrudnienia IS NULL

Wyrażenie na końcu „IS NULL” znaczy tyle co „jest pusty”. Czyli szukamy tylko tych rekordów które w kolumnie „dataKoniecZatrudnienia” dla konkretnej osoby nie mają wartości lub inaczej mają wartość NULL. Całe gotowe zapytanie poniżej.

SELECT
        P.imie+' '+P.nazwisko AS [Imię i Nazwisko]
       ,SZEF.imie+' '+SZEF.nazwisko AS [Imię i Nazwisk Przełożonego]
FROM
      Pracownicy AS P JOIN Pracownicy AS SZEF ON P.ID_przelozonego = SZEF.ID_pracownika
WHERE
      SZEF.nazwisko = 'Banko'
      AND P.dataKoniecZatrudnienia IS NULL

Efekt uruchomienia naszego zapytania poniżej.

kurs sql sql course

poprzedni

Kurs SQL. Zadanie 10. Pokaż wszystkich pracowników, którzy pracują w dziale logistyki lub informatyki.

Czego się dowiesz czytając ten wpis:

  • poznasz predykat IN,
  • poznasz operator OR,
  • powtórzysz wiedzę nt. ALIASÓW i WHERE.

 

Zadanie 10.

Pokaż wszystkich pracowników, którzy pracują w dziale logistyki lub informatyki. W wyniku wyświetl tylko imię i nazwisko pracownika.

Rozwiązanie:

Ponieważ nie znamy jeszcze łączenia tabel będziemy musieli się posłużyć się identyfikatorami wydziałów logistyki i informatyki, które znajdziemy w tabeli „Działy”. Logistyka ma ID=60 a informatyka ma ID=70.

Zaczynamy oczywiście od klauzul SELECT i FROM. Źródłem danych będzie tabela „Pracownicy”, więc:

FROM
Pracownicy AS P

Przypominam i zachęcam do stosowania aliasów tak jak powyżej, nadaliśmy alias P dla tabeli „Pracownicy”, którym za chwilę będziemy się posługiwać w klauzuli SELECT. A w klauzuli SELECT „wyświetlamy” imię i nazwisko (wykorzystując nadany alias), czyli:

SELECT
P.imie
,P.nazwisko

Teraz musimy ograniczyć w wynik zapytania tylko do tych pracowników, którzy pracują w logistyce lub informatyce. Jak już wcześniej pisałem wykorzystamy do tego identyfikatory (ID) tych działów i tak kolejno logistyka ma ID=60 a informatyka ma ID=70.

Aby zawęzić wyniki zapytania tylko do tych rekordów, które spełniają nasze kryterium, w naszym przypadku pracownik musi pracować w logistyce lub informatyce, wykorzystamy znaną nam już klauzulę WHERE, po której definiujemy nasze kryterium. Wiemy, że identyfikator działu znajduje się w polu „ID_dzialu”. Spójrz na definicję poniżej.

WHERE
P.ID_dzialu IN (60,70)

Skorzystaliśmy z operatora IN. Powyższy zapis znaczy tyle co, znajdź tylko te rekordy w których pole „ID_dzialu” przyjmuje wartości ze zbioru który zadeklarowaliśmy, czyli przyjmuje wartość 60 lub 70. Jeśli w tym polu jest wartość z naszego zbioru dany rekord będzie wyświetlony w wynikach zapytania.

Jest jeszcze inna metoda na osiągnięcie tego samego efektu. Wykorzystamy do tego operator OR, który oznacza LUB. Patrz kod poniżej.

WHERE
P.ID_dzialu = 60
OR P.ID_dzialu = 70

Powyższy kod spowoduje wyświetlenia rekordów gdzie w polu „ID_dzialu” znajduje się wartość 60 LUB (OR) znajduje się wartość 70.

Efekt uruchomienia zapytania z IN lub z OR będzie identyczny to Wam pozostawiam osąd, która metoda jest lepsza. Wydaje mi się jednak, że zapis z IN jest czytelniejszy i łatwiejszy w definicji. Kompletne zapytania z IN i OR poniżej.

wariant z IN wariant z OR
SELECT
P.imie
,P.nazwisko
FROM
Pracownicy AS P
WHERE
P.ID_dzialu IN (60,70)
SELECT
P.imie
,P.nazwisko
FROM
Pracownicy AS P
WHERE
P.ID_dzialu = 60
OR P.ID_dzialu = 70

 

Efekt uruchomienia zapytania poniżej.

kurs sql sql course sql where sql in sql or

 
poprzedni | następny

Kurs SQL. Zadanie 9. Pokaż wszystkich pracowników, których nazwiska zaczynają się na M.

Czego się dowiesz czytając ten wpis:

  • poznasz klauzulę WHERE,
  • poznasz predykat LIKE,

Zadanie 9.

Pokaż wszystkich pracowników, których nazwiska zaczynają się na M. Wyświetl tylko Imię i Nazwisko pracownika.

Rozwiązanie:

Określmy na początek źródło danych, czyli w naszym przypadku będzie to tabela „Pracownicy”.

FROM
Pracownicy AS P

P to alias dla tabeli „Pracownicy”.

W wynikach zapytania mamy wyświetlić tylko imiona i nazwiska więc nasza część SELECT będzie wyglądała następująco. Pamiętaj, że utworzyliśmy alias P.

SELECT
P.imie
,P.nazwisko

I teraz dochodzimy do sedna sprawy. Jak wyświetlić tylko tych pracowników, których nazwisko zaczyna się a literę „M”. Pomoże nam w tym predykat LIKE, który sprawdza czy wyrażenia znakowe w danej kolumnie są zgodne ze zdefiniowanym wzorcem. Poniżej definicja predykatu LIKE.

LIKE wzorzec

W naszym przypadku chcemy sprawdzić czy dane nazwisko zaczyna się na literę „M”, więc w miejsce „wzorca” wpisujemy ‘M%’. Znak % zastępuje ciąg znaków. Czyli każde wyrażenie (u nas Nazwisko) którego pierwszym znakiem jest M (kolejne znaki nie mają znaczenia za to odpowiada znak %) będzie zgodny ze zdefiniowanym wzorcem. Ale jeszcze musimy skorzystać z klauzuli WHERE (definicja poniżej).

WHERE pole = kryterium

Pojawiło nam się nowe słówko WHERE. Jest to klauzula, która zawęzi nam wyniki zapytania do tych rekordów, które spełnią zadane przez nas kryterium. W naszym przykładzie kryterium będzie zgodność ze zdefiniowanym wzorcem LIKE. Całość przybierze więc formę.

WHERE P.nazwisko LIKE ‘M%’

W naszym przykładzie zapis ten znaczy tyle co: pokaż mi wszystkie rekordy w których pole nazwisko zaczyna się na znak M.

Nasze całe zapytanie poniżej.

SELECT
P.imie
,P.nazwisko
FROM
Pracownicy AS P
WHERE
P.nazwisko LIKE 'M%'

 

Poniżej efekt uruchomienia zapytania.

kurs sql where like

poprzedni | następny

Kurs SQL. Zadanie 8. Pokaż dniówkę, tygodniówkę, pensję miesięczną i roczną wszystkich pracowników.

Czego się dowiesz czytając ten wpis:

  • poznasz funkcję ROUND i CAST,
  • poznasz klauzulę ORDER BY
  • powtórzysz jak stosować operacje arytmetyczne,
  • powtórzysz wiedzę na temat komend: SELECT i

 

Zadanie 8.

Oblicz ile dany pracownik zarabia dziennie, tygodniowo, miesięcznie i rocznie. Wynik posortuj malejąco. Dane wyświetl w konwencji: Imię i Nazwisko, Dniówka, Tygodniówka, Pensja miesięczna, Pensja roczna.

Rozwiązanie:

Na początek zajmijmy się tylko obliczeniem dniówki. W naszym przykładzie nie będziemy brali pod uwagę dodatków tylko pensję podstawową. Skoro w 2016 r. średnia ilość dni pracy w miesiącu wynosi 21 dni, właśnie taką wartością posłużymy się w zadaniu. Żeby obliczyć dniówkę wystarczy podzielić naszą pensję podstawową przed średnią ilość dni roboczych w miesiącu, w naszym przypadku to 21. Zacznijmy od wyświetlenia imienia i nazwiska, określenia odpowiedniej operacji arytmetycznej do obliczenia dniówki i określenia źródła danych.

1. SELECT
2. P.imie + ' ' + P.nazwisko AS [Imię i Nazwisko]
3. ,P.pensja/23 AS [Dniówka]
4. FROM
5. Pracownicy P

Widać, że w źródle danych dla tabeli „Pracownicy” zastosowałem alias P (linia 5), który wykorzystuję w klauzuli SELECT. W drugiej linii dodałem trzy wyrażenia tekstowe imię, nazwisko i spację. Spacja posłużyła mi do tego żeby w wynikach imię i nazwisko było ładnie oddzielone. Dodałem tutaj także alias [Imię i Nazwisko]. Trzecia linia to wyrażenie obliczające dniówkę. Jak wspomniałem wcześniej posłużymy się liczą 21 (średnia ilość dni roboczych w 2016 r.). Obliczenie dniówki to zwykła operacja arytmetyczna, czyli pensja pracownika podzielona przez 21, dodany także alias [Dniówka]. Uruchamiamy. Poniżej pierwsze 3 rekordy (poglądowo).

kurs-sql-round-cast-where-order-by

Wszystko fajnie nam się poobliczało ale w wynikach mamy dość dożą precyzję do 6 miejsc po przecinku. Do zaokrąglenia tego wyniku zastosujemy funkcję ROUND. Więc nasz wiersz z dniówką przybierze postać.

ROUND(P.pensja/23 , 2) AS [Dniówka]

Wynik uruchomienia poniżej.

kurs-sql-round-cast-where-order-by

Krótki komentarz do funkcji ROUND. Zaokrągla nam wynik do wymaganego miejsca po przecinku. Funkcja ta wymaga 2 parametrów.

ROUND( argument1 , argument2 )

Argument 1 to wyrażenie arytmetyczne, w naszym przypadku obliczenie dniówki, natomiast argument 2 to liczbą określająca, do którego miejsca po przecinku chcemy zaokrąglić wynik. Załóżmy, że my chcemy zaokrąglić nasz wynik do drugiego miejsca po przecinku, dlatego jako drugi argument powyżej jest wartość 2.

Zerknij teraz na wynik. Widać, że owszem wynik jest zaokrąglony do dwóch miejsc po przecinku ale dalej po przecinku prezentowanych jest sześć znaków. Aby to „naprawić” zastosujemy drugą funkcję CAST. Ta funkcja służy do konwersji danych jednego typu na inny typ np. typ liczbowy na ciąg znaków, ale możemy ją także zastosować do naszych celów. Poniże definicja funkcji CAST.

CAST( WARTOŚĆ_KONWERTOWANA AS DOCELOWY_TYP_DANYCH

Za wartość konwertowaną musimy podstawić całe wyrażenie ROUND(P.pensja/23 , 2) a docelowy typ danych ustawiamy na DECIMAL(6,2). Czyli nasze zapytanie w tym momencie będzie wyglądało tak.

SELECT
P.imie + ' ' + P.nazwisko AS [Imiê i Nazwisko]
,CAST(ROUND(P.pensja/23,2) AS DECIMAL(6,2)) AS [Dniówka]
FROM
Pracownicy P

Wynik (3 pierwsze pozycje) uruchomienia zapytania poniżej.

kurs-sql-round-cast-where-order-by

Efekt jest więc osiągnięty. Wartości w kolumnie dniówka są zaokrąglone i wyświetlane do dwóch miejsc po przecinku.

Korzystając z dotychczasowej wiedzy możemy dodać do klauzuli SELECT nowe kolumny. Pozostało nam wyświetlenie kolumny tygodniówka, pensja miesięczna i pensja roczna.

Zajmijmy się linią dotyczącą obliczenia tygodniówki. Do tego celu możemy wykorzystać linię obliczającą dniówkę i lekko ją zmodyfikować. Wystarczy wyrażenie obliczające dniówkę pomnożyć przez liczbę roboczych dni w tygodniu, czyli przez 5.

Pensja miesięczna to nic innego jak wyświetlenie pola „pensja” z tabeli „Pracownicy” a pensja roczna to pensja miesięczna pomnożona przez 12.

Nasze zapytanie przybierze końcową postać.

SELECT
P.imie + ' ' + P.nazwisko AS [Imię i Nazwisko]
,CAST(ROUND(P.pensja/23,2)AS DECIMAL(6,2)) AS [Dniówka]
,CAST(ROUND(P.pensja/23*5,2)AS DECIMAL(6,2)) AS [Tygodniówka]
,P.pensja AS [Pensja miesięczna]
,P.pensja*12 AS [Pensja roczna]
FROM
Pracownicy P

Pozostaje tylko posortować malejąco wyniki naszego zapytania. Do tego służy klauzula ORDER BY. Zaraz po niej podajemy według których kolumn chcemy sortować wyniki i określamy model sortowania malejąco DESC lub rosnąco ASC. W sumie sortowania rosnącego nie musimy deklarować jest to domyślny model sortowania. W naszym przypadku chcemy posortować wyniki malejąco więc dopisujemy na końcu klauzuli DESC. Sortowanie może odbywać się po kilku kolumnach wtedy poszczególne argumenty (kolumny) przedzielamy przecinkiem. W naszym zadania skoro wszystkie wartości w kolumnach zależą od wartości pensji miesięcznej to wystarczy posortować wyniki właśnie po tej kolumnie. Sortować możemy także po aliasach co właśnie wykorzystałem w naszym przykładzie.

SELECT
P.imie + ' ' + P.nazwisko AS [Imię i Nazwisko]
,CAST(ROUND(P.pensja/23,2)AS DECIMAL(6,2)) AS [Dniówka]
,CAST(ROUND(P.pensja/23*5,2)AS DECIMAL(6,2)) AS [Tygodniówka]
,P.pensja AS [Pensja miesiêczna]
,P.pensja*12 AS [Pensja roczna]
FROM
Pracownicy P
Order by
[Pensja miesięczna]DESC

Wynik uruchomienia naszego ostatecznego zapytania poniżej.

kurs-sql-round-cast-where-order-by

poprzedni | następny

Northwind – zadania praktyczne

Northwind – zadania praktyczne
kurs SQL, sql course Zadanie 1 – Wyświetl wszystkich pracowników w formacie: Nazwisko, Imię, Stanowisko. [rozwiązanie]

kurs SQL, sql course Zadanie 2 – Wyświetl wszystkich pracowników w formacie: Nazwisko, Imię, Stanowisko, Kraj i nazwij tak nagłówki kolumn, ale tylko tych ze Stanów Zjednoczonych. [rozwiązanie]

kurs SQL, sql course Zadanie 3 – Znajdź dane firmy o nazwie „Alfreds Futterkiste” [rozwiązanie]

kurs SQL, sql course Zadanie 4 – Znajdź dostawców czekolady. [rozwiązanie]

kurs SQL, sql course Zadanie 5 – Wyświetl raport sprzedaży za 1996 r. [rozwiązanie]

kurs SQL, sql course Zadanie 6 – Wyświetl liczbę pracowników. [rozwiązanie]

kurs SQL, sql course, northwind ćwiczenia praktyczne, northwind practical exercises Zadanie 7 – Wyświetl aktualną listę produktów. [rozwiązanie]

Kurs SQL. Spis treści.

KURS SQL

kurs sql wstęp Wstęp

kurs sql szczypta teorii. Szczypta teorii.

kurs sql SQL Server 2008 R2 (skąd pobrać) SQL Server 2008 R2 (skąd pobrać)

kurs sql SQL Server 2008 R2 Express (instalacja) SQL Server 2008 R2 Express (instalacja)

kurs sql Northwind – treningowa baza danych Northwind – treningowa baza danych

kurs sql Northwind – instalacja/dodanie bazy danych Northwind – instalacja/dodanie bazy danych

kurs sql Treningowa baza danych BANK_v2 (struktura) Treningowa baza danych BANK_v2 (struktura)

kurs sql Treningowa baza danych BANK_v2 (instalacja) Treningowa baza danych BANK_v2 (instalacja)

kurs sql Przygotowanie do tworzenia zapytań. Przygotowanie do tworzenia zapytań.

kurs sql Zadanie 1. Wyświetl dane wszystkich pracowników w banku (SELECT, FROM) Zadanie 1. Wyświetl dane wszystkich pracowników w banku (SELECT, FROM)

kurs sql Zadanie 2. Wyświetl informacje o wszystkich stanowiskach w banku (SELECT, FROM) Zadanie 2. Wyświetl informacje o wszystkich stanowiskach w banku (SELECT, FROM)

kurs sql Zadanie 3. Wyświetl informacje o wszystkich działach w banku (SELECT, FROM) Zadanie 3. Wyświetl informacje o wszystkich działach w banku (SELECT, FROM)

kurs sql Zadanie 4. Wyświetl imiona, nazwiska i pensję wszystkich pracowników (SELECT FROM) Zadanie 4. Wyświetl imiona, nazwiska i pensję wszystkich pracowników (SELECT FROM)

kurs sql Zadanie 5. Wyświetl imiona, nazwiska i całkowite wynagrodzenie (operacje arytmetyczne) Zadanie 5. Wyświetl imiona, nazwiska i całkowite wynagrodzenie (operacje arytmetyczne)

kurs sql korzystanie z aliasów. Zadanie 6. Korzystanie z aliasów.

kurs sql dodawanie komentarzy. Zadanie 7. Dodawanie komentarzy.

kurs sql round cast where order by Zadanie 8. Pokaż dniówkę, tygodniówkę, pensję miesięczną i roczną wszystkich pracowników.

kurs sql round cast where order by Zadanie 9. Pokaż wszystkich pracowników, których nazwiska zaczynają się na M. Wyświetl tylko Imię i Nazwisko pracownika.

kurs sql sql course sql where sql in sql or Zadanie 10. Pokaż wszystkich pracowników, którzy pracują w dziale logistyki lub informatyki. W wyniku wyświetl tylko imię i nazwisko pracownika.

kurs sql sql course sql where join is null Zadanie 11. Pokaż wszystkich pracowników których bezpośrednim przełożonym jest Leopold Banko. WHERE JOIN IS NULL.