Funkcje SQL

Funkcje znakowe

• LOWER(wartosc) – zamienia WIELKIE litery na małe;
• UPPER(wartosc) – zamienia małe litery na WIELKIE;
• INITCAP(wartosc) – zamienia pierwsze litery w słowie na duże;
• LPAD(wartosc, n [,’ciag’]), RPAD(wartosc, n [,’ciag’]) – uzupełnia kolumny z lewej (prawej) strony podanym ciagiem aż do długosci n znaków. Jesli ciag nie został podany to wypełnia spacjami;
  

  select LOWER(nazwa), UPPER(‘miesci sie na’), INITCAP(adres)
  from zespoly;
  
  select LPAD(nazwa,25,’*’), LPAD(nazwa,25), RPAD(nazwa,25,’.’)
  from zespoly;
  

• SUBSTR(wartosc, n [,m]) – z podanego łancucha znaków wycina m znaków poczawszy od pozycji n-tej;
• INSTR(wartosc, ‘ciag’ [,m,n]) – wskazuje miejsce pierwszego (n-tego) wystapienia ciagu w łancuchu znaków poczawszy od pozycji m-tej;
• LTRIM(wartosc [,’znaki’]), RTRIM(wartosc [,’znaki’]) – usuwa z lewej strony (prawej) podane znaki (spacje);
• LENGTH(wartosc) – zwraca długosc łancucha znaków;
  

  select SUBSTR(nazwisko,3,4), INSTR(etat,’PROF.’), LENGTH(etat)
  from pracownicy;
  
  select nazwa, LTRIM(nazwa,’ABCD’)
  from zespoly;
  

• TRANSLATE(zródło,z,na) – każde wystapienie w zródle znaku z ciagu z zostanie zastapione odpowiadajacym mu znakiem z ciagu na;
• REPLACE(zródło, wzór, nowy) – każde wystapienie w zródle ciagu wzorzec zostanie zastapione przez ciag nowy;
  

  select nazwisko, TRANSLATE(nazwisko, ‘ABC’, ‘XYZ’)
  from pracownicy;
  
  select etat, REPLACE(etat, ‘AS’, ‘**’);
  from pracownicy;
  

Funkcje liczbowe

• ROUND(wartosc, n) – zaokragla wartosc do n-tego dziesietnego miejsca po przecinku;
• TRUNC(wartosc, n) – obcina wartosc do n-tego dziesietnego miejsca po przecinku;
• CEIL(wartosc), FLOOR(wartosc) – najmniejsza (najwieksza) liczba całkowita wieksza lub równa (mniejsza lub równa) podanej wartosci;

  select ROUND(123.456,1), ROUND(123.456), ROUND(123.456,-1),
  TRUNC(123.456,1), TRUNC(123.456), TRUNC(123.456,-1)
  from dual;
  
  select FLOOR(1.5), CEIL(1.5)
  from dual;
  

• POWER(wartosc, n) – podnosi wartosc do podanej potegi;
• SQRT(wartosc) – oblicza pierwiastek kwadratowy z podanej wartosci;
• ABS(wartosc) – oblicza wartosc bezwzgledna wyrażenia;
• MOD(wartosc1, wartosc2) – zwraca reszte z dzielenia;

  select POWER(2,16), SQRT(64),ABS(-100), MOD(123456789,10)
  from dual;
  

Funkcje grupowe

Operuja na podzbiorach krotek relacji, nazywanych grupami, wyznaczaja wartosc skalarna operujac na zbiorze wartosci odczytanych z wielu krotek.

funkcje:

• AVG( [distinct|all] wyrażenie)
• COUNT( [distinct|all] wyrażenie)
• MAX( [distinct|all] wyrażenie)
• MIN( [distinct|all] wyrażenie)
• SUM( [distinct|all] wyrażenie)
• VARIANCE( [distinct|all] wyrażenie)
• STDDEV( [distinct|all] wyrażenie)

select count(*), MAX(placa_dod)
from pracownicy
where id_zesp=20;

select AVG(placa_pod)
from pracownicy;

Klauzula GROUP BY umożliwia podział krotek relacji na grupy. Krotki tej samej grupy maja identyczna wartosc atrybutu grupowania, który wskazano w klauzuli. Po podziale do każdej z grup można zastosowac jedna z tzw. funkcji grupowych, w szczególnosci funkcje count, umożliwiajaca okreslenie liczebnosci grupy.

Klauzula GROUP BY może byc stosowana rekurencyjnie, co oznacza, że można w niej wskazac wiele atrybutów grupowania. W takim przypadku jest możliwe wydzielenie podgrup w ramach wczesniej wydzielonych grup. Kolejnosc dzielenia relacji na grupy i podgrupy odpowiada kolejnosci atrybutów grupowania.

select id_zesp, AVG(placa_pod)
from pracownicy
group by id_zesp;

select id_zesp, etat, count(*)
from pracownicy
where etat !=’DYREKTOR’
group by id_zesp, etat;

Klauzula HAVING pozwala na wybór grup spełniajacych okreslone warunki, działa dla grup analogicznie jak klauzula where dla pojedynczych krotek. Klauzula having operuje na wczesniej wydzielonych grupach, a wiec na wyniku działania klauzuli group by. Syntaktycznie klauzula having może wystepowac zarówno przed, jak i po klauzuli group by.

Z logicznego punktu widzenia, powinnismy ja jednak stosowac po klauzuli group by. Tworzenie grup i obliczanie funkcji grupowych jest bowiem realizowane przed selekcja grup.

select etat, SUM(placa_pod)
from pracownicy
group by etat
having MAX(placa_pod)>1000;

select id_zesp, avg(placa_pod)
from pracownicy
group by id_zesp
having count(*)>3;