Archiwum kategorii: Kurs SQL

Kurs SQL to kategoria gdzie znajdują się wpisy pozwalające na poznanie języka zapytań SQL.

Zadanie 13. Pokaż wszystkich pracowników którzy już nie pracują w banku i oblicz ile lat pracowali w banku oraz ile miesięcy minęło od zakończenia stosunku pracy

Czego się dowiesz czytając ten wpis:

•  poznasz funkcję: DATEDIFF, GETDATE,
•  powtórzysz wiedzę nt. JOIN, IS NOT NULL, ALIASÓW i WHERE.

 

Zadanie 13.

Pokaż wszystkich pracowników którzy już nie pracują w banku i oblicz ile lat pracowali w banku oraz ile miesięcy minęło od wygaśnięcia ich stosunku pracy.

Wyniki przedstaw w postaci kolumn:

•  Imię i nazwisko
•  Dział
•  Data Zatrudnienia
•  Data Zakończenia Pracy
•  Lata Pracy /ile lat pracował pracownik/
•  Czas Od Końca Pracy /czas jaki upłynął (w miesiącach) od wygaśnięcia stosunku pracy do dnia dzisiejszego/

 

Rozwiązanie:

Do stworzenia naszego zapytania wykorzystamy zapytanie 12, które tylko zmodyfikujemy, a konkretniej dodamy trzy nowe kolumny: „Data Zatrudnienia” , „Lata Pracy” , „Czas Od Końca Pracy”.

Zacznijmy od dodania kolumny „Data Zatrudnienia”. Z tym zadaniem nie powinniśmy mieć problemów, wystarczy tylko w sekcji SELECT dodać nową kolumnę. Dane które potrzebujemy znajdują się w tabeli Pracownicy w kolumnie dataZatrudnienia. Pamiętaj, że w ćwiczeniu 12 dla tabeli Pracownicy nadaliśmy alias P (kod poniżej).

darmowy-kurs-sql-zadanie-13-free-sql-course-exercise-13

Kolejnym zadaniem z jakim musimy się zmierzyć jest wypełnienie danymi kolumny „Lata Pracy”. Tutaj z pomocą przyjdzie nam nowa dla nas funkcja DATEDIFF. Funkcja ta zwraca nam różnicę pomiędzy dwoma datami w zadeklarowanych przez nas wartościach np. latach, miesiącach, tygodniach itd. Struktura DATEDIFF poniżej.

darmowy-kurs-sql-zadanie-13-free-sql-course-exercise-13

Zacznijmy tworzyć drugą nową kolumnę „Lata Pracy”. Wiemy już z jakiej funkcji należy skorzystać – DATEDIFF. Wiemy także, że wartością czasu w jakich ma być wyrażona wartość w tej kolumnie są lata, czyli nasz pierwszy parametr przybierze wartość YEAR. Jako drugi parametr musimy podstawić datę początkową, czyli w naszym przypadku będzie to data zatrudnienia, a więc „P.dataZatrudnienia”. Trzecim parametrem funkcji DATEDIFF będzie data końcowa, czyli w naszym przypadku data zakończenia stosunku pracy „P.DataKoniecZatrudnienia”. Już poza nawiasem z parametrami funkcji DATEDIFF nazywamy tą kolumnę przy pomocy aliasu „AS [Lata Pracy]”. Cały kod poniżej

darmowy-kurs-sql-zadanie-13-free-sql-course-exercise-13

Przypomnijmy DATEDIFF zwraca czas pomiędzy dwoma datami, datą początkową (parametr 2) i datą końcową (parametr 3) w zadeklarowanych jednostkach czasu (parametr 1, u nas w latach).

Ostatnie zadanie to zadeklarowanie kolumny która wskaże nam ile czasu upłynęło od momentu wygaśnięcia stosunku pracy pracownika do chwili obecnej. No ale jak zadeklarować „chwilę obecną”, przecież możemy wykonać ten skrypt dzisiaj albo jutro i co wtedy? Do określenia „chwili obecnej” a w zasadzie aktualnej daty i godziny służy funkcja GETDATE(). Zwraca ona aktualny czas i godzinę w momencie uruchomienia skryptu. Jeśli wiec uruchomimy skrypt dzisiaj uzyskamy wynik w zadanych jednostkach czasu do dzisiaj, jeśli skrypt uruchomimy jutro otrzymamy wynik adekwatnie większy (do daty jutrzejszej w zależności od jednostek w które wybraliśmy do prezentacji wyniku). Polecenie mówi jasno, że czas obliczamy w miesiącach. Pierwszym parametrem naszej funkcji DATEDIFF  będzie month. Drugim parametrem, czyli nasza data początkowa, będzie data zakończenia pracy, czyli „P.dataKoniecZatrudnienia”. Trzecim parametrem będzie aktualna data, czyli funkcja GETDATE(). Składają wszystko w jedno polecenie otrzymujemy poniższy kod, przypominam, że korzystaliśmy z zapytania z zadnia 12 więc całą resztę mamy gotową.

darmowy-kurs-sql-zadanie-13-free-sql-course-exercise-13

Uruchamiamy zapytanie i otrzymujemy wynik.

darmowy-kurs-sql-zadanie-13-free-sql-course-exercise-13

Zadanie 12. Pokaż wszystkich pracowników którzy już nie pracują w banku.

Czego się dowiesz czytając ten wpis:

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

Zadanie 12.

Pokaż wszystkich pracowników którzy już nie pracują w banku. Wyniki przedstaw w postaci kolumn: Imię i nazwisko /  Dział / Data Zakończenia Pracy.

Rozwiązanie:

Podstawowe dane, czyli Imię, Nazwisko, Data Zakończenia Pracy, weźmiemy z tabeli „Pracownicy”. Dane dotyczące działu znajdują się jednak w tabeli „Dzialy”, więc przy określaniu źródła danych musimy zrobić złączenie obu tych tabel. W tabeli Pracownicy (AS P) polem które przechowuje ID działu w którym pracuje pracownik jest „ID_dzialu” a w tabeli Dzialy (AS D)polem przetrzymującym ID działu jest pole o takiej samej nazwie „ID_dzialu”. Aby w prawidłowy sposób odwołać się do tych pól, nadamy tabelom aliasy i tak odpowiednio tabela Pracownicy otrzyma alias P a tabela Dzialy otrzyma alias D. Kod określający źródło danych znajdziecie poniżej.

Kurs SQL. Baza danych Bank_v2. Zadanie 12.

   Teraz zajmijmy się zdefiniowaniem danych które chcemy wyświetlić, czyli sekcja SELECT. Tak jak jest określone w opisie zadania mamy wyświetlić dane w postaci: Imię i nazwisko /  Dział / Data Zakończenia Pracy. No to do dzieła.

   Kurs SQL. Baza danych Bank_v2. Zadanie 12.

Uruchamiamy nasze zapytanie i otrzymujemy wynik.

Kurs SQL. Baza danych Bank_v2. Zadanie 12.

Zwróćmy uwagę, że w kolumnie Data Zakończenia Pracy w niektórych wierszach znajduje się konkretna data a w niektórych wartość NULL, która oznacza wartość pustą, co świadczy o tym, że dana nie istnieje w bazie. Chodzi o to, że jeżeli dany pracownik pracuje jeszcze w banku to wiadomo, że w kolumnie Data Zakończenia Pracy nie może być żadnej wartości.

Czyli jak można wyświetlić tylko tych pracowników, którzy aktualnie nie pracują już w banku? Musimy wyświetlić tylko tych pracowników którzy w kolumnie Data Zakończenia Pracy mają konkretną wartość/datę, czyli którzy w tym polu mają wartość inną niż NULL i do tego celu użyjemy polecenia IS NOT NULL, czyli tak jakbyśmy powiedzieli do kompilatora: wyświetl mi wszystkich pracowników którzy w polu Data Zakończenia Pracy nie mają NULI. Ponieważ będzie to warunek filtrowania musimy umieścić go w klauzuli WHERE.


Kurs SQL. Baza danych Bank_v2. Zadanie 12.

Zestawiają wszystko ze sobą otrzymujemy zapytanie.

Kurs SQL. Baza danych Bank_v2. Zadanie 12.

Po uruchomieniu otrzymujemy wynik.

Kurs SQL. Baza danych Bank_v2. Zadanie 12.

Gotowy skrypt

Zadanie 11. Pokaż wszystkich pracowników których bezpośrednim przełożonym jest Leopold Banko. WHERE JOIN IS NULL

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

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

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 where in or

 

poprzedni | następny

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

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

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 sql course

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 sql course

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 sql course

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 sql course

poprzedni | następny

Zadanie 7. Dodawanie komentarzy.

Przy tym zadaniu pokażę Wam jak dodać komentarz w zapytaniu. Ogólnie komentarze mają dwa zadania. Pierwsze, w danym miejscu mamy możliwość napisać parę słów wyjaśnienia do kodu zapytania, żeby wracając do tego zapytania za jakiś czas pamiętać o co nam chodziło. Drugie, żeby „wyłączyć” w danym momencie odpowiednią część zapytania (np. do przetestowania działania danego wariantu) bez potrzeby kasowania kodu.

Możemy dodać dwa rodzaje komentarzy:

  • jednowierszowy, znak „– – treść komentarza”. W tym wariancie wszystko w danym wierszu po dwóch myślnikach zostanie potraktowane jako komentarz aż do końca wiersza i nie będzie brane pod uwagę w momencie uruchomienia zapytania,
  • wielowierszowy, znak „ /* treść komentarza */”. W tym wariancie wszystko co znajdzie się pomiędzy dwoma ukośnikami i gwiazdkami zostanie potraktowane jako komentarz i jak w poprzednim wariancie nie będzie brane pod uwagę w momencie uruchomienia zapytania.

Przykład zastosowania obu wariantów komentarzy w poniższym kodzie. Do zaprezentowania działania komentarzy wykorzystamy zapytanie, które stworzyliśmy w zadaniu nr 6.

kurs sql dodawanie komentarzy komentarze

Omówmy trochę to nasze zapytanie. W liniach od 1 do 6 znajduje się komentarz wielowierszowy. Ja już wspominałem wszystko co znajdzie się pomiędzy znakami /* treść komentarza*/  zostanie potraktowane jako komentarz.

W linii 8 został zaprezentowany komentarz jednowierszowy. Tutaj możemy np. wpisać informację czego dotyczy dana część kodu. Oczywiście komentarz jednowierszowy i wielowierszowy może wykorzystać do opisu jednej linii kodu bez wyłączania tej linijki, co pokazuje linia 10 i 11.

Komentarze przydają się także do tego żeby w danej chwili np. w celach testowych wyłączyć jakąś część kodu, ale nie kasować tej części, co obrazuje linia 12. Zwróćcie uwagę, że „wyłączyliśmy” kolumnę [Wynagrodzenie całkowite] i nie pokazuje się ona w wynikach zapytania, ale nie musieliśmy kasować kodu w zapytani. To bardzo przydatna funkcjonalność.

W Management Studio możemy dodawać i usuwać komentarze klikając w odpowiednie ikony. Możemy w taki sposób zaznaczać wiele linii kodu naraz. Wykorzystujemy do tego dwie ikony z paska.

kurs sql dodawanie komentarzy komentarze

Przed kliknięciem w ikonę, w celu dodania komentarza, zaznaczamy kursorem w zapytaniu wiersze które chcemy zakomentować. Pamiętaj, te wiersze staną się wtedy niewidowczne podczas uruchamiania zapytania.

I na odwrót jeśli chcemy „odkomentować” jakieś wiersze które są aktualne „zakomentowane” należy je zaznaczyć i kliknąć w ikonę do usunięcia komentarza.

UWAGA: taki typ dodawania i usuwania komentarzy tyczy się tylko komentarzy jednowierszowych, czyli znak „– – ”. Możemy takim sposobem zaznaczyć więcej wierszy, wtedy przed każdym wierszem zobaczymy znak „– – ”, co obrazuje poniższy zrzut.

kurs sql dodawanie komentarzy komentarze

Jak widzisz w szybki i łatwy sposób możemy dodawać i usuwać komentarze w różnych celach.

poprzedni | następny

Zadanie 6. Korzystanie z aliasów.

Czego się dowiesz czytając ten wpis:

  • dowiesz się co to jest alias i nauczysz się je stosować,
  • powtórzysz jak wyświetlać tylko niektóre kolumny z tabeli,
  • powtórzysz wiedzę na temat komend: SELECT i

 

Zadanie 6.

Zmodyfikuj tak zapytanie z zadania nr 5 aby nadać nową nazwę „wynagrodzenie całkowite” kolumnie o nazwie „No column name”.

Rozwiązanie:

Co to w ogóle jest alias? Alias (inaczej: zwany, znany jako) to alternatywna nazwa dla jakiegoś obiektu, w naszym przypadku tym obiektem będzie kolumna (ale może to być np. cała tabela o czym dowiecie się w dalszej części kursu). Aliasy stosujemy np. kiedy chcemy zastąpić daną nazwę inną (bardziej przystępną dla nas) lub w ogóle rozróżnić obiekty np. jeżeli zdarza się, że w jednym zapytaniu występują obiekty o tej samej nazwie.

Ale wróćmy do naszego zadania. Mamy tutaj zmienić nazwę kolumny lub inaczej w ogóle ją nadać (jeżeli dla kogoś nazwa „No column name” nie jest nazwą). Zerknijmy na nasze zapytanie (z zadania 5) i efekt uruchomienia tego zapytania.

SELECT
                                imie
                               ,nazwisko
                               ,pensja + dodatki
FROM
                               Pracownicy

kurs sql korzystanie z aliasów

Widzimy tutaj, że pierwsze dwie nazwy kolumn to powtórzenie nazw pól po komendzie SELECT (imie, nazwisko). Natomiast trzecia nazwa kolumny, czyli „No column name”, nie odzwierciedla trzeciego parametru po komendzie SELECT, czyli  „pensja + dodatki”. Tak jak już wspominałem wcześniej dzieje się tak ponieważ mamy tutaj operację arytmetyczną. Żeby zmienić, bądź nadać, nazwę tej trzeciej kolumnie wykorzystamy alias i do tej operacji wykorzystamy słówko „AS”. Po trzecim parametrze dodajemy słówko AS po którym wpisujemy nazwę jaką chcemy nadać trzeciej kolumnie np. „Wynagrodzenie całkowite”. Z uwagi na to, że nasza nazwa kolumny składa się z dwóch wyrazów musimy „opisać go” nawiasem kwadratowym, czyli otrzymujemy [Wynagrodzenie całkowite]. W momencie kiedy uruchomilibyśmy zapytanie i nasz alias składałby się z dwóch wyrazów bez nawiasów kwadratowych otrzymalibyśmy komunikat o błędzie: „Incorrect syntax near ‘całkowite’”. Dla aliasów jednorazowych nie musimy stosować nawiasów zamkniętych. Poniżej zapytanie po modyfikacji.

SELECT
                                imie
                               ,nazwisko
                               ,pensja + dodatki AS [Wynagrodzenie całkowite]
FROM
                               Pracownicy

Efekt uruchomienia zapytanie prezentuję poniżej.

kurs sql korzystanie z aliasów

Widzimy, że nazwa trzeciej kolumny ładnie zaczyna się od dużej litery więc zróbmy tak samo z pierwszą i drugą kolumną. Do pierwszej dodajmy „polskie znaki. W tym celu także wykorzystamy aliasy ale nie musimy korzystać z nawiasów kwadratowych. Końcowe zapytanie poniżej.

SELECT
                                Imie AS Imię
                               ,nazwisko AS Nazwisko
                               ,pensja + dodatki AS [Wynagrodzenie całkowite]
FROM
                               Pracownicy

Efekt naszego zapytania poniżej.

kurs sql korzystanie z aliasów

poprzedni | następny

Zadanie 5. Wyświetl imiona, nazwiska i całkowite wynagrodzenie wszystkich pracowników (operacje arytmetyczne na danych).

Czego się dowiesz czytając ten wpis:

  • nauczysz się jak wykonywać operacje arytmetyczne na danych,
  • powtórzysz jak wyświetlać tylko niektóre kolumny z tabeli,
  • powtórzysz wiedzę na temat komend: SELECT i

 

Zadanie 5.

Wyświetl imiona , nazwiska i całkowite wynagrodzenie (pensja + dodatki) wszystkich pracowników banku.

Rozwiązanie:

Zadanie jest prawie identyczne jak zadanie 4 z tą różnicą, że tutaj musimy zastosować operację arytmetyczną na danych. Musimy dodać dane z dwóch kolumn, czyli z kolumny pensja i kolumny dodatek, tak żeby powstało całkowite wynagrodzenie, które pracownik może otrzymać w miesiącu. Sprawa z dodawaniem wartości z kolumn jest bardzo prosta, po prostu pomiędzy nazwy kolumn wstawiamy znak „+”, czyli nasz SELECT będzie wyglądał jak poniżej (zachęcam do stosowania wcięć jak poniżej).

                SELECT
                                imie
                               ,nazwisko
                               ,pensja + dodatki
Teraz określamy źródło danych więc wskazujemy tabelę „Pracownicy”.

FROM
                               Pracownicy
Składamy wszystko razem i otrzymujemy

SELECT
                                imie
                               ,nazwisko
                               ,pensja + dodatki
FROM
                               Pracownicy

Uruchamiamy nasze zapytanie i otrzymujemy wynik (poniżej).

Zadanie 5 - zrzut

Rezultat zapytania to 44 rekordy z których 24 prezentowanych jest na powyższym zrzucie.

Zwróćcie uwagę na jedną rzecz. Otóż nazwa kolumny prezentującej całkowite wynagrodzenie ma nazwę „(No column name)”.

noColumnName

Ktoś zapyta, o co tutaj chodzi? W momencie kiedy dodajemy (lub wykonujemy inne działania arytmetyczne) wartości z więcej niż jednej kolumny program nie wie której nazwy użyć, więc nie użyta jest w ogóle. Jest na to sposób. Możemy zastosować „alias” i nazwać tą kolumnę (i każdą inną) jak tylko chcemy, ale o tym w kolejnej części.

poprzedni | następny

Przygotowanie do tworzenia zapytań.

Teraz kilka zdań na temat jak rozpocząć pisanie zapytań SQL-owych w Management Studio.

  1. Uruchamiamy program Management Studio klikając dwukrotnie na ikonkę na pulpicie.
    Management Studio ikona na pulpicie
  2. W celu włączenie okna gdzie będziemy mogli pisać nasze zapytanie należy kliknąć w przycisk „New Query”. Po naciśnięciu tego przycisku po prawej stronie otworzy się okienko, w którym możemy tworzyć nasze zapytanie.
    NewQuery_
  3. Ważną rzeczą jest, aby przed rozpoczęciem pracy przy tworzeniu zapytania ustawić się w odpowiednim kontekście, czyli żeby nasza baza BANK_v2 była bazą która aktualnie jest w użyciu. Możemy to zrobić wybierając nazwę naszej bazy z rozwijalnej listy na jednym z pasków co prezentuje poniższy zrzut.kontekst_
  4. Jeżeli wykonaliśmy wszystkie powyższe czynności możemy zabrać się za tworzenie naszego zapytania (to omówimy w kolejnych częściach przy pisaniu zapytań do odpowiednich zadań). Poniżej przykładowe zapytanie.
    pierwsze_zapytanie_
  5. Kiedy zapytanie jest gotowe uruchamiamy je wciskają na klawiaturze przycisk F5 lub przycisk Execute na pasku zadań. Efekt naszego zapytania będzie widoczny w okienku poniżej samego zapytania, co prezentuje poniższy zrzut.
    efekt_zapytania_

 

Skoro mamy już zainstalowane odpowiednie środowisko do pracy, mamy już postawioną bazę danych na której będziemy ćwiczyć i wiemy jak korzystać z aplikacji Management Studio w celu tworzenie zapytań, możemy przejść do konkretnych zadań.

poprzedni | następny