SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione...

43
SQL Interrogazioni

Transcript of SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione...

Page 1: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

SQL

Interrogazioni

Page 2: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

2

Interrogazioni in SQL

Non esiste un SQL standard (vari dialetti)Formulazione di interrogazioni (query) è parte del Data Manipulation Language, DMLAnche usato nel Data Declaration Language, DDL (per esempio, per dichiarare vincoli di integrità)

Page 3: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

3

Interrogazioni in SQL

Paradigma dichiarativo: si specifica la descrizione dell’obiettivo e non il modo con cui ottenerlo A differenza dell’algebra relazionale,

che è procedurale

Page 4: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

4

Cenni sull’implementazione

Interrogazione in SQL viene tradotta in linguaggio proceduraleSulla traduzione si fanno ottimizzazioni algebriche (ecco a cosa serve l’algebra…) …… e non (queste ultime dipendono dalle strutture sottostanti al DBMS in questione)

Page 5: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

5

Sintassi

Esistono, in generale, più modi per effettuare un’interrogazione: scelte basate sulla leggibilità (più che sull’efficienza…)Struttura essenziale (introdurremo le variazioni di volta in volta):

select ListaAttributi (target list)

from ListaTabelle (clausola “from”)

[where Condizione] (clausola “where”)

Page 6: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

6

Notazione

Le parentesi angolari <,>: permettono di isolare un termine della sintassiLe parentesi quadre [,]: indicano che il termine all’interno è opzionale Può non comparire o comparire una

sola volta

Page 7: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

7

Notazione

Le parentesi graffe {,}: indicano che il termine racchiuso può non comparire o essere ripetuto un numero arbitrario di volteLe barre verticali |: indicano che deve essere scelto uno tra i termini separati dalle barre

Page 8: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

8

Significato dell’interrogazione

Si considera il prodotto cartesiano fra le tabelle della clausola “from”Si selezionano quelle tuple che soddisfano la condizione della clausola “where” (opzionale)Si danno in ouput i valori di quegli attributi che sono elencati nella target list (“select”)

Page 9: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

9

Tabella “Impiegato”

Nome Cognome

Dipart Ufficio

Stipendio

Città

Mario Rossi Amministr

10 15 Milano

Carlo Bianchi Prod 20 12 Torino

Giuseppe

Verdi Amministr

20 13 Roma

Franco Neri Distrib 16 15 Napoli

Carlo Rossi Direzione

14 27 Milano

Lorenzo Lanzi Direzione

7 21 Genova

Paola Burroni Ammistr 75 13 Venezia

Marco Franco Prod 20 14 Roma

Impiegato

Page 10: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

10

Interrogazione 1

select Stipendio as Salario

from Impiegato

where Cognome = ‘Rossi’

Salario

15

27

Page 11: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

11

Interrogazione 1

select Stipendio as Salario

from Impiegato

where Cognome = ‘Rossi’

Salario

15

27

alias

Page 12: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

12

Interrogazione 2

select *

from Impiegato

where Cognome = ‘Rossi’

Nome

Cognome

Dipart Ufficio

Stipendio

Città

Mario Rossi Amministr

10 15 Milano

Carlo Rossi Direzione

14 27 Milano

Page 13: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

13

Interrogazione 2

select *

from Impiegato

where Cognome = ‘Rossi’

tutti

Nome

Cognome

Dipart Ufficio

Stipendio

Città

Mario Rossi Amministr

10 15 Milano

Carlo Rossi Direzione

14 27 Milano

Page 14: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

14

Interrogazione 3

select Stipendio/12 as StipMens

from Impiegato

where Cognome = ‘Bianchi’

StipMens

1

Page 15: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

15

Interrogazione 3

select Stipendio/12 as StipMens

from Impiegato

where Cognome = ‘Bianchi’

espressioni

StipMens

1

Page 16: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

16

Join in SQL (primo modo)

Per formulare interrogazioni che coinvolgono più tabelle occorre fare un joinIn SQL un modo è: elencare le tabelle di interesse nella

“from” mettere nella “where” le condizioni

necessarie per mettere in relazione fra loro gli attributi di interesse

Page 17: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

17

Tabella “Dipartimento”

Nome Indirizzo Città

Amministr

Via Tito Livio 27

Milano

Prod P.le Lavater 3 Torino

Distrib Via Segre 9 Roma

Direzione

Via Tito Livio 27

Milano

Ricerca Via Morone 6 Milano

Dipartimento

Page 18: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

18

Interrogazione 4

Restituire nome e cognome degli impiegati e delle città in cui lavorano

select

Impiegato.Nome,Cognome,

Dipartimento.Città

from

Impiegato,Dipartimento

where

Dipart = Dipartimento.Nome

Page 19: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

19

Interrogazione 4

Restituire nome e cognome degli impiegati e delle città in cui lavorano

select

Impiegato.Nome,Cognome,

Dipartimento.Città

from

Impiegato,Dipartimento

where

Dipart = Dipartimento.Nome

La notazione punto ( ) serve per disambiguare

Suggerimento: “from”, “where”, target list

Page 20: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

20

Risultato interrogazione 4

Impiegato.Nome

Cognome

Dipartimento.Città

Mario Rossi Milano

Carlo Bianchi Torino

Giuseppe Verdi Milano

Franco Neri Roma

Carlo Rossi Milano

Lorenzo Lanzi Milano

Paola Burroni Milano

Marco Franco Torino

Page 21: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

21

Interrogazione 5

select I.Nome, Cognome, D.Cittàfrom Impiegato [as] I, Dipartimento [as] Dwhere Dipart = D.Nome

Impiegato [as] I : esempio di aliasing di una tabella

L’aliasing per le tabelle serve a abbreviare e disambiguare i riferimenti alle tabelle

Page 22: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

22

Sulla clausola “where”

Ammette come argomento un’espressione booleanaPredicati semplici combinati con not, and, or (not ha la precedenza, consigliato l’uso di parantesi(,))Ciascun predicato usa operatori: =, <>, <, >, <=, >=Confronto tra valori di attributi, costanti, espressioni

Page 23: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

23

Interrogazione 6

select Nome,Cognomefrom Impiegato

where Ufficio = 20 and Dipart =‘Amministr’

Nome Cognome

Giuseppe

Verdi

Page 24: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

24

Interrogazione 7 e 8

select Nome, Cognomefrom Impiegatowhere Dipart=‘Prod’ or Dipart=‘Amministr’

select Nome, Cognomefrom Impiegatowhere Cognome=‘Rossi’ and(Dipart=‘Prod’ or Dipart=‘Amministr’)

1

2

1

Nome

Mario

2

Nome Cognome

Mario Rossi

Carlo Bianchi

Paola Burroni

Marco Franco

Giuseppe

Verdi

Page 25: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

25

Operatore like

_ = carattere arbitrario% = stringa di lunghezza arbitraria (anche 0) di caratteri arbitrariEsempi: like ab%ba_ = tutte le stringhe che

cominciano con “ab” e che hanno “ba” come coppia di caratteri prima dell’ultima posizione (es. abjjhhdhdbak,abbap)

Page 26: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

26

Interrogazione 9

select *from Impiegatowhere Cognome like ‘_o%i’ or Cognome like ‘_u%i’

Nome

Cognome

Dipart Ufficio

Stipendio

Città

Mario Rossi Amministr

10 45 Milano

Carlo Rossi Direzioni 14 80 Milano

Paola Burroni Amministr

75 40 Venezia

Page 27: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

27

Gestione dei valori nulliAttributo con valore nullo = non applicabile a una certa tupla, o valore sconosciuto, o non si sa nullaSQL offre il predicato “is null”:

Attributo is [not] null

Page 28: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

28

Gestione dei valori nulliStipendio>13: cosa succede se l’attributo Stipendio è nullo? Scelte:

Logica a 2 valori e controllo esplicito. Per esempio:

(Stipendio > 13) or (Stipendio <= 13) or (Stipendio is null)

Usare un terzo valore di verità unknown: soluzione che crea problemi nei casi complessi (valutazione “globale” delle formule logiche)

Page 29: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

29

Uso delle variabili di alias

Non solo per disambiguare la notazioneCi sono casi in cui una stessa tabella serve più di una voltaCaso speciale: quando si deve confrontare una tabella con se stessa

(il modello relazionale è relation-oriented, non tuple-oriented )

Page 30: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

30

Interrogazione 10• Estrarre nome e cognome degli impiegati che hanno lo stesso cognome (ma nome diverso) di impiegati che lavorano nel dipartimento Produzione

Page 31: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

31

Interrogazione 10• Estrarre nome e cognome degli impiegati che hanno lo stesso cognome (ma nome diverso) di impiegati che lavorano nel dipartimento Produzione

select I1.Cognome, I1.Nomefrom Impiegato I1, Impiegato I2where I1.Cognome = I2.Cognome and I1.Nome <> I2.Nome and I2.Dipart = ‘Prod’

Page 32: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

32

Interrogazione 10• Estrarre nome e cognome degli impiegati che hanno lo stesso cognome (ma nome diverso) di impiegati che lavorano nel dipartimento Produzione

select I1.Cognome, I1.Nomefrom Impiegato I1, Impiegato I2where I1.Cognome = I2.Cognome and I1.Nome <> I2.Nome and I2.Dipart = ‘Prod’

per evitare output di tupla con se stessa

I2 usata per trovare tuple assoc. a ‘Prod’

Page 33: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

33

Interrogazione 11

• Estrarre il nome e lo stipendio dei capi degliimpiegati che guadagnano più dei loro capi,date:

Impiegati(Matricola, Nome, Età, Stipendio)Supervisione(Capo, Impiegato)

dove Capo e Impiegato sono chiavi esterne diImpiegati (e.g., sono dei numeri di matricola)

Page 34: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

34

Interrogazione 11 (sol.)select

I1.Nome, I1.Stipendiofrom

Impiegato I1, Impiegato I2, Supervisione

where I1.Matricola = Capo

and I2.Matricola = Impiegato and I2.Stipendio > I1.Stipendio

Page 35: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

35

Interrogazione 11 (sol.)select

I1.Nome, I1.Stipendiofrom

Impiegato I1, Impiegato I2, Supervisione

where I1.Matricola = Capo

and I2.Matricola = Impiegato and I2.Stipendio > I1.Stipendio

I1 per i capi,I2 per gli impiegati

Page 36: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

36

Interpretazione algebrica delle interrogazioni SQL

select T1.Attrib_11,…,Th.Attrib_hmfrom Tabella1 as T1,…,Tabellan as Tnwhere condizione

T1.Attrib_11,…,Th.Attrib_hm(condizione(Tabella1 x … x Tabellan))

Per semplicità, omettiamo le ridenominazioni che ci permettono di considerare tutti i join come prodotto cartesiano

Page 37: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

37

Algebra->calcolo, SQL->linguaggio

SQL gestisce i duplicati (select e select distinct), algebra no (basata su insiemi)select *

from Impiegati

ordered by [asc|desc] Matricola

Operatori aggregatiInterrogazioni nidificate

per ordinare le tuple

Page 38: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

38

Operatori aggregati

Algebra relazionale: tutte le condizioni sono valutate su una tupla alla volta, in modo indipendenteSQL offre degli operatori che lavorano su più di una tupla alla volta:

count,sum,max,min,avg

Page 39: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

39

Interrogazione 12select count(*)from Impiegatowhere Dipart = ‘Prod’

count(<*|[distinct|all]ListaAttributi>)

valori diversi tra loro

non null

Page 40: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

40

Interrogazioni 13, 14

Numero di stipendi diversi

select count(distinct Stipendio)from Impiegato

Numero di righe che hanno nome non nullo

select count(all Nome)from Impiegato

Gli operatori aggregati si applicano sulle tuple selezionatedalla clausola “where” (se c’è)

count(<*|[distinct|all]ListaAttributi>)

Page 41: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

41

sum, max, min, avg

Prendono solo espressioni rappresentanti valori numerici o intervalli di tempodistinct e all stesso significato di primaAltri operatori a seconda delle versioni di SQL (solitamente operatori statistici)

Page 42: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

42

Interrogazioni 15, 16Somma stipendi di Amministrazione

select sum(Stipendio)from Impiegatowhere Dipart = ‘Amministr’

Stipendi min, max, medio degli Impiegati

select min(Stipendio),max(Stipendio),avg(Stipendio)from Impiegato

Page 43: SQL Interrogazioni. 2 Interrogazioni in SQL Non esiste un SQL standard (vari dialetti) Formulazione di interrogazioni (query) è parte del Data Manipulation.

43

Interrogazione 17

Max stipendio tra impiegati che lavorano in dip a Milano

select max(Stipendio)from Impiegato, Dipartimentowhere Dipart = Dipartimento.Nome and Dipartimento.Citta = ‘Milano’

Equi-join