CodRep

7. ESERCITAZIONI: Algebra relazionale e QUERY
E’ dato il seguente diagramma E/R di esempio:
Dirige
Descrizione
CodRep
Dirige
E’Diretto
N
1
N
Dipendente
Impiega
E’Impiegato
1
Impiega
Località
Reparto
CodDip
Stipendio
Nome
Cognome
Livello
Si ricava il seguente SCHEMA RELAZIONALE (MAPPING DEL DIAGRAMMA ER)
Reparto (CodRep, Descrizione, Localita)
Dipendente (CodDip, Cognome, Nome, Livello, Stipendio, CodCapo, CodRep1)
Con l’attributo “CodCapo” FK sull’attributo “CodDip” della relazione “Dipendente”
Con l’attributo “CodRep1” FK sull’attributo “CodRep” della relazione “Reparto”
VRCodRep (Reparto)  VRCodRep1 (Dipendente) deriva dalla TOTALITA’ dell’associazione diretta “Impiega”
Sia data, inoltre la seguente istanza di esempio della base di dati di riferimento in un certo istante di
tempo t:
Reparto CodRep
R-01
R-02
R-03
R-04
Dipendente
Descrizione
MAGAZZINO
PRODUZIONE
AMMINISTRAZIONE
DIREZIONE
CodDip
D-01
D-02
D-03
D-04
D-05
D-06
D-07
D-08
Localita
AVELLINO
NAPOLI
ROMA
ROMA
Cognome
Nome
ROSSI
MARIO
VERDI
GIANNI
GIALLI
MARTA
BIANCHI
ALFREDO
NERI
ETTORE
ROCCHI
ANNA
VANNI
ALFREDO
SGARBI
VITTORIO
Livello
8
7
7
6
6
6
8
6
Stipendio
2010,50
1340,75
1230,15
1001,25
990,00
912,25
2130,45
930,25
CodCapo
NULL
D-07
D-07
D-03
D-03
D-02
D-01
D-03
CodRep1
R-04
R-02
R-01
R-01
R-01
R-03
R-02
NULL
TESTO DELLE QUERY
Risolvere le seguenti interrogazioni utilizzando, se possibile, l’algebra relazionale e tradurle in SQL:
Q1. Elenco di tutti i dipendenti;
Q2. Elenco di tutti i dipendenti di livello 6;
Q3. Visualizzare Cognome e Nome di tutti i dipendenti che lavorano nel reparto con codice “R01”;
Q4. Elencare tutti i possibili livelli dei dipendenti;
Q5. Elencare tutti i dipendenti il cui cognome inizia con la lettera ‘R’;
Q6. Elencare tutti i dipendenti che guadagnano tra 900 e 1300 euro;
Q7. Mostrare solo il cognome, il nome e lo stipendio percepito dei dipendenti in ordine
decrescente di livello e crescente per cognome e nome;
Q8. Visualizzare la media degli stipendi, lo stipendio più alto e quello più basso tra tutti i
dipendenti;
Q9. Visualizzare il numero di dei dipendenti presenti per ciascun reparto;
Q10. Visualizzare la somma complessiva degli stupendi di tutti i dipendenti di livello 8;
Q11. Visualizzare il numero di dipendenti presenti per ciascun livello (solo se esso è maggiore
o uguale a 2);
Q12. Visualizzare la media degli stipendi percepiti dai dipendenti per ciascun livello;
Q13. Visualizzare i dipendenti che guadagnano esattamente lo stipendio minimo/massimo
(oppure che guadagnano uno stipendio strettamente minore oppure strettamente maggiore
rispetto alla media complessiva degli stipendi);
Q14. Visualizzare il codice ed il cognome e nome di tutti i dipendenti che lavorano ad Avellino;
Q15. Per ogni reparto fornire le informazioni dei dipendenti che vi lavorano visualizzando
anche i reparti privi al momento di dipendenti;
Q16. Per ogni dipendente fornire le informazioni dei reparti nei quali lavorano visualizzando
anche i dipendenti privi al momento di reparto;
Q17. Per ogni dipendente fornire cognome e nome nonché cognome e nome del suo capo.
SOLUZIONI
Q1. Elenco di tutti i dipendenti
SQL
SELECT *
FROM Dipendente;
Q2. Elenco di tutti i dipendenti di livello 6
SQL
SELECT *
FROM Dipendente
WHERE Livello = 6;
Algebra relazionale

Livello = 6
(Dipendente)
Q3. Visualizzare Cognome e Nome di tutti i dipendenti che lavorano nel reparto con
codice “R-01”
SQL
SELECT Cognome, Nome
FROM Dipendente
WHERE CodRep1 = “R-01”;
Algebra relazionale
Cognome, Nome (CodRep1 =”R-01” (Dipendente))
Q4. Elencare tutti i possibili livelli dei dipendenti
SQL
SELECT DISTINCT Livello
FROM Dipendente;
Algebra relazionale
Livello (Dipendente)
Q5. Elencare tutti i dipendenti il cui cognome inizia con la lettera ‘V’
SQL
SELECT *
FROM Dipendente
WHERE Cognome LIKE “V*”;
Algebra relazionale

Cognome LIKE “V*”
(Dipendente)
Q6. Elencare tutti i dipendenti che guadagnano tra 900 e 1300 euro
SQL
SELECT *
FROM Dipendente
WHERE Stipendio BETWEEN 900 AND 1300;
Algebra relazionale

(Stipendio >= 900) AND (Stipendio <= 1300)
(Dipendente)
Q7. Mostrare solo il cognome, il nome e lo stipendio percepito dei dipendenti in ordine
decrescente di livello e crescente per cognome e nome
SQL
SELECT Livello, Cognome, Nome, Stipendio
FROM Dipendente
ORDER BY Livello DESC, Cognome, Nome;
Q8. Visualizzare la media degli stipendi, lo stipendio più alto e quello più basso tra tutti i
dipendenti
SQL
SELECT AVG(Stipendio) AS media, MAX(Stipendio) AS Massimo, MIN(Stipendio) AS min
FROM Dipendente;
Q9. Visualizzare il numero di dei dipendenti presenti per ciascun reparto
SQL
SELECT CodRep1, COUNT(*) AS numdip
FROM Dipendente
[WHERE CodRep1 <> NULL]
GROUP BY CodRep1;
Q10. Visualizzare la somma complessiva degli stupendi di tutti i dipendenti di livello 8
SQL
SELECT SUM(Stipendio) AS totale
FROM Dipendente
WHERE Livello = 8;
Q11. Visualizzare il numero di dipendenti presenti per ciascun livello (solo se esso è
maggiore o uguale a 2)
SQL
SELECT Livello, COUNT(*) AS numdip
FROM Dipendente
GROUP BY Livello
HAVING COUNT(*) >= 2;
Q12. Visualizzare la media degli stipendi percepiti dai dipendenti per ciascun livello
SQL
SELECT Livello, AVG(Stipendio) AS mediastip
FROM Dipendente
GROUP BY Livello;
Q13.
Visualizzare
i
dipendenti
che
guadagnano
esattamente
lo
stipendio
minimo/massimo (oppure che guadagnano uno stipendio strettamente minore oppure
strettamente maggiore rispetto alla media complessiva degli stipendi)
SQL
SELECT Cognome, Nome
FROM Dipendente
WHERE Stipendio = (SELECT MIN(Stipendio)
FROM Dipendente);
Indirizzo
Q14. Visualizzare il codice ed il cognome e nome di tutti i dipendenti che lavorano ad
Avellino
SQL
SELECT CodDip, Cognome, Nome
FROM Dipendente, Reparto
WHERE (Dipendente.CodRep1=Reparto.CodRep)
AND Reparto.Localita = “AVELLINO”;
SELECT CodDip, Cognome, Nome
FROM Dipendente INNER JOIN Reparto ON Dipendente.CodRep1=Reparto.CodRep
WHERE Reparto.Localita = “AVELLINO”;
Algebra relazionale
CodDip,Cognome, Nome (Localita =”AVELLINO” (Dipendente 
Reparto))
Dipendente.CodRep1 = Reparto.CodRep
CodDip,Cognome, Nome (Dipendente  Localita =”AVELLINO” (Reparto))
Dipendente.CodRep1 = Reparto.CodRep
Q15. Per ogni reparto fornire le informazioni dei dipendenti che vi lavorano visualizzando
anche i reparti privi al momento di dipendenti
SQL
SELECT CodRep, Descrizione, Localita, CodDip, Cognome, Nome
FROM Reparto LEFT JOIN Dipendente ON Reparto.CodRep = Dipendente.CodRep1;
Algebra relazionale
SX
 CodRep, Descrizione, Localita, CodDip, Cognome, Nome (Reparto  Dipendente))
Reparto.CodRep = Dipendente.CodRep1
Q16. Per ogni dipendente fornire le informazioni dei reparti nei quali lavorano
visualizzando anche i dipendenti privi al momento di reparto
SQL
SELECT CodDip, Cognome, Nome, CodRep, Descrizione, Localita
FROM Dipendente LEFT JOIN Reparto ON Dipendente.CodRep1 = Reparto.CodRep;
Algebra relazionale
SX
 CodDip, Cognome, Nome CodRep, Descrizione, Localita, (Dipendente  Reparto))
Dipendente.CodRep1 = Reparto.CodRep
Q17. Per ogni dipendente fornire cognome e nome nonché cognome e nome del suo capo
SQL
SELECT t1.CodDip, t1.Cognome, t1.Nome, t2.CodDip, t2.Cognome, t2.Nome
FROM Dipendente AS t1, Dipendente AS t2
WHERE t1.CodDip = t2.CodCapo;
SELECT t1.CodDip, t1.Cognome, t1.Nome, t2.CodDip, t2.Cognome, t2.Nome
FROM Dipendente AS t1 SELF JOIN Dipendente AS t2 ON t1.CodDip = t2.CodCapo;
Algebra relazionale
 t1.CodDip, t1.Cognome, t1.Nome, t2.CodDip, t2.Cognome, t2.Nome (Dipendente  Dipendente))
t1.CodDip = t2.CodDip