REPÀS: selects (BD FIB)

Qüestió 1 (10%)

Donar una sentència SQL per obtenir per cada mòdul on hi hagi despatxos, la durada mitjana de les assignacions finalitzades (instantFi diferent de null) a despatxos del mòdul. El resultat ha d'estar ordenat ascendentment pel nom del mòdul.

Pel joc de proves que trobareu al fitxer adjunt, la sortida ha de ser:

MODUL		MITJANA_DURADA
Omega 		235.00

SELECT a.modul, AVG(a.instantFi - a.instantInici) AS MITJANA_DURADA
FROM assignacions a, despatxos d
GROUP BY a.modul
HAVING COUNT(a.instantFi IS NOT NULL) >= 2
ORDER BY a.modul ASC




Qüestió 2 (10%)

Doneu una sentència SQL per obtenir els departaments tals que tots els empleats del departament estan assignats a un mateix projecte.
No es vol que surtin a la consulta els departaments que no tenen cap empleat.
Es vol el número, nom i ciutat de cada departament.
El resultat ha d'estar ordenat ascendentment per cadascun dels atributs anteriors.

Pel joc de proves que trobareu al fitxer adjunt, la sortida seria:

Num_dpt		Nom_dpt		Ciutat_dpt
1 		DIRECCIO		BARCELONA

SELECT d.Num_dpt, d.Nom_dpt, d.Ciutat_dpt
FROM Departaments d NATURAL JOIN Empleats e
WHERE 2 > (SELECT COUNT (DISTINCT e.Num_proj)
            FROM Empleats e, Projectes p
            WHERE p.Num_proj = e.Num_proj AND e.num_dpt = d.num_dpt)
ORDER BY d.Num_dpt, d.Nom_dpt, d.Ciutat_dpt ASC



 Qüestió 3 (10%)

Doneu una sentència SQL per obtenir el número i el nom dels empleats que viuen a la mateixa ciutat on està situat el departament on treballen

Pel joc de proves que trobareu al fitxer adjunt, la sortida seria:

num_empl		nom_empl
3		ROBERTO

SELECT e.Num_empl, e.Nom_empl
FROM Empleats e, Departaments d
WHERE e.ciutat_empl = d.ciutat_dpt AND e.num_dpt = d.num_dpt



Qüestió 4 (10%)

Donar una sentència SQL per obtenir els professors que tenen alguna assignació finalitzada (instantFi diferent de null) a un despatx amb superfície superior a 15 i que cobren un sou inferior o igual a la mitjana del sou de tots els professors. En el resultat de la consulta ha de sortir el dni del professor, el nom del professor, i el darrer instant en què el professor ha estat assignat a un despatx amb superfície superior a 15.

Pel joc de proves que trobareu al fitxer adjunt, la sortida ha de ser:

DNI	NomProf	Darrer_instant
111 	toni 	344

SELECT DISTINCT p.dni, p.NomProf, a.instantFi AS Darrer_instant
FROM professors p NATURAL JOIN despatxos d NATURAL JOIN assignacions a
WHERE (a.instantFi IS NOT NULL AND
       d.superficie > 15 AND
       p.sou <= (SELECT AVG(p.sou)
                 FROM professors p))





Qüestió 5 (10%)

Doneu una sentència SQL per obtenir el nom dels professors que o bé se sap el seu número de telèfon (valor diferent de null) i tenen un sou superior a 2500, o bé no se sap el seu número de telèfon (valor null) i no tenen cap assignació a un despatx amb superfície inferior a 20.

Pel joc de proves que trobareu al fitxer adjunt, la sortida seria:

NomProf
toni

-->> NI ZORRA



Qüestió 6 (10%)

Doneu una sentència SQL per obtenir el número i nom dels departaments que tenen 2 o més empleats que viuen a la mateixa ciutat.

Pel joc de proves que trobareu al fitxer adjunt, la sortida seria:

NUM_DPT		NOM_DPT
3 		MARKETING

SELECT DISTINCT d.Num_dpt, d.Nom_dpt
FROM Departaments d NATURAL INNER JOIN Empleats e
GROUP BY  e.ciutat_empl, d.num_dpt
HAVING 2 <= COUNT(*)




Qüestió 7 (5%)

Doneu una sentència SQL per obtenir el número i nom dels departaments tals que tots els seus empleats viuen a MADRID. El resultat no ha d'incloure aquells departaments que no tenen cap empleat.

Pel joc de proves que trobareu al fitxer adjunt, la sortida seria:

Num_dpt		Nom_dpt
3 		MARKETING

SELECT d.num_dpt, d.nom_dpt
FROM Departaments d NATURAL INNER JOIN Empleats e
WHERE d.num_dpt NOT IN (SELECT d1.num_dpt
                        FROM departaments d1
                        WHERE d1.num_dpt = d.num_dpt AND e.ciutat_empl <> 'MADRID' AND e.num_dpt = d1.num_dpt)

GROUP BY num_dpt



Qüestió 8 (5%)

Doneu una sentència SQL per obtenir el número i nom dels departaments que tenen dos o més empleats que viuen a ciutats diferents.

Pel joc de proves que trobareu al fitxer adjunt, la sortida seria:

NUM_DPT		NOM_DPT
3 		MARKETING

SELECT d.num_dpt, d.nom_dpt
FROM Departaments d NATURAL INNER JOIN Empleats e
GROUP BY d.num_dpt
HAVING COUNT (DISTINCT e.ciutat_empl) >= 2



Qüestió 9 (5%)

Doneu una sentència SQL per obtenir les ciutats on hi viuen empleats però no hi ha cap departament.

Pel joc de proves que trobareu al fitxer adjunt, la sortida seria:

CIUTAT_EMPL
GIRONA

SELECT DISTINCT e.Ciutat_empl
FROM Empleats e
WHERE e.Ciutat_empl NOT IN (SELECT d.ciutat_dpt
                            FROM Departaments d)



Qüestió 10 (5%)

Doneu una sentència SQL per obtenir el número i el nom dels departaments que no tenen cap empleat que visqui a MADRID.

Pel joc de proves que trobareu al fitxer adjunt, la sortida seria:

NUM_DPT		NOM_DPT
3 		MARKETING

SELECT d.num_dpt, d.nom_dpt
FROM departaments d
WHERE NOT EXISTS (SELECT e.ciutat_empl
                  FROM empleats e
                  WHERE e.ciutat_empl = 'MADRID' AND e.num_dpt = d.num_dpt)




Qüestió 11 (5%)

Doneu una sentència SQL per obtenir els números i els noms dels projectes que tenen assignats dos o més empleats.
Cal ordenar el resultat descendement per número de projecte.

Pel joc de proves que trobareu al fitxer adjunt, la sortida seria:

NUM_PROJ		NOM_PROJ
1 		IBDTEL

SELECT p.num_proj, p.nom_proj
FROM empleats e NATURAL INNER JOIN projectes p
GROUP BY p.num_proj
HAVING COUNT(*) >= 2
ORDER BY p.num_proj DESC




Qüestió 12 (5%)

Doneu una sentència SQL per obtenir el nom dels empleats que guanyen el sou més alt. Cal ordenar el resultat descendenment per nom de l'empleat.

Pel joc de proves que trobareu al fitxer adjunt, la sortida seria:

NOM_EMPL
JOAN

SELECT DISTINCT e.nom_empl
FROM empleats e
WHERE e.sou >= (SELECT MAX(sou)
                FROM empleats)




Qüestió 13 (5%)

Obtenir per cada departament situat a MADRID la mitjana dels sous dels seus empleats. Concretament, cal donar el número de departament, el nom de departament i la mitjana del sou.

Pel joc de proves que trobareu al fitxer adjunt, la sortida ha de ser:

NUM_DPT		NOM_DPT		SOU
5		VENDES		250000.00

SELECT d.num_dpt, d.nom_dpt, AVG(e.sou) AS SOU
FROM empleats e NATURAL INNER JOIN departaments d
WHERE d.ciutat_dpt = 'MADRID'
GROUP BY d.num_dpt




Qüestió 14 (5%)

Doneu una sentència SQL per obtenir els números i els noms dels departament situats a MADRID, que tenen empleats que guanyen més de 200000.
Pel joc de proves que trobareu al fitxer adjunt, la sortida ha de ser:

NUM_DPT		NOM_DPT
5		VENDES

SELECT DISTINCT d.num_dpt, d.nom_dpt
FROM departaments d NATURAL INNER JOIN empleats e
WHERE d.ciutat_dpt = 'MADRID' AND e.sou >= 200000