Grupowanie danych SELECT department_id, AVG(salary)

„Relacyjne Bazy Danych (Oracle)”
Prezentacja jest współfinansowana przez
Unię Europejską w ramach
Europejskiego Funduszu Społecznego w projekcie pt.
„Innowacyjna dydaktyka bez ograniczeń - zintegrowany rozwój Politechniki Łódzkiej zarządzanie Uczelnią, nowoczesna oferta edukacyjna i wzmacniania zdolności do
zatrudniania osób niepełnosprawnych”
Prezentacja dystrybuowana jest bezpłatnie
Projekt współfinansowany przez Unię Europejską
w ramach Europejskiego Funduszu Społecznego
Politechnika Łódzka, ul. Żeromskiego 116, 90-924 Łódź, tel. (042) 631 28 83
www.kapitalludzki.p.lodz.pl
1
SELECT
Projection
Selection
Table 1
Table 1
Join
Table 1
Table 2
2
SELECT


Kwerenda SELECT służy do pobierania
informacji z bazy danych spełniających podane
kryteria
SELECT ename,sal FROM emp

ename,sal – lista kolumn

* - wszystkie kolumny

emp – nazwa tabeli
3
Kwerendy

Kwerenda SELECT
4
Ograniczanie wierszy
PName
Price
Category
Manufacturer
Gizmo
$19.99
Gadgets
GizmoWorks
Powergizmo
$29.99
Gadgets
GizmoWorks
SingleTouch
$149.99
Photography
Canon
MultiTouch
$203.99
Household
Hitachi
PName
Price
Category
Manufacturer
Gizmo
$19.99
Gadgets
GizmoWorks
Powergizmo
$29.99
Gadgets
GizmoWorks
SELECT *
FROM
Product
WHERE category=‘Gadgets’
5
Ograniczanie wierszy
PName
Price
Category
Manufacturer
Gizmo
$19.99
Gadgets
GizmoWorks
Powergizmo
$29.99
Gadgets
GizmoWorks
SingleTouch
$149.99
Photography
Canon
MultiTouch
$203.99
Household
Hitachi
SELECT PName, Price, Manufacturer
FROM
Product
WHERE Price > 100
PName
Price
Manufacturer
SingleTouch
$149.99
Canon
MultiTouch
$203.99
Hitachi
6
Ograniczanie wierszy


Klauzula WHERE pozwala na ograniczanie
wyników zapytania do wierszy spełniających
kryteria
SELECT * FROM emp WHERE empno>7700
7
Ograniczanie wierszy

Klauzula WHERE



Klauzulę umieszczamy po klauzuli FROM
Warunek w klauzuli WHERE może składać się z
wielu części
SELECT * FROM emp WHERE empno>7700 AND
empno<7900
8
Wyrażenia arytmetyczne

SELECT last_name, salary, salary + 300
FROM employees;
9
Wyrażenia arytmetyczne

SELECT last_name, 12*salary*commission_pct
FROM employees;
10
Ograniczanie wierszy


Warunki można grupować używając nawiasów
SELECT * FROM emp WHERE (empno>7700
AND empno<7900) OR empno=7934;
11
Ograniczanie wierszy

Podstawowe operatory algebraiczne

„=” równy

„>” większy niż

„<” mniejszy niż

„>=” większy bądź równy

„<=” mniejszy bądź równy

„<>” różny
12
Ograniczanie wierszy

Inne operatory


BETWEEN – analogicznie do „>” AND „<”
SELECT * FROM emp WHERE sal BETWEEN
1500 AND 2000
13
Ograniczanie wierszy


Operator IN
SELECT * FROM emp WHERE sal IN
(1500,1600,3000,950);
14
Ograniczanie wierszy

Operator LIKE


Pozwala sprawdzić wzorzec wartości: „%” oznacza
dowolny ciąg znaków, „_” oznacza jeden znak
SELECT * FROM emp WHERE ename LIKE '%R'
15
Ograniczanie wierszy

Operator LIKE – przykłady

ename like '%ER'

ename LIKE '%E%'

ename LIKE '%E_'
16
Ograniczanie wierszy

Warunek wykorzystany po klauzuli WHERE jest
ewaluowany przez bazę danych, zwrócone
zostaną wiersze, dla których dany warunek jest
prawdziwy

Warunek nie musi zawierać nazw kolumn np:

SELECT * FROM emp WHERE 1=1

Warunek zawsze prawdziwy
17
Tabela DUAL


DUAL – metatabela w bazie danych Oracle
służąca do wykonywania prostych operacji
SELECT 2+2 FROM DUAL
18
Wartość specjalna NULL

NULL w danej kolumnie oznacza brak wartości


Wartość NULL nie oznacza ani pustego ciągu
znaków dla kolumn tekstowych ani wartości zero
dla kolumn liczbowych
NULL <> NULL
19
Ograniczanie kolumn


Listę zwróconych kolumn można ograniczyć
wpisując odpowiednie nazwy po słowie
kluczowym SELECT
SELECT ename,job,sal FROM emp WHERE
empno>7700;
20
Ograniczanie wyników

Operatory logiczne – pozwalają na łączenie
wyników wielu warunków

AND – oba warunki muszą być spełnione

OR – jeden z warunków musi być spełniony

NOT – operator jednoargumentowy – negowanie
listy wyników
21
SELECT DISTINCT


Klauzula DISTINCT pozwala na wybranie tylko
wierszy zawierających unikatową wartość
jednej lub więcej kolumn
SELECT DISTINCT job FROM emp;
22
SELECT DISTINCT


Klauzula DISTINCT zastosowana do wielu
kolumn
SELECT DISTINCT job,sal FROM emp;
23
Użycie operatora IS NULL
SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL;
24
Aliasy

SELECT last_name "Name" , salary*12 AS "Annual Salary”
FROM employees;
25
Funkcje


Funkcje pozwalają na zmianę sposobu
prezentacji wyniku, lub wykonanie operacji na
jednej lub więcej kolumnach przed prezentacją
wyniku
SELECT ename,
LENGTH(job) FROM emp;
26
Funkcje


Funkcje można stosować do kolumn, większość
funkcji występuje tylko dla kolumn danego typu
Wywołanie funkcji na typie danych, dla którego
nie została ona określona powoduje błąd
27
Funkcje

Wybrane funkcje zdefiniowane dla ciągów
znaków

CONCAT (wartość1, wartość2)

SUBSTR(wartość, początek, koniec)

LENGTH(wartość)

LPAD(wartość,długość,znak)/RPAD(wartość,długość,znak)

UPPER(wartość)/LOWER(wartość)

REVERSE(wartość)

INITCAP(wartość)
28
Funkcje

SELECT last_name ||' is a '|| job_id AS "Employee Details”
FROM employees;
29
Funkcje

Wybrane funkcje dla wartości liczbowych

ROUND(wartość,liczba_znaków)

TRUNC(wartość,liczba_znaków)

MOD(wartość,dzielnik)

SIN(wartość)/COS(wartość)/SINH(wartość)/COSH(wartość)

POWER(wartość,potega)
30
Funkcje

round(125.315) = 125

round(125.315, 0) = 125

round(125.315, 1) =125.3

round(125.315, 2) =125.32

round(125.315, 3) =125.315

round(-125.315, 2) =-125.32
31
Funkcje


Wykorzystanie funkcji przy klauzuli WHERE
SELECT * FROM emp WHERE
MOD(sal,200)=0;
32
Sortowanie wyników

Klauzula ORDER BY

Klauzulę umieszczamy po klauzuli WHERE
33
Sortowanie wyników



Można wykorzystać kilka kolumn do sortowania
Można sortować rosnąco lub malejąco
(ASC/DESC)
SELECT * FROM emp WHERE sal>2000
ORDER BY sal ASC,ename DESC;
34
Agregacje



Agregowanie wyników pozwala na analizę
wybranych kolumn z tabeli oraz
zaprezentowanie wyniku danej funkcji w postaci
jednej kolumny
Proste agregacje pozwalają np. na:

Obliczenie średniej wartości danej kolumny

Obliczenie wartości minimalnej/maksymalnej

Policzenie liczby wierszy
Funkcje grupujące ignorują wartości null w
kolumnie.
35
Agregacje
36
COUNT(*)



Agregacja COUNT(*) zwraca liczbę wierszy
pasujących do danego zapytania
Agregacja ta może skanować daną tabelę, bądź
indeks, jej wykorzystanie na dużych tabelach
może być powolne
SELECT COUNT(*) FROM emp WHERE
sal>2000;
37
MIN(), MAX(), AVG()

Korzystając z agregacji MIN,MAX lub AVG
można policzyć minimalną, maksymalną bądź
średnią wartość danej kolumny pasującej do
zapytania
38
Grupowanie danych
39
Grupowanie danych
SELECT
column, group_function(column)
FROM
table
[WHERE
condition]
[GROUP BY
group_by_expression]
[ORDER BY
column];
40
Grupowanie danych
SELECT
department_id, AVG(salary)
FROM
employees
GROUP BY department_id ;
41
Grupowanie danych
SELECT
department_id dept_id, job_id, AVG(salary)
FROM
employees
GROUP BY department_id, job_id ;
42
Grupowanie danych
Działanie HAVING odrzuca grup niespełniające warunku
1. Wiersze są grupowane.
2. Funkcja grupująca jest zastosowana.
3. Grupy spełniające warunek z klauzuli HAVING są wyświetlane.
SELECT
column, group_function
FROM
table
[WHERE
condition]
[GROUP BY
group_by_expression]
[HAVING
group_condition]
[ORDER BY
column];
43
Grupowanie danych
SELECT department_id, MAX(salary)
FROM
employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;
44
Funkcja NVL

Konwertuje null do wartości podanej

Dane mogą być liczbami, tekstem lub datami.

NVL(commission_pct,0)

NVL(job_id,‘Bez pracy')
45
Funkcja NVL

SELECT ename, sal, NVL(comm, 0),
(sal*12) + (sal*12*NVL(comm, 0)) AN_SAL
FROM emp;
46
Użycie wyrażenia CASE

Dają możliwość użycia konstrukcji typu IF-THEN-ELSE w
poleceniu SQL
CASE expr WHEN
[WHEN
WHEN
ELSE
END
comparison_expr1 THEN return_expr1
comparison_expr2 THEN return_expr2
comparison_exprn THEN return_exprn
else_expr]
47
Użycie wyrażenia CASE
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG'
THEN 1.10*salary
WHEN 'ST_CLERK'
THEN 1.15*salary
WHEN 'SA_REP'
THEN 1.20*salary
ELSE
"REVISED_SALARY"
salary END
FROM emp;
48
Łączenie tabel
49
Uzyskiwanie danych z wielu tabel
Product
PName
Price
Category
Manufacturer
Gizmo
$19.99
Gadgets
GizmoWorks
Powergizmo
$29.99
Gadgets
GizmoWorks
SingleTouch
$149.99
Photography
Canon
MultiTouch
$203.99
Household
Hitachi
Company
CName
StockPrice
Country
GizmoWorks
25
USA
Canon
65
Japan
Hitachi
15
Japan
50
Uzyskiwanie danych z wielu tabel
Product
Company
PName
Price
Category
Manufacturer
Cname
StockPrice
Country
Gizmo
$19.99
Gadgets
GizmoWorks
GizmoWorks
25
USA
Powergizmo
$29.99
Gadgets
GizmoWorks
Canon
65
Japan
SingleTouch
$149.99
Photography
Canon
Hitachi
15
Japan
MultiTouch
$203.99
Household
Hitachi
SELECT PName, Price
FROM
Product, Company
WHERE Manufacturer=CName AND Country=‘Japan’
AND Price <= 200
PName
Price
SingleTouch
$149.99
51
Iloczyny kartezjańskie
52
Łączenia zgodne z SQL
• Cross – iloczyn kartezjański
• Natural - naturalne
• Klauzula Using
• Pełne lub dwustronne zewnętrzne
• Arbitralne warunki łączenia dla łączeń
zewnętrznych
53
Łączenie tabel
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
54
Łączenie tabel
SELECT *
FROM
employees, departments
WHERE
employees.department_id = departments.department_id;
55
Używanie aliasów tabel
SELECT *
FROM
employees, departments
WHERE
employees.department_id = departments.department_id;
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM
employees e , departments d
WHERE
e.department_id = d.department_id;
56
Łączenia Naturalne
• Klauzula NATURAL JOIN bazuje na wszystkich kolumnach z
dwóch tabel, które mają tę samą nazwę.
• Wybiera wiersze z dwóch tabel tak, aby wartości w tych samych
kolumnach obu tabel były zgodne.
• Jeśli kolumny mające identyczne nazwy mają rożne typy
danych, wówczas zwracany jest błąd.
57
Łączenia Naturalne
SELECT department_id, department_name,
location_id, city
FROM
departments NATURAL JOIN locations ;
58
Tworzenie łączeń z klauzulą ON
• Warunek łączenia dla łączenia naturalnego w podstawowym przypadku jest
warunkiem równościowym dla wszystkich kolumn o tej samej nazwie.
• Aby określić arbitralny warunek lub określić kolumny do łączenia używa się
klauzuli ON.
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM
employees e JOIN departments d
ON
(e.department_id = d.department_id);
59
Tworzenie łączeń z klauzulą ON
SELECT employee_id, city, department_name
FROM
employees e
JOIN
departments d
ON
d.department_id = e.department_id
JOIN
locations l
ON
d.location_id = l.location_id;
60
Dodatkowe warunki
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM
employees e JOIN departments d
ON
(e.department_id = d.department_id)
AND
e.manager_id = 149 ;
61
„Relacyjne Bazy Danych (Oracle)”
Prezentacja jest współfinansowana przez
Unię Europejską w ramach
Europejskiego Funduszu Społecznego w projekcie pt.
„Innowacyjna dydaktyka bez ograniczeń - zintegrowany rozwój Politechniki Łódzkiej zarządzanie Uczelnią, nowoczesna oferta edukacyjna i wzmacniania zdolności do
zatrudniania osób niepełnosprawnych”
Prezentacja dystrybuowana jest bezpłatnie
Projekt współfinansowany przez Unię Europejską
w ramach Europejskiego Funduszu Społecznego
Politechnika Łódzka, ul. Żeromskiego 116, 90-924 Łódź, tel. (042) 631 28 83
www.kapitalludzki.p.lodz.pl
62