Interrogazioni Semplici

1. Interrogazioni Semplici Le operazioni di interrogazione vengono specificate per mezzo dell’istruzione select selectListaAttributi fromListaTabelle [whereCondizione]

2. selectListaAttributifromListaTabelle[whereCondizione] Seleziona tra le righe che appartengono al prodotto cartesiano delle tabelle elencate nella clausola from quelle che soddisfano la clausola where, mostrando gli attributi indicati nella clausola select

3. impiegato +----------+---------+-----------------+---------+-----------+---------+ | nome | cognome | dipart | ufficio | stipendio | città | +----------+---------+-----------------+---------+-----------+---------+ | Carlo | Bianchi | Produzione | 20 | 36 | Torino | | Carlo | Rossi | Direzione | 14 | 80 | Milano | | Franco | Neri | Distribuzione | 16 | 45 | Napoli | | Giuseppe | Verdi | Amministrazione | 20 | 40 | Roma | | Lorenzo | Galli | Direzione | 7 | 73 | Genova | | Marco | Franco | Produzione | 20 | 46 | Roma | | Mario | Rossi | Amministrazione | 10 | 45 | Milano | | Paola | Rosati | Amministrazione | 75 | 40 | Venezia | +----------+---------+-----------------+---------+-----------+---------+ Estrarre lo stipendio degli impiegati di cognome “Rossi” select Stipendio from Impiegato where Cognome = 'Rossi'; +-----------+ | Stipendio | +-----------+ | 80 | | 45 | +-----------+

4. select * from Impiegato where Cognome = 'Rossi' +-------+---------+-----------------+---------+-----------+--------+ | nome | cognome | dipart | ufficio | stipendio | città | +-------+---------+-----------------+---------+-----------+--------+ | Carlo | Rossi | Direzione | 14 | 80 | Milano | | Mario | Rossi | Amministrazione | 10 | 45 | Milano | +-------+---------+-----------------+---------+-----------+--------+ Come argomento della clausola select può comparire il carattere * (asterisco) che rappresenta la selezione di tutti gli attributi delle tabelle elencate nella clausola from

5. Nella clausola select possono comparire generiche espressioni select Stipendio/12 as StipendioMensile from Impiegato where Cognome = 'Bianchi'; +------------------+ | StipendioMensile | +------------------+ | 3.0000 | +------------------+ Si può notare che ogni colonna può essere rinominata con l’Alias (as)

6. Quando l’interrogazione coinvolge più tabelle queste si pongono come argomento della clausola from Estrarre i nomi degli impiegati e le città dove lavorano select Impiegato.Nome, Cognome, Dipartimento.Citta from Impiegato, Dipartimento where Impiegato.Dipart=Dipartimento.Nome; +----------+---------+--------+ | Nome | Cognome | Citta | +----------+---------+--------+ | Giuseppe | Verdi | Milano | | Mario | Rossi | Milano | | Paola | Rosati | Milano | | Carlo | Rossi | Milano | | Lorenzo | Galli | Milano | | Franco | Neri | Roma | | Carlo | Bianchi | Torino | | Marco | Franco | Torino | +----------+---------+--------+ dipartimento +-----------------+-----------------+--------+ | nome | Indirizzo | Città | +-----------------+-----------------+--------+ | Amministrazione | Via Tito, 27 | Milano | | Direzione | Via Tito, 27 | Milano | | Distribuzione | Via segre, 9 | Roma | | Produzione | Piazza Terzi, 3 | Torino | | Ricerca | Via Larga, 12 | Milano | +-----------------+-----------------+--------+ impiegato +----------+---------+-----------------+---------+-----------+---------+ | nome | cognome | dipart | ufficio | stipendio | città | +----------+---------+-----------------+---------+-----------+---------+ | Carlo | Bianchi | Produzione | 20 | 36 | Torino | | Carlo | Rossi | Direzione | 14 | 80 | Milano | | Franco | Neri | Distribuzione | 16 | 45 | Napoli | | Giuseppe | Verdi | Amministrazione | 20 | 40 | Roma | | Lorenzo | Galli | Direzione | 7 | 73 | Genova | | Marco | Franco | Produzione | 20 | 46 | Roma | | Mario | Rossi | Amministrazione | 10 | 45 | Milano | | Paola | Rosati | Amministrazione | 75 | 40 | Venezia | +----------+---------+-----------------+---------+-----------+---------+

7. Estrarre i nomi degli impiegati e le città dove lavoranoselect Impiegato.Nome, Cognome, Dipartimento.Cittafrom Impiegato, Dipartimentowhere Impiegato.Dipart=Dipartimento.Nome; In questa interrogazione si noti l’uso dell’operatore punto per identificare le tabelle da cui vengono estratti gli attributi. E’ obbligatorio specificare il nome della tabelle quando sono presenti attributi con lo stesso nome. Notare che la condizione della clausola where esprime in modo esplicito un join, cioè il legame tra due tabelle.

8. La clausola where ammette come operando una espressione booleana costruita combinando predicati semplici con gli operatori and, or, not.Operatori : =, <>,<,>,<=,<=.L’operatore not ha precedenza massima, mentre or e and non hanno tra loro relazione di precedenza, quindi conviene esplicitare l’ordine di valutazione mediante le parentesi.

9. Estrarre il nome e il cognome degli impiegati che lavorano nell’ufficio 20 del dipartimento “Amministrazione” select Nome, Cognome from Impiegato where Ufficio=20 and Dipart='Amministrazione'; +----------+---------+ | Nome | Cognome | +----------+---------+ | Giuseppe | Verdi | +----------+---------+

10. Estrarre i nomi e i cognomi degli impiegati che lavorano nel dipartimento amministrazione o nel dipartimento produzione mysql> select Nome, Cognome -> from Impiegato -> where Dipart='Amministrazione' or Dipart = 'Produzione'; +----------+---------+ | Nome | Cognome | +----------+---------+ | Carlo | Bianchi | | Giuseppe | Verdi | | Marco | Franco | | Mario | Rossi | | Paola | Rosati | +----------+---------+

11. Estrarre i nomi degli impiegati di cognome “Rossi” che lavorano nei dipartimenti Amministrazione o Produzione: select Nome from Impiegato where Cognome='Rossi' and (Dipart='Amministrazione' or Dipart='Produzione'); +-------+ | Nome | +-------+ | Mario | +-------+

12. Oltre ai normali predicati di confronto SQL mette a disposizione un operatore like per il confronto di stringhe, che permette confronti con stringhe in cui compaiono i caratteri _ (trattino sottolineato) e %(percentuale)._ un carattere arbitrario% una stringa di un numero arbitrario di caratteri arbitrari mysql> select * -> from Impiegato -> where Cognome like '_o%i'; +-------+---------+-----------------+---------+-----------+---------+ | nome | cognome | dipart | ufficio | stipendio | citta | +-------+---------+-----------------+---------+-----------+---------+ | Carlo | Rossi | Direzione | 14 | 80 | Milano | | Mario | Rossi | Amministrazione | 10 | 45 | Milano | | Paola | Rosati | Amministrazione | 75 | 40 | Venezia | +-------+---------+-----------------+---------+-----------+---------+

13. Gestione dei valori nulliSQL mette a disposizione due predicati:is nullis not nullda utilizzare nella clausola where

14. Gestione dei duplicati.Un’interrogazione può produrre dei duplicati select città from impiegato; +---------+ | città | +---------+ | Torino | | Milano | | Napoli | | Roma | | Genova | | Roma | | Milano | | Venezia | +---------+ Per eliminarli si può usare la parola chiave distinct select distinct città from impiegato; +---------+ | città | +---------+ | Torino | | Milano | | Napoli | | Roma | | Genova | | Venezia | +---------+

15. Join I join permettono di distinguere le condizioni che rappresentano le condizioni di join e quelle che rappresentano le condizioni di selezione sulle righe. selectattributi fromtabelle jointabellaonCondizioneDiJoin whereAltraCondizione Estrarre i nomi degli impiegati e le città dove lavorano select Impiegato.Nome, Cognome, Dipartimento.Citta from Impiegato, Dipartimento where Dipart=Dipartimento.Nome; select Impiegato.Nome, Cognome, Dipartimento.Citta from Impiegato join Dipartimento on Dipart=Dipartimento.Nome

16. Il join visto precedentemente è il natural join. Con questo join il risultato è in generale un sottoinsieme delle righe di ciascuna tabella, quelle per le quali esiste una associazione tra le tabelle. Nel caso in cui si vogliano tutte le righe di una o di entrambe le tabelle si devono usare i left, right o full join Join

17. automobile +---------+--------+---------+------------+ | Targa | Marca | Modello | NroPatente | +---------+--------+---------+------------+ | AA652FF | Fiat | Brava | VR2030020Y | | AB574WW | Fiat | Punto | VR2030020Y | | BB421JJ | Fiat | Panda | MI2020030U | | BJ747XX | Lancia | Delta | PZ1012436B | +---------+--------+---------+------------+ Consideriamo le seguenti tabelle: guidatore +------------+-------+---------+ | NroPatente | Nome | Cognome | +------------+-------+---------+ | AP4544442R | Marco | Neri | | PZ1012436B | Carlo | Bianchi | | VR2030020Y | Mario | Rossi | +------------+-------+---------+ Estrarre i guidatori e le loro automobili, compresi i guidatori senza automobili select Nome,Cognome,guidatore.NroPatente,Targa,Modello from Guidatore left join automobile on Guidatore.NroPatente=Automobile.NroPatente; +-------+---------+------------+---------+---------+ | Nome | Cognome | NroPatente | Targa | Modello | +-------+---------+------------+---------+---------+ | Marco | Neri | AP4544442R | | | | Carlo | Bianchi | PZ1012436B | BJ747XX | Delta | | Mario | Rossi | VR2030020Y | AA652FF | Brava | | Mario | Rossi | VR2030020Y | AB574WW | Punto | +-------+---------+------------+---------+---------+ Estrarre i guidatori e le loro automobili, compresi le automobili senza guidatore select Nome,Cognome,guidatore.NroPatente,Targa,Modello from Guidatore right join automobile on Guidatore.NroPatente=Automobile.NroPatente; +-------+---------+------------+---------+---------+ | Nome | Cognome | NroPatente | Targa | Modello | +-------+---------+------------+---------+---------+ | Mario | Rossi | VR2030020Y | AA652FF | Brava | | Mario | Rossi | VR2030020Y | AB574WW | Punto | | | | | BB421JJ | Panda | | Carlo | Bianchi | PZ1012436B | BJ747XX | Delta | +-------+---------+------------+---------+---------+

18. Utilità degli alias Utilizzando gli alias è possibile fare riferimento a più esemplari della stessa tabella e questo ci permetterà di risolvere facilmente alcuni tipi di interrogazione. Ad esempio alla richiesta di estrarre gli impiegati che hanno lo stesso Cognome ma nome diverso select I1.Cognome, I1.Nome from impiegato as I1,impiegato as I2 where I1.Cognome=I2.Cognome and I1.Nome<>I2.Nome; +---------+-------+ | Cognome | Nome | +---------+-------+ | Rossi | Mario | | Rossi | Carlo | +---------+-------+

19. Ordinamento Per effettuare l’ordinamento su uno o più campi si utilizza la clausola order by order by AttrDiOrdinamento [asc|desc] {,AttrDiOrdinamento[asc|desc]} Estrarre le automobili ordinate in base alla marca (in modo discendente) e al modello: select * from automobile order by Marca desc, Modello; +---------+--------+---------+------------+ | Targa | Marca | Modello | NroPatente | +---------+--------+---------+------------+ | BJ747XX | Lancia | Delta | PZ1012436B | | AA652FF | Fiat | Brava | VR2030020Y | | BB421JJ | Fiat | Panda | MI2020030U | | AB574WW | Fiat | Punto | VR2030020Y | +---------+--------+---------+------------+

20. Operatori aggregati Se si devono valutare delle proprietà che non dipendono da una singola riga ma da insiemi di righe si deve ricorrere agli operatori aggregati. Ad esempio il numero degli impiegati del dipartimento produzione corrisponderà al numero di righe della relazione impiegato che hanno “Produzione” come del valore dell’attributo Dipart. Per ottenere il numero si usa l’operatore aggregato count select count(*) from impiegato where dipart='Produzione'; +----------+ | count(*) | +----------+ | 2 | +----------+

21. L’operatore count usa la seguente sintassi:count( * | [distinct|all] Attributo]* restituisce il numero di righedistinct restituisce il numero di valori diversi dell’attributoall (default) restituisce il numero di righe che possiedono valori diversi da nullEstrarre il numero di valori diversi dell’attributo stipendio:mysql> select count(distinct stipendio) -> from impiegato;+---------------------------+| count(distinct stipendio) |+---------------------------+| 6 |+---------------------------+Estrarre il numero di righe che possiedono un valore non nullo per l’attributo nome:mysql> select count(all nome) -> from impiegato;+-----------------+| count(all nome) |+-----------------+| 8 |+-----------------+

22. Gli operatori aggregati più usati sono: sum: restituisce la somma dei valori max e min: restituiscono rispettivamente il valore massimo e minimo avg: restituisce la media dei valori La loro sintassi è: (avg | sum | max | min) ([distinct | all] attributo) L’uso di distinct e all con min e max non ha alcun effetto sul risultato Estrarre gli stipendi minimo, massimo e medio degli impiegati: select min(stipendio),max(stipendio),avg(stipendio) from impiegato; +----------------+----------------+----------------+ | min(stipendio) | max(stipendio) | avg(stipendio) | +----------------+----------------+----------------+ | 36 | 80 | 50.6250 | +----------------+----------------+----------------+ Estrarre lo stipendio massimo tra gli impiegati che lavorano a Milanomysql> select max(stipendio) -> from impiegato -> join dipartimento on dipart=dipartimento.nome -> where dipartimento.Citta='Milano'; +----------------+ | max(stipendio) | +----------------+ | 80 | +----------------+

23. Interrogazioni con raggruppamento Molto spesso sorge l’esigenza di applicare l’operatore aggregato a sottoinsiemi di righe. Per poter operare in questo modo l’operatore aggregato, SQL mette a disposizione la clausola group by, che permette di suddividere le tabelle in sottoinsiemi.

24. Estrarre la somma degli impiegati che lavorano nello stesso dipartimento: select dipart,sum(stipendio) from impiegato group by dipart; +-----------------+-----------+ | dipart | stipendio | +-----------------+-----------+ | Produzione | 36 | | Direzione | 80 | | Distribuzione | 45 | | Amministrazione | 40 | SELEZIONE COMPLETA | Direzione | 73 | | Produzione | 46 | | Amministrazione | 45 | | Amministrazione | 40 | +-----------------+-----------+ +-----------------+-----------+ | dipart | stipendio | +-----------------+-----------+ | Amministrazione | 40 | | Amministrazione | 45 | | Amministrazione | 40 | | Direzione | 80 | RAGGRUPPAMNETO | Direzione | 73 | | Distribuzione | 45 | | Produzione | 36 | | Produzione | 46 | +-----------------+-----------+ +-----------------+----------------+ | dipart | sum(stipendio) | +-----------------+----------------+ | Amministrazione | 125 | | Direzione | 153 | RISULTATO | Distribuzione | 45 | | Produzione | 82 | +-----------------+----------------+

25. Estrarre i dipartimenti, il numero di impiegati che vi lavorano e la città in cui il dipartimento ha sede: select dipart,count(*),dipartimento.Citta from impiegato join dipartimento on impiegato.dipart=dipartimento.nome group by dipart,dipartimento.Citta; +-----------------+----------+--------+ | dipart | count(*) | Citta | +-----------------+----------+--------+ | Amministrazione | 3 | Milano | | Direzione | 2 | Milano | | Distribuzione | 1 | Roma | | Produzione | 2 | Torino | +-----------------+----------+--------+ select dipart,count(*),dipartimento.Citta from impiegato join dipartimento on impiegato.dipart=dipartimento.nome group by dipart; +-----------------+----------+--------+ | dipart | count(*) | Citta | +-----------------+----------+--------+ | Amministrazione | 3 | Milano | | Direzione | 2 | Milano | Alcuni motori sql non accettano questa forma, perché | Distribuzione | 1 | Roma | gli attributi della select devono essere un | Produzione | 2 | Torino | sottoinsieme degli attributi della group by +-----------------+----------+--------+

26. Predicati sui gruppi Se si devono applicare condizioni a gruppi aggregati sarà necessario usare la clausola having La clausola having descrive le condizioni che si devono applicare al termine dell’esecuzione di una interrogazione che fa uso della clausola groupby. Estrarre i dipartimenti che spendono più di 100 per gli stipendi: select dipart, sum(stipendio) from impiegato group by dipart having sum(stipendio)>100; +-----------------+----------------+ | dipart | sum(stipendio) | +-----------------+----------------+ | Amministrazione | 125 | | Direzione | 153 | +-----------------+----------------+

27. La sintassi permette anche la definizione di interrogazioni con la clausola having senza una corrispondente clausola groupby Estrarre i dipartimenti per cui la media degli stipendi degli impiegati che lavorano nell’ufficio 20 è superiore a 25 select dipart from impiegato where ufficio=20 group by dipart having avg(stipendio)>25; +-----------------+ | dipart | +-----------------+ | Amministrazione | | Produzione | +-----------------+

28. Interrogazioni di tipo insiemistico • Gli operatori sono: • union (unione) • intersect (intersezione) (non disponibile in mySql) • except (differenza) (non disponibile in mySql) La sintassi è la seguente: select {(union | intersect | except) [all] select} Gli operatori insiemistici per default eliminano i duplicati, nel caso si vogliano preservare sarà sufficiente utilizzare la parola chiave all

29. Estrarre i nomi e i cognomi eccetto quelli appartenenti al dipartimento amministrazione mantenendo i duplicati: select nome from impiegato where dipart<>'Amministrazione' union all select cognome from impiegato where dipart<>'Amministrazione'; +---------+ | nome | +---------+ | Carlo | | Carlo | | Franco | | Lorenzo | | Marco | | Bianchi | | Rossi | | Neri | | Galli | | Franco | +---------+ Estrarre i nomi e i cognomi in un’unica colonna select nome from impiegato union select cognome from impiegato; +----------+ | nome | +----------+ | Carlo | | Franco | | Giuseppe | | Lorenzo | | Marco | | Mario | | Paola | | Bianchi | | Rossi | | Neri | | Verdi | | Galli | | Rosati | +----------+

30. Interrogazioni nidificate select …. from …… where attributo [=|<>|<|>|>=|<=]([all|any]) (select ….) any con almeno uno dei valori restituiti dalla seconda select all con tutti i valori della seconda select Gli altri operatori possono essere usati solo se la seconda select restituisce un unico valore

31. Estrarre gli impiegati che hanno lo stesso nome di un impiegato del dipartimento produzione select i1.nome from impiegato as i1,impiegato as i2 where i1.nome=i2.nome and i2.dipart='produzione'; +-------+ | nome | +-------+ | Carlo | | Carlo | | Marco | +-------+ select nome from impiegato where nome = any (select nome from impiegato where dipart='produzione'); +-------+ | nome | +-------+ | Carlo | | Carlo | | Marco | +-------+ Le due interrogazioni sono equivalenti.

32. La seguente interrogazione non poteva essere espressa utilizzando il join. Estrarre i dipartimenti in cui non lavorano persone di cognome “Rossi”: select nome from dipartimento where nome <> all (select dipart from impiegato where cognome = 'Rossi'); +---------------+ | nome | +---------------+ | Distribuzione | | Produzione | | Ricerca | +---------------+

33. Estrarre il dipartimento dell’impiegato che guadagno lo stipendio massimo select dipart from impiegato where stipendio=(select max(stipendio) from impiegato); +-----------+ | dipart | +-----------+ | Direzione | +-----------+ select dipart from impiegato where stipendio>= all (select stipendio from impiegato); +-----------+ | dipart | +-----------+ | Direzione | +-----------+

34. Viste Viste, ovvero tabelle virtuali il cui contenuto dipende dal contenuto delle altre tabelle di una base di dati. createviewNomeVista[(ListaAttributi)] asSelect create view ImpiegatiAmmin(matricola,nome,cognome,stipendio) as select matricola,nome,cognome,stipendio) from impiegato where dipart=‘amministrazione’ and stipendio>10; Le viste in SQL possono servire per formulare interrogazioni che non sarebbero altrimenti esprimibili