�ݺ�ߣ

22/07/04 Ing. Ronchi Sergio 1
QUERIES (proiezione)
Visualizzare tutti i dati contenuti in una tabella:
SELECT * FROM PRODOTTI;
Selezione delle colonne (o dei campi)
SELECT NOME,PREZZO FROM PRODOTTI;
Selezione delle colonne (o dei campi) con alias
SELECT NOME As Prodotto FROM PRODOTTI;
(As è opzionale)
Selezione delle colonne con esecuzioni di operazioni sulle colonne:
SELECT NOME prodotto,
PREZZO*(1+IVA /100) “Prezzo con IVA” FROM PRODOTTI;
In Oracle esiste una pseudotabella denominata DUAL che serve per effettuare
prove e per disporre di dati non memorizzati:
SELECT SYSDATE FROM DUAL;
SELECT SYSTIMESTAMP FROM DUAL

FUNZIONI STRINGA
Concatenazione
SELECT COGNOME || ',' || NOME FROM STUDENTI;
SELECT CONCAT(COGNOME, ',' , NOME) FROM STUDENTI;
Sottostringa
SELECT SUBSTR(NOME,1,3) FROM STUDENTI;
Altre funzioni stringa:
UPPER(str), LOWER(str) trasforma in maiuscolo o in minuscolo
INITCAP(str) pone la prima lettera maiuscola
TRIM(str), LTRIM(str), RTRIM(str) elimina spazi a destra e/o a sinistra è anche possibile
specificare il carattere da eliminare
LTRIM(descrizione,’.’) oppure RTRIM(descrizione,’.’)
TRIM(“.” from descrizione)
LPAD(str,n,[car]), RPAD(str,n,[car]) riempie la stringa con n caratteri
LENGTH(str) lunghezza
INSTR(str,'p') dà la posizione della prima occorrenza di 'p‘
ASCII(Str), CHR(n) codice ascii da un carattere o il viceversa
SOUNDEX(str) dà un codice associato al suono di una stringa in
Inglese: permette di confrontare stringhe simili
TRANSLATE(str,maschera,sostituto) sostituisce I caratteri uguali nella stringa di origine
select TRANSLATE('ABCDEF','AXC','123') from dual;
restutuisce 1B3DEF

Funzioni per le date
In Oracle ai campi DATE si applica l’aritmetica delle date, (somma e sottrazione in giorni)
select ename, hiredate, sysdate-hiredate from emp;
select ename, hiredate, ROUND(sysdate)-hiredate from emp; (elimina le frazioni)
Ad esempio mi ritorna il numero di giorni (e frazioni) dalla data di assunzione.
ADD_MONTHS(data,n) aggiunge n mesi alla data (o sottrae se n è negativo)
LAST_DAY(data) ultimo giorno del mese
MONTHS_BETWEEN(d2,d1) differenza tra date in mesi
NEXT_DAY(data,’VENERDI’) dà il prossimo ‘VENERDI’
TO_CHAR(data,formato) dà una sctringa contenente una data
TO_DATE(stringa,formato) ottiene una data da una stringa
TRUNC(data, formato), ROUND(data, formato) tronca/arrotonda una data nel formato dato
il formato può essere MONTH, YEAR,…
Principali formati nelle date:
MM (mese a due cifre), MON(iniziali del mese), MONTH (mese esteso), RM mese romano
DD (giorno del mese), DDD (giorno dell’anno), D giorno della settimana, DAY (nome)
YYYY (anno 4 cifre), YY (Anno 2 cifre)
Q (Trimestre)
WW (settimana nell’anno), W settimana nel mese
HH (ora) HH24(ora base 24), MI (minuti), SS(Secondi)

Funzioni aritmetiche
Esiste un insieme notevole di tutte le principali funzioni aritmetiche e logiche
tra le quali sicuramente importante è la funzione di arrotondamento:
ROUND(X,2) arrotonda con due decimali
Si notino anche le seguenti funzioni che operano su elenchi di valori:
GREATEST(v1,v2,v3,….) dà il valore più alto
LEAST(v1,v2,v3,……) dà il valore più basso
Per i formati numerici è anche importante la funtùzione TO_CHAR(number,formato), questa
permette di ottenere tutti formati comuni, ad esempio
SELECT ename, TO_CHAR(sal,’$9,990.00’) FROM EMP
Per altro esiste anche la possibiltà inversa di un parsing da stringa a numero
TO_NUMBER(stringa,formato)

Funzioni per i valori NULL
NVL(valore, sostituto) specifica un sostituto se valore è NULL
NVL2(valore,espr1,espr2) se valore è nullo torna espr2 altrimenti espr1
NULLIF(espr1,espr2) ritorna NULL se espr1=espr2, altrimenti torna espr1
COALESCE(v1,v2,v3,….) dà il primo valore non nullo

Funzioni di controllo
In oracle esiste una funzione molto particolare per realizzare una struttura di tipo if..then..else,
si tratta anzi di una struttura simile a uno switch.
DECODE (valore, if1,then1,if2,then2,if3,…….,else)
La funzione accetta solo confronti diretti di uguaglainza, non permette cioè confronti > <
Per ovviare a questo è possibile usare in modo opportuno la funzione SIGN(val) che dà 1 se il
valore è positivo, -1 se negativo e 0 se nullo.
Ad esempio DECODE(SIGN(voto-5.9),1,’sufficiente’,’insufficiente’)
Esiste poi anche una vera e propria struttura CASE
SELECT
CASE voto
when 5 then 'insufficiente'
when 6 then 'sufficiente'
when 7 then 'discreto'
else 'voto sconosciuto'
END
FROM valutazioni

Query di Selezione
La clausola where estrae solo i record che rispettano la condizione:
SELECT COGNOME, NOME
FROM ANAGRAFICA
WHERE PROVINCIA='MI'
Ovviamente sono possibili tutti i tipi di confronti logici, i vari operatori logici
AND OR NOT e l'uso di funzioni o operatori aritmetici:
SELECT COGNOME, NOME, ALTEZZA
FROM ANAGRAFICA
WHERE PROVINCIA!='MI' AND ALTEZZA>1.70
AND (TO_CHAR(DATA,'YYYY') BETWEEN '1980' AND '1981');

Query di Selezione
Ordinamento del risultato di una query:
SELECT COGNOME, NOME, ALTEZZA FROM ANAGRAFICA
WHERE PROVINCIA!='MI' ORDER BY COGNOME, NOME;
Ovviamente è possibile invertire l'ordine utilizzando DESC:
SELECT COGNOME, NOME, ALTEZZA FROM ANAGRAFICA
ORDER BY ALTEZZA DESC;
Nell'order by è possibile utilizzare anche gli ALIAS:
SELECT COGNOME, PESO-ALTEZZA*100 SOVRAPPESO
FROM ANAGRAFICA
ORDER BY SOVRAPPESO;

Uso di LIKE
L’operatore LIKE permette di effettuare filtri parziali su un campo di cui non si
conosce il valore in modo completo.
% jolly per n caratteri
_ jolly per un carattere
Ad esempio per avere tutti quelli che iniziano con S
SELECT ename FROM emp where ename LIKE ‘S%’
Ad esempio per avere tutti quelli la cui seconda lettera è una ‘o’
SELECT ename FROM emp where ename LIKE ‘_o%’
Ad esempio per avere tutti quelli che iniziano son ‘SA_’
SELECT ename FROM emp where JOB_ID LIKE ‘SA_%’ ESCAPE ‘’

Altri operatori (IS, IN)
L’operatore IS consente di confrontare un valore null
SELECT * FROM PRODOTTI WHERE CATEGORIA IS NULL
L’Operatore IN consente di verificare l’appartenenza di un valore in un insieme
SELECT * FROM STUDENTI WHERE PROVINCIA IN (‘MI’,’BG’,’BS’)

Funzioni di Raggruppamento
Permettere di ottenere totalizzazioni su tutta la tabella o
su dati congruenti e raggruppati.
SELECT AVG(ALTEZZA) FROM ANAGRAFICA; MEDIA
Altre funzioni di raggruppamento sono
SUM() (somma) COUNT() (conteggio)
MIN() (minimo) MAX() (massimo)
STDDEV() (deviazione standard) VARIANCE() (Varianza)
è possibile ovviamente utilizzare la clausola WHERE

Funzioni di Raggruppamento
Se si vuole calcolare un valore per raggruppamenti:
SELECT PROVINCIA, AVG(ALTEZZA)
FROM ANAGRAFICA GROUP BY PROVINCIA;
SELECT PROVINCIA, SESSO, AVG(ALTEZZA)
FROM ANAGRAFICA GROUP BY PROVINCIA,SESSO;
Uso di WHERE – GROUP BY – ORDER BY:
SELECT PROVINCIA, SESSO, AVG(ALTEZZA) MEDIA
FROM ANAGRAFICA WHERE PESO>50
GROUP BY PROVINCIA, SESSO
ORDER BY MEDIA;
da notare l'uso dell'alias nel ORDER BY
SELECT PROVINCIA, SESSO, AVG(ALTEZZA) MEDIA
FROM ANAGRAFICA
GROUP BY PROVINCIA, SESSO
HAVING PROVINCIA!=‘EE’
ORDER BY MEDIA;
La clausola having è differente dalla where perché si applica solo alle colonne
raggruppate e viene applicata dopo che la where ha già eventualmente eliminato
alcune righe

Query con Join tra tabelle
Riportiamo ora la sintassi delle JOIN in Oracle che tuttavia differisce da quella
standard di SQL.
JOIN NATURALE (EQUIJOIN o INNER JOIN)
SELECT COGNOME, ANAGRAFICA.NOME, PROVINCE.NOME
FROM ANAGRAFICA, PROVINCE
WHERE ANAGRAFICA.PROVINCIA=PROVINCE.SIGLA
vengono visualizzati solo i record che hanno il codice (sigla) corrispondente.
(Attenzione omettendo la condizione WHERE o sbagliando tale condizione si può
ottenere il prodotto cartesiano delle due tabelle)
Per semplicità è possibile dichiarare degli alias per le tabelle in modo da non dover
ripetere sempre tutto il nome per esteso.
SELECT COGNOME, ANAGRAFICA.NOME, PROVINCE.NOME
FROM ANAGRAFICA A, PROVINCE P
WHERE A.PROVINCIA=P.SIGLA

Nonequijoin
Può succedere che la join tra due tabelle non sia semplicemente determinata da un
legame di uguaglianza tra PK e FK. Ma che la relazione sia più complessa.
Vogliamo trovare tutti coloro che hanno un salario all’interno del range del loro
grado:
SELECT last_name,salary,grade
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal

OUTER JOIN
- RIGHT JOIN
SELECT COGNOME, PROVINCIA.NOME
FROM ANAGRAFICA , PROVINCE
WHERE ANAGRAFICA.PROVINCIA(+)=PROVINCE.SIGLA
mostra tutti i record della tabella di destra e solo quelli corrispondenti
della tabella di sinistra. In questo caso mostrano tutte le province, anche se non hanno
persone in anagrafica.
Il simbolo (+) va messa dalla parte della tabella dove l’informazione è mancante
- LEFT JOIN
SELECT COGNOME, PROVINCIA.NOME
FROM ANAGRAFICA , PROVINCE
WHERE ANAGRAFICA.PROVINCIA=PROVINCE.SIGLA(+)
mostra tutti i record della tabella di sinistra. In questo caso mostra tutte le persone
anche se non dovessero avere una provincia associata.

SELF JOIN
In alcuni casi è necessario eseguire una join su campi della stessa tabella:
Ad esempio se volessi trovare tutti coloro che hanno nome e cognome uguali,
ma codici diversi.
SELECT A1.COGNOME, A1.NOME, A1.DATA, A2.DATA
FROM ANAGRAFICA A1 , ANAGRAFICA A2
WHERE A1.COGNOME=A2.COGNOME AND A1.NOME=A2.NOME
AND A1.CODICE>A2.CODICE;

SELF OUTER JOIN
Voglio visualizzare cognome e nome di una persona e cognome e nome della madre.
Tutte le persone si trovano nella stessa tabella
SELECT CONCAT(FIGLIO.COGNOME,’, ‘,FIGLIO.NOME) PERSONA,
CONCAT(MADRE.COGNOME,’, ‘, MADRE.NOME) MAMMA
FROM ANAGRAFICA FIGLIO, ANAGRAFICA MADRE
WHERE FIGLIO.GENITORE=MADRE.CODICE(+);

Query con Join multiple
Tabelle relazionate più volte con la stessa tabella. Ad esempio la relazione tra I Voli
(origine e destinazione) e gli Aeroporti.
SELECT CODVOLO,ORIGINE.NOME, DESTINAZIONE.NOME, DATA
FROM VOLI, AEROPORTI ORIGINE, AEROPORTI DESTINAZIONE
WHERE VOLI.ORIGINE=ORIGINE.CODICE AND
VOLI.DEST=DESTINAZIONE.CODICE;

JOIN CON SINTASSI STANDARD
Oracle accetta per le join anche una sintassi SQL standard
EQUIJOIN:
select ename, dname from emp inner join dept on emp.deptno=dept.deptno
OUTER JOIN:
select ename, dname from emp left join dept on emp.deptno=dept.deptno
select ename, dname from emp right join dept on emp.deptno=dept.deptno
select ename, dname from emp full join dept on emp.deptno=dept.deptno

Subqueries
Permettono diverse operazioni che altrimenti dovrebbero essere realizzate con
più di una query.
Estrae le persone con altezza superiore alla media
FROM ANAGRAFICA
WHERE ALTEZZA> (SELECT AVG(ALTEZZA) FROM ANAGRAFICA);
Estrae le persone della lombardia
FROM ANAGRAFICA WHERE PROVINCIA IN (SELECT SIGLA FROM PROVINCE
WHERE REGIONE='Lombardia');
La stessa fatta con una JOIN
FROM ANAGRAFICA, PROVINCE WHERE
ANAGRAFICA.PROVINCIA=PROVINCE.SIGLA AND
PROVINCE.REGIONE='Lombardia';

Errori da evitare
Questa query è corretta, ma non è efficiente:
SELECT Titolo FROM Biblioteca
WHERE Titolo NOT IN (Select Titolo from BIBLIOTECA_PRESTITO)
ORDER BY TITOLO;
E’ molto meglio usare una JOIN:
SELECT distinct B.Titolo
FROM Biblioteca_PRESTITO BP right join BIBLIOTECA B
on BP.Titolo=B.Titolo
Where BP.Titolo is NULL
Order by B.Titolo
Si noti infattin che in una outer join nella colonna dove non c’è una corrispondenza si
vha un valor NULL:

Uso di EXISTS
EXISTS effettua un controllo di esistenza, può essere usato a volte in sostituzione della clausola IN
con le subquery.
Ad esempio:
SELECT NomeAutore, Titolo FROM Biblioteca_Autore BA
WHERE NomeAutore IN
(SELECT NomeAutore FROM Biblioteca_Autore
GROUP BY NomeAutore
HAVING COUNT(Titolo)>1)
ORDER BY NomeAutore,Titolo
Può essese sostituita da questa con una subquery correlata alla query principale:
SELECT NomeAutore, Titolo FROM Biblioteca_Autore BA
WHERE EXISTS
(SELECT * FROM Biblioteca_Autore BA2
WHERE BA.NomeAutore=BA2.NomeAutore
GROUP BY BA2.NomeAutore
HAVING COUNT(BA2.Titolo)>1)
ORDER BY NomeAutore,Titolo

UNION
Permettono di ottenere una vista in cui record provenienti da più
tabelle vengono uniti. Si pensi ad due insiemi ora saranno possibile le seguenti
situazioni:
- estrarre tutti gli elementi sia del primo che del secondo insieme senza elencare I
doppioni
SELECT CODICE, NOME FROM LIBRI
UNION
SELECT CODICE, NOME FROM SOFTWARE;
- estrarre tutti gli elementi sia del primo che del secondo insieme elencando anche I
doppioni
SELECT CODICE, NOME FROM LIBRI
UNION ALL
SELECT CODICE, NOME FROM SOFTWARE;

INTERSECT - MINUS
Ipotizziamo sempre di avere due insiemi. Questa volta si vogliono vedere solo le voci
presenti in entrambi
SELECT CODICE, TITOLO FROM LIBRI
INTERSECT
SELECT CODICE, TITOLO FROM LIBRI_ORDINATI;
Questa volta si vogliono vedere solo le voci nuove non presenti nel primo insieme
SELECT CODICE, TITOLO FROM LIBRI_ORDINATI
MINUS
SELECT CODICE, TITOLO FROM LIBRI;

�ݺ�ߣ

Sql 3

Recommended

More Related Content

Viewers also liked (19)

Similar to Sql 3 (14)

More from Sergio Ronchi (13)

Sql 3