select

Università di Modena e Reggio Emilia
Basi di dati (4)
Docente: Andrea Bulgarelli
[email protected]
Argomento: select (1.0)
Andrea Bulgarelli
1
Panoramica
  Select
  Joins
  Subquery
  Wildcard
  Esempi
Andrea Bulgarelli
2
SELECT
SELECT [DISTINCT] [TOP n]
select_list
[INTO new_table_name]
FROM table_list
[WHERE criteri di selezione]
[GROUP BY colonne di aggregazione]
[HAVING criteri per le funzioni
aggregate]
[ORDER BY ordinamento risultati
[ASC|DESC] ]
Andrea Bulgarelli
3
SELECT (2)
 
<lista-select>:
  Uno
o più attributi specificati delle tabelle di <listafrom>
  Funzioni aggregate: COUNT, AVG, MIN, MAX,
SUM
  *
  Costanti
 
<lista-from>
  Tabelle,
 
viste o join
<lista-order>
  Uno
o più sttributi di <lista-select>
  ASC | DESC
Andrea Bulgarelli
4
Esempio
  Usiamo il database di test pubs, tabella titles
SELECT type, pub_id,
'avg' = AVG(price), 'sum' = sum(ytd_sales)
FROM titles
GROUP BY type, pub_id
 
 
Da notare l’assegnazione delle intestazioni ed il
raggruppamento delle righe di output rispetto ad
alcune colonne
Da notare che la presenza di funzioni di
aggregazione qualifica il tipo di risultati
presentabili
Andrea Bulgarelli
5
Tipologie di SELECT
  Una
SELECT può riportare
 un
insieme di righe con opportuno tracciato
record (result set) con
  Dati
afferenti a singole entità del database
  Dati aggregati per opportuni raggruppamenti di
entità
 Un
singolo valore scalare
  Ogni
query appartiene ad esattamente
una delle tipologie precedenti
Andrea Bulgarelli
6
Raggruppamenti
 
 
 
 
La clausola GROUP BY implica un raggruppamento
delle righe di output in categorie
Il risultato finale è una sola riga per ciascun
raggruppamento
Le colonne presentate in output possono contenere
solamente i qualificatori del raggruppamento ed i valori
aggregati calcolati per ciascun raggruppamento
Esempio:
SELECT CategoryID, AVG(UnitPrice)
FROM Products
GROUP BY CategoryID
 
Funzioni aggreganti: MAX, MIN, AVG, SUM, COUNT,
STDEV, VAR. Tutte ignorano i NULL tranne COUNT
Andrea Bulgarelli
7
Raggruppamenti (2)
  La
clausola HAVING si comporta come
una WHERE per i GROUP BY
  La
clausola HAVING può anche essere
usata indipendentemente dal GROUP BY,
nel qual caso si comporta come una
WHERE
Andrea Bulgarelli
8
Operazioni di JOIN
 
 
Il JOIN è l’operazione di “collegamento” di due o più tabelle
mediante elementi (chiave) comuni tra queste
Lo standard SQL-92 permette di gestire INNER JOIN (default),
FULL|LEFT|RIGHT OUTER JOIN, CROSS JOIN
SELECT
<selectlist>
FROM
<tab1> AS <alias1>
[<join_type>] JOIN
<tab2> AS <alias2> ON <condizioni>
[<join_type>] JOIN
<tab3> AS <alias3> ON <condizioni>
[WHERE
<condizioni_filtro> ]
Andrea Bulgarelli
9
INNER JOIN
 
 
 
Restituisce (o tratta) tutte le righe per cui nelle due o più tabelle
coinvolte i valori di aggancio sono effettivamente presenti e
verificano la condizione di JOIN
Il qualificatore INNER viene (quasi) sempre omesso
Come conseguenza della definizione l’ordine di esecuzione di più
INNER JOIN non è rilevante per il risultato finale
SELECT
empid,empname,salary,E.deptno, deptname,E.jobid,jobdesc
FROM
Employees
AS E
JOIN
Departments AS D ON E.deptno = D.deptno
JOIN
Jobs
AS J ON E.jobid = J.jobid
Andrea Bulgarelli
10
OUTER JOINs
 
 
Obiettivo degli OUTER JOIN è allargare l’insieme
risultante dalla query includendo anche le righe di una e/
o dell’altra colonna che non hanno controparte, integrate
con valori NULL
SQL-92 è il primo standard a supportare completamente
gli operatori di OUTER JOIN
SELECT *
FROM
Employees
AS E
LEFT OUTER JOIN
Departments AS D ON E.deptno = D.deptno
Andrea Bulgarelli
11
CROSS JOIN
 
 
Il CROSS JOIN costruisce il prodotto cartesiano
delle tabelle coinvolte
In sintassi SQL-89 si scriveva
select deptname, jobdesc from departments, jobs
In sintassi SQL-92 si scrive
select deptname, jobdesc
from departments CROSS JOIN jobs
Il risultato è identico, ma la sintassi SQL-89
porta ad errori perché il cross-join si può
confondere con un inner join senza condizioni
Andrea Bulgarelli
12
Altri usi delle JOIN (SQL server)
 
Utilizzo di JOIN per la cancellazione di dati
USE Northwind
BEGIN TRANSACTION test
DELETE [Order Details]
FROM
[Order Details] AS OD
JOIN
[Orders] AS O
ON O.orderid = OD.orderid
WHERE CustomerID = ‘VINET’
ROLLBACK TRANSACTION test
Andrea Bulgarelli
13
Altri usi delle JOIN (SQL server)
  Utilizzo
di JOIN per la modifica di dati
UPDATE OD
SET Discount = Discount + 0.05
FROM [Order Details] AS OD
JOIN
[Products] AS P
ON OD.productid =
P.productid
WHERE SupplierID = 1
Andrea Bulgarelli
14
Elementi per il miglioramento
della performance di un JOIN
  Creare
indici su colonne usate spesso in
join
  Creare indici compositi sulle combinazioni
di colonne richiamate spesso (attenzione
alle prestazioni in modifica!!)
  Separare su dischi diversi le tabelle che
partecipano a join per sfruttare il
parallelismo di I/O sui dischi
Andrea Bulgarelli
15
Subquery e tabelle derivate
 
 
 
 
Le subquery sono casi speciali di query
all’interno di una query
In genere eliminano la necessità di usare tabelle
temporanee o variabili
In molti casi (ma non in tutti) una subquery può
anche essere trasformata in un join equivalente
Le tabelle derivate sono un tipo speciale di
subquery, posizionate nella clausola FROM della
query e referenziate tramite un alias
Andrea Bulgarelli
16
Tipi di subquery (1): query annidate
 
Le subquery SCALARI ANNIDATE (o innestate,
o nidificate)
  Producono
un result set costituito da una sola riga e
colonna, utilizzabile ovunque sia assegnabile un
valore scalare singolo
  Permettono di rendere le query dinamiche e
rispondenti ai dati presenti nel database, piuttosto che
a valori esplicitamente impostati nel codice
 
Esempio: nel database Northwind, elencare tutti
gli ordini ricevuti nell’ultimo giorno utile di
contabilizzazione
  Adottiamo una strategia in due passi
  Troviamo l’ultima data
  Cerchiamo tutti i record di fatture emesse in tale data
Andrea Bulgarelli
17
Sviluppo dell’esempio
  Primo passo
<ultima_data>
= select
max(OrderDate)
from Orders
  Secondo passo
select customerid,orderid,orderdate
from orders
where orderdate = ( <ultima_data> )
… e quindi abbiamo la query complessiva
select customerid,orderid,orderdate
from orders
where orderdate = ( select
max
(OrderDate) from Orders )
Andrea Bulgarelli
18
Osservazioni
 
La SELECT innestata può essere ovunque; altro esempio
use Northwind
GO
select 'Prodotto'=ProductID,'Deviazione risp.
media'=UnitPrice-(
select avg(UnitPrice) from [Products] )
from [Products]
GO
 
Se la subquery viene introdotta da un operatore di
confronto (=, < >, >, > =, <, ! >, ! <, or < =), allora deve
riportare sempre un unico valore; le subquery introdotte
con [NOT] IN o ALL, ANY, o [NOT] EXISTS possono
riportare delle liste di risultati
Andrea Bulgarelli
19
Esempio di uso del predicato NOT IN
 
Nel database Northwind contare gli ordini che fanno capo a clienti
non inglesi
SELECT Count(*)
FROM Orders
WHERE CustomerID NOT IN
(
SELECT CustomerID
FROM
CUstomers
WHERE Country = ‘UK’
)
 
Attenzione alla presenza di valori NULL!
y ∈ {x} ⇔ OR ( y = x ), quindi y ∉ {x} ⇔ AND ( y <> x ), e se uno
degli x è NULL, allora y<>NULL è indefinito ed il predicato è falso. I
valori NULL vanno esclusi accuratamente dal result set usando la
funzione ISNULL o COALESCE
Andrea Bulgarelli
20
Esempio di uso del predicato EXISTS
 
Nel database Northwind trovare tutti i clienti che hanno ordinato
almeno una volta il prodotto 64
SELECT C.ContactName
FROM Customers AS C
WHERE EXISTS
(
SELECT *
FROM
Orders AS O
JOIN
[Order Details] AS OD
ON OD.OrderID = O.OrderID
WHERE
OD.ProductID = 64
AND C.CustomerID = O.CustomerID
)
Andrea Bulgarelli
21
Tipi di subquery (2): subquery correlate
 
 
Le subquery CORRELATE sono subquery in cui il valore della
SELECT interna è legato a valori della SELECT esterna
Esempio: nel DB Northwind selezionare gli ordini che includono più
di 36 unità del prodotto 17
SELECT O.*
FROM Orders AS O
WHERE
36 <
(
SELECT OD.Quantity
FROM [Order Details] AS OD
WHERE
OD.ProductID = 17
AND
OD.OrderID
= O.OrderID
)
Andrea Bulgarelli
22
Wildcard e LIKE
Andrea Bulgarelli
23