SQL per l

About This Presentation

Title:

SQL per l

Description:

SQL per l interrogazione di basi di dati Breve storia dell SQL SQL: Structured Query Language SQL sviluppato alla IBM nel 1973 Dal 1983 standard de facto Primo ... – PowerPoint PPT presentation

Number of Views:127

Avg rating:3.0/5.0

Slides: 131

Provided by: rosalba5

Category:

more less

Transcript and Presenter's Notes

Title: SQL per l

1
SQL per linterrogazione di basi di dati
2
Breve storia dellSQL

SQL Structured Query Language
SQL sviluppato alla IBM nel 1973
Dal 1983 standard de facto
Primo standard nel 1986 rivisto nel 1989 (SQL-89)
Secondo standard nel 1992 (SQL-2 o SQL-92)
Terzo standard nel 1999 (SQL-3 o SQL-99)
Quasi tutti i DBMS commerciali adottano lo
standard SQL piu estensioni proprie
(non-standard)
Alcuni sistemi commerciali
Oracle, Informix, Sybase, DB2, SQL-Server, etc.
Alcuni sistemi open-source
MySQL, Postgres
Esistono sistemi commerciali che utilizzano
interfacce tipo QBE (Query by Example) ACCESS
Tuttavia hanno sistemi per la traduzione
automatica in SQL

3
Standard SQL-92

E utilizzato, nel DML (Data Manipulation
Language), dai principali DBMS relazionali.
Mentre per il DDL (Data Definition Language) ci
sono variazioni significative.
Prevede 3 livelli di linguaggio, di complessita
crescente Entry SQL, Intermediate SQL, Full SQL

4
Capacità del comando SELECT
Proiezione
Selezione
Tabella 1
Tabella 1
Join
Tabella 1
Tabella 2
5
SELECT

SELECT DISTINCT EsprAttributiFROM
TabelleWHERE Condizione
La query
considera il prodotto cartesiano tra le Tabelle
fra queste seleziona solo le righe che soddisfano
Condizione
e infine valuta le espressioni specificate in
EsprAttributi (chiamata anche Target List)
La SELECT implementa gli operatori
Ridenominazione Proiezione, Selezione e Join
dellalgebra relazionale
Piu altro che vedremo piu avanti

6
Attributi e Tabelle

EsprAttributi EsprAttributo ,
EsprAttributo
Tabelle Tabella , Tabella
Dove Tabella sta per una determinata relazione ed
EsprAttributo e unespressione basata su degli
attributi delle tabelle citate nel FROM

7
Esempio query su una tabella
8
Esempio query su una tabella

SELECT Indirizzo, TelefonoFROM StudentiWHERE
NomeTeo Verdi

9
Query su due tabelle
10
Esempio di JOIN

SELECT CorsoFROM Esami,StudentiWHERE
Esami.Matricola Studenti.Matricola AND
NomeMario Rossi

11
Query su piu tabelle (JOIN)
Studenti
Esami
Corsi
12
Esempio di JOIN su tre tabelle

Quali professori hanno dato piu di 24 a Verdi ed
in quali corsi?
SELECT Professore, Corsi.CorsoFROM Corsi, Esami,
StudentiWHERE Corsi.Corso Esami.Corso AND
Esami.Matricola Studenti.Matricola AND
NomeTeo Verdi AND Voto gt 24

13
Scrittura Comandi SQL

I comandi SQL non sono case sensitive
Possono essere distribuiti in una o più righe
Clausole diverse sono usualmente inserite in
linee separate
Per convenzione si usa scrivere i costrutti SQL
in maiuscolo
SELECT Professore, Corsi.CorsoFROM Corsi, Esami,
StudentiWHERE Corsi.Corso Esami.Corso AND
Esami.Matricola Studenti.Matricola AND
NomeTeo Verdi AND Voto gt 24

14
Selezionare tutte le colonne
SQLgt SELECT 2 FROM dept

DEPTNO DNAME LOC ---------
-------------- ------------- 10 ACCOUNTING
NEW YORK 20 RESEARCH DALLAS
30 SALES CHICAGO 40 OPERATIONS
BOSTON
15
Selezionare certe colonne
SQLgt SELECT deptno, loc 2 FROM dept

DEPTNO LOC --------- ------------- 10
NEW YORK 20 DALLAS 30 CHICAGO
40 BOSTON
DEPTNO DNAME LOC ---------
-------------- ------------- 10 ACCOUNTING
NEW YORK 20 RESEARCH DALLAS
30 SALES CHICAGO 40 OPERATIONS
BOSTON
16
Selezione colonne

Linterpretazione algebrica delle variabili del
SELECT e loperatore ? (Proiezione)
dellalgebra relazionale
Con la variante che in Algebra relazionale
corrisponde alla non-applicazione del ?

17
Espressioni aritmetiche nel SELECT

Creare espressioni attraverso luso di operatori
aritmetici

Operatore - /
Descrizione Somma Sottrazione Moltiplicazione
Divisione
18
Uso degli operatori Aritmetici
SQLgt SELECT ename, sal, sal300 2 FROM emp

ENAME SAL exp ---------- ---------
--------- KING 5000 5300 BLAKE
2850 3150 CLARK 2450
2750 JONES 2975 3275 MARTIN
1250 1550 ALLEN 1600
1900 ... 14 rows selected.

19
Precedenza operatori aritmetici
SQLgt SELECT ename, sal, 12sal100 2 FROM
emp

ENAME SAL exp ---------- ---------
---------- KING 5000 60100 BLAKE
2850 34300 CLARK 2450
29500 JONES 2975 35800 MARTIN
1250 15100 ALLEN 1600
19300 ... 14 rows selected.
20
Uso delle parentesi
SQLgt SELECT ename, sal, 12(sal100) 2 FROM
emp

ENAME SAL exp ---------- ---------
----------- KING 5000
61200 BLAKE 2850 35400 CLARK
2450 30600 JONES 2975
36900 MARTIN 1250 16200 ... 14
rows selected.
21
Alias delle colonne

Ridenominare il nome di una colonna
Operatore ? (Ridenominazione) dellalgebra
relazionale
Deve seguire immediatamente il nome di una
colonna (SENZA VIRGOLA)
può essere usata opzionalmente la parola chiave
AS tra il nome della colonna e lalias.
Richiede doppio apice se lalias ha degli spazi

22
Uso dellAlias
SQLgt SELECT ename AS name, sal salary 2 FROM
emp

NAME SALARY -------------
---------...

SQLgt SELECT ename "Name", 2 sal12
"Annual Salary" 3 FROM emp

23
Alias di tabelle (Correlation Names)

SELECT ProfessoreFROM Corsi c, Esami eWHERE
c.Corso e.Corso AND
Matricola 123456
Per evitare ambiguità, colonne con lo stesso nome
su tabelle diverse devono essere specializzate
tramite lalias delle tabelle
SELECT Professore, c.CorsoFROM Corsi c, Esami
eWHERE c.Corso e.Corso AND
Matricola 123456
In caso contrario lSQL restituisce errore

24
Self JOIN

Alias necessario per self-join
SELECT s1.Matricola, s2.MatricolaFROM Studenti
s1, Studenti s2WHERE s1.matricola ltgt
s2.matricola
Cosa fa?

25
Righe duplicate

Le righe duplicate sono restituite per default

SQLgt SELECT deptno 2 FROM emp

DEPTNO --------- 10 30
10 20 ... 14 rows selected.
26
Eliminazione delle righe duplicate
E consentito dalluso della parola chiave
DISTINCT nella clausola SELECT
SQLgt SELECT DISTINCT deptno 2 FROM emp

DEPTNO --------- 10 20
30
27
Restrizioni ed ordinamento Dati

28
Esempio
29
Limitare le righe selezionate

Limitare le righe tramite luso della clausola
WHERE.
Corrisponde operatore ? (Restrizione)
dellalgebra relazionale
La clausola WHERE segue la clausola FROM
E opzionale

SELECT DISTINCT colonna alias, ... FROM
tabella WHERE condizione(i)

30
Uso della clausola WHERE
SQLgt SELECT ename, job, deptno 2 FROM emp
3 WHERE job'CLERK'

ENAME JOB DEPTNO ----------
--------- --------- JAMES CLERK
30 SMITH CLERK 20 ADAMS
CLERK 20 MILLER CLERK 10

31
Stringhe di caratteri e Date

Stringhe di caratteri e date vanno incluse tra
apici.
I caratteri sono case sensitive e le date sono
format sensitive.

SQLgt SELECT ename, job, deptno 2 FROM emp 3
WHERE ename 'JAMES'
32
Condizione

Condizione Predicato (Condizione)
NOT Condizione Condizione (AND OR) Condizione
Il risultato puo essere TRUE(T),FALSE(F) o
UNKOWN(U).

33
Predicati di confronto

Espr op (Espr ( Sottoselect ) )
op ? , ?, gt, ?, lt, ?
SottoSelect deve dare come risultato una tabella
con un solo elemento o vuota (nel qual caso
produce il valore U). Il valore U viene prodotto
anche nel caso che uno degli operandi ha il
valore NULL.

34
Operatori di confronto
Operatore gt gt lt lt ltgt
Significato Uguale a più grande di maggiore o
uguale di minore di minore o uguale a diverso
35
Uso degli Operatori di Confronto
SQLgt SELECT ename, sal, comm 2 FROM emp 3
WHERE salltcomm

ENAME SAL COMM ----------
--------- --------- MARTIN 1250 1400

36
Altri Operatori di Confronto
Operatore BETWEEN...AND... IN(list) LIKE IS NULL
Significato compreso tra due valori Corrisp. ad
uno dei valori nella lista Operatore di pattern
matching Valore nullo
37
Uso delloperatore BETWEEN

BETWEEN consente la selezione di righe con
attributi in un particolare range.

SQLgt SELECT ename, sal 2 FROM emp 3
WHERE sal BETWEEN 1000 AND 1500

ENAME SAL ---------- --------- MARTIN
1250 TURNER 1500 WARD
1250 ADAMS 1100 MILLER 1300

38
Predicato BETWEEN

Espr1 NOT BETWEEN Espr2 AND Espr3.
Equivale a
NOT Espr2 ?Espr1 AND Espr1?Espr3

39
Uso delloperatore IN

E usato per selezionare righe che hanno un
attributo che assume valori contenuti in una
lista.

SQLgt SELECT empno, ename, sal, mgr 2 FROM
emp 3 WHERE mgr IN (7902, 7566, 7788)

EMPNO ENAME SAL
MGR --------- ---------- --------- ---------
7902 FORD 3000 7566 7369
SMITH 800 7902 7788 SCOTT
3000 7566 7876 ADAMS
1100 7788

40
Uso delloperatore LIKE

LIKE è usato per effettuare ricerche wildcard di
una stringa di valori.
Le condizioni di ricerca possono contenere sia
letterali, caratteri o numeri.
denota zero o più caratteri.
_ denota un carattere.

SQLgt SELECT ename 2 FROM emp 3 WHERE ename
LIKE 'S'
41
Uso delloperatore LIKE

Il pattern-matching di caratteri può essere
combinato.
Iidentificatore ESCAPE (\) deve essere usato
per cercare "" o "_".

SQLgt SELECT ename 2 FROM emp 3 WHERE ename
LIKE '_A'

ENAME ---------- MARTIN JAMES WARD
42
Operatori di Match

Attributo NOT LIKE Stringa
Dove Stringa puo contenere anche
_ che fa match con qualunque carattere
che fa match con qualunque sequenza di
caratteri
vale U se lattributo e NULL

43
Esempio

SELECT NomeFROM StudentiWHERE Indirizzo LIKE
Via Etnea
Restituisce tutti gli studenti che abitano in Via
Etnea

44
Predicati

Espr IS NOT NULL
esempio
SELECT Nome FROM StudentiWHERE Telefono IS NOT
NULL

45
Operatori Logici
Operatore ANDOR NOT
Significato Restituisce TRUE if entrambe le
condizioni sono TRUE Restituisce TRUE se almeno
una delle condizioni è TRUE Restituisce TRUE se
la condizione è FALSE
46
Logica a tre valori
UUnknown
47
Uso delloperatore AND
AND richiede entrambe le condizioni TRUE.
SQLgt SELECT empno, ename, job, sal 2 FROM
emp 3 WHERE salgt1100 4 AND job'CLERK'

EMPNO ENAME JOB
SAL --------- ---------- --------- ---------
7876 ADAMS CLERK 1100 7934
MILLER CLERK 1300

48
Uso delloperatore OR
OR richiede almeno una condizione TRUE.
SQLgt SELECT empno, ename, job, sal 2 FROM
emp 3 WHERE salgt1100 4 OR job'CLERK'

EMPNO ENAME JOB
SAL --------- ---------- --------- ---------
7839 KING PRESIDENT 5000 7698
BLAKE MANAGER 2850 7782 CLARK
MANAGER 2450 7566 JONES
MANAGER 2975 7654 MARTIN SALESMAN
1250 ... 7900 JAMES CLERK
950 ... 14 rows selected.
49
Uso delloperatore NOT
SQLgt SELECT ename, job 2 FROM emp 3 WHERE
job NOT IN ('CLERK','MANAGER','ANALYST')

ENAME JOB ---------- --------- KING
PRESIDENT MARTIN SALESMAN ALLEN
SALESMAN TURNER SALESMAN WARD SALESMAN

50
Regole di precedenza
Ordine di val. Operatore 1 Tutti gli
operatori di confronto 2 NOT 3 AND 4 OR

La precedenza puo essere controllata tramite il
normale uso di parentesi

51
Regole di precedenza
SQLgt SELECT ename, job, sal 2 FROM emp 3
WHERE job'SALESMAN' 4 OR
job'PRESIDENT' 5 AND salgt1500

ENAME JOB SAL ----------
--------- --------- KING PRESIDENT
5000 MARTIN SALESMAN 1250 ALLEN
SALESMAN 1600 TURNER SALESMAN
1500 WARD SALESMAN 1250
52
Regole di precedenza
Luso delle parentesi forza la priorità
SQLgt SELECT ename, job, sal 2 FROM emp
3 WHERE (job'SALESMAN' 4 OR
job'PRESIDENT') 5 AND salgt1500

ENAME JOB SAL ----------
--------- --------- KING PRESIDENT
5000 ALLEN SALESMAN 1600
53
Ordinamento

ORDER BY Attributo DESC , Attributo DESC
Va posto dopo il WHERE e fa si che il risultato
sia ordinato secondo Attributo in senso crescente
mentre se lo si vuole decrescente si deve
aggiungere DESC

54
Esempio

SELECT e.Corso, e.VotoFROM Esami e, Studenti
sWHERE e.Matricola s.Matricola
AND s.Nome Mario RossiORDER BY Voto
DESC

55
Visualizzare Dati da più Tabelle
56
Obiettivi

Al completamento della lezione, dovreste essere
in grado di
Scrivere comandi SELECT per accedere a dati da
più tabelle
Vedere dati che generalmente non soddisfano una
condizione di join con luso delle outer join
Fare la Join di una tabella con se stessa

57
Ottenere dati da più Tabelle
IMPIEGATI
DIPARTIMENTI

EMPNO ENAME ... DEPTNO------ ----- ... ------
7839 KING ... 10 7698 BLAKE ... 30
... 7934 MILLER ... 10
DEPTNO DNAME LOC ------
---------- -------- 10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS 30 SALES CHICAGO
40 OPERATIONS BOSTON
EMPNO DEPTNO LOC ----- ------- -------- 7839
10 NEW YORK 7698 30 CHICAGO 7782
10 NEW YORK 7566 20 DALLAS 7654 30
CHICAGO 7499 30 CHICAGO ... 14 rows selected.
58
Cosa è una Join?

La join viene usata per effettuare query su più
tabelle.
La condizione di join va scritta nella clausola
WHERE.
Mettere come prefisso il nome della tabella se la
stessa colonna appare in più di una tabella.

SELECT tabella1.colonna, tabella2.colonna FROM tab
ella, tabella2 WHERE tabella1.colonna1
tabella2.colonna2
59
Prodotto Cartesiano

Il prodotto cartesiano e ottenuto quando
Una condizione join e omessa
Una condizione join e non valida
Tutte le righe della prima tabella ammettono join
con tutte le righe della seconda
Per evitare il prodotto cartesiano, includere
sempre condizioni join valida nella clausola
WHERE .

60
Generare un Prodotto Cartesiano
IMPIEGATI (14 righe)
DIPARTIMENTI (4 righe)
EMPNO ENAME ... DEPTNO------ ----- ... ------
7839 KING ... 10 7698 BLAKE ... 30
... 7934 MILLER ... 10
DEPTNO DNAME LOC ------
---------- -------- 10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS 30 SALES CHICAGO
40 OPERATIONS BOSTON
ENAME DNAME ------ ---------- KING ACCOUNTIN
G BLAKE ACCOUNTING ... KING RESEARCH BLAKE
RESEARCH ... 56 rows selected.
61
Cosa e una Equijoin?
IMPIEGATI
DIPARTIMENTI
EMPNO ENAME DEPTNO ------ ------- -------
7839 KING 10 7698 BLAKE 30
7782 CLARK 10 7566 JONES 20
7654 MARTIN 30 7499 ALLEN 30
7844 TURNER 30 7900 JAMES 30
7521 WARD 30 7902 FORD 20
7369 SMITH 20 ... 14 rows selected.

DEPTNO DNAME LOC ------- ----------
-------- 10 ACCOUNTING NEW YORK 30
SALES CHICAGO 10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS 30 SALES
CHICAGO 30 SALES CHICAGO 30
SALES CHICAGO 30 SALES CHICAGO
30 SALES CHICAGO 20 RESEARCH DALLAS
20 RESEARCH DALLAS ... 14 rows selected.
62
Estrarre Record con Equijoin
SQLgt SELECT emp.empno, emp.ename, emp.deptno,
2 dept.deptno, dept.loc 3 FROM emp, dept
4 WHERE emp.deptnodept.deptno

EMPNO ENAME DEPTNO DEPTNO LOC ----- ------
------ ------ --------- 7839 KING 10 10
NEW YORK 7698 BLAKE 30 30 CHICAGO
7782 CLARK 10 10 NEW YORK 7566 JONES
20 20 DALLAS ... 14 rows selected.
63
Condizioni di Ricerca addizionaliUso
delloperatore AND
EMP
DEPT

EMPNO ENAME DEPTNO ------ ------- -------
7839 KING 10 7698 BLAKE 30
7782 CLARK 10 7566 JONES 20
7654 MARTIN 30 7499 ALLEN 30
7844 TURNER 30 7900 JAMES 30
7521 WARD 30 7902 FORD 20
7369 SMITH 20 ... 14 rows selected.
DEPTNO DNAME LOC ------
--------- -------- 10 ACCOUNTING NEW YORK
30 SALES CHICAGO 10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS 30 SALES CHICAGO
30 SALES CHICAGO 30 SALES CHICAGO
30 SALES CHICAGO 30 SALES CHICAGO
20 RESEARCH DALLAS 20 RESEARCH DALLAS ... 14
rows selected.
64
Condizioni di Ricerca Uso delloperatore AND
SQLgt SELECT emp.empno, emp.ename, emp.deptno,
2 dept.deptno, dept.loc 3 FROM emp, dept
4 WHERE emp.deptnodept.deptno AND
ENAMEKING

65
Join di piu di due Tabelle
CLIENTI
NAME CUSTID ----------- ------ JOCKSPORTS
100 TKB SPORT SHOP 101 VOLLYRITE 102 JUST
TENNIS 103 KT SPORTS 105 SHAPE UP
106 WOMENS SPORTS 107 ... ... 9 rows selected.
66
Join di piu di due Tabelle
SQLgt SELECT 2 FROM clienti, ordini,
prod 3 WHERE clienti.custidordini.custid
AND prod.ordidprod.ordid

67
Predicati Di Appartenenza e Quantificatori
68
Condizione

Condizione Predicato (Condizione)
NOT Condizione Condizione (AND OR) Condizione
Il risultato puo essere TRUE(T),FALSE(F) o
UNKOWN(U).

69
Predicati di Appartenenza

Espr NOT IN ( (SottoSelect) (Valore ,
Valore ) ).
Vale U se Espr e NULL oppure se
NULL e fra i valori della SottoSelect
SELECT Matricola
FROM Esami
WHERE Voto IN ( 18, 19, 20 )

70
IN o Join?

IN va usato quando e strettamente necessario e
non a posto di giunzioni
SELECT Nome
FROM Studenti
WHERE Matricola IN
(SELECT Matricola
FROM Esami
WHERE Votogt27)

71
Va meglio scritta come

SELECT DISTINCT s.Nome
FROM Studenti s, Esami e
WHERE s.Matricola e.Matricola AND
Voto gt 27
E piu efficiente perche ce un ottimizzatore
che velocizza le join rispetto alle SottoSelect

72
Predicati Esistenziali

NOT EXISTS ( SottoSelect )
E Vero se la SottoSelect non ritorna linsieme
vuoto.
SELECT Nome
FROM Studenti s
WHERE NOT EXISTS
(SELECT
FROM Esami e, Corsi c
WHERE e.Corso c.Corso
AND s.Matricola
e.Matricola
AND c.Professore
Giuffrida)

Nota nella sottoselect si puo usare la
variabile di correlazione della select superiore
ma non il contrario
73
EXISTS o JOIN?

SELECT e.Matricola
FROM Esami e
WHERE EXISTS
(SELECT
FROM Corsi c
WHERE c.Corso e.Corso
AND c.Professore
Giuffrida)

74
Usando lOttimizzatore di Join

SELECT e.Matricola
FROM Esami e, Corsi c
WHERE c.Corso e.Corso
AND c.ProfessoreGiuffrida
E piu efficiente.

75
Altri Quantificatori

Espr op (ANY ALL) (SottoSelect)
op ? , ?, gt, ?, lt, ?
SELECT s.Nome
FROM Studenti s, Esami e
WHERE s.Matricola e.Matricola AND
e.Voto gt ALL
(SELECT DISTINCT f.Voto
FROM Esami f , Studenti t
WHERE f.Matricola
t.Matricola AND
t.Nome Mario Rossi)

76
Altri quantificatori

Espr ANY (SottoSelect) equivale a Espr IN
(Sottoselect)
Espr NOT IN (SottoSelect) non equivale a Espr ltgt
ANY (SottoSelect) ma a Espr ltgt All (SottoSelect)

77
Ricordiamo lesempio

Agenti(CodiceAgente,Nome,Zona Supervisore,Commissi
one)
Clienti(CodiceCliente,Nome,Citta,Sconto)
Ordini(CodiceOrdine,CodiceCliente,CodiceAgente,Art
icolo,Data,Ammontare)

78
Quantificatore Universale

Supponiamo di voler trovare i codici di quei
clienti che hanno fatto ordini a TUTTI gli agenti
di Catania.
Per ogni agente z di Catania esiste un ordine y
del nostro cliente x a z.
?z?y y(n,x,z,p,d,a) sse
? ?z ? ?y y(n,x,z,p,d,a)

79
Tradotta in SQL

SELECT c.CodiceClienteFROM Clienti cWHERE
NOT EXISTS (SELECT
FROM Agenti a
WHERE a.Zona Catania AND
NOT EXISTS (
SELECT FROM
Ordini v WHERE
v.CodiceCliente c.CodiceCliente
AND v.CodiceAgente
a.CodiceAgente) )

80
Aggregazione dati

81
Obiettivi

Al completamento della lezione, dovreste essere
in grado di
Identificare le funzioni di raggruppamento
Descriverne luso
Raggruppare dati usando GROUP BY
Includere ed escludere righe tramite luso di
HAVING

82
Cosa sono?

Operano su insiemi di righe per dare un risultato
per gruppo.

IMPIEGATI

DEPTNO SAL --------- --------- 10
2450 10 5000 10
1300 20 800 20 1100
20 3000 20 3000 20
2975 30 1600 30 2850
30 1250 30 950 30
1500 30 1250

MAX(SAL) --------- 5000
Salario Massimo
83
Funzioni Statistiche

MAX,MIN,COUNT,AVG,SUM
Operano sui valori di un certo attributo
ignorando i valori NULL
Se i valori sono tutti NULL allora valgono tutte
NULL eccetto COUNT che vale zero .
COUNT(DISTINCT..) da il numero dei valori
distinti di un attributo mentre COUNT() da il
numero delle righe

84
Esempi

SELECT MIN(Voto),MAX(Voto),AVG(Voto)FROM
EsamiWHERE Matricola 123456
SELECT COUNT()FROM EsamiWHERE Corso
Database 1

85
Quali sono

AVG
COUNT
MAX
MIN
STDDEV
SUM
VARIANCE

86
Uso

SELECT column, group_function(column) FROM tabl
e WHERE condition GROUP BY column ORDER
BY column
87
Uso di AVG e SUM

Possono essere usati su dati numerici.

SQLgt SELECT AVG(sal), MAX(sal), 2 MIN(sal),
SUM(sal) 3 FROM emp 4 WHERE job LIKE 'SALES'

AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL) --------
--------- --------- --------- 1400 1600
1250 5600
88
Uso di MIN e MAX

Possono essere usati su qualsiasi tipo.

SQLgt SELECT MIN(hiredate), MAX(hiredate) 2
FROM emp

MIN(HIRED MAX(HIRED --------- --------- 17-DEC-80
12-JAN-83
89
Uso di COUNT

COUNT() ritorna il numero di righe di una
tabella.

SQLgt SELECT COUNT() 2 FROM emp 3
WHERE deptno 30

COUNT() --------- 6
90
Creare gruppi di dati
IMPIEGATI

DEPTNO SAL --------- --------- 10
2450 10 5000 10
1300 20 800 20 1100
20 3000 20 3000 20
2975 30 1600 30 2850
30 1250 30 950 30
1500 30 1250
2916.6667
salario medio in IMPIEGATIper
ogni dipartimento
DEPTNO AVG(SAL) ------- --------- 10
2916.6667 20 2175 30 1566.6667

2175
1566.6667
91
Creare gruppi tramite GROUP BY
SELECT column, group_function(column) FROM table
WHERE condition GROUP BY group_by_expression
ORDER BY column

Divide le righe di una tabella in gruppi piu
piccoli.

92
Raggruppamento

GROUP BY Attributo , Attributo HAVING
Condizione
Va posto dopo WHERE e opera una partizione delle
righe del risultato in base ad eguali valori su
quegli attributi (NULL incluso). Quindi si
produce una n-upla per ogni classe di equivalenza
che soddisfa la condizione HAVING

93
Uso di GROUP BY

Tutte le colonne della SELECT che non sono in
funzioni di gruppo devono essere nella GROUP BY.

SQLgt SELECT deptno, AVG(sal) 2 FROM emp
3 GROUP BY deptno

DEPTNO AVG(SAL) --------- --------- 10
2916.6667 20 2175 30 1566.6667

94
Uso GROUP BY

La colonna di GROUP BY non deve essere
necessariamente nella SELECT.

SQLgt SELECT AVG(sal) 2 FROM emp 3
GROUP BY deptno

AVG(SAL) --------- 2916.6667 2175 1566.6667

95
Raggruppare piu di una colonna
IMPIEGATI
DEPTNO JOB SAL --------- ---------
--------- 10 MANAGER 2450 10
PRESIDENT 5000 10 CLERK
1300 20 CLERK 800 20
CLERK 1100 20 ANALYST
3000 20 ANALYST 3000 20
MANAGER 2975 30 SALESMAN
1600 30 MANAGER 2850 30
SALESMAN 1250 30 CLERK
950 30 SALESMAN 1500 30
SALESMAN 1250

JOB SUM(SAL) --------- --------- CLERK
1300 MANAGER 2450 PRESIDENT
5000 ANALYST 6000 CLERK
1900 MANAGER 2975 CLERK
950 MANAGER 2850 SALESMAN 5600
DEPTNO -------- 10 10 10 20 20 20 30 30 30

sommare I salari in IMPIEGATIper ogni lavoro,
Ragruppati per dipartimeno
96
Uso di GROUP BY su colonne multiple
SQLgt SELECT deptno, job, sum(sal) 2 FROM
emp 3 GROUP BY deptno, job

DEPTNO JOB SUM(SAL) --------- ---------
--------- 10 CLERK 1300 10
MANAGER 2450 10 PRESIDENT
5000 20 ANALYST 6000 20
CLERK 1900 ... 9 rows selected.

97
Query illegali con funzioni di raggruppamento

Ogni colonna o espressione della SELECT che non
e argomento di funzioni di deve essere nella
GROUP BY.

SQLgt SELECT deptno, COUNT(ename) 2 FROM emp
SELECT deptno, COUNT(ename) ERROR at
line 1 ORA-00937 not a single-group group
function
98
Query illegali con funzioni di raggrup.

Non puo essere usata la WHERE per restringere I
gruppi.
Deve essere usata la HAVING.

SQLgt SELECT deptno, AVG(sal) 2 FROM emp 3
WHERE AVG(sal) gt 2000 4 GROUP BY deptno
WHERE AVG(sal) gt 2000 ERROR at line
3 ORA-00934 group function is not allowed here
99
Escludere gruppi
IMPIEGATI
DEPTNO SAL --------- --------- 10
2450 10 5000 10
1300 20 800 20 1100
20 3000 20 3000 20
2975 30 1600 30 2850
30 1250 30 950 30
1500 30 1250

salario massimo per dipartmento maggiore
di2900
DEPTNO MAX(SAL) --------- --------- 10
5000 20 3000

100
Clausola HAVING

Uso di HAVING per restringere gruppi
Le righe sono raggruppate.
La funzione di raggruppamento e applicata.

SELECT column, group_function FROM table WHERE c
ondition GROUP BY group_by_expression HAVING g
roup_condition ORDER BY column

101
Uso di HAVING
SQLgt SELECT deptno, max(sal) 2 FROM emp
3 GROUP BY deptno 4 HAVING max(sal)gt2900

DEPTNO MAX(SAL) --------- --------- 10
5000 20 3000

102
Uso di HAVING

SQLgt SELECT job, SUM(sal) PAYROLL 2 FROM
emp 3 WHERE job NOT LIKE 'SALES' 4
GROUP BY job 6 ORDER BY SUM(sal)
5 HAVING SUM(sal)gt5000
JOB PAYROLL --------- --------- ANALYST
6000 MANAGER 8275

103
Funzioni di raggruppamento annidate
SQLgt SELECT max(avg(sal)) 2 FROM emp 3
GROUP BY deptno

MAX(AVG(SAL)) ------------- 2916.6667

104
Esempio

SELECT Nome, Matricola MIN(Voto),MAX(Voto),AVG(Vot
o)FROM Esami, StudentiWHERE Esami.Matricola
Studenti.MatricolaGROUP BY Nome,MatricolaHAVING
COUNT() gt 8

105
Sommario
SELECT column, group_function(column) FROM table
WHERE condition GROUP BY group_by_expression
HAVING group_condition ORDER BY column

Ordine di valutazione delle clausole
WHERE
GROUP BY
HAVING

106
Visualizzare Dati da piu tabelle ()

Diversi tipi di Joins
Operatori Insiemistici

107
Tabelle

Tabelle Tabella Ide , Tabella Ide
Tabella Ide Tabella OpInsiem Tabella
Tabella Giunzione Tabella
USING (Attributo,Attributo)ON Condizione

108
Giunzioni ed Operatori Insiemistici

Giunzione CROSSUNIONNATURAL LEFT RIGHT
FULL JOIN
OpInsiem (UNION INTERSECT EXCEPT)
CORRESPONDING BY ( Attributo ,Attributo)
USING e ON solo con JOIN LEFT, RIGHT,FULL solo
con NATURAL JOIN e JOIN

109
Ancora su Join ed Operatori Insiemistici

Cross Join e il prodotto cartesiano
Union Join e lunione esterna cioe si estendono
le due tabelle con le colonne dellaltra con
valori nulli e si fa lunione delle due stesse
tabelle.

110
Ancora sulle Join

Natural Join e quella classica
Join... Using e la natural join sui dati
attributi
JoinOn su quelli che soddisfano una data
condizione

111
Esempi

Natural Join
SELECT Studenti.Nome,Esami.Corso,Esami.VotoFROM
Esami NATURAL JOIN Studenti
Nome,Corso e Voto degli esami

112

LEFTRIGHTFULL usato con Natural Join o Join
e la giunzione esterna nelle tre modalita
sinistra,destra o completa.

113
Outer Join Operators
Full outer join
Right Outer Join
Left Outer Join
Join
Le righe che soddisfano la join
Le righe escluse dalla join della tabella a sx
Le righe escluse dalla join della tabella a dx
114
Altro Esempio

Agenti(CodiceAgente,Nome,Zona Supervisore,Commissi
one)
Clienti(CodiceCliente,Nome,Citta,Sconto)
Ordini(CodiceOrdine,CodiceCliente,CodiceAgente,Art
icolo,Data,Ammontare)

115
Esempio di Join On

SELECT Agenti.CodiceAgente,Ordini.AmmontareFROM
Agenti JOIN Ordini ON Agenti.Supervisore
Ordini.CodiceAgente
Codice agente ed ammontare degli ordini dei
supervisori

116
Giunzione Esterna

SELECT Agenti.CodiceAgente,Ordini.AmmontareFROM
Agenti NATURAL LEFT JOIN Ordini
Codice agente ed ammontare degli agenti incluso
quelli che non hanno effettuato ordini (avranno
ammontare NULL)

117
Le operazioni su insiemi
A UNION B
A INTERSECT B
A MINUS B
118
Le operazioni su insiemi

OpInsiem (UNION INTERSECT EXCEPT)
CORRESPONDING BY ( Attributo ,Attributo)
Union,Intersect,Except sono ?,?,-. CORRESPONDING
fa proiettare sugli attributi comuni e poi si
applica loperatore insiemistico. Se ce anche
BY si specificano su quali comuni attributi
proiettare

119
Unione

SELECT FROM Clienti UNION CORRESPONDING Agenti
Fornisce tutti i nomi dei clienti e degli agenti.
In effetti nei sistemi commerciali sarebbe
SELECT Nome FROM Clienti
UNION
SELECT Nome FROM Agenti

120
Formalizziamo
121
Attributi

Attributi Espr AS NuovoNome ,
Espr AS NuovoNome
Espr Ide.Attributo Costante
( Espr ) - Espr ? Espr (SUM COUNT
AVG MAX MIN) ( DISTINCT Ide. Attributo
) COUNT ( )
? ( - / )

122
Esempio