Operatori aggregati e raggruppamenti Il numero di figli

Operatori aggregati e raggruppamenti • Il numero di figli di ciascun padre select padre, count(*) from paternita group by Padre paternita Padre Sergio Luigi Franco Figlio Franco Olga Filippo Andrea Aldo AS Num. Figli Padre Sergio Luigi Franco Num. Figli 1 2 2

Interrogazioni con raggruppamento In ogni interrogazione che usa group by, argomento della select (escludendo l’operatore aggregato) può essere solo un sottoinsieme degli attributi usati nella clausola group by Es. di interrogazione scorretta select Ufficio from Impiegato group by Dipart Poiché deve venire prodotta una sola riga per ogni valore di Dipart, e, a parità di tale valore, possono aversi diversi valori di Ufficio, il risultato dell’interrogazione è indeterminato. In alcuni casi (es. se l’attributo non compreso nella clausola group by è chiave) la query potrebbe fornire un risultato corretto, ma per semplicità SQL lo vieta comunque.

Raggruppamenti e target list scorretta select padre, avg(f. reddito), p. reddito from persone f join paternita on figlio = nome join persone p on padre =p. nome group by padre, p. reddito

Condizioni sui gruppi Può essere anche necessario restringere i gruppi attraverso l’applicazione di condizioni. Se le condizioni sono verificabili a livello delle singole righe, basta utilizzare la clausola where, altrimenti si aggiunge una condizione alla group by attraverso l’estensione having select Dipart, sum(Stipendio) as Somma. Stipendi from Impiegati group by Dipart having sum(Stipendio) > 100 Se non si specifica group by e si usa having da solo la condizione è applicata a tutte le righe. Il problema è che se la condizione non è verificata, il risultato sarà vuoto.

Condizioni sui gruppi • I padri i cui figli hanno un reddito medio maggiore di 25 select padre, avg(f. reddito) from persone f join paternita on figlio = nome group by padre having avg(f. reddito) > 25

Select La forma di select cui siamo arrivati dopo le estensioni viste è quindi: Select. SQL : : = select Lista. Attributi. OEspressioni from Lista. Tabelle [ where Condizioni. Semplici ] [group by Lista. Attributi. Di. Raggruppamento] [ having Condizioni. Aggregate] [ order by Lista. Attributi. Di. Ordinamento]

Interrogazioni di tipo insiemistico SQL fornisce anche gli operatori di tipo insiemistico union, intersect, except (minus) con la seguente sintassi: Select. SQL {< union | intersect | except > [all]} Select. SQL NB • intersect e except potrebbero anche essere derivati attraverso opportune query • gli operatori insiemistici eseguono per default una eliminazione dei duplicati; all specifica di non farla • gli schemi su cui si opera non devono essere identici ma avere uguale numero di attributi, con domini compatibili. La corrispondenza è per posizione.

Interrogazioni di tipo insiemistico Es. Estrarre nomi e cognomi degli impiegati select Nome from Impiegato union select Cognome from Impiegato Estrarre i cognomi degli impiegati che sono anche nomi select Nome from Impiegato intersect select Cognome from Impiegato

WHERE o HAVING? • I padri i cui figli sotto i 30 anni hanno un reddito medio maggiore di 20 select padre, avg(f. reddito) from persone f join paternita on figlio = nome where eta < 30 group by padre having avg(f. reddito) > 25

Interrogazioni nidificate E’ possibile anche realizzare clausole where in cui il confronto non avviene fra predicati semplici o fra valori, ma fra valori e risultati di interrogazioni. Tipicamente, il risultato dell’interrogazione è un attributo. Sorge il problema di confrontare un valore con un insieme di valori (il risultato della interrogazione). SQL offre 2 possibilità per estendere i normali operatori di confronto: all specifica che il confronto è vero se è vero per tutte le righe del risultato dell’interrogazione. any specifica che il confronto è vero se è vero per una qualunque delle righe del risultato dell’interrogazione.

Interrogazioni Nidificate Es. select * from Impiegato where Dipart = any (select Nome from Dipartimento where Città=‘Firenze’) Il risultato è una tabella che comprende tutte le righe di IMPIEGATO per cui il valore Dipart è uguale ad almeno uno dei valori di Nome in DIPARTIMENTO, limitatamente alle tuple per cui Citta’=‘Firenze’. Lo stesso risultato si poteva ottenere con un join, ma così, specialmente per interrogazioni complesse, è più leggibile.

Nome e reddito del padre di Franco select Nome, Reddito from Persone, Paternita where Nome = Padre and Figlio = 'Franco' select Nome, Reddito from Persone where Nome = (select Padre from Paternita where Figlio = 'Franco')

Interrogazioni Nidificate L’uso delle interrogazioni nidificate può anche eliminare la necessità degli alias. Es. select I 1. Nome from Impiegato I 1, Impiegato I 2 where I 1. Nome = I 2. Nome and I 1. Dipart =‘Produzione’ and I 2. Dipart <> ‘Produzione’ equivale a select Nome from Impiegato where Nome = any (select Nome from Impiegato where Dipart = ‘Produzione’) and Dipart <> ‘Produzione’

Interrogazioni Nidificate Non tutte le interrogazioni nidificate corrispondono però ad un join. Es. select Nome from Dipartimento where Nome <> all ( select Dipart from Impiegato where Cognome=‘Rossi’ ). La condizione è verificata per le righe che NON contengono un certo valore, quindi non è esprimibile mediante un join, che richiede una corrispondenza fra valori. Però è equivalente a : PNome(DIPARTIMENTO) - PDipart (s Cognome=‘Rossi’(IMPIEGATO))) e quindi le due query potevano essere unite da except Nota =any e <>all si possono anche scrivere in e not in

Interrogazioni nidificate Un’altra equivalenza può essere evidenziata con operatori aggregati. select Dipart from Impiegato where Stipendio = (select max(Stipendio) from Impiegato) equivale a select Stipendio from Impiegato where Stipendio >= all ( select Stipendio from Impiegato )

Interpretazione delle query nidificate Per analizzare il risultato di una interrogazione nidificata si può supporre di valutare prima il risultato dell’interrogazione nidificata e poi quella della interrogazione che la contiene. Questo va anche a favore dell’efficienza in quanto l’interrogazione nidificata viene eseguita una sola volta. Talvolta però esiste un riferimento tramite una variabile fra l’interrogazione nidificata e quella che la contiene (passaggio di binding). In questo caso bisogna tornare alla definizione originaria di query, che implica il calcolo del prodotto cartesiano fra le tabelle e poi la verifica della condizione where separatamente per ciascuna riga. Quindi: per ogni riga della query esterna si valuta prima la query nidificata poi si calcola il predicato a livello di riga sulla query esterna.

Interrogazioni Nidificate Visibilità delle variabili Le variabili SQL sono utilizzabili solo nell’ambito della query in cui sono definite o nell’ambito di una query nidificata all’interno di essa. Se due query sono allo stesso livello non possono condividere variabili. exists è un operatore logico applicabile a query nidificate. Restituisce vero se la query dà un risultato non nullo, falso se è nullo. E’ utilizzabile in modo significativo solo se esiste un passaggio di binding fra interrogazione esterna e interrogazione nidificata.

Le persone che hanno almeno un figlio select * from Persone where exists ( select * from Paternita where Padre = Nome) or exists ( select * from Maternita where Madre = Nome)

exists select * from Persona P where exists ( select * from Persona P 1 where P 1. Nome = P. Nome and P 1. Cognome = P. Cognome and P 1. Cod. Fiscale <> P. Cod. Fiscale ) In questo caso non è possibile eseguire prima la query nidificata, in quanto indeterminata se non si risolve il riferimento. Quindi per ogni riga dell’interrogazione esterna dovrà essere valutata l’interrogazione nidificata.

Costruttore di Tuple Quando esiste un’uguaglianza fra un insieme di attributi è possibile sintetizzare la query inserendo gli attributi all’interno di una parentesi tonda. Es. Select * from Persona P where (Nome, Cognome) not in (select Nome, Cognome from Persona Q where P. Cod. Fiscale <> Q. Cod. Fiscale)

Massimo e nidificazione • La persona (o le persone) con il reddito massimo select * from persone where reddito = ( select max(reddito) from persone)

Manipolazione dei dati I comandi SQL che permettono di modificare il contenuto di una base di dati sono insert delete update insert ha la seguente sintassi insert into Nome. Tabella [Lista. Attributi] < values (Lista. Di. Valori) | Select. SQL > Nel primo caso si inserisce una singola riga all’interno della tabella. Nel secondo si possono aggiungere degli insiemi di righe, estratti dal contenuto della base di dati.

Manipolazione dei Dati Es. insert into Dipartimento(Nome. Dip, Città) values (‘Produzione’, ‘Torino’) Si utilizza in genere mediante una maschera (form) per consentire agli utenti di inserire dati. L’ordinamento degli attributi (se presente) e dei valori è significativo e le due liste devono avere lo stesso numero di elementi insert into Prodotti. Milanesi (select codice, descrizione from Prodotto where Luogo. Prod = ‘Milano’) Se in un inserimento non vengono inseriti tutti i dati di una riga si usa o il default (se esiste) o il valore nullo. La corrispondenza fra valori inseriti e attributi è per posizione.

INSERT INTO Persone VALUES ('Mario', 25, 52) INSERT INTO Persone(Nome, Eta, Reddito) VALUES('Pino', 25, 52) INSERT INTO Persone(Nome, Reddito) VALUES('Lino', 55) INSERT INTO Persone ( Nome ) SELECT Padre FROM Paternita WHERE Padre NOT IN (SELECT Nome FROM Persone)

Cancellazione delete from Nome. Tabella [ where Condizione ] se where non è specificato tutte le righe della tabella vengono eliminate. Se esiste un vincolo di integrità referenziale con politica di cascade … ATTENZIONE!!!! Siccome delete ha la stessa sintassi di select, è possibile utilizzare interrogazioni nidificate al suo interno. Es. delete from Dipartimento where Nome not in (select Dipart from Impiegato) delete from Dipartimento distrugge il contenuto della tabella drop table Dipartimento cascade altera lo schema

Modifica update Nome. Tabella set Attributo = < Espressione | Select. SQL | null | default > { , Attributo = < Espressione | Select. SQL | null | default > } [ where Condizione ] aggiorna uno o più attributi delle righe di Nome. Tabella che soddisfano l’eventuale Condizione. Se non c’è condizione la modifica avviene per tutte le righe. Il nuovo valore può essere: • il risultato di un’espressione valutata sugli attributi della tabella • il risultato di una generica interrogazione SQL • il valore nullo • il valore di default

Modifica update Dipendente set Stipendio = Stipendio. Base + 5 where Matricola = ‘XYZ’ aggiorna una sola riga update Dipendente set Stipendio = Stipendio * 1. 1 where Dipart = ‘Amministrazione’ aggiorna un insieme di righe Nella valutazione delle espressioni bisogna ricordarsi che SQL ha una natura orientata agli insiemi e non alle tuple. Quindi non è possibile pensare all’esecuzione di un comando come esecuzione riga per riga, ma come un’operazione effettuata contemporaneamente su tutto un insieme.

Modifica Attenzione! Vogliamo aumentare del 10% gli stipendi degli impiegati con stipendio inferiore ai 30 milioni e del 15% quello degli impiegati con stipendio superiore ai 30 milioni. Se scriviamo update Impiegato set Stipendio = Stipendio * 1. 1 where Stipendio <= 30 update Impiegato set Stipendio = Stipendio * 1. 15 where Stipendio > 30 ad alcuni impiegati lo stipendio viene aumentato 2 volte!!! Se invertiamo l’ordine la procedura risulta corretta. Ma non sempre è così facile……. . .