Ordinamento

1. Ordinamento

2. Ordinamento del risultato E‘ possibile dare un ordinamento del risultato di una select. L‘ordinamento si può effettuare in base a un attributo, e può essere crescente o decrescente. La sintassi è la seguente: SELECT lista_attributi FROM nome_tabella WHERE condizioni ORDER BY Attributo [ASC/DESC] Le righe vengono ordinate in base al campo Attributo in maniera crescente o decrescente secondo se è data la specifica ASC o DESC. ASC è il default. Secondo il tipo dell’attributo, l’ordinamento è quello più naturale su quel dominio.

3. Esempio Nome e reddito delle persone con meno di trenta anni in ordine alfabetico SELECT nome, reddito FROM persone WHERE eta < 30 ORDER BY nome

4. SELECT nome, reddito FROM persone WHERE eta < 30 ORDER BY nome SELECT nome, reddito FROM persone WHERE eta < 30 Persone Persone

5. Doppio ordinamento Si può voler ordinare i dati in base a una certa chiave (attributo) e poi ordinare i dati che che coincidono su quella chiave in base a un’altra chiave (attributo).

6. Ordinamento, Esempio Ordinare gli studenti in base al loro cognome, in modo tale che due persone con lo stesso cognome siano ordinate in base al nome, e persone con lo stesso nome e cognome siano ordinate in base all’ordine inverso della data di nascita Select * From Studenti Order by cognome [asc], nome [asc] , nascita desc

7. Esercizi Esercizio 1: Ordinare i negozi di Roma in base ai nomi. Select * From Negozi Where neg_citta=‘Roma’ Order by neg_nome Esercizio 2: Ordinare i componenti per costi decrescenti Select * From Componenti Order by com_costo desc Esercizio 3: Ordinare i componenti per costi decrescenti e, a parità di costo, per codici crescenti Select * From componenti Order by com_costo desc, com_cod asc

8. Operatori Aggregati

9. Operatori aggregati • Nella target list possiamo avere anche espressioni che calcolano valori a partire da insiemi di ennuple e che restituiscono una tabella molto particolare, costituita da un singolo valore scalare. • SQL-2 prevede 5 possibili operatori di aggregamento: • Conteggio (COUNT), • Minimo (MIN), • Massimo(MAX), • Media (AVG), • Somma (SUM) • Gli operatori di aggregamento NON sono rappresentabili in Algebra Relazionale

10. Operatori aggregati: COUNT • COUNT restituisce il numero di righe della tabella o il numero di valori 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'

11. Paternità • SELECT * • FROM Paternita • WHERE Padre = 'Franco' count • SELECT count(*) • as NumFigliDiFranco • FROM Paternita • WHERE Padre = 'Franco'

12. (*), ALL e DISTINCT Mediante le specifiche (*), ALL e DISTINCT è possibile contare (*): tutte le righe selezionate; ALL: tutti i valori non nulli delle righe selezionate; DISTINCT: tutti i valori non nulli distinti delle righe selezionate. Se la specifica viene omessa, il default è ALL.

13. EsamiBD Contare il numero di studenti iscritti al corso di BD e Laboratorio SELECT count(*) as “NumStud” FROM EsamiBD Contare il numero di esami di BD superati positivamente SELECT count([ALL] BD) “ContaBD” FROM EsamiBD Numero di voti distinti dati all’esame di LBD SELECT count(distinct LBD) “ContDistLBD” FROM EsamiBD

14. Esercizi Esercizio 1: Calcolare il numero di codici presenti nella tabella Articoli; spiegare perché in questo caso non ha senso applicare le specifiche ALL e DISTINCT Art_cod è una chiave primaria SELECT count(art_cod) FROM Articoli Esercizio 2: calcolare il numero totale delle categorie, basandosi sulla tabella categorie. SELECT count(*) FROM Categorie

15. Esercizi Esercizio 3: Calcolare il numero di categorie presenti nella tabella Articoli SELECT count (Distinct cat_cod) FROM Articoli Esercizio 4: Calcolare il numero di categorie presenti nella tabella Articoli, per cui il prezzo dell’articolo corrispondente sia inferiore a 400 euro. SELECT count (Distinct cat_cod) FROM articoli WHERE art_prezzo<400

16. Max e Min Le funzioni MAX e MIN calcolano rispettivamente il maggiore e il minore degli elementi di una colonna. Esempio L’età della persona più anziana nella tabella persone SELECT max(eta) FROM Persone Il più basso dei voti assegnati all’esame di BD SELECT min(BD) FROM EsamiBD

17. Esercizi Esercizio 1: Quali sono la massima e la minima quantità di articoli richiesti in un ordine? SELECT MIN (ordart_qta) FROM Ordart SELECT MAX (ordart_qta) FROM Ordart Esercizio 2: Calcolare a quanto ammonterebbe il prezzo massimo della tabella articoli se vi venisse apportato un aumento del 12% SELECT MAX (1,12 * art_prezzo) FROM Articoli

18. Esercizi Esercizio 3: Determinare qual è il costo minimo di un articolo, considerato il suo prezzo, l’iva e le spese di trasporto SELECT MIN ((1+art_iva/100)*Art_prezzo+art_spese_trasporto) FROM Articoli

19. Sum La funzione SUM calcola la somma dei valori di una colonna. Le specifiche ALL e DISTINCT permettono di sommare tutti i valori non nulli o tutti i valori distinti. Il default in mancanza di specifiche è ALL. Esempio: Calcolare la somma degli stipendi mensili degli impiegati del settore Produzione. SELECT SUM (ALL stipendio) FROM Impiegati WHERE Dipart=‘Produzione’

20. Esercizi Esercizio 1: Sommare i prezzi di tutti i componenti SELECT SUM (com_prezzo) FROM Componenti Esercizio 2: Sommare i prezzi distinti di tutti i componenti SELECT SUM (DISTINCT com_prezzo) FROM Componenti Esercizio 3: Sommare i prezzi di tutti i componenti prodotti nel laboratorio dal codice ‘0050’ SELECT SUM (com_prezzo) FROM Componenti Where lab_cod=‘0050’

21. Esercizi Esercizio 4: Calcolare la somma dei prezzi dei due armadi presenti nella tabella Articoli SELECT SUM (Art_prezzo) FROM Articoli Where Art_descrizione LIKE ‘Armadio%’ Esercizio 5: Calcolare il numero totale di articoli ordinati SELECT SUM (ordart_qta) FROM Ordart

22. AVG La funzione AVG calcola la media (average) dei valori non nulli di una colonna. Le specifiche ALL e DISTINCT servono a calcolare la media fra tutti i valori o tra i valori distinti. Il default è ALL. Esempio: Calcolare la media degli stipendi degli impiegati del dipartimento di Produzione e che hanno meno di 30 anni SELECT AVG(stipendi) FROM Impiegati WHERE Dipart=‘Produzione’ AND eta<30

23. Esercizi Esercizio 1: Calcolare la media delle spese di trasporto degli articoli SELECT AVG (Art_spese_trasporto) FROM Articoli Esercizio 2: Calcolare la media dei prezzi dei componenti SELECT AVG (Com_costo) FROM Componenti Esercizio 3: Calcolare la media dei prezzi lordi degli articoli, inclusivi di spese di trasporto (per gli articoli per cui prezzo, IVA e Spese trasporto sono definite) Select AVG(Art_prezzo*(1+art_IVA/100)+Art_Spese_trasporto) FROM Articoli

24. Operatori aggregati e target list Non è possibile utilizzare in una stessa select una proiezione su alcuni attributi della tabella considerata e operatori aggregati sulla stessa tabella. • un’interrogazione scorretta: SELECT nome, max(reddito) FROM persone • di chi sarebbe il nome? La target list deve essere omogenea. E’ corretta invece la seguente: SELECT min(eta), avg(reddito) FROM persone

25. Raggruppamenti

26. Group by A volte può essere richiesto di calcolare operatori aggregati non per l’intera tabella, ma raggruppando le righe i cui valori coincidono su un certo attributo. Per esempio, vogliamo sapere la media degli stipendi degli impiegati per ogni dipartimento. In tal caso si può utilizzare la clausola GROUP BY. SELECT Dipart, AVG(stipendio) FROM Impiegati GROUP BY Dipart Nella colonna AVG(stipendio) c’è la media di tutti gli stipendi degli impiegati di ogni reparto

27. Semantica degli operatori di raggruppamento (1) • La query è innanzitutto eseguita senza operatori aggregati e senza GROUP BY: SELECT Dipart, stipendio FROM Impiegati

28. Semantica degli operatori di raggruppamento (2) Quindi il risultato è diviso in sottoinsiemi aventi gli stessi valori per gli attributi indicati nel GROUP BY (Dept nel nostro caso) Quindi l’operatore aggregato è calcolato su ogni sottoinsieme: AVG

29. Esercizi Esercizio 1: Calcolare il prezzo più alto degli articoli per ciascuna categoria. SELECT cat_cod, MAX (art_prezzo) FROM Articoli GROUP BY cat_cod Esercizio 2: Calcolare il numero di articoli appartenenti a ciascuna categoria SELECT cat_cod, count(art_cod) FROM Articoli GROUP BY cat_cod

30. Esercizi Esercizio 3: Calcolare il numero di componenti di ciascun articolo. SELECT art_cod, sum(compart_qta) FROM compart GROUP BY art_cod Esercizio 4: Calcolare quanti articoli sono richiesti in ciascun ordine SELECT ord_cod, SUM(ordart_qta) FROM ordart GROUP BY ord_cod

31. Condizioni sui gruppi, clausola HAVING • Si possono applicare condizioni sul valore aggregato per ogni gruppo. Si può realizzare mediante la clausola HAVING. • Esempio: I dipartimenti la cui media degli stipendi è maggiore di 1700 euro Select dipart, AVG(stipendio) FROM Impiegati Group by Dipart HAVING AVG(stipendio)>1700 HAVING AVG(stipendio)>1700

32. Where o Having In generale se la condizione coinvolge un attributo, si usa la clausola where, mentre se coinvolge un operatore aggregato si usa la clausola having. EsamiBD (matricola,nome, cognome, città, voto, età) Le città per cui la media dei voti dei suoi studenti di meno di 21 anni è maggiore di 26 SELECT città, avg(voto) FROM EsamiBD WHERE eta < 21 GROUP BY città HAVING avg(voto) > 26

33. Sintassi, riassumiamo SelectSQL ::= selectListaAttributi O Espressioni fromListaTabelle [ whereCondizioniSemplici ] [ groupbyListaAttributiDiRaggruppamento ] [ havingCondizioniAggregate ] [ orderbyListaAttributiDiOrdinamento ]

34. Esercizio 1 Quanti laboratori sono presenti in ogni città, escludendo le città con un solo laboratorio? Select lab_citta, count(lab_cod) as “numero laboratori” From Laboratori Group by lab_citta Having count(lab_cod)>1

35. Esercizio 2 Calcolare da quanti componenti è costituito ciascun articolo, escludendo gli articoli costituiti da meno di 10 componenti. Select art_cod, sum(com_cod) From Compart Group by art_cod Having sum(com_cod)>10

36. Esercizio 3 Calcolare da quanti tipi di componenti è costituito ciascun articoli, escudendo gli articoli costituiti da meno di 5 tipi di componenti Select art_cod, count(com_cod) From Compart Group by art_cod Having count (com_cod)>5

37. Esercizio 4 Calcolare da quanti componenti è costituito ciascun articolo, escludendo gli articoli costituiti da meno di 5 tipi di componenti. Select art_cod, sum(compart_qta) From Compart Group by art_cod Having count (com_cod)>5 14/10/2014

38. Esercizio 5 Per ogni categoria di mobili, calcolare la media dei prezzi degli articoli le cui spese di trasporto sono superiori a 50 euro, e la cui media non supera i 400 euro Select cat_cod, avg(art_prezzo) From Articoli Where art_spese_trasporto>50 Group by cat_cod Having AVG(art_prezzo)<400

39. Esercizio 6 Calcolare la media dei prezzi delle categorie che compendono almeno un articolo per il quale il campo art_prezzo è inferiore a 300 euro e ordinare il risultato secondi i valori della media. Select cat_cod, avg(art_prezzo) From Articoli Group by cat_cod Having min(art_prezzo)<300 Order by avg(art_prezzo)