BD - REPÀS: selects

-- 1)

SELECT a.modul, AVG(a.instantFi - a.instantInici)
FROM assignacions a
WHERE a.instantFi IS NOT NULL
GROUP BY a.modul
ORDER BY a.modul ASC

-- 2)

SELECT d.NUM_DPT, d.NOM_DPT, d.CIUTAT_DPT
FROM DEPARTAMENTS d
WHERE 1 = (SELECT COUNT(DISTINCT e.NUM_PROJ)
       FROM EMPLEATS e
       WHERE e.NUM_DPT = d.NUM_DPT);

-- 3)

SELECT e.NUM_EMPL, e.NOM_EMPL
FROM EMPLEATS e, DEPARTAMENTS d
WHERE e.NUM_DPT = d.NUM_DPT AND e.CIUTAT_EMPL = d.CIUTAT_DPT;

-- 4)

SELECT p.dni, p.nomProf, MAX(a.InstantFi)
FROM professors p, assignacions a, despatxos d
WHERE p.dni = a.dni AND a.instantFi IS NOT NULL AND
      a.modul = d.modul AND a.numero = d.numero AND
      d.superficie > 15 AND p.sou <= (SELECT AVG(p1.sou)
                      FROM professors p1)
GROUP BY p.dni, p.nomProf;

-- 5)

SELECT DISTINCT p.nomProf
FROM professors p
WHERE (p.telefon IS NOT NULL AND p.sou > 2500) OR
      (p.telefon IS NULL AND NOT EXISTS(SELECT *
                                        FROM assignacions a, despatxos d
                                        WHERE a.dni = p.dni AND (d.modul = a.modul AND d.numero = a.numero AND d.superficie < 20)));

-- 6)

SELECT DISTINCT d.NUM_DPT, d.NOM_DPT
FROM DEPARTAMENTS d, EMPLEATS e
WHERE d.NUM_DPT = e.NUM_DPT
GROUP BY d.NUM_DPT, e.CIUTAT_EMPL
HAVING 2 <= COUNT(*);

-- 7)

SELECT d.NUM_DPT, d.NOM_DPT
FROM DEPARTAMENTS d
WHERE (EXISTS(SELECT *
              FROM EMPLEATS e
              WHERE e.NUM_DPT = d.NUM_DPT)
       AND NOT EXISTS(SELECT *
                      FROM EMPLEATS e
                      WHERE e.NUM_DPT = d.NUM_DPT AND e.CIUTAT_EMPL != 'MADRID'));

-- 8)

SELECT d.NUM_DPT, d.NOM_DPT
FROM DEPARTAMENTS d
WHERE 2 <= (SELECT COUNT(DISTINCT e.CIUTAT_EMPL)
            FROM EMPLEATS e
            WHERE e.NUM_DPT = d.NUM_DPT);

-- 9)

SELECT DISTINCT e.CIUTAT_EMPL
FROM EMPLEATS e
WHERE NOT EXISTS(SELECT *
                 FROM DEPARTAMENTS d
                 WHERE e.CIUTAT_EMPL = d.CIUTAT_DPT);

-- 10)

SELECT d.NUM_DPT, d.NOM_DPT
FROM DEPARTAMENTS d
WHERE NOT EXISTS(SELECT *
                 FROM EMPLEATS e
                 WHERE e.NUM_DPT = d.NUM_DPT AND e.CIUTAT_EMPL = 'MADRID');

-- 11)

SELECT p.NUM_PROJ, p.NOM_PROJ
FROM PROJECTES p
WHERE (2 <= (SELECT COUNT(*)
             FROM EMPLEATS e
             WHERE e.NUM_PROJ = p.NUM_PROJ))
ORDER BY p.NUM_PROJ DESC;

-- 12)

SELECT DISTINCT e.NOM_EMPL
FROM EMPLEATS e
WHERE e.sou = (SELECT(MAX(d.sou)) FROM EMPLEATS d)
ORDER BY e.NOM_EMPL DESC;

-- 13)

SELECT d.NUM_DPT, d.NOM_DPT, AVG(e.sou)
FROM DEPARTAMENTS d, EMPLEATS e
WHERE(d.ciutat_dpt = 'MADRID' AND e.NUM_DPT = d.NUM_DPT)
GROUP BY(d.NUM_DPT);

-- 14)

SELECT DISTINCT d.NUM_DPT, d.NOM_DPT
FROM EMPLEATS e, DEPARTAMENTS d
WHERE (d.ciutat_dpt = 'MADRID' AND e.num_dpt = d.num_dpt AND e.SOU > 200000);

-- JosepRivaille