Post on 03-May-2015
SQL per l’interrogazione
di basi di dati
DB - SQL per interrogazione basi di dati 2
Breve storia dell’SQL 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
DB - SQL per interrogazione basi di dati 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
DB - SQL per interrogazione basi di dati 4
Capacità del comando SELECT
SelezioneSelezione ProiezioneProiezione
Tabella 1Tabella 1 Tabella 2Tabella 2
Tabella 1Tabella 1 Tabella 1Tabella 1JoinJoin
DB - SQL per interrogazione basi di dati 5
SELECT
SELECT [DISTINCT] EsprAttributiFROM Tabelle[WHERE Condizione]
La query1. considera il prodotto cartesiano tra le Tabelle2. fra queste seleziona solo le righe che soddisfano Condizione 3. e infine valuta le espressioni specificate in EsprAttributi
(chiamata anche “Target List”) La SELECT implementa gli operatori Ridenominazione
Proiezione, Selezione e Join dell’algebra relazionale Piu’ altro che vedremo piu’ avanti
DB - SQL per interrogazione basi di dati 6
Attributi e Tabelle
EsprAttributi ::= * | EsprAttributo {, EsprAttributo} Tabelle ::= Tabella {, Tabella}
Dove Tabella sta per una determinata relazione ed EsprAttributo e’ un’espressione basata su degli attributi delle tabelle citate nel FROM
DB - SQL per interrogazione basi di dati 7
Esempio query su una tabella
NOME MATRICOL INDIRIZZO TELEFONO Mario Rossi 123456 Via Etnea 1 222222 Ugo Bianchi 234567 Via Roma 2 333333 Teo Verdi 345678 Via Enna 3 444444
Vorrei conoscere indirizzo e telefono di Teo Verdi
Via Enna 3 444444
DB - SQL per interrogazione basi di dati 8
Esempio query su una tabella
SELECT Indirizzo, TelefonoFROM StudentiWHERE Nome=‘Teo Verdi’
NOME MATRICOL INDIRIZZO TELEFONO Mario Rossi 123456 Via Etnea 1 222222 Ugo Bianchi 234567 Via Roma 2 333333 Teo Verdi 345678 Via Enna 3 444444
DB - SQL per interrogazione basi di dati 9
Query su due tabelleNOME MATRICOL INDIRIZZO TELEFONO Mario Rossi 123456 Via Etnea 1 222222 Ugo Bianchi 234567 Via Roma 2 333333 Teo Verdi 345678 Via Enna 3 444444
CORSO MATRICOLA VOTO Programmazione 345678 27 Architetture 123456 30 Programmazione 234567 18 Matematica Discreta
345678 22
Architettura 345678 30
Quali esami ha superato Mario Rossi?
Architetture
DB - SQL per interrogazione basi di dati 10
Esempio di JOIN
SELECT CorsoFROM Esami,StudentiWHERE Esami.Matricola = Studenti.Matricola AND Nome=‘Mario Rossi’;
DB - SQL per interrogazione basi di dati 11
Query su piu’ tabelle (JOIN)NOME MATRICOL INDIRIZZO TELEFONO Mario Rossi 123456 Via Etnea 1 222222 Ugo Bianchi 234567 Via Roma 2 333333 Teo Verdi 345678 Via Enna 3 444444
CORSO MATRICOLA VOTO Programmazione 345678 27 Architetture 123456 30 Programmazione 234567 18 Matematica Discreta
345678 22
Architettura 345678 30
CORSO PROFESSORE Programmazione Ferro Architetture Pappalardo Matematica Discreta Lizzio
Quali Professori hanno dato piu' di 24 a Teo Verdi ed in quali corsi? Ferro Programmazione
Pappalardo Architetture
Studenti
Esami
Corsi
DB - SQL per interrogazione basi di dati 12
Esempio di JOIN su tre tabelle
Quali professori hanno dato piu’ di 24 a Verdi ed in quali corsi?SELECT Professore, Corsi.Corso
FROM Corsi, Esami, StudentiWHERE Corsi.Corso = Esami.Corso AND Esami.Matricola = Studenti.Matricola AND Nome=‘Teo Verdi’ AND Voto > 24
DB - SQL per interrogazione basi di dati 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.Corso
FROM Corsi, Esami, StudentiWHERE Corsi.Corso = Esami.Corso AND Esami.Matricola = Studenti.Matricola AND Nome=‘Teo Verdi’ AND Voto > 24
DB - SQL per interrogazione basi di dati 14
Selezionare tutte le colonne
DEPTNO DNAME LOC--------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
SQL> SELECT * 2 FROM dept;
DB - SQL per interrogazione basi di dati 15
Selezionare certe colonne
DEPTNO LOC--------- ------------- 10 NEW YORK 20 DALLAS 30 CHICAGO 40 BOSTON
SQL> SELECT deptno, loc 2 FROM dept;
DEPTNO DNAME LOC--------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
DB - SQL per interrogazione basi di dati 16
Selezione colonne
L’interpretazione algebrica delle variabili del SELECT e’ l’operatore (Proiezione) dell’algebra relazionale
Con la variante ‘*’ che in Algebra relazionale corrisponde alla non-applicazione del
DB - SQL per interrogazione basi di dati 17
Espressioni aritmetiche nel SELECT
Creare espressioni attraverso l’uso di operatori aritmetici
Operatore
+
-
*
/
Descrizione
Somma
Sottrazione
Moltiplicazione
Divisione
DB - SQL per interrogazione basi di dati 18
Uso degli operatori Aritmetici
SQL> SELECT ename, sal, sal+300 2 FROM emp;
ENAME SAL exp---------- --------- ---------KING 5000 5300BLAKE 2850 3150CLARK 2450 2750JONES 2975 3275MARTIN 1250 1550ALLEN 1600 1900...14 rows selected.
DB - SQL per interrogazione basi di dati 19
Precedenza operatori aritmeticiSQL> SELECT ename, sal, 12*sal+100 2 FROM emp;
ENAME SAL exp---------- --------- ----------KING 5000 60100BLAKE 2850 34300CLARK 2450 29500JONES 2975 35800MARTIN 1250 15100ALLEN 1600 19300...14 rows selected.
DB - SQL per interrogazione basi di dati 20
Uso delle parentesiSQL> SELECT ename, sal, 12*(sal+100) 2 FROM emp;
ENAME SAL exp---------- --------- -----------KING 5000 61200BLAKE 2850 35400CLARK 2450 30600JONES 2975 36900MARTIN 1250 16200...14 rows selected.
DB - SQL per interrogazione basi di dati 21
Alias delle colonne
Ridenominare il nome di una colonna Operatore (Ridenominazione) dell’algebra
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 l’alias.
Richiede doppio apice se l’alias ha degli spazi
DB - SQL per interrogazione basi di dati 22
Uso dell’Alias
SQL> SELECT ename AS name, sal salary 2 FROM emp;
NAME SALARY
------------- ---------
...
SQL> SELECT ename "Name", 2 sal*12 "Annual Salary" 3 FROM emp;
Name Annual Salary
------------- -------------
...
DB - SQL per interrogazione basi di dati 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 l’alias delle tabelle SELECT Professore, c.Corso
FROM Corsi c, Esami eWHERE c.Corso = e.Corso AND Matricola = 123456
In caso contrario l’SQL restituisce errore
DB - SQL per interrogazione basi di dati 24
Self JOIN
Alias necessario per self-joinSELECT s1.Matricola, s2.Matricola
FROM Studenti s1, Studenti s2WHERE s1.matricola <> s2.matricola
Cosa fa?
DB - SQL per interrogazione basi di dati 25
Righe duplicate Le righe duplicate sono restituite per
defaultSQL> SELECT deptno 2 FROM emp;
DEPTNO--------- 10 30 10 20...14 rows selected.
DB - SQL per interrogazione basi di dati 26
Eliminazione delle righe duplicate
E’ consentito dall’uso della parola chiave E’ consentito dall’uso della parola chiave DISTINCT nella clausola SELECTDISTINCT nella clausola SELECTSQL> SELECT DISTINCT deptno 2 FROM emp;
DEPTNO--------- 10 20 30
Restrizioni ed ordinamento Dati
Restrizioni ed ordinamento Dati
DB - SQL per interrogazione basi di dati 28
Esempio"…selezionare "…selezionare
tutti gli impiegati tutti gli impiegati del dipartimeto 10"del dipartimeto 10"
IMPIEGATIIMPIEGATI
EMPNO ENAME JOB ... DEPTNO
7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20 ...
IMPIEGATIIMPIEGATI
EMPNO ENAME JOB ... DEPTNO
7839 KING PRESIDENT 10 7782 CLARK MANAGER 10 7934 MILLER CLERK 10
DB - SQL per interrogazione basi di dati 29
Limitare le righe selezionate Limitare le righe tramite l’uso della clausola
WHERE. Corrisponde operatore (Restrizione) dell’algebra
relazionale
La clausola WHERE segue la clausola FROM E’ opzionale
SELECT [DISTINCT] {*| colonna [alias], ...}FROM tabella[WHERE condizione(i)];
DB - SQL per interrogazione basi di dati 30
Uso della clausola WHERE
SQL> SELECT ename, job, deptno 2 FROM emp 3 WHERE job='CLERK';
ENAME JOB DEPTNO---------- --------- ---------JAMES CLERK 30SMITH CLERK 20ADAMS CLERK 20MILLER CLERK 10
DB - SQL per interrogazione basi di dati 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.
SQL> SELECT ename, job, deptno 2 FROM emp 3 WHERE ename = 'JAMES';
DB - SQL per interrogazione basi di dati 32
Condizione
Condizione : := Predicato | “(“Condizione”)” | NOT Condizione | Condizione (AND | OR) Condizione
Il risultato puo’ essere TRUE(T),FALSE(F) o UNKOWN(U).
DB - SQL per interrogazione basi di dati 33
Predicati di confronto
Espr op (Espr | “(“ Sottoselect “)” ) op {=, , >, , <, } 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.
DB - SQL per interrogazione basi di dati 34
Operatori di confrontoOperatore
=
>
>=
<
<=
<>
Significato
Uguale a
più grande di
maggiore o uguale di
minore di
minore o uguale a
diverso
DB - SQL per interrogazione basi di dati 35
Uso degli Operatori di Confronto
SQL> SELECT ename, sal, comm 2 FROM emp 3 WHERE sal<=comm;
ENAME SAL COMM---------- --------- ---------MARTIN 1250 1400
DB - SQL per interrogazione basi di dati 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
DB - SQL per interrogazione basi di dati 37
Uso dell’operatore BETWEEN
ENAME SAL---------- ---------MARTIN 1250TURNER 1500WARD 1250ADAMS 1100MILLER 1300
SQL> SELECT ename, sal 2 FROM emp 3 WHERE sal BETWEEN 1000 AND 1500;
Limiteinferiore
Limitesuperiore
BETWEEN consente la selezione di righe con attributi in un particolare range.
DB - SQL per interrogazione basi di dati 38
Predicato BETWEEN
Espr1 [NOT] BETWEEN Espr2 AND Espr3.
Equivale a [NOT] Espr2 Espr1 AND Espr1Espr3
DB - SQL per interrogazione basi di dati 39
Uso dell’operatore IN E’ usato per selezionare righe che
hanno un attributo che assume valori contenuti in una lista.
SQL> 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
DB - SQL per interrogazione basi di dati 40
Uso dell’operatore 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.
SQL> SELECT ename 2 FROM emp 3 WHERE ename LIKE 'S%';
DB - SQL per interrogazione basi di dati 41
Uso dell’operatore LIKE Il pattern-matching di caratteri può essere
combinato.
I’identificatore ESCAPE (\) deve essere usato per cercare "%" o "_".
SQL> SELECT ename 2 FROM emp 3 WHERE ename LIKE '_A%';
ENAME---------- MARTINJAMES WARD
DB - SQL per interrogazione basi di dati 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
caratterivale U se l’attributo e’ NULL
DB - SQL per interrogazione basi di dati 43
Esempio
SELECT NomeFROM StudentiWHERE Indirizzo LIKE “Via Etnea %” Restituisce tutti gli studenti che abitano in Via
Etnea
DB - SQL per interrogazione basi di dati 44
Predicati Espr IS [NOT] NULL
esempio: SELECT Nome
FROM StudentiWHERE Telefono IS NOT NULL
DB - SQL per interrogazione basi di dati 45
Operatori Logici
Operatore
AND
OR
NOT
Significato
Restituisce TRUE if entrambe le
condizioni sono TRUE
Restituisce TRUE se almeno una
delle condizioni è TRUE
Restituisce TRUE se la condizione è FALSE
DB - SQL per interrogazione basi di dati 46
Logica a tre valori
p q p and q P or q not p
T T T T F
T F F T F
T U U T F
F F F F T
F U F U T
U U U U U
U=Unknown;
DB - SQL per interrogazione basi di dati 47
Uso dell’operatore ANDAND AND richiede entrambe le condizionirichiede entrambe le condizioni TRUE. TRUE.
SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal>=1100 4 AND job='CLERK';
EMPNO ENAME JOB SAL--------- ---------- --------- --------- 7876 ADAMS CLERK 1100 7934 MILLER CLERK 1300
DB - SQL per interrogazione basi di dati 48
Uso dell’operatore OR
OR OR richiede almeno una condizionerichiede almeno una condizione TRUE. TRUE.SQL> SELECT empno, ename, job, sal 2 FROM emp 3 WHERE sal>=1100 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.
DB - SQL per interrogazione basi di dati 49
Uso dell’operatore NOTSQL> SELECT ename, job 2 FROM emp 3 WHERE job NOT IN ('CLERK','MANAGER','ANALYST');
ENAME JOB---------- ---------KING PRESIDENTMARTIN SALESMANALLEN SALESMANTURNER SALESMANWARD SALESMAN
DB - SQL per interrogazione basi di dati 50
Regole di precedenza
La precedenza puo’ essere controllata tramite il normale uso di parentesi
Ordine di val. Operatore
1 Tutti gli operatori diconfronto
2 NOT
3 AND
4 OR
DB - SQL per interrogazione basi di dati 51
Regole di precedenza
ENAME JOB SAL---------- --------- ---------KING PRESIDENT 5000MARTIN SALESMAN 1250ALLEN SALESMAN 1600TURNER SALESMAN 1500WARD SALESMAN 1250
SQL> SELECT ename, job, sal 2 FROM emp 3 WHERE job='SALESMAN' 4 OR job='PRESIDENT' 5 AND sal>1500;
DB - SQL per interrogazione basi di dati 52
Regole di precedenza
ENAME JOB SAL---------- --------- ---------KING PRESIDENT 5000ALLEN SALESMAN 1600
L’uso delle parentesi forza la prioritàL’uso delle parentesi forza la priorità
SQL> SELECT ename, job, sal 2 FROM emp 3 WHERE (job='SALESMAN' 4 OR job='PRESIDENT') 5 AND sal>1500;
DB - SQL per interrogazione basi di dati 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
DB - SQL per interrogazione basi di dati 54
Esempio
SELECT e.Corso, e.VotoFROM Esami e, Studenti sWHERE e.Matricola = s.Matricola AND s.Nome = ‘Mario Rossi’ORDER BY Voto DESC
Visualizzare Dati da più Tabelle
Visualizzare Dati da più Tabelle
DB - SQL per interrogazione basi di dati 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 l’uso delle outer join
Fare la Join di una tabella con se stessa
DB - SQL per interrogazione basi di dati 57
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ù TabelleIMPIEGATIIMPIEGATI 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
DB - SQL per interrogazione basi di dati 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.colonnaFROM tabella, tabella2WHERE tabella1.colonna1 = tabella2.colonna2;
DB - SQL per interrogazione basi di dati 59
Prodotto Cartesiano Il prodotto cartesiano e’ ottenuto
quando:Una condizione join e’ omessaUna condizione join e’ non validaTutte 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 .
DB - SQL per interrogazione basi di dati 60
Generare un Prodotto Cartesiano
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
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”
DB - SQL per interrogazione basi di dati 61
Cosa e’ una Equijoin?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 Straniera Chiave Straniera Chiave PrimariaChiave Primaria
DB - SQL per interrogazione basi di dati 62
Estrarre Record con Equijoin
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.
DB - SQL per interrogazione basi di dati 63
Condizioni di Ricerca addizionaliUso dell’operatore AND
EMP EMP DEPT 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.
DB - SQL per interrogazione basi di dati 64
Condizioni di Ricerca Uso dell’operatore AND
SQL> SELECT emp.empno, emp.ename, emp.deptno, 2 dept.deptno, dept.loc 3 FROM emp, dept 4 WHERE emp.deptno=dept.deptno AND ENAME=‘KING’;
DB - SQL per interrogazione basi di dati 65
Join di piu’ di due Tabelle
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.
ORDINIORDINI
ORDID ITEMID------ ------- 610 3 611 1 612 1 601 1 602 1...64 rows selected.
PROD. PROD.
DB - SQL per interrogazione basi di dati 66
Join di piu’ di due Tabelle
SQL> SELECT * 2 FROM clienti, ordini, prod 3 WHERE clienti.custid=ordini.custid
AND prod.ordid=prod.ordid;
DB - SQL per interrogazione basi di dati 67
Predicati Di Appartenenza e Quantificatori
DB - SQL per interrogazione basi di dati 68
Condizione
Condizione : := Predicato | “(“Condizione”)” | NOT Condizione | Condizione (AND | OR) Condizione
Il risultato puo’ essere TRUE(T),FALSE(F) o UNKOWN(U).
DB - SQL per interrogazione basi di dati 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 )
DB - SQL per interrogazione basi di dati 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 Voto>27)
DB - SQL per interrogazione basi di dati 71
Va meglio scritta come
SELECT DISTINCT s.Nome
FROM Studenti s, Esami e
WHERE s.Matricola = e.Matricola AND
Voto > 27 E’ piu’ efficiente perche’ c’e’ un
ottimizzatore che velocizza le join rispetto alle SottoSelect
DB - SQL per interrogazione basi di dati 72
Predicati Esistenziali
[NOT] EXISTS “(“ SottoSelect “)” E’ Vero se la SottoSelect non ritorna l’insieme 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
DB - SQL per interrogazione basi di dati 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’)
DB - SQL per interrogazione basi di dati 74
Usando l’Ottimizzatore di Join
SELECT e.Matricola
FROM Esami e, Corsi c
WHERE c.Corso = e.Corso
AND c.Professore=‘Giuffrida’
E’ piu’ efficiente.
DB - SQL per interrogazione basi di dati 75
Altri Quantificatori
Espr op (ANY | ALL) “(“SottoSelect”)” op {=, , >, , <, }
SELECT s.Nome
FROM Studenti s, Esami e
WHERE s.Matricola = e.Matricola AND
e.Voto > ALL
(SELECT DISTINCT f.Voto
FROM Esami f , Studenti t
WHERE f.Matricola = t.Matricola AND
t.Nome = ‘Mario Rossi’)
DB - SQL per interrogazione basi di dati 76
Altri quantificatori
Espr = ANY (SottoSelect) equivale a Espr IN (Sottoselect)
Espr NOT IN (SottoSelect) non equivale a Espr <> ANY (SottoSelect) ma a Espr <> All (SottoSelect)
DB - SQL per interrogazione basi di dati 77
Ricordiamo l’esempio
Agenti(CodiceAgente,Nome,Zona Supervisore,Commissione)
Clienti(CodiceCliente,Nome,Citta’,Sconto) Ordini(CodiceOrdine,CodiceCliente,Codic
eAgente,Articolo,Data,Ammontare)
DB - SQL per interrogazione basi di dati 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.
zy y(n,x,z,p,d,a) sse z y y(n,x,z,p,d,a)
DB - SQL per interrogazione basi di dati 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) )
Aggregazione datiAggregazione dati
DB - SQL per interrogazione basi di dati 81
Obiettivi
Al completamento della lezione, dovreste essere in grado di: Identificare le funzioni di
raggruppamentoDescriverne l’usoRaggruppare dati usando GROUP BY Includere ed escludere righe tramite
l’uso di HAVING
DB - SQL per interrogazione basi di dati 82
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
DB - SQL per interrogazione basi di dati 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
DB - SQL per interrogazione basi di dati 84
Esempi
SELECT MIN(Voto),MAX(Voto),AVG(Voto)FROM EsamiWHERE Matricola = ‘123456’
SELECT COUNT(*)FROM EsamiWHERE Corso = ‘Database 1’
DB - SQL per interrogazione basi di dati 85
Quali sono
AVG COUNT MAXMIN STDDEV SUMVARIANCE
DB - SQL per interrogazione basi di dati 86
Uso
SELECT [column,] group_function(column)FROM table[WHERE condition][GROUP BY column][ORDER BY column];
DB - SQL per interrogazione basi di dati 87
Uso 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%';
DB - SQL per interrogazione basi di dati 88
Uso 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
DB - SQL per interrogazione basi di dati 89
Uso di COUNT
COUNT(*)--------- 6
SQL> SELECT COUNT(*) 2 FROM emp 3 WHERE deptno = 30;
COUNT(*) ritorna il numero di righe di una tabella.
DB - SQL per interrogazione basi di dati 90
Creare gruppi di datiIMPIEGATIIMPIEGATI
““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
DB - SQL per interrogazione basi di dati 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.
DB - SQL per interrogazione basi di dati 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
DB - SQL per interrogazione basi di dati 93
Uso 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
DB - SQL per interrogazione basi di dati 94
Uso 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
DB - SQL per interrogazione basi di dati 95
Raggruppare piu’ di una colonnaIMPIEGATIIMPIEGATI
““sommare I salari sommare I salari in IMPIEGATIin IMPIEGATI
per ogni lavoro, per ogni 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
DB - SQL per interrogazione basi di dati 96
Uso 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.
DB - SQL per interrogazione basi di dati 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.
SQL> SELECT deptno, COUNT(ename) 2 FROM emp;
SELECT deptno, COUNT(ename) *ERROR at line 1:ORA-00937: not a single-group group function
DB - SQL per interrogazione basi di dati 98
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;
WHERE AVG(sal) > 2000 *ERROR at line 3:ORA-00934: group function is not allowed here
Query illegali con funzioni di raggrup.
DB - SQL per interrogazione basi di dati 99
Escludere gruppi
““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
DB - SQL per interrogazione basi di dati 100
Clausola HAVING
Uso di HAVING per restringere gruppiLe 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];
DB - SQL per interrogazione basi di dati 101
Uso 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
DB - SQL per interrogazione basi di dati 102
Uso 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
DB - SQL per interrogazione basi di dati 103
Funzioni di raggruppamento annidate
SQL> SELECT max(avg(sal)) 2 FROM emp 3 GROUP BY deptno;
MAX(AVG(SAL))------------- 2916.6667
DB - SQL per interrogazione basi di dati 104
Esempio
SELECT Nome, Matricola MIN(Voto),MAX(Voto),AVG(Voto)FROM Esami, StudentiWHERE Esami.Matricola = Studenti.MatricolaGROUP BY Nome,MatricolaHAVING COUNT(*) > 8
DB - SQL per interrogazione basi di dati 105
SommarioSELECT column, group_function(column)FROM table[WHERE condition][GROUP BY group_by_expression][HAVING group_condition][ORDER BY column];
Ordine di valutazione delle clausole:WHEREGROUP BYHAVING
DB - SQL per interrogazione basi di dati 106
Visualizzare Dati da piu’ tabelle (||)
Diversi tipi di Joins Operatori Insiemistici
DB - SQL per interrogazione basi di dati 107
Tabelle
Tabelle ::= Tabella [Ide] {, Tabella [Ide]}
Tabella::= Ide | Tabella OpInsiem Tabella | Tabella Giunzione Tabella
[USING “(“Attributo{,Attributo}“)”|ON Condizione]
DB - SQL per interrogazione basi di dati 108
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
DB - SQL per interrogazione basi di dati 109
Ancora su Join ed Operatori Insiemistici Cross Join e’ il prodotto cartesiano Union Join e’ l’unione esterna cioe’ si
estendono le due tabelle con le colonne dell’altra con valori nulli e si fa l’unione delle due stesse tabelle.
DB - SQL per interrogazione basi di dati 110
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
DB - SQL per interrogazione basi di dati 111
Esempi
Natural Join
SELECT Studenti.Nome,Esami.Corso,Esami.VotoFROM Esami NATURAL JOIN Studenti
Nome,Corso e Voto degli esami
DB - SQL per interrogazione basi di dati 112
[LEFT|RIGHT|FULL] usato con Natural Join o Join e’ la giunzione esterna nelle tre modalita’ sinistra,destra o completa.
DB - SQL per interrogazione basi di dati 113
Right Outer Join
Outer Join Operators
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
Full outer join
DB - SQL per interrogazione basi di dati 114
Altro Esempio
Agenti(CodiceAgente,Nome,Zona Supervisore,Commissione)
Clienti(CodiceCliente,Nome,Citta’,Sconto) Ordini(CodiceOrdine,CodiceCliente,Codic
eAgente,Articolo,Data,Ammontare)
DB - SQL per interrogazione basi di dati 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
DB - SQL per interrogazione basi di dati 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)
DB - SQL per interrogazione basi di dati 117
Le operazioni su insiemi
A UNION B
A INTERSECT B
A MINUS B
DB - SQL per interrogazione basi di dati 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 l’operatore insiemistico. Se c’e’ anche BY si specificano su quali comuni attributi proiettare
DB - SQL per interrogazione basi di dati 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
DB - SQL per interrogazione basi di dati 120
Formalizziamo
DB - SQL per interrogazione basi di dati 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 “(“ * “)”
::= ( + | - | * | / )
DB - SQL per interrogazione basi di dati 122
Esempio
SELECT AVG(Voto) AS Media_RossiFROM Esami, StudentiWHERE Nome = ‘Paolo Rossi’ AND Esami.Matricola = Studenti.Matricola
Media dei voti di Paolo Rossi
DB - SQL per interrogazione basi di dati 123
Tabelle
Tabelle ::= Tabella [Ide] {, Tabella [Ide]}
Tabella::= Ide | Tabella OpInsiem Tabella | Tabella Giunzione Tabella
[USING “(“Attributo{,Attributo}“)”|ON Condizione]
DB - SQL per interrogazione basi di dati 124
Esempi
SELECT ProfessoreFROM CP,CMV,MNITWHERE CP.Corso = CMV.Corso AND CMV.Matricola = NMIT.Matricola AND Nome=‘Paolo Rossi’ AND Voto > 27
DB - SQL per interrogazione basi di dati 125
Sintassi Completa del SELECT
Select ::= Sottoselect {(UNION|EXCEPT) Sottoselect} [ORDER BY Attributo[DESC] {, Attributo[DESC]} ]
DB - SQL per interrogazione basi di dati 126
Sottoselect
Sottoselect ::= SELECT [DISTINCT] (* | Espr[[AS] NewName] {,Espr [[AS]
NewName]}) FROM Tabella [Ide]{,Tabella[Ide]} [WHERE Condizione] [GROUP BY Attributo {,Attributo}] [HAVING Condizione]
DB - SQL per interrogazione basi di dati 127
Condizione
Condizione ::= Predicato | “(“ Condizione “)” | NOT Condizione | Condizione (AND | OR) Condizione
DB - SQL per interrogazione basi di dati 128
Predicato
Predicato::= Espr [NOT] IN “(“ SottoSelect “)” | Espr [NOT] IN “(“ Valore {,Valore} “)” | Espr opc (Espr | “(“ SottoSelect “)” ) | Espr IS [NOT] NULL | Espr opc (ANY | ALL) “(“ SottoSelect “)” | [NOT] EXISTS “(“ SottoSelect “)” | Espr [NOT] BETWEEN Espr AND Espr | Espr [NOT] LIKE Stringa opc ::= | | | | |
DB - SQL per interrogazione basi di dati 129
Espressioni
Espr ::= [Ide.] Attributo | Costante | “(” Espr “)” | [-] Espr [ opa Espr] | (SUM | COUNT | AVG | MAX | MIN) “(” [DISTINCT] [Ide.] Attributo“)” | COUNT “(” * “)” opa ::= (+ | - | * | / )
DB - SQL per interrogazione basi di dati 130
Tabelle
Tabella ::= Ide | Tabella opins Tabella | Tabella giunzione Tabella [USING “(“ Attributo {, Attributo } “)” | ON Condizione]
giunzione ::= [CROSS|UNION|NATURAL] [LEFT!RIGHT|FULL]JOIN
opins ::= (UNION|INTERSECT|EXCEPT) [CORRESPONDING [BY”(Attributo {,Attributo} “)”]]