07/12/2017
1
a cura del Prof.
Claudio Traini
Linguaggio SQL: Linguaggio SQL:
esempi di esempi di queryquery
Modello logico utilizzato
07/12/2017
2
Tabella PERSONALE
Tabella DIPARTIMENTI
07/12/2017
3
Tabella PRODOTTI
Tabella COMPONENTI
07/12/2017
4
Tabella COMPOSIZIONE
QUERY: STRUTTURA GENERALE
SELECT
Elenco dei campi da estrarre (separati da virgole)
FROM
Elenco delle tabelle interessate dalla query (separati da virgole)
WHERE
Condizioni sui valori dei campi
07/12/2017
5
RICORDATI!!!
Il nome di un campo nella forma completa si scrive come
NOME TABELLA . NOME CAMPO
Esempio:Il campo NOMINATIVO della tabella PERSONALE
È identificabile come:
PERSONALE.NOMINATIVO
QUERY: GLI “ALIAS”……….
SELECT
nominativo AS DIPENDENTE
FROM
PERSONALE PER
WHERE
PER.qualifica = ‘tecnico’
“ALIAS” DEL CAMPO
“ALIAS” DELLA TABELLA
Gli alias hanno lo scopo di rendere più agevole la scrittura delle query
07/12/2017
6
Query: NOME E QUALIFICA DEI DIPENDENTI
SELECT nominativo AS DIPENDENTE, qualifica FROM PERSONALE';
Query: L’ELENCO DELLE QUALIFICHE PRESENTI
SELECT DISTINCT qualifica FROM PERSONALE
“ALIAS”
Query: NOME E QUALIFICA DEI DIPENDENTI IN ORDINE ALFABETIC O
SELECT nominativo AS DIPENDENTE, qualifica FROM PERSONALE‘ORDER BY nominativo;
“ALIAS”
07/12/2017
7
Query: IL NOME ED IL PREZZO DEI PRODOTTI REALIZZATIDAL DIPARTIMENTO D2
SELECT nome_prodotto, prezzoFROM PRODOTTIWHERE id_dip = 'D2';
Query: IL NOMINATIVO DI TUTTI I DIPENDENTI LA CUI QUALIFICA E' TECNICO
SELECT nominativoFROM PERSONALEWHERE qualifica = 'tecnico';
07/12/2017
8
Query: NOME E QUALIFICA DEI DIPENDENTI TECNICI O IMPIEGATI
SELECT nominativo AS DIPENDENTE, qualifica AS MANSIONEFROM PERSONALEWHERE qualifica = 'tecnico' OR qualifica ='impiegato';
Query: L’ELENCO DEI DIPENDENTI CHE SICHIAMANO PAOLO
SELECT nominativoFROM PERSONALEWHERE nominativo LIKE 'Paolo*';
Query: IL NOMINATIVO E IL RELATIVO NOME DEL DIPARTIMENTO DI TUTTI I DIPENDENTI LA CUI QUALIFICA E' TECNICO
SELECT P.nominativo, D.nome_dipartimentoFROM PERSONALE P, DIPARTIMENTI DWHERE P.qualifica = 'tecnico'AND P.id_dip = D.id_di p;
07/12/2017
9
Query: IL NOMINATIVO ED IL NOME DEL DIPARTIMENTO DITUTTI I DIRIGENTI
SELECT P.nominativo AS DIRIGENTE, D.nome_dipartimento AS DIPARTIMENTOFROM PERSONALE P, DIPARTIMENTI DWHERE P.qualifica = ‘dirigente'AND P.id_dip = D.id_ dip;
“ALIAS”
Query: IL IL NOMINATIVO, LA QUALIFICA DEI DIPENDENTI CHE LAVORANO NEI DIPARTIMENTI D2, D4 e D5
SELECT id_dip, nominativo, qualifica FROM PERSONALEWHERE id_dip IN ('D2','D4','D5');
07/12/2017
10
Query: L'ELENCO DEI DIPENDENTI CHE SI CHIAMANO MARIOINDICANDO DIPARTIMENTO E SEDE DOVE LAVORANO
SELECT P.matricola AS MATRICOLA, P.nominativo AS DIPENDENTE,D.nome_dipartimento AS DIPARTIMENTO, D.localita AS SEDE, D.provincia AS PROVINCIAFROM PERSONALE P, DIPARTIMENTI DWHERE P.nominativo LIKE 'Mario*'AND P.id_dip = D.id_dip;
Query: L'ELENCO DEI DIPENDENTI ORDINATI IN BASE ALLO STIPENDIO DAL PIU' BASSO AL PIU' ALTO
SELECT nominativo, stipendio FROM PERSONALEORDER BY stipendio;
07/12/2017
11
Query: L'ELENCO DEI DIPENDENTI ORDINATI IN BASE ALLO STIPENDIO COMPRESO TRA € 1.500 e € 2.200
SELECT nominativo AS DIPENDENTE, stipendio FROM PERSONALE WHERE stipendio BETWEEN 1500 AND 2200ORDER BY stipendio;
Query: L'ELENCO DEI DIPENDENTI COMPRENSIVO DI NOME DEL DIPARTIMENTO ORDINATI IN BASE ALLO STIPENDIO COMPRESO TRA € 1.500 e € 2.200
SELECT P.nominativo, P.stipendio, D.nome_dipartimento AS DIPARTIMENTOFROM PERSONALE P, DIPARTIMENTI DWHERE P.id_dip = D.id_dipAND P.stipendio >= 1500 AND P.stipendio <=2200ORDER BY P.stipendio;
07/12/2017
12
Query: L'ELENCO DEI DIPENDENTI COMPRENSIVO DI NOME DEL DIPARTIMENTO ORDINATI IN BASE ALLO STIPENDIO COMPRESO TRA € 1.500 e € 2.200
SELECT P.nominativo, P.stipendio, D.nome_dipartimento AS DIPARTIMENTOFROM PERSONALE P, DIPARTIMENTI DWHERE P.id_dip = D.id_dipAND P.stipendio BETWEEN 1500 AND 2200ORDER BY P.stipendio;
Query: LO STIPENDIO MASSIMO, LO STIPENDIO MINIMO, LO STIPENDIO MEDIO
SELECT MAX(stipendio) AS STIPENDIO_MASSIMO, MIN(stipendio) AS STIPENDIO_MINIMO, AVG(stipendio) AS STIPENDIO_MEDIOFROM PERSONALE
07/12/2017
13
Query: IL NOME DEL DIPENDENTE CHE PERCEPISCELO STIPENDIO MASSIMO
SELECT nominativo, stipendio FROM PERSONALEWHERE stipendio IN (SELECT MAX(stipendio) FROM PERS ONALE);
Query: IL NOME DEL DIPARTIMENTO DOVE SI TROVA IL DIPENDENTE CHE PERCEPISCE LO STIPENDIO MASSIMO
SELECT D.nome_dipartimento, P.stipendio FROM DIPARTIMENTI D, PERSONALE PWHERE P.id_dip = D.id_dip ANDP.stipendio IN (SELECT MAX(stipendio) FROM PERSONAL E);
07/12/2017
14
Query: NOME DEI DIPENDENTI CHE SONO NATI NEGLI ANNI NOVANTA
SELECT nominativo, nascita FROM PERSONALEWHERE nascita BETWEEN #01/01/1990# AND #31/12/1999# ;
Query: NOME DEI PRODOTTI CHE USANO 2 UNITA‘DEL COMPONENTE C005
SELECT PRO.nome_prodottoFROM PRODOTTI PRO, COMPOSIZIONE COM WHERE COM.unita_comp = 2 AND COM.id_comp = 'C005'AND PRO.id_prod = COM.id_prod;
07/12/2017
15
Query: NOME, CODICE E NOME DIPARTIMENTO DEI PRODOTTI CHE USANO 2 UNITA' DEL COMPONENTE C005
SELECT PRO.id_prod AS CODICE, PRO.nome_prodotto AS PRODOTTO, DIP.nome_dipartimento AS DIPARTIMENTOFROM PRODOTTI PRO, COMPOSIZIONE COM, DIPARTIMENTI DIPWHERE COM.unita_comp = 2 AND COM.id_comp = 'C005'AND PRO.id_prod = COM.id_prodAND PRO.id_dip = DIP.id_dip;
Query: NOME DEI DIPARTIMENTI CHE USANO IL COMPONENTE C003
SELECT DIP.nome_dipartimento AS UTILIZZATORIFROM DIPARTIMENTI DIP, COMPOSIZIONE COM, PRODOTTI PROWHERE COM.id_comp = 'C003'AND PRO.id_prod = COM.id_prodAND DIP.id_dip = PRO.id_dip;
07/12/2017
16
Query: LA LISTA DEI PRODOTTI CHE UTILIZZANO LA MASSIMA QUANTITA' DI COMPONENTE C004
SELECT PRO.nome_prodotto AS UTILIZZATORIFROM PRODOTTI PRO, COMPOSIZIONE COMWHERE COM.id_comp = 'C004'AND PRO.id_prod = COM.id_prodAND COM.unita_comp = (SELECT MAX(unita_comp)
FROM COMPOSIZIONE WHERE id_comp = 'C004');
Query: IL NUMERO DI DIPENDENTI DEL DIPARTIMENTO D4
SELECT COUNT(*) AS NUM_DIP_D4 FROM PERSONALEWHERE id_dip = 'D4';
07/12/2017
17
Query: IL NUMERO DI DIPENDENTI PER OGNI DIPARTIMENTO
SELECT id_dip AS CODICE, count(*) AS NUMEROFROM PERSONALEGROUP BY id_dip;
Query: LO STIPENDIO MEDIO PER OGNI DIPARTIMENTO
SELECT id_dip AS DIPARTIMENTO, avg(stipendio) AS STIPENDIO_MEDIOFROM PERSONALEGROUP BY id_dip;
07/12/2017
18
Query: LA QUANTITA’ TOTALE UTILIZZATA DI OGNI COMPONENTEPER LA PRODUZIONE DEI PRODOTTI
SELECT id_comp AS COMPONENTE,SUM(unita_comp) AS Q_TOTALEFROM COMPOSIZIONEGROUP BY id_comp;
Query: L'IMPORTO TOTALE DEGLI STIPENDI CON QUALIFICA DI DIRETTORE
SELECT SUM(stipendio) AS COSTO_DIRIGENTIFROM PERSONALEWHERE qualifica = 'dirigente';
07/12/2017
19
Query: IL NUMERO DEI TECNICI EDIL LORO COSTO COMPLESSIVO
SELECT COUNT(*) AS NUM_TECNICI, SUM(stipendio) AS COSTO_TECNICIFROM PERSONALEWHERE qualifica = 'tecnico';
Query: CODICE DEI PRODOTTI CHE IMPIEGANO ALMENO3 COMPONENTI
SELECT id_prod AS CODICE_PRODOTTOFROM COMPOSIZIONEGROUP BY id_prodHAVING COUNT(*)>=3;
07/12/2017
20
Query: NOME DEI DIPARTIMENTI (ED IL RELATIVO PRODOTTO) I CUI PRODOTTI IMPIEGANO ALMENO 3 COMPONENTI
SELECT D.nome_dipartimento AS DIPARTIMENTO, P.nome_prodotto AS PRODOTTOFROM DIPARTIMENTI D, PRODOTTI PWHERE P.id_dip = D.id_dipAND P.id_prod IN (SELECT id_prod
FROM COMPOSIZIONEGROUP BY id_prodHAVING COUNT(*)>=3);
Query: IL COSTO DI PRODUZIONE DEL PRODOTTO P01 DATO IL COSTO UNITARIO DELLE SINGOLE COMPONENTIPER LA QUANTITA’ IMPIEGATA
SELECT SUM(COMPOS.unita_comp * COM.costo_unitario) AS COST O_P01FROM COMPOSIZIONE COMPOS, COMPONENTI COMWHERE COMPOS.id_comp = COM.id_compAND id_prod='P01'
E se lo volessi per tutti i prodotti?
07/12/2017
21
SELECT
COMPOS.id_prod AS PRODOTTO,
SUM(COMPOS.unita_comp * COM.costo_unitario) AS COST O
FROM COMPOSIZIONE COMPOS, COMPONENTI COM
WHERE COMPOS.id_comp = COM.id_comp
GROUP BY COMPOS.id_prod;
INNER, LEFT, RIGHT …… JOIN …………
07/12/2017
22
E’ in sostanza una sintassi alternativa che permettedi differenziare le condizioni che compaiono nelleInterrogazioni da quelle che sono oggetto del prodottocartesiano sulle tabelle
SELECT lista attributiFROMTabella_1 ….. JOIN Tabella_2 ON condizioni del joinWHERE eventuali altre condizioni
In genere sono così strutturate:
Query: NOME DEI PRODOTTI CHE USANO 2 UNITA‘DEL COMPONENTE C005
SELECT nome_prodotto AS PRODOTTOFROM PRODOTTI INNER JOIN COMPOSIZIONE ON PRODOTTI.id_prod = COMPOSIZIONE.id_prodWHERE unita_comp = 2 AND id_comp = 'C005';
Tramite l’utilizzo dell’operatore INNER JOIN
07/12/2017
23
Query: L’ELENCO DEI DIPARTIMENTI CON I PROPRI IMPIEGATI, L ALORO DATA DI NASCITA E LA MANSIONE
SELECT nome_dipartimento, nominativo, nascita, qual ificaFROM DIPARTIMENTI INNER JOIN PERSONALEON DIPARTIMENTI.id_dip = PERSONALE.id_dip
Supponiamo di aver aggiunto un Settimo Dipartimento che al momentonon ha impiegati e eseguiamo la stessa query
SELECT nome_dipartimento, nominativo, nascita, qual ificaFROM DIPARTIMENTI INNER JOIN PERSONALEON DIPARTIMENTI.id_dip = PERSONALE.id_dip
RISULTATO IDENTICO!
07/12/2017
24
Riformuliamo la query usando l’operatore LEFT JOIN
SELECT nome_dipartimento, nominativo, nascita, qual ificaFROM DIPARTIMENTI LEFT JOIN PERSONALEON DIPARTIMENTI.id_dip = PERSONALE.id_dip
Compare in elenco anche il Settimo Dipartimento
Supponiamo di aver aggiunto un ulteriore tecnico, B runo Bruni nato il 5 maggio del 2000 che al momento non è asse gnato ad alcun dipartimento e riproviamo………
SELECT nome_dipartimento, nominativo, nascita, qual ificaFROM DIPARTIMENTI LEFT JOIN PERSONALEON DIPARTIMENTI.id_dip = PERSONALE.id_dip
Il nuovo dipendente non compare in elenco………
07/12/2017
25
Riformuliamo la query usando l’operatore RIGHT JOIN …….
SELECT nome_dipartimento, nominativo, nascita, qual ificaFROM DIPARTIMENTI RIGHT JOIN PERSONALEON DIPARTIMENTI.id_dip = PERSONALE.id_dip
Compare in elenco il nuovo dipendente…..senza dipart imento
E se di nuovo riusassimo l’operatore INNER JOIN ?
SELECT nome_dipartimento, nominativo, nascita, qual ificaFROM DIPARTIMENTI INNER JOIN PERSONALEON DIPARTIMENTI.id_dip = PERSONALE.id_dip
Non compare il nuovo dipartimento e neppure il nuov odipendente……….
07/12/2017
26
In poche parole……………….
Con l’operatore INNER JOIN abbiamo l’elenco di tutti i dipartimenti ed i relativi dipendenti a patto che non esistano dipartimenti senza dipendentioppure dipendenti senza diprtimenti
Con l’operatore LEFT JOIN abbiamo l’elenco di tutti i dipartimenti ed i relativi dipendenti compresi i dipartimenti senza dipendenti
Con l’operatore RIGHT JOIN abbiamo l’elenco dei dipartimenti che abbianoalmeno un dipendente compresi i dipendenti sprovvisti di dipartimento
DIPARTIMENTI JOIN PERSONALELeft Right
Left: la tabella DIPARTIMENTI ha priorità (sta a sinistra)Righ: la tabella PERSONALE ha priorità (sta a destra)