Il linguaggio SQL: Interrogazioni in SQLa080066/didattica/BD/6 - SQL interrogazioni.pdf ·...

Il linguaggio SQL:Interrogazioni in SQL

[email protected]

Link al software

• http://www2.ing.unipi.it/~a080066/didattica/BD/mysql-installer-community-5.7.20.0.msi

Interrogazioni in SQL

• Le interrogazioni o query di selezione, sono la funzionalità principale di SQL. L’SQL esprime le interrogazioni in modo dichiarativo, cioè specifica l’obiettivo dell’interrogazione e non il modo con cui ottenerlo

• I dati estratti tramite una query possono essere:• Valori singoli: il valore restituito è una tabella formata da una sola tupla

con una sola colonna

• Liste: il valore restituito è una tabella formata da una sola colonna

• Tabella: il risultato della query è una tabella temporanea

• La struttura generale di una query SQL può essere espressa per mezzo dell’espressione algebrica:

(colonna1, colonna2,…) ( condizione (tabella1 X tabella2))

Query Optimizer

• Le interrogazioni espresse dall’utente programmatore prima di essere eseguite vengono analizzate dal DBMS e ottimizzate

• In particolare un componente, il Query Optimizer, analizza l’interrogazione e formula un’interrogazione equivalente nel linguaggio procedurale interno al sistema (che rimane comunque nascosto all’utente)

• In questo processo il query optimizer ottimizza la richiesta applicando tecniche di ottimizzazione standard

• Esistono in generale molti modi diversi per esprimere la stessa interrogazione in SQL, ma grazie all’azione del queryoptimizer l’utente deve solo preoccuparsi della leggibilità e modificabilità della query senza preoccuparsi della sua ottimizazione

Interrogazione – Sintassi Base• Le interrogazioni in SQL vengono specificate per mezzo

dell’istruzione SELECT

• La sintassi di base di una interrogazione è la seguente:

SELECT ListaAttributiFROM ListaTabelle[ WHERE Condizione]

• ListaAttributi (cosa si vuole come risultato) è la lista delle colonne da estrarre, l’ordine e il nome che si vuole assegnare.

• ListaTabelle (da dove si prende) è la lista delle tabelle sulle quali verranno estratti i dati

• Condizione (che condizioni deve soddisfare) definisce una condizione su cui verranno filtrati i record da estrarre. Le tuple estratte sono quelle per cui la condizione vale true

(colonna1, colonna2,…) ( condizione (tabella1 X tabella2))

Sintassi Base – Note

• Non è obbligatorio scrivere le clausole su righe separate

• Per mandare in esecuzione un comando è necessario che termini con il ;

• Le istruzioni SQL possono essere scritte in maiuscolo o minuscolo

• Solo le clausole SELECT e FROM sono obbligatorie• Ma c’e’ anche SELECT expr

• In ogni istruzione SQL si possono inserire commenti• con -- se su singola riga• oppure /* in questo modo se si tratta

di commenti su più righe */

Clausola SELECTSELECT [ ALL | DISTINCT] <attrEspr> [[ AS] <alias>] {,

<attrEspr> [[ AS] <alias>]}

• <attrEspr> definisce le colonne nel risultato della query, ciascuna colonna può essere rinominata. In alcuni casi particolari permette anche di generare di nuove

• Corrisponde ad un’operazione di proiezione e ridenominazione (tramite As alias)

• <attrEspr> può assumere il valore *. Permette di recuperare tutti gli attributi della tabella specificata nella clausola FROM.

• La parola chiave DISTINCT e ALL regola come si comporterà il sistema nel caso di tuple duplicate nel risultato, nel caso ALL (comportamento di default) i duplicati vengono inclusi, nel caso di DISTINCT i duplicati vengono eliminati

DB di esempioConsideriamo i seguenti schemi di una base di dati relazionale:

• IMPIEGATI(Matricola, Cognome, Nome, Mansione, IdReparto, StipendioAnnuale, PremioProduzione, DataAssunzione)

• REPARTI(IdReparto, NomeReparto, Indirizzo, Città)

Viene data la seguente istanza della base di dati:

IdReparto NomeReparto Indirizzo Città

1 Amministrazione Via Roma, 5 Milano

2 Sviluppo Via Padova, 6 Roma

3 Direzione Piazza Vittorio Emanuele, 23 Milano

Matricola Cognome Nome Mansione IdReparto StipendioAnnuale PremioProduzione DataAssunzione

1 Rossi Giulia Analista 2 25000 2000 2012-12-07

2 Bianchi Mario Segretario 1 25000 1000 2001-03-14

3 Verdi Paola Dirigente 3 60000 8000 2006-01-10

4 Rossi Marco Sviluppatore 2 40000 5000 2004-05-03

5 Neri Luca Dirigente 3 65000 6000 2001-10-27

6 Bianchi Andrea Segretario 1 25000 NULL 2001-03-14

Esempi select: proiezione senza selezione • Selezionare nome e cognome di tutti gli impiegati:

SELECT nome, cognome

FROM impiegati;nome cognome

Giulia Rossi

Mario Bianchi

Paola Verdi

Marco Rossi

Luca Neri

Andrea Bianchi

• Equivale a :SELECT nome, cognomeFROM impiegatiWHERE true;

Esempi select: selezione e proiezione • Estrarre nome e cognome degli impiegati che

guadagnano più di 40000 EuroSELECT nome, cognome

FROM impiegati

WHERE StipendioAnnuale > 40000;

nome cognome

Paola Verdi

Luca Neri

Note: 1) mysql è case insensitive sul nome degli attributi.2) Sul nome delle tabelle, dipende dal S.O. su cui è implementato. Se windows è CI, su

Linux CS.

Esempi select: selezione e proiezione • Se non vi sono tuple che soddisfano la condizione,

l’interrogazione restituisce un insieme vuoto.

• Estrarre nome e cognome degli impiegati che guadagnano più di 100000 Euro

SELECT nome, cognome

FROM impiegati


nome cognome

Esempi select: selezione senza proiezione

• Estrarre tutte le informazioni relative agli impiegati che guadagnano più di 40000 Euro

SELECT *

FROM impiegati





• Equivale a SELECT Matricola, Cognome, Nome, Mansione, IdReparto, StipendioAnnuale, PremioProduzione, DataAssunzione

FROM impiegati


Clausola SELECT: ridenominazione ed espressioni• La clausola AS permette di rinominare un attributo

• Nella clausola SELECT possono comparire anche generiche espressioni sul valore degli attributi al fine di manipolarne il valore

• <attrEspr> è in generale una espressione, e quindi si possono usare operatori e funzioni.

• In generale, si può rinominare <attrEspr> , e quindi non solo il nome di una colonna ma anche il risultato di una operazione.

Esempio ridenominazione

• Trovare il cognome e lo stipendio annuale dei dirigenti, rinominando il campo risultato come stipendio:

SELECT cognome, StipendioAnnuale AS stipendio

FROM impiegati

WHERE Mansione = 'Dirigente';

cognome stipendio

Verdi 60000

Neri 65000

Esempio espressioni e ridenominazione• calcolare lo stipendio mensile di ciascun Dirigente,

indicando nel risultato nome e cognomeSELECT nome, cognome, StipendioAnnuale/12 AS StipendioMensile

FROM impiegati

WHERE Mansione = 'Dirigente';nome cognome StipendioMensile

Paola Verdi 5000.0000

Luca Neri 5416.6667

nome cognome StipendioAnnuale/12

Paola Verdi 5000.0000

Luca Neri 5416.6667Senza ridenominazione

Altro esempio

• Tabella impiegato(Nome, Cognome, CodiceFiscale, StipendioAnnuale)

• La seguente query permette di ottenere una tabella che elenca i codici fiscali e rinominando la colonna in CF

SELECT CodiceFiscale AS CF FROM Impiegato;

Ricapitolazione: SELECT -EspressioniSELECT[ ALL | DISTINCT]<* | <espressione> [ AS<nome>]{ [,…] }>

• Permette di specificare gli attributi che devono comparire nel risultato della query

• Può contenere anche espressioni (numeriche, su stringhe, ecc.)

Esempio:

SELECT Nome, StipendioAnnuale, PremioProduzione,

PremioProduzione+StipendioAnnuale

FROM Impiegati WHERE Mansione = 'Sviluppatore’;

Risultato query:

Nome StipendioAnnuale PremioProduzione PremioProduzione+StipendioAnnuale

Marco 40000 5000 45000

Ricapitolazione: SELECT - AliasSELECT[ ALL | DISTINCT]<* | <espressione> [ AS <nome>] {[,…] }>

Ad ogni elemento della Target list (lista di attributi) è possibile associare un

nome a piacere.

• la keyword AS può anche essere omessa

Esempio:

SELECT Nome, StipendioAnnuale, PremioProduzione, PremioProduzione+StipendioAnnuale AS Reddito

FROM Impiegati WHERE Mansione = ‘Sviluppatore’;

oppure

SELECT Nome, StipendioAnnuale, PremioProduzione, PremioProduzione+StipendioAnnuale Reddito

FROM Impiegati WHERE Mansione = ‘Sviluppatore’;

Risultato query:

Clausola FROMFROM <nome tabella> [ [ AS ]<alias> ] {, <nome tabella> [ [ AS

]<alias> ] }

• Specifica la tabella o le tabelle su cui si fa la query

• Costruisce la tabella intermedia a partire da una o più tabelle, su cui operano le altre clausole (SELECT, WHERE …)

• La tabella intermedia è il risultato del prodotto cartesiano delle tabelle elencate

• Tramite la clausola AS si può specificare un alias della tabella per convenienza (l’alias non è obbligatorio)

• In alcuni casi necessario• Definito l’alias, gi attributi sia nel select che nel where sono visibili con

nome alias.attributo e non più nome_tabella.attributo (ma sempre some attributo)

Clausola FROM

Codice Partenza Arrivo

AZ427 FCO JFK

TW056 LAX FCO

Voli(Codice, Data, Comandante)

Codice Data Comandante

AZ427 21/07/2001 Bianchi

AZ427 23/07/2001 Rossi

TW056 21/07/2001 Smith

Linee(Codice, Partenza, Arrivo)

SELECT * FROM Voli AS V, Linee AS L;

V.Codice Data Comandante L.Codice Partenza Arrivo

AZ427 21/07/2001 Bianchi AZ427 FCO JFK

AZ427 23/07/2001 Rossi AZ427 FCO JFK

TW056 21/07/2001 Smith AZ427 FCO JFK

AZ427 21/07/2001 Bianchi TW056 LAX FCO

AZ427 23/07/2001 Rossi TW056 LAX FCO

TW056 21/07/2001 Smith TW056 LAX FCO

Per evitare ambiguità gli attributi con lo stesso nome vengono (almeno dovrebbero) identificati nella tabella risultante attraverso NomeTabella.Attributo (oppure usando l’alias definito) – MYSQL non fa il renaming (come in tabella), ACCESS si

Esempio: collegare i dati fra tabelle• Il collegamento viene effettuato imponendo

opportune condizioni nella clausola where

• Esempio: trovare i comandanti dei voli e le relative città di partenza:

SELECT Comandante, Partenza

FROM Voli , Linee

WHERE Voli.Codice = Linee.codice ;

Comandante PartenzaBianchi FCO

Rossi FCO

Smith LAX

• Per indentificare campi a comune fra le due tabelle, si usa la notazione

• Nome_tabella.Nome_campo• Tale notazione è obbligatoria per

campi con lo stesso nome, facoltativa negli altri casi

Uso degli alias per semplificare/migliorare

SELECT Comandante, Partenza As CittadiPartenza

FROM Voli As V, Linee As L

WHERE V.Codice = L.codice ;

Comandante CittadiPartenzaBianchi FCO

Rossi FCO

Smith LAX

ricap.: FROM - AliasFROM <nome tabella> [ [ AS ]<alias> ]

• Per abbreviare la scrittura si può introdurre uno pseudonimo (alias) per la tabella

• Inoltre per referenziare una colonna si può anche fare uso della forma estesa

• <nome tabella>.<nome colonna>

Esempio:

SELECT Imp.matricola FROM impiegati AS Imp WHERE Imp.Nome = ‘Paola’;

oppure

SELECT impiegati.matricola FROM impiegati WHERE impiegati.Nome = ‘Paola’;

NOTA: La referenziazione estesa non è obbligatoria. Lo diventa in caso di ambiguità (vedi prodotto cartesiano)

NOTA: L’uso degli alias non è obbligatorio. Lo può diventare in alcuni casi (self join?)

Clausola WHERE

[ WHERE <Condizione>]

• Permette di filtrare le tuple in base ad una condizione

• consiste, nel caso generale, di una espressione logica di predicati

• i predicati possono essere combinati usando gli operatori logici (AND, OR, NOT), di comparazione, matematici e operatori sulle stringhe

• è opzionale

• Una tupla soddisfa la clausola WHERE se e solo se l’espressione risulta vera per tale tupla

• Se compaiono nomi di attributi, questi devono appartenere a tabelle specificate nella clausola from

• Utilizza la logica a 3 valori……

Clausola where• <condizione> è in generale una espressione booleana,

ottenuta combinando predicati semplici con gli operatori and, or e not

• Il not ha precedenza su and e or, l’and maggiore di or in MYSQL e altri(Java?).

• Lo standard (Ceri Pag. 110 non prevede precedenze fra AND e OR)• L’associatività è da sx a dx• Usare parentesi per esplicitare l’ordine di valutazione

• I predicati semplici sono costruiti, in generale, utilizzando gli operatori di confronto

• A dx e sx di tali operatori di trovano espressioni costruite a partire dagli attributi delle righe o valori costanti

• Nella costruzione delle espressioni si possono usare altri operatori (ad esempio aritmetici) o funzioni scalari

• Operatori di confronto: = < <= > >= <> o !=

Esempio: predicati complessi• Dato lo schema: IMPIEGATI(Matricola, Cognome, Nome,

Mansione, IdReparto, StipendioAnnuale, PremioProduzione, DataAssunzione)

• Trovare Nome e Cognome dei dirigenti che guadagnano più di 60000 Euro

select nome, cognomefrom impiegatiwhere mansione='dirigente' and StipendioAnnuale > 60000;

• Trovare Nome e Cognome degli impiegati che sono ‘segretario’ o ‘sviluppatore’

select nome, cognome from impiegatiwhere mansione='segretario' or mansione='sviluppatore';

• Trovare Nome, Cognome e mansione degli impiegati che non sono dirigenti

• select nome, cognome, mansione• from impiegati• where NOT mansione='dirigente';• In alternativa• select nome, cognome, mansione• from impiegati• where mansione<>'dirigente';

Istanza del db

Matricola Cognome Nome Mansione IdReparto StipendioAnnuale

PremioProduzione

DataAssunzione

1 Rossi Giulia Analista 2 25000 20002012-12-07

2 Bianchi Mario Segretario 1 25000 10002001-03-14

3 Verdi Paola Dirigente 3 60000 80002006-01-10

4 Rossi MarcoSviluppatore

2 40000 50002004-05-03

5 Neri Luca Dirigente 3 65000 60002001-10-27

6 Rossi Andrea Segretario 1 25000 NULL2001-03-14

7 Celesti Italo Analista 1 25000 2000 NULL

impiegati1

Esempio: precedenza and or e parentesi• Trovare Nome, Cognome degli impiegati che sono

‘analista’ o ‘segretario’ e che si chiamano ‘Rossiselect nome, cognome, mansione

from impiegati1

where mansione='analista' or mansione='segretario' and cognome='rossi';

select nome, cognome

from impiegati1

where mansione='segretario' or mansione='analista' and cognome='rossi';

Nome Cognome Mansione

Giulia Rossi Analista

Andrea Rossi Segretario

Italo Celesti Analista



Mario Bianchi Segretario


Soluzione: le parentesi

select nome, cognome, mansione

from impiegati1

where (mansione= 'segretario' or mansione= 'analista') and cognome='rossi';




Esempio: utilizzo di altri operatori nel where

• Trovare nome, cognome e mansione di tutti i dipendenti per i quali il premio di produzione è superiore allo stipendio mensile

select nome, cognome, mansione

from impiegati1

where PremioProduzione > StipendioAnnuale/12;

Gestione dei Null e Logica predicati a tre valori

• I valori NULL rappresentano valori non noti o non applicabili o entrambi (assenza di informazione)

• Qualsiasi confronto logico con un valore che è NULL porta a UNKNOWN come risultato

• L’applicazione di funzioni e/o operatori in cui un operando è NULL genera un risultato NULL (unknown)

• Per i connettori logici, SQL ricorre alla logica dei predicati a tre valori: vero (V), falso (F), sconosciuto (unknown, indicato con U)

• Fanno eccezione i predicati IS NULL e IS NOT NULL, il cui valore è sempre o vero o falso, anche se il valore di confronto è sconosciuto

• Condizioni di selezione per cui il valore è UNKNOWN (NULL) vengono comunque scartate

In sostanza:• NOT A è vero se e solo se A

è falso• A AND B è vero se e solo se

entrambi A e B sono veri • A OR B è vero se e solo

almeno uno tra A e B è vero

Controllo valori NULL• Operatori IS NULL e IS NOT NULL

• A IS NULL è true se A vale NULL, false altrimenti

• A IS NOT NULL è true se A ha un valore noto, false altrimenti

• Esempio:

SELECT * FROM Impiegati WHERE PremioProduzioneIS NULL;

Dà come risultato:



Esempio: Effetti del null sul whereSELECT * FROM impiegati

where PremioProduzione <= 30000 or PremioProduzione>30000;

• Per le condizioni del where, dovrebbero essere selezionate tutte le tuple. Invece il risultato è:


PremioProduzione

DataAssunzione






Perchè la tupla con valore NULL su premio di produzione genera NULL or NULL e viene esclusa dal risultato

Per generare tutta la tabella

SELECT * FROM impiegati

where PremioProduzione <= 30000 or PremioProduzione>30000 or PremioProduzione IS NULL ;

• Questa equivale a

SELECT * FROM impiegati;

Operatori DISTINCT e ALL

SELECT[ ALL | DISTINCT]{* | <espressione> [ AS<nome>] }[,…]

• Il risultato di una query SQL può contenere righe duplicate

• Per eliminare I duplicati si usa la parola chiave DISTINCT nella clausola SELECT.

• ALL è il valore di default

Esempio:

1. SELECT Mansione FROM Impiegati WHERE PremioProduzione>1000;

2. SELECT DISTINCT Mansione FROM Impiegati WHEREPremioProduzione>1000;

Mansione

Analista

Dirigente

Sviluppatore

Dirigente

Mansione

Analista

Dirigente

Sviluppatore

Risultato Query 1 Risultato Query 2

Confronto tra stringhe – oltre gli operatori convenzionaliOperatore LIKE

• L’ operatore LIKE permette di trovare stringhe che soddisfano un certo “pattern” mediante le “wildcard”

• _ (corrisponde a un carattere arbitrario – uno e uno solo)

• % (corrisponde a una stringa arbitraria – anche di lunghezza nulla)

• L’espressione è vera se il pattern ha un match con la stringa

• Esempi:• ‘M%A’ fà match con ‘MA’, ‘MAMMA’, ecc.

• ‘%MA%’ fà match con qualsiasi stringa che contiene la sottostringa ‘MA’, ecc

• ‘’M_R%’ fà match con MARIO, MARCO ma NON con Mario e Marco (case sensitive fa distinzione tra le lettere maiuscole e quelle minuscole)

• In realtà mysql è case-insensitive come pure Access, cosi la differenza fra maiuscolo e minuscolo non è rilevante per la ricerca

Esempio:

Impiegati il cui nome finisce con ‘o’

SELECT * FROM Impiegati WHERE Nome LIKE ‘%o’;

Impiegati il cui nome inizia per ‘M’ e hanno il carattere r in terza posizione

SELECT * FROM Impiegati WHERE Nome LIKE 'M_r%‘;

Confronto numerico

Operatore BETWEEN• L’operatore BETWEEN permette di esprimere condizioni di

appartenenza ad un intervallo (estremi inclusi)

• Sintassi: <operando> between <operando> and <operando>

Esempio:Impiegati il cui stipendio annuale è compreso tra 40000 e

65000:SELECT * FROM impiegati WHERE stipendioannuale BETWEEN 40000 AND 65000;

SELECT * FROM impiegati WHERE stipendioannuale >= 40000 AND stipendioannuale<= 65000;


PremioProduzione

DataAssunzione


4 Rossi MarcoSviluppatore

2 40000 5000 2004-05-03


Appartenenza ad un insiemeOperatori IN e NOT IN• L’operatore IN permette di esprimere condizioni di appartenenza ad un insieme

di valori

• L’operatore NOT IN permette di esprimere condizioni di NON appartenenza ad un insieme di valori

Esempio:Impiegati che hanno uno stipendio annuale di 40000 o 65000SELECT * FROM impiegati WHERE stipendioannuale IN (40000,65000);

è equivalente a:

SELECT * FROM impiegati WHERE stipendioannuale = 40000 OR stipendioannuale = 65000;

Impiegati che non hanno uno stipendio annuale di 40000 o 65000SELECT * FROM impiegati WHERE stipendioannuale NOT IN (40000,65000);

è equivalente a:

SELECT * FROM impiegati WHERE stipendioannuale != 40000 AND stipendioannuale != 65000;

Insieme di appartenenza: stringhe ed altro• L’insieme di appartenenza può essere un insieme di

valori numerici, stringhe o altri tipi di dato

• Esempio: trovare tutte le informazione degli impiegati la cui qualifica è dirigente, analista, sviluppatore

SELECT * FROM impiegati WHERE mansione in ('dirigente', 'analista', 'sviluppatore');

• La query può anche essere scritta come or di condizioni.

Ordinamento dei risultati[ ORDER BY <espressione> [ ASC | DESC ] [,…] ]

La clausola ORDER BY si usa per ordinare il risultato di una query secondo i

valori di una o più colonne

• Per ogni colonna si specifica se l'ordinamento è per valori ascendenti (operatore ASC) o discendenti (operatore DESC)

• Valore di default è ASC

Esempio:

SELECT Cognome, Nome FROM Impiegati ORDER BY Cognome ASC, Nome DESC;

è equivalente a:

SELECT Cognome, Nome FROM Impiegati ORDER BY Cognome, Nome DESC;

Nota: ordina prima rispetto a Cognome in modo crescente, poi sulle tuple che hanno valori uguali per Cognome ordina rispetto a Nome in modo decrescente

esempi• Trovare cognome e nome degli impiegati,

ordinando i cognomi in modo crescenteselect cognome, nome from impiegati

order by cognome;

Equivalente a

select cognome, nome from impiegati

order by cognome asc;

• I nomi non hanno alcun ordinamento

• Imponiamo un ordinamento crescente per i nomi:select cognome, nome from impiegati

order by cognome, nome

Cognome Nome

Bianchi Mario

Bianchi Andrea

Neri Luca

Rossi Giulia

Rossi Marco

Verdi Paola

Cognome Nome

Bianchi Andrea

Bianchi Mario

Neri Luca

Rossi Giulia

Rossi Marco

Verdi Paola

Osservazione 1

• L’attributo su cui si effettua l’ordinamento non deve comparire necessariamente nella select

• Prima si fa l’ordinamento sulla tabella generato dal from, poi si fa la selezione con il where, poi si ordina, infine si proietta (select)

• Esempio: ordiniamo rispetto allo stipendio, quindi rispetto a cognome e nome

select cognome, nome, stipendioannualefrom impiegatiorder by StipendioAnnuale, cognome, nome;

• Lo stesso risultato si ottiene omettendo stipendio annuale dalla select

Cognome Nome stipensioannuale

Bianchi Andrea 25000

Bianchi Mario 25000

Rossi Giulia 25000

Rossi Marco 40000

Verdi Paola 60000

Neri Luca 65000

Cognome Nome

Bianchi Andrea

Bianchi Mario

Rossi Giulia

Rossi Marco

Verdi Paola

Neri Luca

Osservazione 2• La clausola è ORDER BY <espressione>,

dove <espressione> può essere una qualunque espressione e non necessariamente il nome di una colonna

• Esempio: estrarre il nome e cognome degli impiegati, ordinati in modo decrescente rispetto alla retribuzione totale. Per coloro che hanno uguale retribuzione, si ordina in modo crescente rispetto al cognome ed al nome

SELECT nome, cognome, StipendioAnnuale+PremioProduzione asretr_totaleFROM impiegati order by StipendioAnnuale+PremioProduzionedesc , cognome, nome ;

• NOTE: il NULL è considerato inferiore a tutti i valori

Cognome Nome Retr_totale

Neri Luca 71000

Verdi Paola 68000

Rossi Marco 45000

Rossi Giulia 27000

Bianchi Mario 26000

Bianchi Andrea NULL

Il linguaggio SQL:funzioni aggregate e

interrogazioni con raggruppamento

[email protected]

Funzioni aggregate o di gruppo

• Le funzioni di gruppo permettono di calcolare espressioni/valori su di un insieme di righe

• Operano su più righe ma restituiscono un singolo valore• In generale, ignorano i valori null

• Funzioni Previste dallo standard:• Il numero di elementi (COUNT)• Il minimo (MIN)• Il massimo (MAX)• La media (AVG)• Il totale (SUM)

• E tante altre nelle varie implementazioni

Sintassi Generale:

SELECT Funzione ( [DISTINCT | ALL ] EspressioneSuAttributi )

COUNT• Permette di contare il numero di tuple della tabella risultante

Sintassi:

COUNT ( <* | [DISTINCT | ALL ] ListaEspressioniSuAttributi> )

Esempi:

COUNT(*)• conta il numero di tuple del risultato• Se non vi sono tuple, restituisce 0• Conta anche i valori null

COUNT(Attributo) o COUNT (ALL Attributo) • conta il numero di righe che hanno valori di attributo diverso da

null (considerando i duplicati)

COUNT(DISTINCT Attributo)• conta i valori distinti di un attributo (i duplicati non sono considerati

ed il null è escluso)

DB di esempio (il solito)Consideriamo i seguenti schemi di una base di dati relazionale:


• REPARTI(IdReparto, NomeReparto, Indirizzo, Città)


IdReparto NomeReparto Indirizzo Città











esempi• Il numero di righe della tabella impiegati (il numero di

impiegati)

SELECT COUNT(*) FROM Impiegati;• 6

• Il numero di premi di produzione diversi da null

SELECT COUNT(PremioProduzione) FROM Impiegati;• 5 perchè c’e’ un NULL

• Il numero di stipendi annuali distinti

SELECT COUNT(DISTINCT stipendioannuale) FROMImpiegati;

• 4 perchè un valore è ripetuto 3 volte

COUNT(*)

6

COUNT()

5

COUNT(*)

4

Altri esempi• SELECT count(*) FROM impiegati where

matricola=6;

• SELECT count(*) FROM impiegati where matricola=7;

• SELECT count(PremioProduzione) FROM impiegatiwhere matricola=6; // risultato 0

• SELECT count(PremioProduzione) FROM impiegatiwhere matricola=7; // risultato 0








count( *)

1

count( *)

0

osservazioni

• Nel COUNT possono apparire più attributi

• In tal caso:• Count (ALL attr1, attr2, etc) conta il numero di tuple diverse

da null• Se il valore di un solo attributo è null, la tupla è null

• Count (distinct attr1, attr2, etc) conta il numero di tuplediverse (e diverse da null)

• Se il valore di un solo attributo è null, la tupla è null

• Osservazione: mySQL supporta solo count(ALL expr) e non Count (ALL attr1, attr2, etc), per cui si può rendere equivalente con la funzione concact

esempi• Trovare le coppie distinte cognome, premioProduzione

select count(distinct cognome, PremioProduzione) from impiegati;

• Risultato 5: perché?








esempi• Trovare le coppie distinte cognome, IdReparto ed il

numero di coppie cognome, IdReparto

SELECT count( distinct cognome, IdReparto), count(all concat(cognome, IdReparto))

FROM impiegati








count( distinct cognome, IdReparto) count(concat(cognome, IdReparto))

4 6

SUM, AVG, MAX e MIN• Permettono di aggregare i dati delle tuple

• Calcolano la somma, la media, il minimo ed il max degli attributi/espressioni facenti parte del risultato

• Ammettono come argomento un attributo o un’espressione (ma non “*” del count)

• SUM e AVG: argomenti numerici (o tempo ma non in mysql)• MAX e MIN: argomenti su cui è definito un ordinamento (anche

le stringhe e le date)• Anche STD

• Restituiscono null se il risultato non ha righe o contiene tutti null

• Ammettono le opzioni ALL e DISTINCT con il consueto significato

• Ininfluenti per MAX e MIN

esempi

• Trovare dei premi produzione il più basso, il più alto, la media e il totale

SELECT MIN(PremioProd) AS MinorPremio, MAX(PremioProd) ASMaggiorPremio, AVG(PremioProd) AS MediaPremi, SUM(PremioProd) AS TotalePremi

FROM Impiegati;

• Trovare il premio produzione più alto del reparto ‘Sviluppo’

SELECT MAX(premioproduzione) AS MaxPremioSviluppo

FROM impiegati, reparti

WHERE reparti.Nome = 'Sviluppo' AND impiegati.IdReparto = Reparti.IdReparto;

• Le funzioni aggregate consentono di ricavare proprietà/funzioni su di un insieme di tuple

• Sarebbe utile poter raggruppare le tuple su cui applicare le funzioni aggregate

• Ad esempio se si volesse trovare il più alto premio di produzione per ogni reparto:

• Si fanno tante query del tipo precedente, una per ogni reparto

• Si considerano parte di un gruppo (sottoinsieme) le tuple con lo stesso valore di reparto, e ad ognuno di questi gruppi si applica la funzione max o in generale la funzione aggregata

• Cio viene svolto tramite la clausola GROUP BY

Raggruppamento di Tuple

Group BYClausola GROUP BY

• La clausola GROUP BY consente di realizzare l’operazione di aggregazione su sottoinsiemi delle tuple

• Le tuple vengono raggruppate secondo i valori degli attributi specificati nella clausola, e poi su ciascun sottoinsieme viene calcolato il valore aggregato

• La clausola group by si utilizza con le funzioni aggregate

• La clausola va inserita dopo la clausola WHERE

Sintassi: [ GROUP BY <ListaAttributiDiRaggruppamento> ]

Esempio:

Fornire il massimo dei premi di produzione degli impiegati di ciascun Reparto.

SELECT IdReparto, MAX(premioproduzione) as maxpremio FROM impiegati GROUP BY IdReparto;

IdReparto maxpremio

1 1000

2 5000

3 8000

Aumentare la leggibilità

• Se non si vuole idreparto, ma il nome del reparto?• Occorre un join

SELECT rep.nome as reparto, MAX(PremioProduzione) asmaxpremio

FROM impiegati as imp, reparti as rep

where imp.IdReparto = rep.idreparto

group by (rep.nome) ;

reparto maxpremio

Amministrazione 1000

Direzione 8000

Sviluppo 5000

Attributi sulla select e group by• NOTA: a parte gli attributi che compaiono nelle funzioni aggregate, le

colonne usate per il raggruppamento (in GROUP BY) sono le sole che possono apparire anche nella clausola SELECT e ORDER BY.

• Esempio:SELECT imp.nome, rep.nome as reparto, MAX(PremioProduzione) as maxpremioFROM impiegati as imp, reparti as repwhere imp.IdReparto = rep.idrepartogroup by (rep.nome) ;

• Si vuole avere in tal modo il nome dell’impiegato che ottiene quel max.• ERRORE: solo ciò che compare in group by può comparire nella lista di selezione• Attenzione a MYSQL

• Ciò anche per tuple senza group by. Le funzioni di aggregazione senza la clausola GROUP BY non possono essere usate insieme ad attributi normali

select nome, max(StipendioAnnuale)from impiegati;

Group by e null

• Il raggruppamento considera come gruppo anche le tuple che hanno sull’attributo di raggruppamento il valore null

select premioproduzione, max(StipendioAnnuale) asmaxsti

from impiegati

group by PremioProduzione;

premioproduzione maxsri

NULL 25000

1000 25000

2000 25000

5000 40000

6000 65000

8000 60000

Raggruppamento di Tuple - Selezione

• Alle volte devono essere considerati sottoinsiemi che verificano alcune condizioni.

• Se le condizioni sono verificabili a livello di tupla, si usa la clausola where.

• Se le condizioni da soddisfare sono di tipo aggregato, occorre la (nuova) clausola having

esempi• Determinare le spese per premio di produzione di ciascun

reparto, escludendo il reparto 1 (amministrazione)• Si può raggruppare, ma occorre escludere le tuple di impiegati il cui

campo idreparto vale 1select IdReparto, sum(PremioProduzione) as spesepremifrom impiegatiwhere IdReparto <> 1group by IdReparto;

• Determinare i reparti che spendono meno di 8000 in premi produzione

• Il criterio è su sum(PremioProduzione)• La clausola where non può contenere funzioni di aggregazione

(viene valutata prima del raggruppamento)• Si introduce la clausola HAVING, che può contenere funzioni di

aggregazione

idReparto spesepremi

2 7000

3 14000

Clausola HAVING• Sintassi: HAVING <espressione>

• Deve essere scritta dopo la GROUP BY (e dunque dopo la where)

• Può contenere condizioni su funzioni di aggregazione

• Permette di specificare condizioni sui gruppi di tuple generati dalla clausola GROUP BY in modo da selezionare solo i gruppi che rispettano certi criteri. (Nota, la condizione è una condizione sul gruppo!)

Esempio:

Determinare i reparti che spendono meno di 8000 in premi produzione

SELECT IdReparto, SUM(PremioProduzione) AS SpesePremi

FROM impiegati

GROUP BY IdReparto

HAVING SUM(PremioProduzione) < 8000;idReparto spesepremi

1 1000

2 7000

Struttura di una interrogazione

select ListaAttributioEspressioni

from ListaTabelle

[where condizioni (semplici)]

[group by ListaAttributidiRaggruppamento]

[having Condizioni (aggregate)]

[order by ListaAttributiDiOradinamento]

Il linguaggio SQL:join

[email protected]

Join• L’operatore di Join permette di effettuare la ricerca di informazioni

mettendo in relazione i dati presenti in tabelle diverse

• L’algebra razionale definisce una serie di varianti dell’operatore derivato JOIN: Join Naturale, Join Esterno, Equi-Join, Theta-Join

• Nonostante i vari tipi di operazioni di JOIN sono stati standardizzati in SQL nelle varie versioni, non tutti i DBMS supportano esplicitamente le operazioni di JOIN tramite una singola operazione atomica con costrutti definiti ad hoc

• Essendo comunque l’operazione di JOIN un’operazione derivata questa può essere effettuata tramite una composizione delle operazioni di base

• Le varie operazioni di Join possono essere implementate con particolari configurazioni delle interrogazioni di base:

• Nella clausola FROM si genera il prodotto Cartesiano delle tabelle coinvolte

• Nella clausola WHERE si applicano i predicati dell’operazione di JOIN

• Nella clausola SELECT si estraggono le colonne

Sintassi generica per il join

• Con l’operatore di join, si separano le condizioni di join (che, di norma, vanno nell’operatore), dalle altre condizioni di selezione delle tuple, che vanno nel where o nelle altre clausole

• Sintassi semplificata e non esaustiva

select ListaAttributioEspressioni

from tabella [[as] alias]

{[tipojoin] join tabella [[as] alias] on condJoin}

[where altracondizione]

Join Naturale/Natural Join

• Sintassi:

from tabella [[as] alias] natural join tabella [[as] alias]

• Il Join naturale presuppone che alcuni degli attributi delle due tabelle abbiano lo stesso nome e dominio coerente

• Si realizza imponendo nella WHERE le condizioni di uguaglianza tra tutti gli attributi che hanno lo stesso nome tra le due tabelle

• Lo schema è l’unione degli schemi: gli attributi ripetuti non sono replicati

esempi

• Per il db sul fondo, indicare nome e cognome del guidatore e modello di macchina guidata

select nome, cognome, modellofrom automobile natural join guidatore;

Equivalente senza join:select nome, cognome, modellofrom automobile as auto, guidatore as guidWhere auto.nropatente=guid.nropatente;

targa marca modello nropatente

BJ 747 XX Lancia Delta PZ 1012436B

BB 421 JJ Fiat Uno MI 2020030U

AB 574 WW Fiat Punto VR 2030020Y

AA 652 FF Fiat Brava VR 2030020Y

automobile

Nome cognome nropatente

Marco Neri AP 4544442R

Carlo Bianchi PZ 1012436B

Mario Rossi VR 2030020Y

guidatori

Nome cognome nmodelli

Carlo Bianchi Delta

Mario Rossi Brava

Mario Rossi Punto

DB di esempioConsideriamo i seguenti schemi di una base di dati relazionale:


• REPARTI(IdReparto, Nome, Indirizzo, Città)


IdReparto Nome Indirizzo Città











Rischi del join naturale• Esempio: mostrare le informazioni relative agli impiegati ed ai

dipartimenti associati:

SELECT * FROM impiegati AS imp, reparti AS rep WHERE imp.IdReparto = rep.IdReparto;

• La sintassi del metodo diretto è la seguente:

SELECT * FROM impiegati NATURAL JOIN reparti;

• Cosa restituisce il natural join?

• Le due query sono equivalenti?

• Non è detto che il fatto che i nomi siano uguali esprima una relazione fra tabelle.

• Per tale motivo, l’uso del Natural Join non è consigliabile o deve essere fatto con accortezza

• Gli attributi con lo stesso nome devono essere quelli che legano le tabelle

Join Interno/Inner Join

• In alcune implementazioni il Natural Join non è direttamente supportato in quanto la comparazione automatica degli attributi (senza controllo da parte dell’utente) può produrre risultati a volte indesiderati

• Solitamente è invece supportata un costrutto join generico chiamato inner join (in contrapposizione al join esterno o outer join) attraverso il quale possono essere implementate tutte le varianti (theta-join, equi-join e anche il natural join)


• L’inner join è un join generico interno basato sull’applicazione di una selezione sulla tabella frutto di un prodotto cartesiano

• La condizione di selezione non deve essere necessariamente d’ugualglianza

• In algebra relazionale è l’equivalente del theta-join, se la condizione della selezione è d’uguaglianza allora l’inner join si riduce all’equi-join

• E’ equivalente a aggiungere una WHERE con le condizioni della selezione da applicare

Esempio:Selezionare gli impiegati che lavorano a Milano:

SELECT imp.*

FROM impiegati imp, reparti rep

WHERE imp.IdReparto = rep.IdReparto AND citta = ‘Milano’;


• La sintassi esplicita per l’Inner Join è la seguente:

SELECT * FROM t1 INNER JOIN t2 ON condizioneEsempio:

Selezionare di ogni impiegato il Nome, Cognome e la città in cui lavora:

SELECT imp.Nome, imp.Cognome, rep.Citta


WHERE imp.IdReparto = rep.IdReparto;

è equivalente a:


FROM impiegati AS imp INNER JOIN reparti AS rep ON imp.IdReparto = rep.IdReparto;

NOTA: il costrutto INNER JOIN può essere riferito direttamente usando JOIN


FROM impiegati AS imp JOIN reparti [AS] rep ON imp.IdReparto = rep.IdReparto;

Equi-Join• Nel caso la condizione sia di uguaglianza la sintassi

esplicita può essere :

SELECT * FROM t1 INNER JOIN t2 USING(attributi)Esempio:

Selezionare di ogni impiegato il Nome, Cognome e la città in cui lavora:



WHERE imp.IdReparto = rep.IdReparto;

è equivalente a:


FROM impiegati INNER JOIN reparti USING(IdReparto)

oppure:


FROM impiegati JOIN reparti USING(IdReparto)

Non tutti i join sono equi join

• Esempio: trovare il nome dei reparti in cui non lavora Giulia Rossi.

select rep.nome

from impiegati as imp join reparti as rep on imp.IdReparto<> rep.IdReparto

where imp.cognome = 'rossi' and imp.nome = 'giulia';

Nome

Amministrazione

Direzione

Join Esterno/Outer Join

• Il join esterno (outer join) prevede che tutte le tuple diano un contributo al risultato, estendendo con valori nulli le tuple che non hanno una corrispondenza.

• La outer-join può mantenere i valori per cui non esiste corrispondenza per una, per l’altra o per entrambe le tabelle; i tre casi vengono rispettivamente definiti outer-join sinistra, outer-join destra, outer-join completa.

• Il join sinistro estende le tuple del primo operando

• Il join destro estende le tuple del secondo operando

• Il join completo le estende tutte

Join Esterno/Outer Join

• In SQL l'operatore di OUTER JOIN può essere applicato usando la seguente sintassi :

• {LEFT|RIGHT|FULL} [OUTER] JOIN + ON <predicato>

• { LEFT|RIG HT|FULL} [OUTER] JOIN + USING <colonne>

• La parola outer è opzionale

• Il FULL non è supportato da mysql

esempio

• Estrarre tutti i guidatori con le auto associate, mantenendo anche l’informazione per i guidatori senza auto:

select *

from guidatore left join automobile using (nropatente);

nropatente, nome cognome targa marca modello

VR 2030020Y Mario Rossi AA 652 FF Fiat Brava

VR 2030020Y Mario Rossi AB 574 WW Fiat Punto

PZ 1012436B Carlo Bianchi BJ 747 XX Lancia Delta

AP 4544442R Marco Neri Null Null Null

Self Join• E’ un inner join di una tabella con se stessa

• Occorre rinominare le tabelle in FROMEsempio:

• trovare i colleghi di Giulia Rossi (cioè quelli che lavorano nello stesso reparto)

select imp1.nome, imp1.cognomefrom impiegati imp1 join impiegati imp2 on (imp1.idreparto = imp2.idreparto)where imp2.nome='Giulia' and imp2.cognome = 'Rossi' and imp1.matricola <> imp2.matricola;

• I due alias sono in realtà due variabili distinte, inizializzate (non per riferimento) alla stessa tabella.

• Il join è dunque effettivamente un join fra due tabelle distinte

Nome cognome

Mario Rossi

Il linguaggio SQL:operatori insiemistici

e subquery

[email protected]

Operatori InsiemisticiUNION, INTERSECT, EXCEPT

• L’istruzione SELECT non permette di eseguire unione, intersezione e differenza di tabelle

• si può invece utilizzarli per combinare in modo opportuno i risultati di due istruzioni SELECT

UNION (unione)

• L’operatore UNION realizza l’operazione di unione definita nell’algebra relazionale.

• Gli operandi sono due tabelle risultanti da comandi SELECT e restituisce come risultato una terza tabella che contiene tutte le righe della prima tabella e tutte le righe della seconda tabella.

• Lo schema è quello definito dalla prima select

• Per poter avere l’ unione occorre che gli schemi delle due relazioni (tabelle) siano “compatibili”

• stesso numero di argomenti

• tipo degli argomenti corrispondenti dovrebbero essere uguali o almeno compatibili• Il tipo risultate tiene conto di tutti i valori, cosi se si uniscono int e varchar, si ottengono varchar.

• non è richiesto che abbiano gli stessi nomi

NOTA: Il risultato è di default privo di duplicati (la select di default invece lascia I duplicati). Per ottenerli occorre aggiungere l'opzione ALL.

sintassi

selectSQL

{<union|intersect|except>[all]

selectSQL}

esempi• Recuperare i nomi degli impiegati e dei reparti

insieme con i relativi codiciSELECT IdReparto, Nome FROM impiegati UNION ALL SELECT IdReparto, NomeReparto FROM reparti;

• Esempio: trovare il cognome degli impiegati che sono dirigenti o segretari

select cognome

from impiegati

where Mansione = 'dirigente‘

union

select cognome

from impiegati

where Mansione = 'segretario';

select cognome

from impiegati

where Mansione = 'dirigente‘

union ALL

select cognome

from impiegati

where Mansione = 'segretario';

Cognome

Verdi

Neri

Bianchi

Bianchi

Cognome

Verdi

Neri

Bianchi

Operatori InsiemisticiINTERSECT (intersezione)

➢ L’operatore INTERSECT realizza l’operazione di intersezione definita nell’algebra relazionale.

➢ Gli operandi sono due tabelle risultanti da comandi SELECT e restituisce come risultato una terza tabella che contiene tutte le righe che compaiono sia nella prima tabella che nella seconda.

NOTA: Il risultato è di default privo di duplicati. Per ottenerli occorre aggiungere l'opzione ALL.

EXCEPT (differenza)

• L’operatore EXCEPT realizza l’operazione di differenza definita nell’algebra relazionale.

• Gli operandi sono due tabelle risultanti da comandi SELECT e restituisce come risultato una terza tabella che contiene tutte le righe della prima tabella che non si trovano nella seconda.

NOTA: Il risultato è di default privo di duplicati. Per ottenerli occorre aggiungere l'opzione ALL.

Subquery

• In SQL è possibile esprimere delle query che contengono al loro interno delle altre interrogazioni, dette subquery, o query innestate.

• Il risultato di una query effettuata su di un certo numero di relazioni è una relazione, Nel caso più semplice è il valore di uno specifico attributo

• con dovute accortezze, nella costruzione di uno statement SQL dove compare una relazione o una colonna si può far comparire il risultato di una select

• Nel caso di subquery il sistema prima esegue le queryinnestate e poi esegue quella esterna usando il risultato della query innestata.

• Nel caso in cui la subquery abbia delle dipendenze (cioe’ nella subquery compaiano degli attributi della query esterna) questa viene rieseguita per ogni tupla della query esterna (non visto)

Subquery in WHERE – subqueryscalare• Le subquery possono essere usate nella clausola WHERE per

esprimere delle condizioni di confronto complesse

• Sintassi generica:• non_subquery_operand comparison_operator (subquery)

• Il caso piu’ semplice e’ quello in cui la subquery restituisce un valore singolo (una tabella con un solo attributo e una sola tupla)

• Questa puo’ essere usata come un valore scalare ad esempio per verificare una condizione

Esempio:

Trovare Nome e Cognome degli impiegati che lavorano nel reparto di Marco

SELECT Nome, Cognome

FROM impiegati

WHERE IdReparto = (SELECT IdReparto FROM impiegati WHERE Nome = ‘Marco’);

DB di esempio (il solito)Consideriamo i seguenti schemi di una base di dati relazionale:


• REPARTI(IdReparto, Nomereparto, Indirizzo, Città)


IdReparto Nomereparto Indirizzo Città











Subquery e operatore IN• Nel caso in cui la subquery restituisca una tabella con piu’ di una tupla

questa puo’ essere usata nella clausola WHERE insieme con l’operatore IN – NOT IN

• La clausola IN usata con una subquery serve per verificare se il valore di un attributo per una determinata tupla è presente nel risultato della subquery. In tal caso il valore dell’espressione è vero e la tupla viene selezionata

Esempio:

Trovare le matricole degli impiegati dei reparti con sede a Milano.

SELECT Matricola

FROM impiegati

WHERE IdReparto IN (SELECT IdReparto

FROM reparti

WHERE Citta = ‘Milano’);

La clausola WHERE equivale a:

• WHERE IdReparto IN (1,3);

La subquery

restituisce

l’insieme di reparti

(1, 3)

Subquery e operatore NOT IN

• Esempio: Trovare matricola e cognome degli impiegati che non lavorano a milano

- SELECT Matricola, cognome

- FROM impiegati

- WHERE IdReparto NOT IN (SELECT IdReparto

- FROM reparti

- WHERE Citta = 'Milano');

Subquery e operatori ANY e ALL (confronto quantificato)• Gli operatori di confronto =, <, >… si possono usare solo se la subquery

restituisce non più di una tupla.

• Se la subquery restituisce più di una tupla si devono usare gli operatori:• <op> ANY: <op> vale per almeno uno dei valori • <op> ALL: <op> vale per tutti i valori

• ANY: la tupla soddisfa la condizione se il confronto (con l’operatore specificato) tra il valore che l’attributo/i assume sulla tupla e almeno uno degli elementi restituiti dall’ interrogazione nidificata risulta VERO.

• ALL: la tupla soddisfa la condizione se il confronto (con l’operatore specificato) tra il valore che l’attributo/i assume sulla tupla e ciascuno degli elementi restituiti dall’ interrogazione nidificata risulta VERO.

NOTE:

▪ <op> è un operatore di confronto

▪ La forma = ANY equivale a IN

▪ La forma <> ALL equivale a NOT IN

Subquery e operatori ANY e ALL –ES1• Selezionare I nomi dei reparti che in cui esiste

almeno un impiegato con stipendio maggiore di 30000

SELECT Nome

FROM reparti

WHERE IdReparto =ANY

(SELECT IdReparto FROM impiegati WHERE StipendioAnnuale > 30000)

Subquery e operatori ANY e ALL ES2

• Selezionare tutti gli impiegati che prendono uno stipendio annuale maggiore di tutti I lavoratori del reparto Sviluppo

SELECT Nome, cognome

FROM impiegati

WHERE StipendioAnnuale >ALL

(SELECT StipendioAnnuale FROM impiegati WHERE IdReparto = 2)

In alternativa si può usare il max


FROM impiegati

WHERE StipendioAnnuale >

(SELECT max(StipendioAnnuale)

FROM impiegati

WHERE IdReparto = 2)

Subquery e operatori ANY e ALL ES2 -cont

• Si può innestare anche più di una query.

• La soluzione dell’esercizio precedente (corretta) è:


FROM impiegati

WHERE StipendioAnnuale >ALL (SELECT StipendioAnnuale

FROM impiegati

WHERE IdReparto =

(select idrepartofrom reparti

where nome='sviluppo'));

Altro esempio

• Nomi dei reparti in cui lavorano persone di cognome rossi

select nome

from reparti

where IdReparto = any (select idrepartofrom impiegati

where cognome = 'rossi');

• Nomi dei reparti in cui non lavorano persone di cognome rossi

select nome

from reparti

where IdReparto not in (select idrepartofrom impiegati

where cognome = 'rossi');

INTERSECT/EXCEPTSelezionare i cognomi dei proprietari che sono anche nomi (di qualche proprietario)

SELECT cognomeFROM proprietariINTERSECTSELECT nomeFROM proprietari

Selezionare i nomi dei proprietari che non sono anche cognomi (di qualche proprietario)

SELECT nomeFROM proprietariEXCEPTSELECT cognomeFROM proprietari

INTERSECT/EXCEPT in MySQL

• MySQL non implementa le operazioniINTERSECT/EXCEPT

• Queste operazioni possono essere comunqueimplementate usando IN o NOT IN

Selezionare i cognomi dei proprietari che sono anche nomi (di qualche proprietario)

SELECT cognomeFROM proprietariWHERE cognome IN (SELECT nome

FROM proprietari)

Selezionare i nomi dei proprietari che non sono anche cognomi (di qualche proprietario)

SELECT nomeFROM proprietariWHERE nome NOT IN (SELECT cognome

FROM proprietari)

Esempio

Subquery in FROM

• Le subquery possono essere usate anche come argomento dellaclausola FROM

• In questo caso il risultato della subquery viene usato come tabelladalla quale vengono presi i dati per la query esterna

• E’ obbligatorio in questo caso l’uso dell’alias per dare un nomealla tabella risultante subquery

Esempio:

Trovare il Nome degli impiegati che hanno un premio di produzione piu’ alto di 3000 e il cognome ‘Rossi’

SELECT Nome

FROM (SELECT * FROM impiegati WHERE PremioProduzione > 3000)

AS impiegatipremioalto

WHERE Cognome = 'Rossi';

Subquery in FROM

Esempio:

Trovare i nomi dei reparti e l’indirizzo, per gli impiegati che hanno un premio di produzione piu’ alto di 3000

SELECT DISTINCT rep.Nome, rep.indirizzo

FROM

(SELECT * FROM impiegati WHERE PremioProduzione > 3000) AS impiegatipremioalto

JOIN reparti using (idreparto);

Subquery e operatore Exist

• Mediante EXISTS (SELECT * …) è possibile verificare se il risultato di una subquery restituisce almeno una tupla

SELECT IdReparto

FROM Reparti

WHERE EXISTS (SELECT *

FROM Impiegati

WHERE Mansione = ‘Programmatore’);

• Facendo uso di NOT EXISTS il predicato è vero se la subquery non restituisce alcuna Tupla.

• la sintassi è <EXISTS | NOT EXISTS> subquery

• Se ESISTS, La clausola where seleziona la tupla se la subquery restituiscealmeno una tupla

esempio

SELECT IdReparto

FROM Reparti

WHERE EXISTS (SELECT *

FROM Impiegati

WHERE Mansione = ‘Programmatore’);

• Se vi è un impiegato ‘programmatore’, la clausola exists è sempre vera, dunque viene selezionata tutta la tabella Reparti, altrimenti non viene selezionata nessuna tupla

• Occorre un meccanismo per rendere più flessibile la clausola exists.

• Ciò avviene rendendo le condizioni della subquery dipendenti dalla tupla della query principale (query esterna)

• Si introduce un legame fra la query e la subquery (query correlate)• Si definisce una variabile (un alias) nella query esterna, che si utilizza nella

subquery

Subquery complesse

• Nella maggioranza dei casi le subquery sono eseguite prima di analizzare le righe dell’interrogazione esterna

• Il linguaggio SQL da la possibilità di inserire nelle queryannidate riferimenti alla query più esterna (es. il valore di un attributo specifico)

• In tal caso (query correlate), dato che il risultato della query interna dipende dai valori della tupla esterna considerata, questa viene eseguita nuovamente per ogni tupla della query esterna

Regole di visibilità delle variabili SQL• Una variabile (alias) definita nell’ambito di una

query, è usabile solo nella query o in tutte le subquery (anche nidificate) definite in essa

• Una variabile definita in una subquery non è visibile nella query principale, né in subquery dello stesso livello

esempio• Trovare il nome dei reparti in cui lavorano

‘sviluppatori’select rep.Nomefrom reparti repwhere exists (select *

from impiegati empwhere emp.Mansione = 'sviluppatore‘and emp.IdReparto=rep.IdReparto)

• Soluzione con joinselect reparti.nomefrom reparti join impiegati using (idreparto)where impiegati.mansione='sviluppatore';

esempio

• Trovare il nome dei reparti in cui non lavorano ‘sviluppatori’

select rep.Nome

from reparti rep

Where not exists (select *

from impiegati emp

where emp.Mansione = 'sviluppatore‘

and emp.IdReparto=rep.IdReparto)

Subquery complesse

Esempio:

Estrarre le persone che hanno degli omonimi nella tabella Persona(Nome, Cognome, CodiceFiscale)

SELECT * FROM Persona AS P

WHERE EXISTS ( SELECT * FROM Persona AS P1

WHERE P1.Nome = P.Nome AND

P1.Cognome = P.Cognome AND

P1.CodiceFiscale <> P.CodiceFiscale)

Il linguaggio SQL: Interrogazioni in SQLa080066/didattica/BD/6 - SQL interrogazioni.pdf ·...

Documents

Transcript of Il linguaggio SQL: Interrogazioni in SQLa080066/didattica/BD/6 - SQL interrogazioni.pdf ·...