Nowy wpis zamiast lekcji będzie dotyczył dosyć popularnego ostatnio tematu w świecie IT – Cloud Computing.

Praca w chmurze weszła w nasze życie w siedmiomilowych butach i radzi sobie bardzo dobrze. Model chmury obliczeniowej sprawdza się bardzo dobrze także, a może i zwłaszcza w polskich warunkach. Bo przecież redukujemy koszty, bo oszczędzamy sobie pracy, bo jest to łatwiejsze.

Oczywiście nadal istnieje korporacyjne przekonanie, że duża firma powinna mieć swoją serwerownię i swoich administratorów. Swoje licencje i serwery poczty oraz serwery domeny i plików… Oczywiście w dobie oszczędności i recesji na rynku – to myślenie powoli się zmienia.

Ale przedstawmy zagadnienie obrazowo.

Firma będzie się nazywała JACEX – to takie ‘polskie’

Firma ta, której właścicielem jestem oczywiście ja – Jacek –  zajmuje się produkcją rowerów. Mam 10 fabryk, poddostawców, magazyny logistyczne, dział personalny, księgowość, zakupy itd. Ale moja firma MUSI korzystać z komputerów ponieważ inaczej się nie da. Ponieważ mamy system magazynowy, serwer poczty, system księgowy oraz RCP – potrzebujemy także serwerów.

Serwery pociągają za sobą konieczność postawienia serwerowni oraz służb, które to utrzymują – czyli musimy zainwestować w informatyków. Serwerownia to – prócz przetargów i projektowania podczas budowania tego monstrum – także system gaśniczy, system podtrzymania zasilania i mnóstwo przepisów do spełnienia. Także oczywisty problem z całą otoczką jaką są przeglądy, zużywanie sprzętu, serwisy serwerów, kończące się miejsce na dyskach…

Kwoty rosną z dnia na dzień a i sama praca staje się coraz bardziej wymagająca.

Możemy wejść w popularny w ostatnich czasach outsourcing – wypchnięcie pewnych usług na zewnątrz. Czy jest to tańsze i lepsze zależy tylko i wyłącznie od tego, czy mądrze wejdziemy w ten proces.

Czyli co? Powierzchnie magazynowe wynajmujemy, służby utrzymania ruchu oddajemy firmie zewnętrznej.. co jeszcze? Co tylko nam sie podoba – świat oferuje usługi na wszystko co możliwe – oczywiście musimy to zrobić mądrze aby nam się to opłacało.

Zapytacie jaki ma to związek z samouczkiem albo w ogóle z IT? I po co właściwie o tym piszę? Dochodzimy powoli do sedna czyli do tematów „wydania” na zewnątrz systemów IT.

Z punktu widzenia menadżera IT – mam oczywiście swoje zdanie na ten temat. Przede wszystkim zdrowy rozsądek. Natomiast jesli mówimy o firmie JACEX – zastanówmy się, po co w mojej firmie serwerownia? Po co mi serwery? Po co oprogramowanie i licencje? Po co drodzy specjaliści? I wreszcie po co mi taki problem? Przecież dla mnie najważniejsze żeby to działało.

Mail oddajemy więc np… do Google – oni po podpisaniu umowy zagwarantują nam obieg poczty oraz zagwarantują bezpieczeństwo. Ale co z systemami, które sa nasze – napisane przez nas lub dla nas? Tutaj dochodzimy do Cloud Computing czyli chmury obliczeniowej.

Usługi takie są oferowane przez różne firmy – w skrócie polega to na wykupieniu/wynajęciu u firmy trzeciej mocy obliczeniowej i dyskowej, z której będziemy korzystać na potrzeby naszej firmy.

Takie rozwiązanie testowałem dłuższy czas aby zorientować się po części dla własnych potrzeb a po części dla potrzeb mojej firmy. W ręce wpadła mi firma City Cloud – głównie ze względu na ceny.

Ponieważ nigdy nie korzystałem z rozwiązań Cloud Computing – miałem pewne obawy czy potrafię w ogóle to poustawiać i czy zorientuje się jak to jeść… i czym… i za ile

No i miłe zaskoczenie – interfejs przyjazny, tworzenie konta błyskawiczne, wybór maszyn prosty, konfiguracja systemu banalna.

System testowałem pod kątem serwerów SQL (no ba! ) oraz zdalnego łączenia sie do nich jak i do samych serwerów. Także dla mnie ważna sprawa, żeby moje programy w firmie JACEX można było łatwo podłączyć przez ODBC do serwerów bazodanowych.

Wszystko banał.

Ceny jak dla mnie znośne jeśli brać pod uwagę prywatną osobę – dla firmy małej i średniej jest na pewno taniej niż utrzymanie działu IT i serwerowni (uwierzcie mi, wiem co mówię ).

A jeśli nie lubicie klikać w linki to podam kilka szczegółów, które mnie osobiście przekonały, że w niektórych przypadkach Cloud Computing z wykorzystaniem City Cloud jest najlepszym rozwiązaniem.

Prócz cen serwerów oczywiście bogata gama softu i systemów operacyjnych. No i najwazniejsze dla mine – jest MSSQL

Niestety nie zauważyłem tam Oracle’a ale można swój soft dograć więc jeśli macie licencję to śmiało możecie soft odpalić na ich serwerach.

Aha… ważna uwaga – jeśli nie potrzebujecie serwera full wypas to nie zaznaczajcie tej opcji – ceny są dosyć wysokie. Dla potrzeb średniej firmy najtańsze serwery zazwyczaj wystarczają – oczywiście wybierzecie to, co dla Was najodpowiedniejsze.

Kolejna sprawa, na którą warto zwrócić uwagę, a co oferuję City Cloud – serwery można wstrzymać lub zatrzymać jesli z nich nie korzystacie (przerwa świąteczna, noc, przestój w firmie itp). Kwoty wtedy maleją prawie do zera – polecam zapamiętać.

I to tyle – natchniony mnóstwem Cloudów opisywanych w magazynach IT chciałem sam stworzyć coś o tej technologii no i stworzyłem o produkcie, który sam na własnej skórze przetestowałem.

Druga część tego małego zbioru informacji o funkcjach wbudowanych to tak na prawdę uzupełnienie o dwie ważne funkcje.

Pierwszą z nich jest funkcja skalarna decode.

składnie decode:

decode (wyrażenie_testowane,

wartość_a_1, wartość_b_1,

wartość_a_2, wartość_b_2,

…..,

wartość_a_n, wartość_b_n,

wartość_domyślna)

A oto opis tego co zapisaliśmy: funkcja testuje wartość wyrażenia testowanego i zwraca wartość_b_x w przypadku, gdy wyrażenie testowane ma wartość_a_x. Jeżeli żadne z wartości_a_x nie jest równe wartości testowanej, to funkcja zwraca wartość_domyślna.

myślę, że sprawa jest tutaj prosta. Ale… dla przykładu zróbmy selecta.

SELECT tow_nazwa, tow_cena,

decode (tow_podatek,

22, ‘podatek pełny’,

7, ‘podatek ulgowy’,

‘brak podatku’) podatki

FROM towary;

Możemy łatwo teraz ocenić, że z tabelki towary wybieramy nazwę i cenę oraz kolumnę podatki, która wypisze nam ‘podatek pełny’ lub ‘podatek ulgowy’ w zależności czy w kolumnie tow_podatek znajdzie 22 czy 7. Jeśli nic nie znajdzie – napisze ‘brak podatku’.

FUNKCJA DECODE ROZUMIE NULL - co nie jest tak oczywiste w przypadku kolejnej funkcji (mam tu na myśli rozumienie w sensie niepomijania, nie wypisywania 0 ani błędu. Rozumie także jeśli testujemy NULL)

Kolejna funkcja to właściwie całe wyrażenie – CASE

W pewnym zakresie odpowiada ono funkcji DECODE. Pozwala nam kształtować wartość zwracanej kolumny w sposób elastyczny i dynamicznie modyfikowany.

Oto składnia:

SELECT tow_nazwa, CASE tow_podatek

WHEN 22 THEN ‘normalny’

WHEN 7 THEN ‘ulgowy’

ELSE ‘maly’

END

FROM towary;

Widzimy tutaj, że prócz nowych słów WHEN, THEN, END – mamy podobną sytuację jak w przypadku DECODE – osiągnęliśmy to samo.

Można zapytać po co nam w takim razie CASE? Otóż bywa mnóstwo sytuacji kiedy nie da się jej zastąpić przez DECODE ponieważ nie osiągniemy zadanego wyniku lub tez mamy zbyt wiele zmiennych.

Oto prosty przykład:

SELECT tow_nazwa

,CASE

WHEN tow_podatek IS NULL THEN ‘brak danych’

WHEN tow_cena > 2000 THEN ‘drogo’

WHEN tow_cena > 200 THEN ‘srednio’

ELSE ‘tanio’

END

FROM towary t;

Spróbujcie to samo zrobić z DECODE nie ma szans

* Ważne jest też, że CASE NIE ROZUMIE ‘NULL’ – ale rozumie IS NULL – wiem, głupie ale nie zgłębiłem jeszcze powodu dla czego taki a nie inny mechanizm tu działa.

To tyle z funkcji wbudowanych – mam nadzieję, że się przyda taka lekcja.

Zwróćcie także uwagę na zapisy tych kodów – użyłem tam specjalnie pewnych znaków w pewnych miejscach – ktoś wie czemu?

Dzisiejszy temat to właściwie teoria do przeczytania w każdej książce czy manualu – jednak skoro mamy mieć wszystko w jednym miejscu; jest to pozycja obowiązkowa.

W SQL jest wbudowanych wiele gotowych już funkcji, które ułatwiają nam życie. Po wywołaniu ich w poleceniu SELECT bądź innych poleceniach DML (data manipulation language – zapytania służące do manipulacji danymi), oraz podaniu argumentów – funkcje obliczają coś dla nas bądź też wykonują za nas pewne obliczenia.

Funkcje możemy zagnieżdżać w innych funkcjach i to dowolna ilość razy – to znaczy, że wartość zwrócona z jednej funkcji może być argumentem dla kolejnej funkcji itd.

Ponieważ sam język SQL ma wiele różnych funkcji – przytoczę tylko najczęściej używane lub zwyczajnie takie, którymi ja się posługuję najczęściej.

Funkcje znakowe

- lower (tekst), upper (text), initcap (text) – te funkcje zwracają ‘text’ zmieniając odpowiednio wielkość liter (na małe, na duże, zaczynające się od dużej litery)

- lpad (text, n [,text1])  , rpad (text, n [,text1]) – funkcja służąca do dopisywania z lewej lub prawej strony tekst1 w taki sposób aby text osiągał szerokość n znaków. Jeżeli text1 nie jest podany to text jest dopełniany spacjami.

- ltrim (text [,text1]), rtrim (text [,text2]) – usuwają z prawej lub lewej strony znaki z text, które znajdują się w text1. Jeżeli w text1 nie ma znaków to usuwane są spacje. ( na przykład ltrim (samouczek [,aue]) usunie nam z ‘samouczek’ litery aue dokładnie w takiej kolejności.)

- replace (text, text1 [,text2]) – chyba wszystko jasne w tej funkcji – czy aby na pewno? : podmieniamy wszystkie wystąpienia ciągu text1 w ciągu text na text2

- substr (text, m [,n]) – wycina z ciągu text n znaków począwszy od pozycji m. Jeśli nie podamy n to wycina wszystkie znaki od pozycji m do końca ciągu text. Jeśli chcemy wycinać znaki od końca ciągu text – musimy m ustawić na ujemne.

- to_char (liczba [,wzorzec]) – zamienia liczbę na postać ZNAKOWĄ podaną we wzorcu. O wzorcach później, chyba, że kogoś nagli to pytać, pisać itd

- to_number (text, [,wzorzec]) – zamienia ciąg ZNAKÓW na postać LICZBOWĄ oczywiście wg. wzorca

- length (text) – zwraca nam długość ciągu tekstu, jeśli text ma wartość NULL to zwraca nam oczywiście NULL (bo nie wiadomo co) a nie 0. Wbrew pozorom użyteczna funkcja.

Warto tez wspomnieć o funkcji, o której sam dowiedziałem się stosunkowo niedawno

- soundEX(text) – zwraca podobnie brzmiace ciągi tekstów – głównie może służyć do wyszukiwania nazwisk kiedy wiemy, że dzwoni ale nie wiemy w jakiej parafii

Do kompletu mamy jeszcze funkcje numeryczne, których oczywiście także jest bez liku. Oto kilka podstawowych:

Funkcje numeryczne

- round (m, [,n]) – zaokrągla m do n-tego miejsca po przecinku. Ważne, że ujemne n zaokrągla do n-tego miejsca PRZED przecinkiem. Jeśli n nie jest podane to n=0

- trunc (m, [,n]) – obcina m do n-tego miejsca po przecinku, ujemne n zamienia na 0 n cyfr przed przecinkiem, jeśli nie podano to wiadomo, że n=0

- power (m, n) – podnoszenie do potęgi n (podnosimy m do n)

- sqrt (m) – pierwiastek kwadratowy z liczby m

- sign (m) – zwraca nam znak liczby, czyli podaje informacje czy liczba jest ujemna czy dodatnia czy 0. W prosty sposób – wynikiem są 1, 0 , -1 w zalezności od znaku liczby m

- abs (m) – zwraca wartość bezwzględną liczby m

- mod (m, n) – zwraca resztę z dzielenia m przez n, jeśli n=0 zwraca m.

Oczywiście jest ich o wiele więcej łącznie ze wszystkimi sin, tan, sinh, cosh itd…

Przedstawiłem Wam podstawowe funkcje jakie są wbudowane w języku SQL – ponieważ jest ich ogrom, nie sposób wypisać je wszystkie. Stosując kombinacje tych funkcji jesteśmy w stanie wyciągnąć bardzo wiele informacji z tabel.

W następnej lekcji rozszerzymy temat o funkcje skalarne oraz ciekawe wyrażenia CASE.

Temat dzisiejszy należy do łatwych i przyjemnych. A dodatkowo do banalnych

Pod tajemniczą nazwą selekcji wierszy kryje się nic innego jak tylko warunek WHERE, który zawęża nam wynik zapytania. Nie zawsze bowiem potrzebujemy informacji o wszystkich rekordach w tabeli – to oczywiste. Dodając do instrukcji SELECT klauzulę WHERE wraz z jakimś wyrażeniem logicznym (czyli tym co będzie określało dokładniej czego szukamy) – zapytanie zwróci nam jedynie wiersze, dla których wyrażenie przyjęło wartość prawdziwą (tylko to o co zapytaliśmy i nic więcej).

Oczywiście aby ćwiczyć skuteczniej możemy wykonywać instrukcje na bazie, którą Wam udostępniłem.

Przykłady zastosowań (oczywiście ogranicza nas jedynie wyobraźnia i potrzeby szefa )

1. Wyświetl informację o klientach indywidualnych

SELECT *

FROM klienci

WHERE klt_typ = ‘IND’;

2. Wypisz opis zamówień złożonych przez klientów o numerach 1002 i 1003

SELECT zam_opis

FROM zamowienia

WHERE zam_klt_id IN (1002, 1003);

Formuła po klauzuli WHERE może być dowolnie skomplikowana

!! Musimy pamiętać o naszych kochanych NULLach – zostaną one POMINIĘTE przy selekcji jeżeli nie uwzględnimy ich po klauzuli WHERE !!

Przykładem niech będzie dajmy na to wybranie towarów objętych podatkiem innym niż 22%.

Z pozoru proste zadanie komplikuje się ponieważ kiedy napiszemy:

SELECT *

FROM towary

WHERE tow_podatek <> 22;

… to zapytanie nie zwróci wszystkich rekordów różnych od 22, NULL zostanie pominięty. A przecież może zdarzyć się tak, że jeszcze nie wpisaliśmy jaki mamy podatek lub mamy w firmie metodę, że nie wpisujemy 0 (zero) dla nieopodatkowanych produktów/usług/czegokolwiek – zwyczajnie zostawiamy puste pole, które domyślnie jest NULLem.

Poprawnie to zapytanie powinno wyglądać więc tak:

SELECT *

FROM towary

WHERE tow_podatek <> 22

OR tow_podatek is NULL;

 

Jeżeli mamy trochę wyobraźni lub gdy musimy zamienić NULL na 0 (zero), powinniśmy użyć funkcji NVL – takie zapytanie także będzie poprawne i NULLe nie zostaną pominięte:

SELECT *

FROM towary

WHERE nvl(tow_podatek,0) <> 22;

Czyli jeżeli gdzieś będzie NULL to zamieni nam się na 0.

Ot i cała filozofia. Bierzcie i jedzcie

Dzisiaj czas na ciut trudniejsze tematy. O ile ważne w naszej pracy – o tyle ciężkie do zrozumienia i zwirtualizowania sobie zagadnienia. Jednak przy odrobinie chęci okazuje się, że nie są to czary i magia ale zwykła logika, która ułatwi nam analizę danych oraz pozwoli zrozumieć nasze dotychczasowe błędy. (poza tym później będzie jeszcze trudniej )

Zaczynamy!

Każde wyrażenie arytmetyczne zawierające choć jeden składnik lub czynnik z wartością pustą produkuje pusty wynik.

Wypiszemy teraz z naszej bazy, nazwy towarów oraz ceny brutto (bez używania funkcji NVL)

SELECT tow_nazwa, tow_cena *(1+ tow_podatek/100)

FROM towary;

Przyjrzyjmy się wynikom i przeanalizujmy pojawienie się NULLi.

*Zasada ta nie obowiązuje w przypadku operatora konkatenacji (złączenia), kiedy wartość NULL jest traktowana jako pusty napis o zerowej długości.*

Wypiszmy teraz jako jeden napis imię, nazwisko, nazwę i adres klientów. Całość nazwiemy „Dane klientów”

SELECT klt_imie||‘ ‘||klt_nazwisko||‘ ‘||klt_nazwa||‘ ‘||klt_adres „Dane klientów”

FROM klienci

Możemy zauważyć, że podana na początku zasada nie zawsze działa… Jeżeli wyrażenie logiczne zawiera wartość pustą i jeżeli pomimo to wynik wyrażenia jest jednoznaczny, to wynik jest niepusty.

Wykonując te polecenia SQL musimy przeanalizować sobie wyniki oraz czytać na głos regułki – pomoże to zrozumieć o co chodzi.

Oczywiście wspieramy się tablicą prawdy dla NULL:

NOT NULL => NULL

TRUE AND NULL => NULL

FALSE AND NULL => FALSE

TRUE OR NULL => TRUE

FALSE OR NULL => NULL

Myślimy tutaj czysto logicznie.  Pod NULL podstawiamy jednocześnie TRUE i FALSE (bo nie wiemy co to jest) i na podstawie tabeli prawdy dostajemy wyniki. Dla przykładu: FALSE i NULL to FALSE ponieważ FALSE przy operaterze AND dla zarówno FALSE jak i TRUE daje FALSE. TRUE i NULL natomiast są NULLem ponieważ dla TRUE przy operatorze AND występują zarówno TRUE i FALSE – nie wiemy więc co tam jest i dlatego dajemy NULL. Proste

Dla miłego zakończenia dzisiejszej lekcji, wypiszemy sobie domyślne realizowane kolejności operatorów.

Priorytety operatorów

Składowe złożonych wyrażeń łączone są ze sobą w kolejności zależnej od priorytetów operatorów. W przypadku operatorów o tym samym priorytecie łączenie następuje od lewej do prawej strony.

Domyślną kolejność łączenia możemy zmienić przy pomocy nawiasów.

Poniżej znajdują się operatory uszeregowane w wiersze według priorytetu łączenia, od największego do najmniejszego.

*, /, NOT

+,  -,  ||

<, <=, =, >=,  >, <>, !=, ^=, IS, IN, BETWEEN, LIKE

AND

OR

DB2Docbook – dokumentowanie bazy danych w formacie Docbook

Świetny program do wspierania procesu dokumentacji baz danych. MSSQL, mySQL, PostgreSQL mu nie straszne. Pliki źródłowe w formacie XML, możliwość rozbicia dokumentacji na fragmenty, możliwość przekształcania formatu Docbook na HTML, PDF, docx itp..  to tylko kilka z wielu zalet programu.

Czemu o tym piszę?

Wiem, że większość z Wass nie dokumentuje swoich poczynań w świecie IT. Programy, całe systemiki powstają bez dokumentacji technicznej.

Piszę tego posta trochę na marginesie mojej głównej działalności ale i ku przestrodze. Jako szef działu IT widzę teraz problem od innej strony – bez dokumentacji nasze dzieła są martwe i skazane na niepowodzenie.

Ponieważ to Samouczek SQL – uczulam na dokumentację baz danych. Ułatwi to pracę i nauczy dobrych nawyków na przyszłość.

Polecam DB2Docbook firmy ProITSoft i namawiam do choćby przetestowania aplikacji. Dokumentacje i utrzymywanie ich aktualnych wersji to podstawa pracy w działach IT – nie zapominajcie o tym!

Czas na trochę logiki bazodanowej i sposobu myślenia Oracle. Ta lekcja będzie wstępem do konstruowania bardziej rozbudowanych zapytań (niekoniecznie trudniejszych).

Logika, którą musimy poznać zanim zaczniemy brnąć dalej, opiera się na zrozumieniu kolejności wykonywanych działań, zrozumieniu co to jest NULL, oraz poznaniu operatorów.

Oczywiście nie przedstawię wszystkich operatorów dostępnych w Oracle – wszystko możemy doczytać w sieci lub manualu do Oracla zwanym ORAREF (Oracle reference manual) – te, które przedstawię są wystarczające w 80% czynności administracyjnych lub statystycznych.

Zacznijmy

W instrukcji SELECT mogą wystąpić dowolnie skomplikowane wyrażenia zbudowane z nazw kolumn, wywołań funkcji i ze stałych literałów. Budując różne wyrażenia łączymy poszczególne komponenty przy pomocy operatorów. Poniżej przedstawię opis niektórych operatorów dostępnych w Oracle SQL.

Operatory arytmetyczne

+ , – , * , /        – dodawanie, odejmowanie, mnożenie, dzielenie

Operator konkatenacji

||    -operator ten służy do łączenia dwu wartości tekstowych.

Operatory porównań

> , >= , = , < , <= , <> , != , ^=

Operator zakresu

x [NOT] BEETWEEN y AND z           – operator ten sprawdza czy wartość x mieści [nie mieści]  się w przedziale domkniętym <y,z>

Operator przynależności do listy

x [NOT] IN (x1, x2,…,xn)        – operator ten sprawdza, czy wartość x znajduje [nie znajduje] się na liście wartości x1,x2…

—————————————————————-

Operator wzorca

x [NOT] LIKE y

Tutaj należy się wyjaśnienie. Operator ten sprawdza czy wartość x ‘przystaje’ [nie przystaje] do maski y. Chodzi tutaj o porównanie wartości (będziemy o tym pisać szerzej w innych lekcjach). Dla przykładu jeśli wybrać z kolumny wiek wszystkich 22-latków wystarczy, że napiszemy  = ’22′ . natomiast jeżeli chcemy znaleźć nazwiska zaczynające się na ‘Oło’, musimy skorzystać z maski. Czyli porównamy sobie to tak: wybierz z tabeli ludzie imię i nazwisko ale tylko tych osób których nazwiska zaczynają się na oło. Skorzystamy z tekiego zapisu SELECT imie, nazwisko FROM ludzie where nazwisko LIKE ‘oło%’;

! w ogromnym skrócie: % zastępuje nam dowolną ilość znaków natomiast ‘_’  (podkreślenie) zastępuje dokładnie jeden znak. Bazę danych o moje nazwisko możemy więc zapytać na mnóstwo sposobów:

- o___ia%

- oł%k

- o_o_i%

PAMIĘTAJMY O CASE SENSITIVE – WIELOŚĆ LITER MA ZNACZENIE

—————————————————————-

Operator testowania wartości

x IS [NOT] NULL                            – sprawdza czy x zawiera NULL czy nie

Operatory logiczne

AND, NOT, OR     – podstawowe operatory logiczne (i, nie, lub)

Dla operatorów logicznych istnieje tabela prawdy – niektórzy mogą kojarzyć ją z logiczną tabelą prawdy, o której mówi się na matematyce w szkole podczas logiki lub teorii zbiorów. Na podstawie tej tabeli możemy określić czy nasz SQL będzie miał powodzenie czy wyświetli bzdurę tylko dla tego, że źle zapytaliśmy. Niby jest to oczywiste ale warto tabelę znać.

Temat próbek jest dość krótki i prosty (chociaż wyniki są czasem zaskakujące).

Jeżeli nie wyspecyfikujemy w zapytaniu klauzuli ORDER BY , to dane są zwracane w kolejności w jakiej są składowane.

Aby ze wskazanej tabeli w losowy sposób wybrać tylko część wierszy, można skorzystać z klauzuli SAMPLE.

SELECT tow_nazwa FROM towary SAMPLE(20);

powyższy sql wyświetli nazwy losowo wybranych 20% towarów.

Na pewno są jakieś zastosowania tej funkcji, ja osobiście nie używam ale jednak warto o niej wiedzieć

Wiersze zwracane przez zapytanie (wyświetlane) nie są uporządkowane w żaden sposób, pojawiają się w nieokreślonej kolejności.

Aby uporządkować ich kolejność należy zastosować na końcu zapytania klauzulę (polecenie) ORDER BY.

Jeżeli nie potrzebujemy sortowania lepiej nie używać niepotrzebnie tej klauzuli – generuje ona dodatkowy nakład pracy dla naszej bazy danych a w przypadku wielu milionów rekordów, będzie to miało znaczenie.

* Aby posortować wynik zapytania po klauzuli ORDER BY należy dodać nazwę kolumny, według której chcemy posortować wynik zapytania

SELECT * FROM towary ORDER BY tow_nazwa;

Posortowaliśmy wynik w sposób następujący: wszystkie rekordy z tabeli towary będą posortowane po kolumnie tow_nazwa.

Domyślnie porządek sortowania jest rosnący, można to zmienić wstawiając po nazwie kolumny, według której sortujemy, odpowiednio ASC lub DESC.

Spróbujmy wypisać wszystkie informacje o towarach, porządkując je wg, nazwy malejąco:

SELECT * FROM towary ORDER BY tow_nazwa DESC;

* Kilka ciekawostek, których nie stosuje się na codzień w kodowaniu, a które mogą przydać się niektórym  wścibskim koderom

- zamiast kolumny lub aliasu po klauzuli ORDER BY może pojawić się numer określający pozycję składnika z listy składników klauzuli SELECT, według którego ma być posortowany wynik

SELECT tow_nazwa,

tow_cena,

tow_cena * (1 + NVL(tow_podatek, 0)/100)

FROM towary

ORDER BY 3 DESC;

Powyższy slq posortuje wynik według ceny brutto czyli 3 pozycji naszego selectu.

- po klauzuli ORDER BY może pojawić się kilka kolumn lub wyrażeń lub numerów rozdzielonych przecinkami. W takim wypadku wynik zapytania zostanie posortowany według pierwszej pozycji, następnie w przypadku, gdy pierwsza kolumna jest równa, to podzbiór zostanie posortowany według drugiej pozycji, itd.

- możemy zastosować więcej niż jedno sortowanie, jak to zrobić? oczywiście do Waszej dyspozycji przykład:

SELECT tow_nazwa,

tow_cena,

NVL(tow_podatek,0)

FROM towary

ORDER BY 3 DESC, tow_nazwa ASC;

W ten sposób sortujemy wg podatku malejąco a następnie wg ceny rosnąco.

!!! W przypadku sortowania w porządku rosnącym wartości puste (NULL) zostają wypisane na końcu, w przypadku sortowania w porządku malejącym wypisane zostają na początku !!!

! Pozycje znajdujące się po klauzuli ORDER BY nie muszą znajdować się na liście pozycji po klauzuli SELECT !

Aby wydobyć informacje z bazy danych musimy o nie jakoś zapytać. Skupimy się na razie na jednej tablicy czy jak kto woli tabeli.

Aby zacząć pytać musimy wiedzieć co chcemy otrzymać oraz gdzie się to znajduje.

Zakładając, że mamy dane osobowe: imię, nazwisko, adres znajdujące się w tabeli ADRESY – zapytajmy tabelę o wyświetlenie imion z naszej tabeli. Posłuży nam do tego SELECT:

SELECT imie FROM adresy

czyli: wybierz imiona z tabeli adresy – w ten sposób otrzymamy wynik, którym jest kolumna ‘imie’ z jej zawartością.

!WAŻNE! – bazę możemy skonfigurować w dowolny sposób, między innymi tak aby tolerowała znaki narodowe czyli tzw ogonki czy np. tak aby ignorowała kapitaliki czyli wielkie litery. W naszym kursie zakładamy, że nie mamy polskich znaków oraz, że bazie danych jest wszystko jedno czy zapytamy o IMIE, imie, Imie, czy ImiE – w przeciwnym razie wszystkie te nazwy oznaczały by co innego. Dla uproszczenia przyjmiemy w kursie, że tylko instrukcję będziemy pisać wielkimi literami a nazwy obiektów (kolumn, tabel itp) małymi. Pamiętajmy jednak, że w przypadku baz danych – wielkość MA znaczenie

———————-

Możemy oczywiście wybrać więcej niż jedną kolumnę – załóżmy, że chcemy tym razem mieć imię i nazwisko – w tym przypadku oddzielamy nazwy kolumn przecinkiem

SELECT imie, nazwisko FROM adresy

Wybierać możemy nie tylko kolumny ale także ‘stałe’. Stałe to właściwie słowa, które wyświetlą nam się kiedy spełnimy odpowiednie warunki – ponieważ do warunków jeszcze nie doszliśmy, zakładamy że spełniamy wszystkie

Przykład: wybiezmy z naszej tabeli adresy ale w taki sposób aby poprzedzał je wyraz ‘adres’. Nic prostrzego:

SELECT ‘ADRES’, adres FROM adresy

w wyniku otrzymamy coś w ten deseń:

ADRES   warszawa kolejowa 3

ADRES  starachowice szkolna 2

ADRES  kielce sienkiewicza 18

Czyli każdy nasz rekord (pojedyńczy wynik, wiersz) zostanie poprzedzony wyrazem ADRES.

Czasami musimy wybrać wszystkie kolumny z danej tabeli. W naszym przykładzie jest ich 3 ale co kiedy było by ich 120? Oczywiście nie musimy ich wszystkich wypisywać – możemy zastąpić je wszystkie gwiazdką:

SELECT * FROM klienci

w wyniku otrzymamy wszystkie kolumny z tabeli klienci

——————-

Czasami kiedy tworzymy jakieś zestawienie dla naszego szefa – musimy wykonać ładną prezentację. Problem pojawia się kiedy w jakiejś kolumnie nie ma wartości. Na przykład mamy w tabeli imię i nazwisko ale nie wpisaliśmy adresu bądź jest nam on nieznany. Jeżeli w tabelce nie ma wartości to przyjmuje ona domyślną wartość NULL (o nullach więcej i szerzej pomówimy w przyszłości). Jak teraz wyglądał by przykładowy wiersz w naszym wyniku?

imie|nazwisko|adres

Jacek |Ołowiak |NULL

nie wygląda to uroczo, może dało by się zastąpić ten NULL czymś sensownym? Ano dało by się. Służy nam funkcja NVL – sprawdza ona czy w danej kolumnie jest wartość NULL, jeżeli jest to zastępuje ją jakimś słowem lub inną daną

Zróbmy to w taki sposób aby dla wpisów nie posiadających adresu umieścić informację „brak adresu”. Ok? No to do dzieła:

SELECT imie , nazwisko , NVL(adres, ‘brak adresu’) FROM adresy

czyli NVL(nazwa_kolumny, wartosc_zastepcza)

!WAŻNE! – wartość zastępcza MUSI być tego samego typu co badana kolumna – czyli jeżeli tutaj mamy tekst to nie moglibyśmy wstawić tutaj np. kolumny wiek, która była by numerem

———————–

To jeszcze nie koniec poznawania zaledwie podstaw SELECTu

z prostrzych, które możemy tutaj poruszyć nie mącąc w głowach to:

DISTINCT – służy nam do wybrania unikatowych wartości z tabel, czyli nie interesuje nas ilość danych ale to jakie dane tam są.

Dla przykładu może się zdarzyć, że chcemy zobaczyć jakie nazwiska znajdują się w naszej tabelce – jeśli wybralibyśmy normalnie to otrzymamy pełną kolumnę nazwisko. A co jeśli mamy tam dwudziestu Ołowiaków i 40 Nowaków? Nie chcemy tego liczyć, chcemy aby pokazały się tylko raz:

SELECT DISTINCT nazwisko FROM adresy

ALIASY - to nazwy jakie możemy nadać kolumnom – nie musimy ich pokazywać szefowi tak jak się nazywają w rzeczywistości  – zwłaszcza, że często używa się skrotów w nazewnictwie kolumn.

Zakładając, że mamy w tabeli faktury, kolumny cen_net, cen_bru…. – dla przeciętnego odbiorcy takie zestawienie będzie kłopotliwe do odczytania. Nazwijmy je normalnie ceny netto i ceny brutto. Robimy to używając aliasów.

SELECT   cen_net    AS   ‘ceny netto’  , cen_bru AS   ‘ceny brutto’   FROM   faktury

I po kłopocie, dodam tylko, że słowo kluczowe AS nie jest wymagane.

————

Ostatnim elementem w tym rozdziale (aby nie było wszystkiego zbyt dużo) są operacje modyfikujące wyświetlane dane.

Jak wspomniałem na samym początku, w klauzuli SELECT możemy dokonywać obliczeń.

podam prosty przykład ponieważ szerzej omówimy to w kolejnych rozdziałach.

Dla ułatwienia przyjmijmy, że z tabeli wypłaty chcemy mieć kwoty netto i brutto. Niestety nasz durny administrator nie przewidział, że będziemy potrzebować wartości brutto…. musimy więc ją jakoś wyliczyć. Wiemy, że stawka podatku dla naszych zarobków to 17%. Co należy zrobić? No dodać wartośc podatku do wartości netto, brawo

więc:

SELECT netto,

netto + ((netto *17)/100) AS brutto

FROM wyplaty

Zgadza się? Jednocześnie użyliśmy aliasu aby nasz wynik nazwać brutto

Operacje, które ćwiczyliśmy NIE ZMIENIAJĄ WARTOŚCI W TABELI a jedynie modyfikują je przy wyświetlaniu dla uzyskania oczekiwanego wyniku – niby oczywiste ale warto wspomnieć.