Corso di Informatica - · Corso di Informatica –Dipartimento di Scienze Aziendali, Economiche e...

Corso di InformaticaLinguaggio SQL – seconda parte

Anno Accademico 2018-2019

2Corso di Informatica – Dipartimento di Scienze Aziendali, Economiche e Metodi quantitativi – Università di Bergamo

Le condizioni di ricerca


• Usate nelle clausole WHERE e HAVING

• Costruite con gli operatori di confronto: >, >=, <, <=, =, <>, . . .

• Espressioni ottenute concatenando confronti con gli operatori logici: AND,

OR, NOT, XOR

• Altri operatori di confronto: BETWEEN, NOT BETWEEN, IN, NOT IN,

LIKE, NOT LIKE, IS NULL , IS NOT NULL

• BETWEEN: controlla l’appartenenza di un valore in un dato intervallo

• IN: controlla l’appartenenza a uno dei valori di un elenco

• LIKE: confronta una stringa di caratteri con un modello di stringa costruita,

in genere, con caratteri jolly

• IS NULL: controlla la presenza di valori nulli

Le condizioni di ricerca


BETWEEN, IN

• BETWEEN

• IN

SELECT Cognome, Nome, Residenza

FROM Impiegati

WHERE Stipendio BETWEEN 30000 AND 45000;

WHERE Stipendio >= 30000 AND Stipendio <= 45000;

equivale a:

SELECT *

FROM Impiegati

WHERE Residenza IN ('Torino','Venezia','Palermo');

WHERE Residenza = 'Torino' OR Residenza = 'Venezia' OR

Residenza = 'Palermo';

equivale a:


Caratteri Jolly

? indica uno e un solo carattere qualsiasi in quella posizione della stringa

(nello standard SQL si usa il carattere “_” )

* indica una sequenza qualsiasi di caratteri in quella posizione della stringa

(nello standard SQL si usa il carattere “%”)

LIKE 'xyz*' riconosce le stringhe che iniziano con 'xyz';

LIKE '*xyz' riconosce le stringhe che finiscono con 'xyz';

LIKE '*xyz*' riconosce le stringhe di 3 o più caratteri che contengono 'xyz';

LIKE '?xyz' riconosce le stringhe di 4 caratteri che finiscono con 'xyz'.

LIKE (1)

SELECT Cognome, Dipartimento

FROM Impiegati

WHERE Cognome LIKE ‘R*’;

Gli impiegati con il cognome che inizia per R


LIKE (2)

[ ] ricerca un carattere fra quelli elencati fra le parentesi quadre

! ricerca caratteri diversi da quelli specificati tra [ ] (anche ^)

[α-β] ricerca i caratteri di un intervallo. Deve essere α<β

# ricerca un carattere numerico (“caratteri jolly” nella guida in linea)

Like “R[ai]s*” Raso, Riso, Rasente, Risoluto ma non Rosoni

Like “R[!i]s?” Raso, Reso, Roso, ma non Rosoni, Riso, Resto

Like “[A-M]*” tutte le stringhe che iniziano con un

carattere compreso tra A ed M

SELECT Cognome, Dipartimento

FROM Impiegati

WHERE Cognome LIKE [Carattere iniziale?] & ‘*’;

L’iniziale del cognome è scelta quando si esegue l’interrogazione


Funzioni di aggregazione (1)

• COUNT, SUM, MIN, MAX, AVG, …

• Sintetizzano le informazioni di una colonna in un solo valore

• Possono comparire SOLO nelle clausole SELECT e HAVING

• COUNT: conta il numero di righe o di valori non nulli in una colonna

– COUNT(*) conta le righe di una tabella

• SUM: somma i valori non nulli di una colonna

• AVG: restituisce la media dei valori non nulli di una colonna

– AVG(Stipendio) = SUM(Stipendio)/COUNT(Stipendio)

• MIN, MAX: restituiscono il valore minimo e massimo di una colonna

SELECT COUNT(*) FROM Impiegati; Restituisce 12



SELECT COUNT(Dipartimento) FROM Impiegati; Restituisce 11

SELECT COUNT(*), COUNT(*) AS ResidentiRoma

FROM Impiegati

WHERE Residenza = 'Roma';

SELECT SUM(Stipendio) AS StipendiAmm,

SUM(Stipendio*1.03) AS NuoviStipendiAmm

FROM Impiegati

WHERE Dipartimento = 'Amm';



• Per arrotondare i risultati si usa la funzione ROUND:

ROUND( Espressione [, NumCifre] )

SELECT AVG(Stipendio)

FROM Impiegati, Dipartimenti

WHERE Dipartimento = Codice AND

Sede = 'Torino';

SELECT ROUND(AVG(Stipendio)), ROUND(AVG(Stipendio),2)


WHERE Dipartimento = Codice AND

Sede = 'Torino';

ROUND(AVG(Stipendio), 0)



SELECT MIN(Stipendio), MAX(Stipendio)

FROM Impiegati;

SELECT MIN(Cognome), MAX(Cognome)

FROM Impiegati;


Ordinamenti e Raggruppamenti


Ordinamenti (1)

ORDER BY, se presente, deve essere l’ultima clausola di SELECT

SELECT Cognome, Nome, Residenza

FROM Impiegati

ORDER BY Cognome, Nome;

SELECT Cognome, Stipendio

FROM Impiegati

ORDER BY Stipendio DESC, Cognome;

ORDER BY Colonna ASC | DESC

Ordinato per valori crescenti di Cognome e, a parità di

Cognome, per Nome


Ordinamenti (2)

• Ordinare secondo i valori di un campo calcolato

• Attenzione: con Access bisogna usare una delle due forme

SELECT NomeProdotto, PrezUnit, Qta, PrezUnit*Qta

FROM Fatture

ORDER BY 4 DESC;

SELECT NomeProdotto, PrezUnit, Qta, PrezUnit*Qta AS Totale

FROM Fatture

ORDER BY Totale DESC;Forma raccomandata

Forma sconsigliata

ORDER BY 4 DESC

1 2 3 4

ORDER BY [PrezUnit]*[Qta] DESC


Raggruppamenti (1)

• GROUP BY per sintetizzare i valori di un campo in classi omogenee,

applicando le funzioni di aggregazione a singole parti di una colonna

SELECT Dipartimento, COUNT(ID) AS .. , SUM(Stipendio) AS ..

FROM Impiegati

GROUP BY Dipartimento; Nella clausola SELECT possono comparire solo i campi presenti in GROUP BY e

funzioni di aggregazione

Le righe sono raggruppate per dipartimento Le funzioni di aggregazione sono applicate

separatamente a ogni raggruppamento

LinguaggioSQL1.ppt#5. Il database degli esempi (2)


Raggruppamenti (2)

• La precedente interrogazione in SQL e QBE di Access


Raggruppamenti (3)

• Per mostrare il nome del dipartimento, si deve raggruppare per Descrizione:

• Oppure

• Ma non:

SELECT Descrizione, COUNT(*), SUM(Stipendio)


WHERE Impiegati.Dipartimento = Dipartimenti.Codice

GROUP BY Descrizione;




GROUP BY Dipartimento, Descrizione;




GROUP BY Dipartimento ;Errore per Access

LinguaggioSQL1.ppt#5. Il database degli esempi (2)


Condizioni sui raggruppamenti (1)

• HAVING per elencare i dipartimenti con più di due dipendenti

SELECT Dipartimento, COUNT(ID), SUM(Stipendio)

FROM Impiegati

GROUP BY Dipartimento

HAVING COUNT(ID) > 2;

HAVING predica

sui raggruppamenti



• HAVING e WHERE: i dipartimenti di Torino con più di 1 dipendente

SELECT Descrizione, Count(ID) AS Dipendenti,

Sum(Stipendio) AS Stipendi

FROM Dipartimenti D INNER JOIN Impiegati I ON

D.Codice = I.Dipartimento

WHERE Sede = 'Torino'

GROUP BY Descrizione

HAVING Count(ID)>1;

HAVING predica sui

raggruppamenti

WHERE predica

sulle righe



• DOVE nelle query QBE per inserire condizioni sulle righe

Condizione sui

raggruppamenti

Condizione

sulle righe

. . .

WHERE Sede = 'Torino'

GROUP BY Descrizione

HAVING Count(ID) > 1


Il comando SELECT

• Tutte le clausole del comando SELECT

• Obbligatorie le sole clausole SELECT (e FROM se ci sono tabelle)

• Devono rispettare l’ordinamento: SELECT FROM …ORDER BY

• SELECT è valutata dal motore del DBMS nell’ordine:

FROMWHEREGROUP BYHAVINGSELECTORDER BY

SELECT Elenco di espressioni da mostrare

FROM Tabelle da cui estrarre le righe

WHERE Condizioni sulle congiunzioni e sulle righe estratte

GROUP BY Campi da considerare per i raggruppamenti

HAVING Condizioni sui raggruppamenti

ORDER BY Ordinamenti sulle espressioni elencate in SELECT


Esercizi (1)

Anagrafiche (KCodice, Nome, Indirizzo)

Movimenti (KNumero, Descrizione, Data, Importo, Codice)

Scrivere il comando SQL per ricavare le seguenti informazioni:

• Numero delle persone in anagrafica aventi un indirizzo prefissato

• Importo totale dei movimenti riferiti a un codice prefissato

• Importo medio dei movimenti

• Valori minimo e massimo tra gli importi dei movimenti

• Elenco dei movimenti con importo compreso tra 1000 e 2000.

• Media degli importi per i movimenti aventi una descrizione prefissata.

• Valore massimo per gli importi dei movimenti di un’anagrafica di nome noto.

• Elenco dei movimenti con importo superiore a 300 in ordine crescente di importo

• Elenco delle anagrafiche con indirizzo Milano, Torino o Venezia

• Elenco delle anagrafiche con nome che inizia con ‘Ros’

• Nome delle anagrafiche con movimenti con importo compreso tra 3000 e 6000.

• Elenco delle anagrafiche che hanno l’iniziale del nome uguale ad A

• Elenco delle anagrafiche con iniziale del nome da A ad L


Esercizi (2)

Anagrafiche (KCodice, Nome, Indirizzo)

Movimenti (KNumero, Descrizione, Data, Importo, Codice)


• Totale degli importi dei movimenti per ciascun codice anagrafico

• Importo medio dei movimenti per le anagrafiche aventi più di 20 movimenti registrati

• Quante persone sono di Milano?

• Elenco degli indirizzi con più di 10 persone in anagrafica

• Raggruppare le anagrafiche per indirizzo e fornirne il numero per ogni indirizzo

• Nome delle anagrafiche che hanno almeno 30 movimenti registrati nella tabella dei

movimenti

• Numero di movimenti eseguiti nel 2013 da ogni soggetto di anagrafica

• Somma degli importi dei movimenti per codice e per Descrizione

• Per ogni soggetto di anagrafica elencare codice, nome, numero dei movimenti e

somma degli importi, ordinando l’elenco per valore decrescente del numero di

movimenti

• Elencare i soggetti di anagrafica con importo medio dei movimenti superiore a 500

• Elenco dei soggetti di anagrafica di Milano con somma degli importi superiore a 4500

• Elenco dei soggetti di anagrafica di Milano che hanno valore medio degli importi

superiore a 600. Elenco con codice, nome e numero di movimenti


Esercizi (3)

Riviste ( CodRiv, Titolo, Periodicità, Prezzo )

Abbonati ( CodAbb, Cognome, Indirizzo, Città )Abbonamenti ( ID, Data, CodRiv, CodAbb )


• Elenco degli abbonati (con Cognome e Indirizzo) ordinato alfabeticamente

• Titolo, Periodicità e Prezzo delle riviste ordinato per valori decrescenti di Prezzo

• Numero delle riviste e degli abbonati in archivio

• Numero degli abbonamenti sottoscritti per una rivista di cui si conosce il Titolo

• Prezzo medio, minimo e massimo delle riviste trimestrali

• Importo complessivo degli abbonamenti sottoscritti da abbonati di Milano

• Elenco degli abbonamenti sottoscritti nel primo trimestre del 2012 con titolo rivista,

codice abbonato e data, ordinato per data crescente

• Numero di riviste alle quali è abbonato un abbonato di cui si conosce il cognome

• Numero degli abbonati di Bergamo

• Elenco delle riviste per le quali non è precisata la periodicità ordinato per Titolo

• Numero di abbonamenti sottoscritti nel 2013 a una rivista di cui si conosce il codice


Esercizi (4)




• Numero degli abbonamenti sottoscritti per ogni rivista

• Valore totale abbonamenti per ogni rivista

• Valore totale abbonamenti per ogni rivista per le sole riviste per le quali sono stati

sottoscritti più di 3000 abbonamenti

• Elenco delle riviste con: titolo, numero degli abbonamenti sottoscritti e valore

complessivo degli abbonamenti, ordinato per numero decrescente di abbonamenti

• Numero degli abbonamenti sottoscritto da ogni abbonato

• Suddividere le riviste per periodicità e per ogni periodicità precisare il numero riviste e

il prezzo medio delle riviste che hanno quella periodicità


Query di comando e di servizio - SQL


Un doppio raggruppamento

• Numero dei dipendenti suddivisi per dipartimento e residenza:

SELECT Dipartimento, Residenza, Count(ID)

FROM Impiegati

GROUP BY Dipartimento, Residenza;

Elenco scomodo da consultare

Perché non disporre i dati in

una tabella a doppia entrata?


Query a Campi incrociati

E’ possibile inserire una sola

funzione di aggregazione

all’incrocio fra righe e colonne:


Query di comando

• Le query di comando modificano il contenuto del database

• Creando una query in Visualizzazione struttura, dopo avere scelto le

tabelle, la scheda Progettazione ha l’aspetto:

• Le query di comando servono per: Creare una nuova tabella, Aggiungere

dati a una tabella esistente, Modificare i dati in una tabella, Cancellare

righe di una tabella

• Le abituali interrogazioni sono indicate come: query di Selezione

• Con le query Pass-through si accede a una finestra per scrivere

interrogazioni in SQL


Query di Creazione tabella

– Creare una nuova tabella con: codice identificativo, cognome, nome,

stipendio e descrizione del dipartimento per i dipendenti della

produzione


Query di Accodamento (1)

– Accodare alla tabella Impiegati i dipendenti della tabella

NuoviDipendenti con ID uguale a 2 e 3.

I campi Dipartimento

e Stipendio sono

scambiati


Query di Accodamento (2)

Le due tabelle possono essere di

grado differente


Query di Eliminazione

– Eliminare da Impiegati i dipendenti con ID di valore 2 e 3.


Query di Aggiornamento

– Aumentare di 1000 le retribuzioni dei dipendenti della Produzione


Limitare l’output di una query

I tre dipendenti con le retribuzioni più elevate

TOP 3 potrebbe restituire 4 righe!


Interrogare una query (1)

• Qual è il dipartimento che spende di più in stipendi?

• La query StipendiPerDipartimento:

SELECT TOP 1 Dipartimento, Stipendi

FROM StipendiPerDipartimento

ORDER BY Stipendi DESC;

Nelle interrogazioni si

possono usare sia tabelle che

query come origine dei dati


Interrogare una query (2)

• Forme alternative alla precedente query:

• Ma l’interrogazione più facile da progettare (e più elegante) è:


FROM (SELECT Dipartimento, SUM(Stipendio) AS Stipendi

FROM Impiegati

GROUP BY Dipartimento)


SELECT TOP 1 Dipartimento, SUM(Stipendio) AS Stipendi

FROM Impiegati

GROUP BY Dipartimento

ORDER BY Stipendi DESC; ORDER BY SUM(Stipendio) DESC




Come il DBMS

esegue una query

su query


Interrogazioni nidificate


Query nidificate (1)

• Nome, Cognome e Dipartimento del dipendente con lo stipendio massimo

• Perché non cortocircuitare le due interrogazioni?

Noto lo stipendio massimo ..



Una costante in una clausola WHERE può essere rimpiazzata

con l’interrogazione che genera tale costante

SELECT Nome, Cognome, Dipartimento

FROM Impiegati

WHERE Stipendio = ( SELECT MAX(Stipendio)

FROM Impiegati );

Sottointerrogazione: deve essere racchiusa da parentesi.

L’interrogazione è una sola e c’è

un solo ; finale.



• Cognome, Nome e Stipendio degli impiegati con stipendio inferiore alla

media degli stipendi degli altri impiegati

• Il dipartimento che spende di più in stipendi (problema già risolto)

SELECT Cognome, Nome, Stipendio

FROM Impiegati

WHERE Stipendio < ( SELECT AVG(Stipendio)

FROM Impiegati );

SELECT Dipartimento, Stipendi


WHERE Stipendi = ( SELECT MAX(Stipendi)

FROM StipendiPerDipartimento );

Quarto modo



• Sottointerrogazioni che restituiscono un elenco di valori: Nome, Cognome e

Stipendio dei dipendenti che sono manager.

L’elenco di valori che compare nel predicato IN, in una clausola

WHERE, può essere sostituito dalla sottointerrogazione che lo

genera.

Restituisce l’elenco di valori: 10,12,13,14

Perché non cortocircuitare le

due query?



Bisogna usare un comando SELECT anche nelle query nidificate in modalità QBE


Numero di valori diversi

• Da quante differenti città di residenza provengono i dipendenti del

dipartimento Produzione?

• Bisogna ricorrere a una query annidata nella clausola FROM

SELECT COUNT(DISTINCT Residenza)

FROM Impiegati

WHERE Dipartimento = 'Prod';Sintassi non ammessa

nell’SQL di Access

SELECT COUNT(*)

FROM ( SELECT DISTINCT Residenza

FROM Impiegati

WHERE Dipartimento = 'Prod');


Ricerca di valori duplicati

• Chi sono i manager responsabili di più di un dipartimento?

SELECT Manager, Descrizione

FROM Dipartimenti

WHERE Manager IN ( SELECT Manager FROM Dipartimenti

GROUP BY Manager

HAVING Count(*)>1 )

ORDER BY Manager;

Query di ricerca duplicati di Access


Osservazioni (1)

• Interrogazioni basate su altre interrogazioni e interrogazioni annidate

permettono di risolvere problemi impossibili da risolvere diversamente.

Esempio: qual è il dipartimento con il maggior numero di impiegati?

• Viste Logiche sono tabelle virtuali ottenute da interrogazioni su altre tabelle.

In Access non ci sono Viste Logiche ma, potendo interrogare le query, è

come se ci fossero.

• Con le Viste Logiche si realizza l’indipendenza logica dei dati

• Le viste logiche sono un utile strumento concettuale per costruire

interrogazioni:

– se avessi una tabella fatta così allora potrei …

– se c’è la si usa se non c’è la si costruisce con una query …

SELECT Dipartimento, Dipendenti


WHERE Dipendenti = ( SELECT MAX(Dipendenti)

FROM StipendiPerDipartimento );

Nei confronti la sottoquery deve apparire come

secondo termine. La forma:

WHERE ( SELECT AVG(Stipendio) FROM

Impiegati ) > Stipendio

non è ammessa!


Osservazioni (2)

• Anche le interrogazioni con sottointerrogazioni sono un utile strumento

concettuale per costruire interrogazioni

• In alcune query le sottointerrogazioni sono indispensabili perché non si

possono usare funzioni di aggregazione nella clausola WHERE …

• Sono utili anche per la costruzione “alternativa” di interrogazioni …

• Soluzione “alternativa” all’interrogazione relazionale:

SELECT Nome, Cognome

FROM Impiegati

WHERE ID = ( SELECT Manager

FROM Dipartimenti

WHERE Descrizione = 'Personale‘ );

Chi è il manager del “Personale”?

SELECT Nome, Cognome

FROM Impiegati I, Dipartimenti D

WHERE I.ID = D.Manager AND D.Descrizione = 'Personale';


Query e Indipendenza logica (1)

La query

StudIng

La query

StudEco


Query e Indipendenza logica (2)

• Con StudIng e StudEco si accede in modo riservato e specializzato

all’archivio Studenti

• I comandi SQL per ottenere StudIng e StudEco:

• Se lo schema di Studenti cambia con l’aggiunta di nuovi campi, cosa

succede alle tabelle visualizzate dalle due query (viste logiche)?


La normalizazione in pratica (1)

SELECT Prodotto,

Magazzino,

Quantità INTO Inventario

FROM InventarioNonNorm;

SELECT DISTINCT Magazzino,

IndirizzoMagazzino

INTO Magazzini

FROM InventarioNonNorm;

Da InventarioNonNorm a

due tabelle normalizzate


La normalizazione in pratica (2)

• Per ricostruire le informazioni contenute in InventarioNonNorm

• Come prevenire situazioni come quella in figura?

SELECT Prodotto, M.Magazzino, Quantità, IndirizzoMagazzino

FROM Inventario AS I, Magazzini AS M

WHERE I.Magazzino = M.Magazzino;

?


Esercizi




• Qual è la rivista che costa di più?

• In quale città è stato sottoscritto il maggior numero di abbonamenti nel 2013?

• Quali sono le riviste mensili di prezzo inferiore al prezzo medio di tali riviste?

• Qual è la rivista settimanale meno costosa?

• In quale città è stato sottoscritto il maggior numero di abbonamenti?

• Quale rivista ha ottenuto i maggiori ricavi?

• Quale rivista ha ottenuto il maggior numero di abbonamenti?

• Quante riviste hanno prezzo superiore al prezzo medio?

• Chi è l’abbonato che ha sottoscritto il primo abbonamento? (cioè l’abbonamento più in

là nel tempo?)

• Da chi è stato sottoscritto l’ultimo abbonamento a una rivista di cui si conosce il

codice?


Linguaggio SQL2 - Fine

Grazie per la vostra attenzione

Corso di Informatica - · Corso di Informatica –Dipartimento di Scienze Aziendali, Economiche e...

Documents

Transcript of Corso di Informatica - · Corso di Informatica –Dipartimento di Scienze Aziendali, Economiche e...