Alguns exercicis no fets a classe (BD FIB)

1. 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.

SELECT e.num_empl, e.nom_empl
FROM empleats e NATURAL INNER JOIN departaments d
WHERE e.ciutat_empl = d.ciutat_dpt;

2. 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.

SELECT d.modul,  AVG(a.instantFi - a.instantInici) AS mitjana_durada
FROM assignacions a NATURAL INNER JOIN despatxos d
GROUP BY d.modul
ORDER BY d.modul ASC;

3. 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.

SELECT DISTINCT d.num_dpt, d.nom_dpt, d.ciutat_dpt
FROM departaments d NATURAL INNER JOIN empleats e
WHERE 1 = (SELECT COUNT (DISTINCT e1.num_proj)
           FROM empleats e1 NATURAL INNER JOIN projectes p
           WHERE e1.num_dpt = d.num_dpt)
ORDER BY d.num_dpt, d.nom_dpt, d.ciutat_dpt ASC;

4. Suposem la base de dades que podeu trobar al fitxer adjunt.
Suposem que aquesta base de dades està en un estat on no hi ha cap fila.
Doneu una seqüència de sentències SQL d'actualització (INSERTs i/o UPDATEs) que violi la integritat d'entitat de la taula Empleats al'intentar duplicar la primary key d'aquesta taula de l'empleat 21.
Les sentències NOMÉS han de violar aquesta restricció.

INSERT INTO  EMPLEATS VALUES (21,NULL,NULL,NULL,NULL,NULL),(21,NULL,NULL,NULL,NULL,NULL);

5. Doneu una sentència d'inserció de files a la taula cost_ciutat que l'ompli a partir del contingut de la resta de taules de la base de dades. Tingueu en compte el següent: Cal inserir una fila a la taula cost_ciutat per cada ciutat on hi ha un o més departaments, i que cap departament de la ciutat tingui empleats.
Per tant, només s'han d'inserir les ciutats on cap dels departaments situats a la ciutat tinguin empleats.
El valor de l'atribut cost ha de ser 0.

INSERT INTO cost_ciutat (
	SELECT DISTINCT d.ciutat_dpt, 0
	FROM departaments d
	WHERE NOT EXISTS (SELECT * FROM departaments d1 NATURAL INNER JOIN empleats e WHERE d.ciutat_dpt = d1.ciutat_dpt));

6. Tenint en compte l'esquema de la BD que s'adjunta, proposeu una sentència de creació de la taula següent:

    presentacioTFG(idEstudiant, titolTFG, dniDirector, dniPresident, dniVocal, instantPresentacio, nota)

Hi ha una fila de la taula per cada treball final de grau (TFG) que estigui pendent de ser presentat o que ja s'hagi presentat.

En la creació de la taula cal que tingueu en compte que:
- L'identificador de l'estudiant i el títol del TFG són chars de 100 caràcters.
- Un estudiant només pot presentar un TFG.
- No hi pot haver dos TFG amb el mateix títol.
- Tot TFG ha de tenir un títol.
- El director, el president i el vocal han de ser professors que existeixin a la base de dades.
- El director del TFG no pot estar en el tribunal del TFG (no pot ser ni president, ni vocal).
- El president i el vocal no poden ser el mateix professor.
- L'instant de presentació ha de ser un enter diferent de nul.
- La nota ha de ser un enter entre 0 i 10.
- La nota té valor nul fins que s'ha fet la presentació del TFG.

Respecteu els noms i l'ordre en què apareixen les columnes (fins i tot dins la clau o claus que calgui definir). Tots els noms s'han de posar en majúscues/minúscules com surt a l'enunciat.

CREATE TABLE presentacioTFG
	(idEstudiant CHAR(100),
	 titolTFG CHAR(100) NOT NULL UNIQUE,
	 dniDirector CHAR(50) NOT NULL CHECK ((dniDirector <> dniPresident) AND (dniDirector <> dniVocal)),
	 dniPresident CHAR(50) NOT NULL CHECK (dniPresident <> dniVocal),
	 dniVocal CHAR(50) NOT NULL CHECK (dniVocal <> dniPresident),
	 instantPresentacio INTEGER NOT NULL,
	 nota INTEGER DEFAULT NULL CHECK (nota IN (0,1,2,3,4,5,6,7,8,9,10)),
	 PRIMARY KEY (idEstudiant),
	 FOREIGN KEY (dniDirector) REFERENCES professors,
	 FOREIGN KEY (dniPresident) REFERENCES professors,
	 FOREIGN KEY (dniVocal) REFERENCES professors);

7. Doneu una seqüència d'operacions en àlgebra relacional per obtenir el nom dels professors que o bé tenen un sou superior a 2500, o bé no tenen cap assignació a un despatx amb superfície inferior a 20.

A = professors(sou > 2500)
B = despatxos(superficie < 20)
C = assignacions * B
D = professors * C
E = D[dni,nomProf,telefon,sou]
F = professors - E
G = A _u_ F
R = G[nomProf]

8. Doneu una seqüència d'operacions d'àlgebra relacional per obtenir el nom dels empleats que guanyen més que l'empleat amb num_empl 3.

A = empleats(num_empl = 3)
B = A[sou]
C = B{sou -> souB}
D = empleats[sou >souB]C
R = D[nom_empl]