Ordinamento 11302020 Ordinamento del risultato E possibile dare

Ordinamento 11/30/2020

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. 11/30/2020

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 11/30/2020

SELECT nome, reddito FROM persone WHERE eta < 30 Persone Nome Andrea Aldo Filippo 11/30/2020 SELECT nome, reddito FROM persone WHERE eta < 30 ORDER BY nome Persone Reddito 21 15 30 Nome Aldo Andrea Filippo Reddito 15 21 30

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

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 11/30/2020

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 11/30/2020

Operatori Aggregati 11/30/2020

Operatori aggregati l l 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: l l l Conteggio (COUNT), Minimo (MIN), Massimo(MAX), Media (AVG), Somma (SUM) Gli operatori di aggregamento NON sono rappresentabili in Algebra Relazionale 11/30/2020

Operatori aggregati: 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 Num. Figli. Di. Franco FROM Paternita WHERE Padre = 'Franco' l’operatore aggregato (count) viene applicato al risultato dell’interrogazione: SELECT * FROM Paternita WHERE Padre = 'Franco' 11/30/2020

Paternità Padre Sergio Luigi Franco Figlio Franco Olga Filippo Andrea Aldo SELECT * FROM Paternita WHERE Padre = 'Franco' Padre Franco Figlio Andrea Aldo count SELECT count(*) as Num. Figli. Di. Franco FROM Paternita WHERE Padre = 'Franco' 11/30/2020 Num. Figli. Di. Franco 2

(*), 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. 11/30/2020

Esami. BD Studente BD LBD 012345 27 NULL 032456 25 23 035221 NULL 033445 28 30 032441 NULL 30 Contare il numero di studenti iscritti al corso di BD e Laboratorio SELECT count(*) as “Num. Stud” FROM Esami. BD Contare il numero di esami di BD superati positivamente SELECT count([ALL] BD) “Conta. BD” FROM Esami. BD Numero di voti distinti dati all’esame di LBD SELECT count(distinct LBD) “Cont. Dist. LBD” FROM Esami. BD Num. Stud 5 Conta. BD 3 Cont. Dist. LBD 2

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 SELECT count(art_cod) FROM Articoli Art_cod è una chiave primaria Esercizio 2: calcolare il numero totale delle categorie, basandosi sulla tabella categorie. SELECT count(*) FROM Categorie 11/30/2020

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 11/30/2020

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 Esami. BD 11/30/2020

Esercizio 1: Quali sono la massima e la minima quantità di articoli richiesti in un ordine? SELECT MAX (ordart_qta) FROM Ordart SELECT MIN (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 11/30/2020

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 11/30/2020

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’ 11/30/2020

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’ 11/30/2020

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 11/30/2020

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 11/30/2020

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 11/30/2020

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. l un’interrogazione scorretta: SELECT nome, max(reddito) FROM persone l di chi sarebbe il nome? La target list deve essere omogenea. E’ corretta invece la seguente: SELECT min(eta), avg(reddito) FROM persone 11/30/2020

Raggruppamenti 11/30/2020

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 11/30/2020 Dipart AVG(stipendio) Produzione 1330 Amministrazione 1505 Distribuzione 1810 Direzione 2500

Semantica degli operatori di raggruppamento (1) l La query è innanzitutto eseguita senza operatori aggregati e senza GROUP BY: SELECT Dipart, stipendio FROM Impiegati 11/30/2020 Dipart Stipendio Amministrazione 1560 Produzione 1340 Amministrazione 1450 Distribuzione 1750 Produzione 1400 Direzione 2500 Produzione 1250 Distribuzione 1870

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: Dipart Stipendio Amministrazione 1560 Amministrazione 1450 Distribuzione 1750 Distribuzione 1870 Direzione Dipart AVGstipendio Amministrazione 1505 Distribuzione 1810 2500 Direzione 2500 Produzione 1340 Produzione 1330 Produzione 1400 Produzione 1250 11/30/2020 AVG

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 11/30/2020

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 11/30/2020

Condizioni sui gruppi, clausola HAVING l l 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 11/30/2020 Dipart AVG(stipendio) Distribuzione 1810 Direzione 2500 Dipart AVG(stipendio) Amministrazione 1505 Distribuzione 1810 Direzione 2500 Produzione 1330 HAVING AVG(stipendio)>1700

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. Esami. BD (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 Esami. BD WHERE eta < 21 GROUP BY città HAVING avg(voto) > 26 11/30/2020

Sintassi, riassumiamo Select. SQL : : = select Lista. Attributi O Espressioni from Lista. Tabelle [ where Condizioni. Semplici ] [ group by Lista. Attributi. Di. Raggruppamento ] [ having Condizioni. Aggregate ] [ order by Lista. Attributi. Di. Ordinamento ] 11/30/2020

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 11/30/2020

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 11/30/2020

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 11/30/2020

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 11/30/2020

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 11/30/2020

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) 11/30/2020