Dipendenti e mansioni

Il problema

Schema relazionale

• DIPENDENTI(IDdipendente, cognome, nome, stipendio, mansione)
• MANSIONI(IDmansione, nome, tariffa_oraria)

DIPENDENTI.mansione è chiave esterna collegata MANSIONI.IDmansione

Query

1. Stipendio medio

   SELECT AVG(stipendio) AS [Stipendio medio]
   FROM   DIPENDENTI

2. Stipendio medio per mansione

   SELECT   MANSIONI.IDmansione, MANSIONI.nome, AVG(stipendio) AS [Stipendio medio]
   FROM     MANSIONI INNER JOIN DIPENDENTI ON MANSIONI.IDmansione = DIPENDENTI.mansione
   GROUP BY MANSIONI.IDmansione, MANSIONI.nome

3. Stipendio medio per mansione, che supera la media di tutti i dipendenti

   SELECT   MANSIONI.IDmansione, MANSIONI.nome, AVG(stipendio) AS [Stipendio medio]
   FROM     MANSIONI INNER JOIN DIPENDENTI ON MANSIONI.IDmansione = DIPENDENTI.mansione
   GROUP BY MANSIONI.IDmansione, MANSIONI.nome
   HAVING   AVG(stipendio) > (SELECT AVG(stipendio)
                              FROM DIPENDENTI)

4. I dipendenti con stipendio superiore al “top manager” dell’azienda

   SELECT *
   FROM   DIPENDENTI
   WHERE  stipendio > (SELECT stipendio
                       FROM   DIPENDENTI INNER JOIN MANSIONI ON DIPENDENTI.mansione = MANSIONI.IDmansione
                       WHERE  MANSIONI.nome = "top manager")

5. oppure

   SELECT D1.*
   FROM   DIPENDENTI AS D1, DIPENDENTI AS D2, MANSIONI
   WHERE  (D1.stipendio > D2.stipendio) AND (D2.mansione = MANSIONI.IDmansione) AND
          (MANSIONI.nome = "top manager")

6. oppure

   SELECT D1.*
   FROM   (DIPENDENTI AS D1 INNER JOIN DIPENDENTI AS D2 ON D1.stipendio > D2.stipendio)
          INNER JOIN MANSIONI ON D2.mansione = MANSIONI.IDmansione
   WHERE  MANSIONI.nome = "top manager"

7. Chi ha uno stipendio minore di 1500 € e ha la stessa mansione di chi ha uno stipendio maggiore di 1500 €

   SELECT *
   FROM   DIPENDENTI
   WHERE  (stipendio <= 1500) AND
          (mansione = ANY (SELECT mansione
                           FROM DIPENDENTI
                           WHERE stipendio > 1500))

8. oppure

   SELECT *
   FROM   DIPENDENTI
   WHERE  (stipendio <= 1500) AND
          (mansione IN (SELECT mansione
                        FROM DIPENDENTI
                        WHERE stipendio > 1500))

9. Chi ha uno stipendio superiore a quello di tutti gli autisti

   SELECT *
   FROM   DIPENDENTI
   WHERE  stipendio > ALL (SELECT stipendio
                           FROM DIPENDENTI INNER JOIN MANSIONI ON DIPENDENTI.mansione = MANSIONI.IDmansione
                           WHERE MANSIONI.nome = "autista")

10. oppure

    SELECT *
    FROM   DIPENDENTI
    WHERE  stipendio > (SELECT MAX(stipendio)
                        FROM DIPENDENTI INNER JOIN MANSIONI ON DIPENDENTI.mansione = MANSIONI.IDmansione
                        WHERE MANSIONI.nome = "autista")

11. Tutto sui dipendenti con tariffa oraria superiore a quella degli impiegati

    SELECT DIPENDENTI.*
    FROM   DIPENDENTI INNER JOIN MANSIONI ON DIPENDENTI.mansione = MANSIONI.IDmansione
    WHERE  tariffa_oraria > ALL (SELECT tariffa_oraria
                                 FROM MANSIONI
                                 WHERE nome LIKE "impiegato*")

12. oppure

    SELECT DIPENDENTI.*
    FROM   DIPENDENTI INNER JOIN MANSIONI ON DIPENDENTI.mansione = MANSIONI.IDmansione
    WHERE  tariffa_oraria > (SELECT MAX(tariffa_oraria)
                            FROM MANSIONI
                            WHERE nome LIKE "impiegato*")

13. Tutto sui dipendenti con tariffa oraria superiore alla tariffa media degli impiegati

    SELECT DIPENDENTI.*
    FROM   DIPENDENTI INNER JOIN MANSIONI ON DIPENDENTI.mansione = MANSIONI.IDmansione
    WHERE  tariffa_oraria > (SELECT AVG(tariffa_oraria)
                             FROM MANSIONI
                             WHERE nome LIKE "impiegato*")

14. Tutto sui dipendenti con tariffa oraria superiore a qualche impiegato

    SELECT DIPENDENTI.*
    FROM   DIPENDENTI INNER JOIN MANSIONI ON DIPENDENTI.mansione = MANSIONI.IDmansione
    WHERE  tariffa_oraria > ANY (SELECT (tariffa_oraria)
                                 FROM MANSIONI
                                 WHERE nome LIKE "impiegato*")

15. oppure

    SELECT DIPENDENTI.*
    FROM   DIPENDENTI INNER JOIN MANSIONI ON DIPENDENTI.mansione = MANSIONI.IDmansione
    WHERE  tariffa_oraria > (SELECT MIN(tariffa_oraria)
                             FROM MANSIONI
                             WHERE nome LIKE "impiegato*")

16. I dipendenti con stipendio superiore a quello medio della loro mansione

    SELECT *
    FROM   DIPENDENTI AS D1
    WHERE  stipendio > (SELECT AVG(stipendio)
                        FROM DIPENDENTI AS D2
                        WHERE D2.mansione = D1.mansione)

17. I dipendenti con stipendio tra 1000 € e 2000 € e tariffa oraria superiore a 20 €

    SELECT DIPENDENTI.*
    FROM   DIPENDENTI
    WHERE  (stipendio BETWEEN 1000 AND 2000) AND
           (mansione IN (SELECT IDmansione
                         FROM MANSIONI
                         WHERE tariffa_oraria > 20))

18. oppure

    SELECT DIPENDENTI.*
    FROM   DIPENDENTI
    WHERE  (stipendio BETWEEN 1000 AND 2000) AND
           (mansione = ANY (SELECT IDmansione
                            FROM MANSIONI
                            WHERE tariffa_oraria > 20))

19. Stipendio medio di chi non svolge la mansione né di manager né di impiegato

    SELECT AVG(stipendio)
    FROM   DIPENDENTI
    WHERE  mansione NOT IN (SELECT IDmansione
                            FROM   MANSIONI
                            WHERE  (nome LIKE "*manager*") OR (nome LIKE "impiegato*"))

1. oppure

   SELECT AVG(stipendio)
   FROM   DIPENDENTI
   WHERE  mansione <> ALL (SELECT IDmansione
                           FROM   MANSIONI
                           WHERE  (nome LIKE "*manager*") OR (nome LIKE "impiegato*"))