1 / 58

SQL :S tructured Q uery L anguage: SELECT (III)

SQL (Slide tratte in parte da da Atzeni, Ceri, Paraboschi, Torlonee, Basi di dati McGraw-Hill, 2002). SQL :S tructured Q uery L anguage: SELECT (III). Persone. Maternità. Madre. Figlio. Nome. Età. Reddito. Luisa. Maria. Andrea. 27. 21. Luisa. Luigi. Aldo. 25. 15. Anna. Olga.

jenski
Download Presentation

SQL :S tructured Q uery L anguage: SELECT (III)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL (Slide tratte in parte da da Atzeni, Ceri, Paraboschi, Torlonee, Basi di datiMcGraw-Hill, 2002) SQL :Structured Query Language: SELECT (III)

  2. Persone Maternità Madre Figlio Nome Età Reddito Luisa Maria Andrea 27 21 Luisa Luigi Aldo 25 15 Anna Olga Maria 55 42 Anna Filippo Anna 50 35 Maria Andrea Filippo 26 30 Maria Aldo Luigi 50 40 Paternità Padre Figlio Franco 60 20 Sergio Franco Olga 30 41 Luigi Olga Sergio 85 35 Luigi Filippo Luisa 75 87 Franco Andrea Franco Aldo Giugno-Pulvirenti AA 2005-2006

  3. Attributi: sintassi estesa • Attributi ::= * | Espr [[AS] NuovoNome] {, Espr [[AS] NuovoNome] } • Espr ::= [Ide.]Attributo | Costante | “(“ Espr “)” | [-] Espr [ Espr] | (SUM | COUNT |AVG | MAX | MIN) “(“ [DISTINCT] [Ide.] Attributo “)” | COUNT “(“ * “)” •  ::= ( + | - | * | / ) Giugno-Pulvirenti AA 2005-2006

  4. Esempio SELECT AVG(Voto) AS Media Rossi FROM Esami, Studenti WHERE Nome = ‘Paolo Rossi’ AND Esami.Matricola = Studenti.Matricola • Media dei voti di Paolo Rossi Giugno-Pulvirenti AA 2005-2006

  5. Operatori aggregati • Nelle espressioni della target list possiamo avere anche espressioni che calcolano valori a partire da insiemi di ennuple • SQL-2 prevede 5 possibili operatori di aggregamento: • conteggio, minimo, massimo, media, somma • Gli operatori di aggregamento NON sono rappresentabili in Algebra Relazionale Giugno-Pulvirenti AA 2005-2006

  6. Cosa sono? • Operano su insiemi di righe per dare un risultato per gruppo. IMPIEGATI DEPTNO SAL --------- --------- 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250 MAX(SAL) --------- 5000 “Salario Massimo” Giugno-Pulvirenti AA 2005-2006

  7. Uso SELECT [column,] group_function(column) FROM table [WHERE condition] [GROUP BY column] [ORDER BY column]; Giugno-Pulvirenti AA 2005-2006

  8. Operatori aggregati: COUNT • COUNT restituisce il numero di righe o il numero di valori distinti di un particolare attributo • Esempio: Il numero di figli di Franco: • SELECT count(*) as NumFigliDiFranco FROM Paternita WHERE Padre = 'Franco' • l’operatore aggregato (count) viene applicato al risultato dell’interrogazione: SELECT * FROM Paternita WHERE Padre = 'Franco' Giugno-Pulvirenti AA 2005-2006

  9. Esempio Sergio Franco Luigi Olga Luigi Filippo Franco Franco Andrea Andrea Franco Franco Aldo Aldo Paternità Padre Figlio NumFigliDiFranco 2 Giugno-Pulvirenti AA 2005-2006

  10. COUNT e valori nulli Persone Nome Età Reddito Andrea 27 21 Aldo 25 NULL Maria 55 21 Anna 50 35 • Numero di tuple SELECT count(*) FROM persone • Numero di volte il campo ‘reddito’ non e’ NULL SELECT count(reddito) FROM persone • Numero di valori distinti del campo ‘reddito’ (senza i NULL) SELECT count(distinct reddito) FROM persone Giugno-Pulvirenti AA 2005-2006

  11. Altri operatori aggregati SUM, AVG, MAX, MIN Media dei redditi di coloro che hanno meno di 30 anni: SELECT avg(reddito) FROM persone WHERE eta < 30 Uso del JOIN: media dei redditi dei figli di Franco: SELECT avg(reddito) FROM persone JOIN paternita ON nome=figlio WHERE padre='Franco‘ Uso di piu’ operatori di aggregamento nella target list: SELECT avg(reddito), min(reddito), max(reddito) FROM persone WHERE eta < 30 Giugno-Pulvirenti AA 2005-2006

  12. Uso di AVG e SUM • Possono essere usati su dati numerici. SQL> SELECT AVG(sal), MAX(sal), 2 MIN(sal), SUM(sal) 3 FROM emp 4 WHERE job LIKE 'SALES%'; AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL) -------- --------- --------- --------- 1400 1600 1250 5600 Giugno-Pulvirenti AA 2005-2006

  13. Uso di MIN e MAX • Possono essere usati su qualsiasi tipo. SQL> SELECT MIN(hiredate), MAX(hiredate) 2 FROM emp; MIN(HIRED MAX(HIRED --------- --------- 17-DEC-80 12-JAN-83 Giugno-Pulvirenti AA 2005-2006

  14. Operatori aggregati e valori nulli Persone Nome Età Reddito Andrea 27 30 Aldo 25 NULL Maria 55 36 Anna 50 36 SELECT avg(reddito) AS redditomedio FROM persone Giugno-Pulvirenti AA 2005-2006

  15. Operatori aggregati e target list • un’interrogazione scorretta: SELECT nome, max(reddito) FROM persone • di chi sarebbe il nome? La target list deve essere omogenea SELECT min(eta), avg(reddito) FROM persone Giugno-Pulvirenti AA 2005-2006

  16. Operatori aggregati e raggruppamenti Le funzioni possono essere applicate a partizioni delle relazioni Clausola GROUP BY Syntax: GROUP BY listaAttributi Il numero di figli di ciascun padre SELECT padre, count(*) AS NumFigli FROM paternita GROUP BY Padre Sergio Franco Luigi Olga Luigi Filippo Franco Andrea Franco Aldo paternita Padre Figlio Padre NumFigli Sergio 1 Luigi 2 Franco 2 Giugno-Pulvirenti AA 2005-2006

  17. Raggruppare piu’ di una colonna IMPIEGATI DEPTNO JOB SAL --------- --------- --------- 10 MANAGER 2450 10 PRESIDENT 5000 10 CLERK 1300 20 CLERK 800 20 CLERK 1100 20 ANALYST 3000 20 ANALYST 3000 20 MANAGER 2975 30 SALESMAN 1600 30 MANAGER 2850 30 SALESMAN 1250 30 CLERK 950 30 SALESMAN 1500 30 SALESMAN 1250 DEPTNO -------- 10 10 10 20 20 20 30 30 30 JOB SUM(SAL) --------- --------- CLERK 1300 MANAGER 2450 PRESIDENT 5000 ANALYST 6000 CLERK 1900 MANAGER 2975 CLERK 950 MANAGER 2850 SALESMAN 5600 “sommare I salari in IMPIEGATIper ongi lavoro, Ragruppati per dipartimeno” Giugno-Pulvirenti AA 2005-2006

  18. Uso di GROUP BY • Tutte le colonne della SELECT che non sono in funzioni di gruppo devono essere nella GROUP BY. SQL> SELECT deptno, AVG(sal) 2 FROM emp 3 GROUP BY deptno; DEPTNO AVG(SAL) --------- --------- 10 2916.6667 20 2175 30 1566.6667 Giugno-Pulvirenti AA 2005-2006

  19. Esempio GROUP BY • Per ogni dipartimento, restituire la somma degli stipendi di tutti gli impiegati (di quel dipartimento) • Relazione: Employee(Name,Dept,Office,Salary,City) Giugno-Pulvirenti AA 2005-2006

  20. Semantica degli operatori di raggruppamento (1) • La query e’ innanzitutto eseguita senza operatori aggregati e senza GROUP BY: Giugno-Pulvirenti AA 2005-2006

  21. Semantica degli operatori di raggruppamento (2) • Quindi il risultato e’ diviso in sottoinsiemi aventi gli stessi valori per gli attributi indicati nel GROUP BY (Dept nel nostro caso) • Quindi l’operatore di aggregamento e’ calcolato su ogni sottoinsieme: Giugno-Pulvirenti AA 2005-2006

  22. 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.nomeGROUP BY padre corretta SELECT padre, avg(f.reddito) FROM persone f JOIN paternita ON figlio = nome JOIN persone p ON padre =p.nomeGROUP BY padre Giugno-Pulvirenti AA 2005-2006

  23. Condizioni sui gruppi, clausola HAVING • Si possono applicare condizioni sul valore aggregato per ogni gruppo • Esempio: I dipartimenti la cui somma dei salari e’ maggiore di 100 Giugno-Pulvirenti AA 2005-2006

  24. WHERE o HAVING? • I padri i cui figli sotto i 30 anni hanno un reddito medio maggiore di 25: SELECT padre, avg(f.reddito) FROM persone f JOIN paternita ON figlio = nome WHERE eta < 30 GROUP BY padre HAVING avg(f.reddito) > 25 Giugno-Pulvirenti AA 2005-2006

  25. Sintassi, riassumiamo SelectSQL ::= select ListaAttributiOEspressioni from ListaTabelle [ where CondizioniSemplici ] [ group by ListaAttributiDiRaggruppamento ] [ having CondizioniAggregate ] [ order by ListaAttributiDiOrdinamento ] Giugno-Pulvirenti AA 2005-2006

  26. Esercitazione • Considera il seguente schema relazionale: • Quadri( Pittore, Titolo, DataCreazione, NomeMuseo) • Musei( NomeMuseo, Citta, Curatore) • Costo( Titolo, Prezzo) • Scrivere le seguenti query in SQL: • Elenco dei musei e del valore medio dei quadri posseduti. • Il numero di quadri per ogni pittore fra tutti i musei parigini. • Il museo con il maggior numero di quadri esposti. Giugno-Pulvirenti AA 2005-2006

  27. Elenco dei musei e del valore medio dei quadri posseduti. • Quadri( Pittore, Titolo, DataCreazione, NomeMuseo) • Musei( NomeMuseo, Citta, Curatore) • Costo( Titolo, Prezzo) SELECT avg(c.prezzo) ‘prezzo medio’, m.nomeMuseo FROM quadri q, musei m, costo c WHERE q.nomeMuseo = m.nomeMuseo AND q.Titolo = c.Titolo GROUP BY m.nomeMuseo Giugno-Pulvirenti AA 2005-2006

  28. Elenco dei musei e del valore medio dei quadri posseduti. • Quadri( Pittore, Titolo, DataCreazione, NomeMuseo) • Musei( NomeMuseo, Citta, Curatore) • Costo( Titolo, Prezzo) SELECT avg(c.prezzo) ‘prezzo medio’, m.nomeMuseo FROM (musei JOIN quadri ON musei.nomeMuseo = quadri.nomeMuseo) JOIN costo ON quadri.Titolo = costo.Titolo GROUP BY m.nomeMuseo Giugno-Pulvirenti AA 2005-2006

  29. Il numero di quadri per ogni pittore fra tutti i musei parigini. • Quadri( Pittore, Titolo, DataCreazione, NomeMuseo) • Musei( NomeMuseo, Citta, Curatore) • Costo( Titolo, Prezzo) SELECT count(*) ‘numero quadri’, pittore FROM musei m, quadri q, costo c WHERE m.Citta = ‘Parigi’ AND m.nomeMuseo = q.nomeMuseo AND q.titolo = c.Titolo GROUP BY pittore Giugno-Pulvirenti AA 2005-2006

  30. Il museo con il maggior numero di quadri esposti. • Quadri( Pittore, Titolo, DataCreazione, NomeMuseo) • Musei( NomeMuseo, Citta, Curatore) • Costo( Titolo, Prezzo) Giugno-Pulvirenti AA 2005-2006

  31. Unione, intersezione e differenza • La SELECT da sola non permette di fare unioni; serve un costrutto esplicito: SELECT … UNION [all] SELECT ... • i duplicati vengono eliminati (a meno che si usi all) • anche dalle proiezioni! Giugno-Pulvirenti AA 2005-2006

  32. Notazione posizionale! SELECT padre FROM paternita UNION SELECT madre FROM maternita • quali nomi per gli attributi del risultato? • nessuno • quelli del primo operando • … Giugno-Pulvirenti AA 2005-2006

  33. Figlio Padre Figlio Luisa Luisa Maria Maria Luisa Luisa Luigi Luigi Anna Anna Olga Olga Anna Anna Filippo Filippo Maria Maria Andrea Andrea Maria Maria Aldo Aldo Sergio Sergio Franco Franco Luigi Luigi Olga Olga Luigi Luigi Filippo Filippo Franco Franco Andrea Andrea Franco Franco Aldo Aldo Giugno-Pulvirenti AA 2005-2006

  34. Notazione posizionale, 2 SELECT padre, figlio FROM paternita UNION SELECT figlio, madre FROM maternita SELECT padre, figlio FROM paternita UNION SELECT madre, figlio FROM maternita Giugno-Pulvirenti AA 2005-2006

  35. Notazione posizionale, 3 • Anche con le ridenominazioni non cambia niente: SELECT padre as genitore, figlioFROM paternitaUNIONSELECT figlio, madre as genitoreFROM maternita • Corretta: SELECT padre as genitore, figlioFROM paternitaUNIONSELECT madre as genitore, figlio FROM maternita Giugno-Pulvirenti AA 2005-2006

  36. Differenza SELECT NomeFROM ImpiegatoEXCEPTSELECT Cognome as NomeFROM Impiegato vedremo che si può esprimere con SELECT nidificate Giugno-Pulvirenti AA 2005-2006

  37. Intersezione SELECT NomeFROM ImpiegatoINTERSECTSELECT Cognome as NomeFROM Impiegato equivale a SELECT I.NomeFROM Impiegato I, Impiegato JWHERE I.Nome = J.Cognome Giugno-Pulvirenti AA 2005-2006

  38. Limitare le righe selezionate • Limitare le righe tramite l’uso della clausola WHERE. • La clausola WHERE segue la clausola FROM. SELECT [DISTINCT] {*| colonna [alias], ...} FROM tabella [WHERE condizione(i)]; Giugno-Pulvirenti AA 2005-2006

  39. Interrogazioni nidificate SELECT [DISTINCT] {*| colonna [alias], ...} FROM tabella [WHERE condizione(i)]; • Condizione : :=Predicato | “(“Condizione”)” | NOT Condizione | Condizione (AND | OR) Condizione • Predicato • Espr op (Espr | “(“ Sottoselect “)” ) • op  {=, <>, >, >=, <, <=} • SottoSelect deve dare come risultato una tabella con un solo elemento o vuota (vedremo alcuni esempi) Giugno-Pulvirenti AA 2005-2006

  40. Interrogazioni nidificate • le condizioni in SQL permettono anche il confronto fra un attributo e il risultato di una sottoquery • Syntax: • Scalare Operatore (ANY | ALL) SelectQuery • ANY: il predicato e’ vero se almeno uno dei valori restituiti da Query soddisfano la condizione • ALL: il predicato e’ vero se tutti i valori restituiti dalla Query soddisfano la condizione • quantificatore esistenziale • EXISTS SelectQuery • Il predicato e’ vero se la SelectQuery restituisce almeno una tupla Giugno-Pulvirenti AA 2005-2006

  41. Esempio di SELECT nidificate • nome e reddito del padre di Franco SELECT Nome, RedditoFROM Persone, PaternitaWHERE Nome = Padre AND Figlio = 'Franco' SELECT Nome, RedditoFROM PersoneWHERE Nome = ( SELECT Padre FROM Paternita WHERE Figlio = 'Franco') • La query nella clausola WHERE e’ la query nidificata Giugno-Pulvirenti AA 2005-2006

  42. Interrogazioni nidificate, commenti • La forma nidificata è “meno dichiarativa”, ma talvolta più leggibile (richiede meno variabili) • La forma piana e quella nidificata possono essere combinate • Le sottointerrogazioni non possono contenere operatori insiemistici (“l’unione si fa solo al livello esterno”); la limitazione non è significativa Giugno-Pulvirenti AA 2005-2006

  43. Nome e reddito dei padri di persone che guadagnano più di 20 milioni SELECT distinct P.Nome, P.RedditoFROM Persone P, Paternita, Persone FWHERE P.Nome = Padre AND Figlio = F.Nome AND F.Reddito > 20 SELECT Nome, RedditoFROM PersoneWHERE Nome in (SELECT Padre FROM Paternita WHERE Figlio = any (SELECT Nome FROM Persone WHERE Reddito > 20)) Giugno-Pulvirenti AA 2005-2006

  44. Nome e reddito dei padri di persone che guadagnano più di 20 milioni SELECT distinct P.Nome, P.RedditoFROM Persone P, Paternita, Persone FWHERE P.Nome = Padre AND Figlio = F.Nome AND F.Reddito > 20 SELECT Nome, RedditoFROM PersoneWHERE Nome in (SELECT Padre FROM Paternita, Persone WHERE Figlio = Nome AND Reddito > 20) Giugno-Pulvirenti AA 2005-2006

  45. Negazione con le query nidificate • Trovare quei dipartimenti dove non c’e’ nessuno che si chiama ‘Brown’: • Oppure: Giugno-Pulvirenti AA 2005-2006

  46. Operatori IN e NOT IN • IN e’ sinonimo di: =ANY • NOT IN e’ sinonimo di: <>ALL Giugno-Pulvirenti AA 2005-2006

  47. MAX e MIN con le query nidificate • Esempio: Il dipartimento(i) dove lavora colui con lo stipendio piu’ alto di tutta l’azienda: • Oppure: Giugno-Pulvirenti AA 2005-2006

  48. Interrogazioni nidificate, commenti, 2 • La prima versione di SQL prevedeva solo la forma nidificata (o strutturata), con una sola relazione in ogni clausola FROM. Il che è insoddisfacente: • la dichiaratività è limitata • non si possono includere nella target list attributi di relazioni nei blocchi interni Giugno-Pulvirenti AA 2005-2006

  49. Nome e reddito dei padri di persone che guadagnano più di 20 milioni, con indicazione del reddito del figlio SELECT distinct P.Nome, P.Reddito, F.RedditoFROM Persone P, Paternita, Persone FWHERE P.Nome = Padre AND Figlio = F.Nome AND F.Reddito > 20 SELECT Nome, Reddito, ????FROM PersoneWHERE Nome in (SELECT Padre FROM Paternita WHERE Figlio = any (SELECT Nome FROM Persone WHERE Reddito > 20)) Giugno-Pulvirenti AA 2005-2006

  50. Interrogazioni nidificate, commenti, 3 • regole di visibilità: • non è possibile fare riferimenti a variabili definite in blocchi più interni • se un nome di variabile è omesso, si assume riferimento alla variabile più “vicina” • in un blocco si può fare riferimento a variabili definite in blocchi più esterni Giugno-Pulvirenti AA 2005-2006

More Related