Basi di dati I Prof.ssa Rosalba Giugno Prof. Alfredo Pulvirenti SQL :Structured Query Language:...
-
Upload
bibiana-pavan -
Category
Documents
-
view
217 -
download
0
Transcript of Basi di dati I Prof.ssa Rosalba Giugno Prof. Alfredo Pulvirenti SQL :Structured Query Language:...
Basi di dati I
Prof.ssa Rosalba GiugnoProf. Alfredo Pulvirenti
SQL :Structured Query Language: SELECT (II)
Giugno-Pulvirenti AA 2005-2006
Tabelle
• Tabelle ::= Tabella [Ide] {, Tabella [Ide]}
• Tabella::= Ide | Tabella OpInsiem Tabella | Tabella Giunzione Tabella
• [USING “(“Attributo{,Attributo}“)”|ON Condizione]
Giugno-Pulvirenti AA 2005-2006
Giunzioni ed Operatori Insiemistici
• Giunzione ::= [CROSS|UNION|NATURAL] [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
Giugno-Pulvirenti AA 2005-2006
Ancora su Join ed Operatori Insiemistici
• Union,Intersect,Except sono ,,-. CORRESPONDING fa proiettare sugli attributi comuni e poi si applica l’operatore insiemistico. Se c’e’ anche BY si specificano su quali comuni attributi proiettare
• Cross Join e’ il prodotto cartesiano• Union Join e’ l’unione esterna cioe’ si
estendono le due tabelle con le colonne dell’altro con valori nulli e si fa l’unione.
Giugno-Pulvirenti AA 2005-2006
Ancora sulle Join
• Natural Join e’ quella classica• Join... Using e’ la natural join sui dati
attributi • Join…On su quelli che soddisfano una
data condizione• [LEFT|RIGHT|FULL] usato con Natural
Join o Join e’ la giunzione esterna nelle tre modalita’ sinistra,destra o completa.
Giugno-Pulvirenti AA 2005-2006
Esempi
• Natural Join
• SELECT Studenti.Nome,Esami.Corso,Esami.Voto
• FROM Esami NATURAL JOIN Studenti
• Nome,Corso e Voto degli esami
Giugno-Pulvirenti AA 2005-2006
Altro Esempio
• Agenti(CodiceAgente,Nome,Zona Supervisore,Commissione)
• Clienti(CodiceCliente,Nome,Citta’,Sconto)
• Ordini(CodiceOrdine,CodiceCliente,CodiceAgente,Articolo,Data,Ammontare)
Giugno-Pulvirenti AA 2005-2006
Esempio di Join On
• SELECT Agenti.CodiceAgente,Ordini.Ammontare
• FROM Agenti JOIN Ordini• ON Agenti.Supervisore =
Ordini.CodiceAgente
• Codice agente ed ammontare degli ordini dei supervisori
Giugno-Pulvirenti AA 2005-2006
Giunzione Esterna
• SELECT Agenti.CodiceAgente,Ordini.Ammontare
• FROM Agenti NATURAL LEFT JOIN Ordini
• Codice agente ed ammontare degli agenti incluso quelli che non hanno effettuato ordini (avranno ammontare NULL)
Giugno-Pulvirenti AA 2005-2006
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.
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.
Ottenere dati da più TabelleOttenere dati da più Tabelle
IMPIEGATIIMPIEGATI DDIPARTIMENTIIPARTIMENTI
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
Giugno-Pulvirenti AA 2005-2006
Cosa è una Join?Cosa è una Join?
SELECT tabella1.colonna, tabella2.colonnaFROM tabella, tabella2WHERE tabella1.colonna1 = tabella2.colonna2;
SELECT tabella1.colonna, tabella2.colonnaFROM tabella, tabella2WHERE tabella1.colonna1 = tabella2.colonna2;
• La join viene usata per effettuare query su più tabelle.
– La condizione di join va scritta nella clausola WHERE.
– Mettere come prefisso della colonna il nome della tabella se questa stessa colonna appare in più di una tabella.
Giugno-Pulvirenti AA 2005-2006
Nome Età
Persone
Reddito
Andrea 27
Maria 55
Anna 50
Filippo 26
Luigi 50
Franco 60
Olga 30
Sergio 85
Luisa 75
Aldo 25
21
42
35
30
40
20
41
35
87
15
MadreMaternità Figlio
Luisa
Anna
Anna
Maria
Maria
Luisa
Maria
Olga
Filippo
Andrea
Aldo
Luigi
PadrePaternità Figlio
Luigi
Luigi
Franco
Franco
Sergio
Olga
Filippo
Andrea
Aldo
Franco
Giugno-Pulvirenti AA 2005-2006
Join naturale
• Padre e madre di ogni persona
paternita JOIN maternita
SELECT paternita.figlio,padre, madreFROM maternita, paternitaWHERE paternita.figlio = maternita.figlio
Giugno-Pulvirenti AA 2005-2006
Prodotto CartesianoProdotto 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 .
Giugno-Pulvirenti AA 2005-2006
Generare un Prodotto CartesianoGenerare un Prodotto Cartesiano
ENAME DNAME------ ----------KING ACCOUNTINGBLAKE ACCOUNTING ...KING RESEARCHBLAKE RESEARCH...56 rows selected.
ENAME DNAME------ ----------KING ACCOUNTINGBLAKE ACCOUNTING ...KING RESEARCHBLAKE RESEARCH...56 rows selected.
IMPIEGATI (14 righe) IMPIEGATI (14 righe) DIPARTIMENTI (4 righe) DIPARTIMENTI (4 righe)
EMPNO ENAME ... DEPTNO------ ----- ... ------ 7839 KING ... 10 7698 BLAKE ... 30 ... 7934 MILLER ... 10
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
DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
““ProdottoProdottoCartesiano: Cartesiano:
14*4=56 rows”14*4=56 rows”
Giugno-Pulvirenti AA 2005-2006
Cosa è una Natural join?Cosa è una Natural join?
IMPIEGATI IMPIEGATI DIPARTIMENTI 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.
Chiave esterna Chiave esterna Chiave PrimariaChiave Primaria
Giugno-Pulvirenti AA 2005-2006
Estrarre Record con Natural joinEstrarre Record con Natural join
SQL> SELECT emp.empno, emp.ename, emp.deptno, 2 dept.deptno, dept.loc 3 FROM emp, dept 4 WHERE emp.deptno=dept.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.
Giugno-Pulvirenti AA 2005-2006
Condizioni di Ricerca addizionaliUso dell’operatore AND
Condizioni di Ricerca addizionaliUso dell’operatore AND
EMP EMP DEPTDEPT 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.
Giugno-Pulvirenti AA 2005-2006
Join di piu’ TabelleJoin di piu’ Tabelle
NAME CUSTID----------- ------JOCKSPORTS 100TKB SPORT SHOP 101VOLLYRITE 102JUST TENNIS 103K+T SPORTS 105SHAPE UP 106WOMENS SPORTS 107... ...9 rows selected.
NAME CUSTID----------- ------JOCKSPORTS 100TKB SPORT SHOP 101VOLLYRITE 102JUST TENNIS 103K+T SPORTS 105SHAPE UP 106WOMENS SPORTS 107... ...9 rows selected.
CLIENTI CLIENTI
CUSTID ORDID------- ------- 101 610 102 611 104 612 106 601 102 602 106 604 106 605... 21 rows selected.
CUSTID ORDID------- ------- 101 610 102 611 104 612 106 601 102 602 106 604 106 605... 21 rows selected.
ORDINIORDINI
ORDID ITEMID------ ------- 610 3 611 1 612 1 601 1 602 1...64 rows selected.
ORDID ITEMID------ ------- 610 3 611 1 612 1 601 1 602 1...64 rows selected.
PROD. PROD.
Giugno-Pulvirenti AA 2005-2006
Nome Età
Persone
Reddito
Andrea 27
Maria 55
Anna 50
Filippo 26
Luigi 50
Franco 60
Olga 30
Sergio 85
Luisa 75
Aldo 25
21
42
35
30
40
20
41
35
87
15
MadreMaternità Figlio
Luisa
Anna
Anna
Maria
Maria
Luisa
Maria
Olga
Filippo
Andrea
Aldo
Luigi
PadrePaternità Figlio
Luigi
Luigi
Franco
Franco
Sergio
Olga
Filippo
Andrea
Aldo
Franco
Giugno-Pulvirenti AA 2005-2006
Join esplicito (JOIN-ON)
• Sintassi:– SELECT …
FROM Tabella { … JOIN Tabella ON CondDiJoin }, …[ WHERE AltraCondizione ]
• Esempio: padre e madre di ogni persona (le due versioni):
SELECT paternita.figlio,padre, madreFROM maternita, paternitaWHERE paternita.figlio = maternita.figlio
SELECT madre, paternita.figlio, padre FROM maternita JOIN paternita ON
paternita.figlio = maternita.figlio
Giugno-Pulvirenti AA 2005-2006
Selezione, proiezione e join
•I padri di persone che guadagnano più di 20
πPadre(paternita JOIN Figlio=Nome (σReddito>20
(persone)))
SELECT distinct padreFROM persone, paternitaWHERE figlio = nome AND
reddito > 20
Giugno-Pulvirenti AA 2005-2006
• Le persone che guadagnano più dei rispettivi padri; mostrare nome, reddito e reddito del padre
πNome, Reddito, RP (σReddito>RP
(δNP,EP,RP Nome,Eta,Reddito(persone)JOIN NP=Padre
(paternita JOINFiglio =Nome persone)))
SELECT f.nome, f.reddito, p.redditoFROM persone p, paternita, persone fWHERE p.nome = padre AND
figlio = f.nome ANDf.reddito > p.reddito
Necessita’ di ridenominazione
Giugno-Pulvirenti AA 2005-2006
SELECT, con ridenominazione del risultato
SELECT figlio, f.reddito AS reddito, p.reddito AS redditoPadre
FROM persone p, paternita, persone fWHERE p.nome = padre AND figlio = f.nome
AND f.reddito > p.reddito
SELECT x.figlio, f.reddito AS reddito, p.reddito AS redditoPadre
FROM persone p, paternita x, persone fWHERE p.nome = x.padre AND x.figlio = f.nome
AND f.reddito > p.reddito
O meglio
Giugno-Pulvirenti AA 2005-2006
– Le persone che guadagnano più dei rispettivi padri; mostrare nome, reddito e reddito del padre
SELECT f.nome, f.reddito, p.redditoFROM persone p, paternita, persone fWHERE p.nome = padre AND
figlio = f.nome ANDf.reddito > p.reddito
SELECT f.nome, f.reddito, p.redditoFROM persone p JOIN paternita ON p.nome =
padre JOIN persone f ON figlio = f.nome
WHERE f.reddito > p.reddito
Giugno-Pulvirenti AA 2005-2006
Join esterno
• Padre e, se nota, madre di ogni persona
SELECT paternita.figlio, padre, madreFROM paternita LEFT JOIN maternita
ON paternita.figlio = maternita.figlio
Giugno-Pulvirenti AA 2005-2006
join esterno
SELECT paternita.figlio, padre, madreFROM maternita JOIN paternita
ON maternita.figlio = paternita.figlio
SELECT paternita.figlio, padre, madreFROM maternita LEFT JOIN paternita
ON maternita.figlio = paternita.figlio
SELECT paternita.figlio, padre, madreFROM maternita FULL JOIN paternita
ON maternita.figlio = paternita.figlio
Basi di dati I
Prof.ssa Rosalba GiugnoProf. Alfredo Pulvirenti
Aggregazione datiAggregazione dati
Giugno-Pulvirenti AA 2005-2006
Operatori aggregati
• Nelle espressioni della target list possiamo avere anche espressioni che calcolano valori a partire da insiemi di ennuple
• SQL-2 prevede 5 possibili operatori di aggregamento:– conteggio, minimo, massimo, media, somma
• Gli operatori di aggregamento NON sono rappresentabili in Algebra Relazionale
Giugno-Pulvirenti AA 2005-2006
ObiettiviObiettivi
• Al completamento di questa parte, dovreste essere in grado di:– Identificare le funzioni di
raggruppamento– Descriverne l’uso– Raggruppare dati usando GROUP
BY– Includere ed escludere righe
tramite l’uso di HAVING
Giugno-Pulvirenti AA 2005-2006
Cosa sono?Cosa sono?
• Operano su insiemi di righe per dare un risultato per gruppo.IMPIEGATIIMPIEGATI
““SalarioSalarioMassimo”Massimo”
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
Giugno-Pulvirenti AA 2005-2006
Quali sonoQuali sono
– AVG – COUNT – MAX– MIN – SUM
Giugno-Pulvirenti AA 2005-2006
UsoUso
SELECT [column,] group_function(column)FROM table[WHERE condition][GROUP BY column][ORDER BY column];
Giugno-Pulvirenti AA 2005-2006
Uso di AVG e SUMUso di AVG e SUM
AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL)-------- --------- --------- --------- 1400 1600 1250 5600
• Possono essere usati su dati numerici.SQL> SELECT AVG(sal), MAX(sal), 2 MIN(sal), SUM(sal) 3 FROM emp 4 WHERE job LIKE 'SALES%';
Giugno-Pulvirenti AA 2005-2006
Uso di MIN e MAXUso di MIN e MAX
• Possono essere usati su qualsiasi tipo.SQL> SELECT MIN(hiredate), MAX(hiredate) 2 FROM emp;
MIN(HIRED MAX(HIRED--------- ---------17-DEC-80 12-JAN-83
Giugno-Pulvirenti AA 2005-2006
Uso di COUNTUso di COUNT
COUNT(*)--------- 6
SQL> SELECT COUNT(*) 2 FROM emp 3 WHERE deptno = 30;
• COUNT(*) ritorna il numero di righe di una tabella.
Giugno-Pulvirenti AA 2005-2006
Operatori aggregati: COUNT
• COUNT come detto restituisce il numero di righe o il numero di valori distinti di un particolare attributo
• Esempio: Il numero di figli di Franco:• SELECT count(*) as NumFigliDiFranco
FROM PaternitaWHERE Padre = 'Franco'
– l’operatore aggregato (count) viene applicato al risultato dell’interrogazione:
SELECT * FROM PaternitaWHERE Padre = 'Franco'
Giugno-Pulvirenti AA 2005-2006
PadrePaternità Figlio
LuigiLuigi
SergioOlga
FilippoFrancoFranco
AndreaAldo
Franco
FrancoFranco
AndreaAldo
NumFigliDiFranco
2
Giugno-Pulvirenti AA 2005-2006
COUNT e valori nulli
• Numero di tuple SELECT count(*) FROM persone
• Numero di volte il campo ‘reddito’ non e’ NULLSELECT count(reddito) FROM persone
• Numero di valori distinti del campo ‘reddito’ (senza i NULL)SELECT count(distinct reddito) FROM persone
Nome EtàPersone RedditoAndrea 27
Maria 55Anna 50
Aldo 2521
2135
NULL
Giugno-Pulvirenti AA 2005-2006
• Se una colonna A contiene solo valori nulli, MAX, MIN, AVG, SUM restituiscono NULL, mentre Count vale zero.
Giugno-Pulvirenti AA 2005-2006
Altri operatori aggregati
• SUM, AVG, MAX, MIN• Media dei redditi di coloro che hanno meno di 30 anni:
SELECT avg(reddito) FROM persone WHERE eta < 30
• Uso del JOIN: media dei redditi dei figli di Franco:SELECT avg(reddito) FROM persone JOIN paternita ON nome=figlio WHERE padre='Franco‘
• Uso di piu’ operatori di aggregamento nella target list:SELECT avg(reddito), min(reddito), max(reddito) FROM persone WHERE eta < 30
Giugno-Pulvirenti AA 2005-2006
Operatori aggregati e valori nulli
SELECT avg(reddito) AS redditomedioFROM persone
Nome EtàPersone RedditoAndrea 27
Maria 55Anna 50
Aldo 2530
3636
NULL
Giugno-Pulvirenti AA 2005-2006
Uso di COUNTUso di COUNT
• COUNT(espr) ritorna il numero di righe non nulle.
SQL> SELECT COUNT(comm) 2 FROM emp 3 WHERE deptno = 30;
COUNT(COMM)----------- 4
Giugno-Pulvirenti AA 2005-2006
Valori NullValori Null
• Ignorano I valori nulli nelle colonne.
SQL> SELECT AVG(comm) 2 FROM emp;
AVG(COMM)--------- 550
Giugno-Pulvirenti AA 2005-2006
Creare gruppi di datiCreare gruppi di dati
IMPIEGATIIMPIEGATI
““salariosalariomedio medio
in IMPIEGATIin IMPIEGATIper ogniper ogni
dipartimento”dipartimento”
2916.66672916.6667
21752175
1566.66671566.6667
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
DEPTNO AVG(SAL)
------- ---------
10 2916.6667
20 2175
30 1566.6667
Giugno-Pulvirenti AA 2005-2006
Creare gruppi tramite: GROUP BY
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.
Giugno-Pulvirenti AA 2005-2006
Uso di GROUP BYUso di GROUP BY
• Tutte le colonne della SELECT che non sono in funzioni di gruppo devono essere nella GROUP BY.
SQL> SELECT deptno, AVG(sal) 2 FROM emp 3 GROUP BY deptno;
DEPTNO AVG(SAL)--------- --------- 10 2916.6667 20 2175 30 1566.6667
Giugno-Pulvirenti AA 2005-2006
Uso GROUP BYUso GROUP BY
• La colonna di GROUP BY non deve essere necessariamente nella SELECT.
SQL> SELECT AVG(sal) 2 FROM emp 3 GROUP BY deptno;
AVG(SAL)--------- 2916.6667 21751566.6667
Giugno-Pulvirenti AA 2005-2006
Raggruppare piu’ di una colonnaRaggruppare piu’ di una colonna
IMPIEGATIIMPIEGATI
““sommare I salari sommare I salari in IMPIEGATIin IMPIEGATI
per ongi lavoro, per ongi lavoro, RagruppatiRagruppati
per dipartimeno”per dipartimeno”
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
Giugno-Pulvirenti AA 2005-2006
Uso di GROUP BY su colonne multipleUso di GROUP BY su colonne multiple
SQL> 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.
Giugno-Pulvirenti AA 2005-2006
Operatori aggregati e target list
• un’interrogazione scorretta:SELECT nome, max(reddito)FROM persone
• di chi sarebbe il nome? La target list deve essere omogenea
SELECT min(eta), avg(reddito)FROM persone
Giugno-Pulvirenti AA 2005-2006
• Le funzioni possono essere applicate a partizioni delle relazioni
• Clausola GROUP BY– Syntax: GROUP BY listaAttributi
• Il numero di figli di ciascun padreSELECT padre, count(*) AS NumFigliFROM paternitaGROUP BY Padre
Operatori aggregati e raggruppamenti
Padrepaternita Figlio
LuigiLuigi
SergioOlga
FilippoFrancoFranco
AndreaAldo
Franco Padre NumFigli
LuigiSergio
2Franco 2
1
Giugno-Pulvirenti AA 2005-2006
Query illegali con funzioni di raggruppamento
Query illegali con funzioni di raggruppamento
• Ogni colonna o espressione della SELECT che non e’ argomento di funzioni di gruppo deve essere nella GROUP BY.
SQL> SELECT deptno, COUNT(ename) 2 FROM emp;
SQL> SELECT deptno, COUNT(ename) 2 FROM emp;
SELECT deptno, COUNT(ename) *ERROR at line 1:ORA-00937: not a single-group group function
SELECT deptno, COUNT(ename) *ERROR at line 1:ORA-00937: not a single-group group function
Giugno-Pulvirenti AA 2005-2006
– Non puo’ essere usata la WHERE per restringere I gruppi.
– Deve essere usata la HAVING.
SQL> SELECT deptno, AVG(sal) 2 FROM emp 3 WHERE AVG(sal) > 2000 4 GROUP BY deptno;
SQL> SELECT deptno, AVG(sal) 2 FROM emp 3 WHERE AVG(sal) > 2000 4 GROUP BY deptno;
WHERE AVG(sal) > 2000 *ERROR at line 3:ORA-00934: group function is not allowed here
WHERE AVG(sal) > 2000 *ERROR at line 3:ORA-00934: group function is not allowed here
Query illegali con funzioni di raggrup.
Query illegali con funzioni di raggrup.
Giugno-Pulvirenti AA 2005-2006
Escludere gruppi di ris.Escludere gruppi di ris.
““salariosalariomassimo massimo
per dipartmentoper dipartmentomaggiore dimaggiore di
$2900”$2900”
IMPIEGATIIMPIEGATI
50005000
30003000
28502850
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
DEPTNO MAX(SAL)
--------- ---------
10 5000
20 3000
Giugno-Pulvirenti AA 2005-2006
Clausola HAVINGClausola HAVING
• Uso di HAVING per restringere gruppi– Le righe sono raggruppate.– La funzione di raggruppamento e’
applicata.
SELECT column, group_functionFROM table[WHERE condition][GROUP BY group_by_expression][HAVING group_condition][ORDER BY column];
Giugno-Pulvirenti AA 2005-2006
Uso di HAVINGUso di HAVING
SQL> SELECT deptno, max(sal) 2 FROM emp 3 GROUP BY deptno 4 HAVING max(sal)>2900;
DEPTNO MAX(SAL)--------- --------- 10 5000 20 3000
Giugno-Pulvirenti AA 2005-2006
Uso di HAVINGUso di HAVING
SQL> SELECT job, SUM(sal) PAYROLL 2 FROM emp 3 WHERE job NOT LIKE 'SALES%' 4 GROUP BY job 6 ORDER BY SUM(sal);
JOB PAYROLL--------- ---------ANALYST 6000MANAGER 8275
5 HAVING SUM(sal)>5000
Giugno-Pulvirenti AA 2005-2006
Funzioni di raggruppamento annidateFunzioni di raggruppamento annidate
SQL> SELECT max(avg(sal)) 2 FROM emp 3 GROUP BY deptno;
MAX(AVG(SAL))------------- 2916.6667
Giugno-Pulvirenti AA 2005-2006
SommarioSommario
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
Giugno-Pulvirenti AA 2005-2006
Tabelle
• Tabelle ::= Tabella [Ide] {, Tabella [Ide]}
• Tabella::= Ide | Tabella OpInsiem Tabella | Tabella Giunzione Tabella
• [USING “(“Attributo{,Attributo}“)”|ON Condizione]
Giugno-Pulvirenti AA 2005-2006
Giunzioni ed Operatori Insiemistici
• OpInsiem ::= (UNION | INTERSECT | EXCEPT) [CORRESPONDING [BY “(“ Attributo {,Attributo}”)” ] ]
• Union,Intersect,Except sono ,,-. CORRESPONDING fa proiettare sugli attributi comuni e poi si applica l’operatore insiemistico. Se c’e’ anche BY si specificano su quali comuni attributi proiettare
Giugno-Pulvirenti AA 2005-2006
Unione, intersezione e differenza
• La SELECT da sola non permette di fare unioni; serve un costrutto esplicito:
SELECT … UNION SELECT ...
• i duplicati vengono eliminati
Giugno-Pulvirenti AA 2005-2006
Notazione posizionale!
SELECT padre FROM paternitaUNIONSELECT madreFROM maternita
• quali nomi per gli attributi del risultato?– nessuno – quelli del primo operando– …
Giugno-Pulvirenti AA 2005-2006
Notazione posizionale, 2
SELECT padre, figlioFROM paternitaUNIONSELECT figlio, madreFROM maternita
SELECT padre, figlioFROM paternitaUNIONSELECT madre, figlioFROM maternita
Giugno-Pulvirenti AA 2005-2006
Notazione posizionale, 3
• Anche con le ridenominazioni non cambia niente:SELECT padre as genitore, figlioFROM paternitaUNIONSELECT figlio, madre as genitoreFROM maternita
• Corretta:SELECT padre as genitore, figlioFROM paternitaUNIONSELECT madre as genitore, figlio FROM maternita
Giugno-Pulvirenti AA 2005-2006
Differenza
SELECT NomeFROM ImpiegatoEXCEPTSELECT Cognome as NomeFROM Impiegato
• vedremo che si può esprimere con SELECT nidificate
Giugno-Pulvirenti AA 2005-2006
Intersezione
SELECT NomeFROM ImpiegatoINTERSECTSELECT Cognome as NomeFROM Impiegato
– equivale a
SELECT I.NomeFROM Impiegato I, Impiegato JWHERE I.Nome = J.Cognome