BD SQL i Àlgebra Relacional

REPÀS:selects, inserts, deletes, updates, creates, Àlgebra

--------------------------------------------------------
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 p.dni, p.NomProf, MAX(a.instantFi)AS Darrer_instant
    FROM professors p, despatxos d, assignacions a
    WHERE (a.instantFi IS NOT NULL AND d.superficie>15 AND p.dni = a.dni AND a.modul = d.modul AND a.numero = d.numero AND p.sou <= (SELECT AVG(p1.sou) FROM professors p1))
GROUP BY p.dni;
--------------------------------------------------------
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
---------------------
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 p.dni = a.dni AND a.numero = d.numero AND a.modul = d.modul AND d.superficie <=20))
--------------------------------------------------------
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, empleats e
WHERE(d.num_dpt = e.num_dpt)
GROUP BY d.num_dpt, e.ciutat_empl
HAVING COUNT (*) >=2
--------------------------------------------------------
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 DISTINCT d.num_dpt, d.nom_dpt
FROM departaments d NATURAL INNER JOIN empleats e
WHERE NOT EXISTS (SELECT *
            FROM empleats e
            WHERE e.ciutat_empl <> 'MADRID' AND
                    d.num_dpt = e.num_dpt);

--------------------------------------------------------
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 DISTINCT d.num_dpt, d.nom_dpt
FROM departaments d NATURAL INNER JOIN empleats e
GROUP BY (num_dpt)
HAVING COUNT(DISTINCT e.ciutat_empl)>=2
--------------------------------------------------------
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 ciutat_empl
FROM empleats e
WHERE   NOT EXISTS(SELECT *
FROM departaments d
WHERE e.ciutat_empl = d.ciutat_dpt)
--------------------------------------------------------
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 DISTINCT d.num_dpt, d.nom_dpt
FROM departaments d
WHERE NOT EXISTS(SELECT *
FROM empleats e
WHERE e.ciutat_empl = 'MADRID' AND d.num_dpt = e.num_dpt);
--------------------------------------------------------
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 DISTINCT p.num_proj, p.nom_proj
FROM projectes p, empleats e, empleats e2
WHERE (e.num_empl <> e2.num_empl and e.num_proj = e2.num_proj and e.num_proj = p.num_proj)
ORDER BY p.num_proj DESC;
--------------------------------------------------------
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(e1.sou)
FROM empleats e1))
ORDER BY e.nom_empl DESC;
--------------------------------------------------------
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)
FROM departaments d, empleats e
WHERE (e.num_dpt = d.num_dpt and d.ciutat_dpt = 'MADRID')
GROUP BY(d.num_dpt)
--------------------------------------------------------
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, empleats e
WHERE (d.ciutat_dpt = 'MADRID' and d.num_dpt = e.num_dpt and e.sou > 200000)
--------------------------------------------------------
Doneu una sentència SQL per incrementar en 500000 el pressupost dels projectes que tenen algun empleat que treballa a BARCELONA.
Pel joc de proves que trobareu al fitxer adjunt, el pressupost del projecte que hi ha d'haver després de l'execució de la sentència és 1100000
---------------------
UPDATE PROJECTES p
SET pressupost= pressupost + 500000 WHERE EXISTS(
SELECT p.num_proj
FROM departaments d, empleats e
WHERE (d.num_dpt = e.num_dpt and d.ciutat_dpt = 'BARCELONA' and e.num_proj = p.num_proj));
--------------------------------------------------------
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:

Hi haurà una fila de la taula per cada ciutat on hi ha un departament. El valor de l'atribut cost serà la suma del sou dels empleats dels departaments situats a la ciutat.

Només han de sortir les ciutats on hi ha departament que tinguin empleats.

Pel joc de proves públic del fitxer adjunt, un cop executada la sentència d'inserció, a la taula cost_ciutat hi haurà les tuples següents:

CIUTAT_DPT      COST
BARCELONA       100

---------------------
INSERT INTO cost_ciutat (SELECT d.ciutat_dpt, SUM(e.sou)
FROM departaments d, empleats e
WHERE d.num_dpt = e.num_dpt
GROUP BY d.ciutat_dpt);
--------------------------------------------------------
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 referencial de la clau forana de la taula Assignacions que referencia la taula Despatxos (la violació l'ha de causar una tupla de la taula Assignacions amb mòdul C1 i numero de despatx 119).
Les sentències NOMÉS han de violar aquesta restricció.
---------------------
INSERT INTO PROFESSORS VALUES(123, 'Pere', '611611611', 250000);
INSERT INTO DESPATXOS VALUES('C1', '120', 13);
INSERT INTO ASSIGNACIONS VALUES(123, 'C1', '119', 12, 14);
--------------------------------------------------------
Suposeu la base de dades que podeu trobar al fitxer adjunt.
Doneu una seqüència de sentències SQL d'actualització (INSERTs i/o UPDATEs) de tal manera que, un cop executades, el resultat de la consulta següent sigui el que s'indica. El nombre de files de cada taula ha de ser el més petit possible, i hi ha d'haver com a màxim un professor.

Per a la consulta:

Select count(*) as quant
From assignacions ass
Where ass.instantInici>50
Group by ass.instantInici
order by quant;

El resultat haurà de ser:

quant
1
2

---------------------
INSERT INTO PROFESSORS VALUES(123, 'Marta Sacher', '611611611', 250000);
INSERT INTO DESPATXOS VALUES('C1', '119', 20);
INSERT INTO DESPATXOS VALUES('C1', '120', 20);
INSERT INTO ASSIGNACIONS VALUES(123, 'C1', '119', 51, 53);
INSERT INTO ASSIGNACIONS VALUES(123, 'C1', '119', 52, 54);
INSERT INTO ASSIGNACIONS VALUES(123, 'C1', '120', 52, 55);
--------------------------------------------------------
Tenint en compte l'esquema de la BD que s'adjunta, proposeu una sentència de creació de les taules següents:
FRANGES_HORARIES(INSTANT_INICI, INSTANT_FI, NUM_EMPL)
TASQUES_REALITZADES(NUM_TASCA, INSTANT_INICI, INSTANT_FI, NUM_EMPL, DESCRIPCIO)

Cada fila de la taula franges_horaries representa un periode d'hores seguides què ha treballat un empleat.
Cada fila de la taula tasques_realitzades representa una tasca que un empleat ha realitzat en una franja horaria.

En la creació de les taules cal que tingueu en compte que:
- No hi poden haver dues franges d'un mateix empleat que comencin i acabin en uns mateixos instants.
- L'instant fi d'una franja ha de ser més gran que l'instant d'inici més 180.
- Una franja horària ha de ser d'un empleat que existeixi a la base de dades
- No hi pot haver dues tasques amb el mateix número de tasca.
- Una tasca es fa sempre en una franja horària que existeixi a la base de dades
- La descripció d'una tasca ha de tenir un valor definit (valor diferent de null).
- Els atributs instant_inici, instant_fi, num_tasca són enters.
- L'atribut descripció ha de ser un char(50).

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úscules com surt a l'enunciat.
---------------------
CREATE TABLE FRANGES_HORARIES
        (INSTANT_INICI INTEGER ,
        INSTANT_FI  INTEGER CHECK (INSTANT_INICI - INSTANT_FI > 180),
        NUM_EMPL INTEGER,
        PRIMARY KEY(INSTANT_INICI, INSTANT_FI, NUM_EMPL),
        FOREIGN KEY(NUM_EMPL) REFERENCES EMPLEATS(NUM_EMPL)
        );

CREATE TABLE TASQUES_REALITZADES
        (NUM_TASCA INTEGER,
        INSTANT_INICI INTEGER NOT NULL,
        INSTANT_FI INTEGER NOT NULL,
        NUM_EMPL INTEGER NOT NULL,
        DESCRIPCIO CHAR(50) NOT NULL,
        PRIMARY KEY(NUM_TASCA),
        FOREIGN KEY(INSTANT_INICI, INSTANT_FI, NUM_EMPL)
                REFERENCES FRANGES_HORARIES (INSTANT_INICI, INSTANT_FI,NUM_EMPL)
        );
--------------------------------------------------------
Tenint en compte l'esquema de la BD que s'adjunta, proposeu una sentència de creació de les taules següents:
VENDES(NUM_VENDA, NUM_EMPL, CLIENT)
PRODUCTES_VENUTS(NUM_VENDA, PRODUCTE, QUANTITAT, IMPORT)

Cada fila de la taula vendes representa una venda que ha fet un empleat a un client.
Cada fila de la taula productes_venuts representa una quantitat de producte venut en una venda, amb un cert import.

En la creació de les taules cal que tingueu en compte que:
- No hi poden haver dues vendes amb un mateix número de venda.
- Un empleat només li pot fer una única venda a un mateix client.
- Una venda l'ha de fer un empleat que existeixi a la base de dades
- No hi pot haver dues vegades un mateix producte en una mateixa venda.
- La venda d'un producte venut ha d'existir a la base de dades.
- La quantitat de producte venut no pot ser nul, i té com a valor per defecte 1.
- Els atributs num_venda, quantitat, import són enters.
- Els atributs client, producte són char(30), i char(20) respectivament.

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 com surt a l'enunciat.
---------------------
CREATE TABLE VENDES
        ( NUM_VENDA INTEGER,
        NUM_EMPL INTEGER NOT NULL,
        CLIENT CHAR(30),
        PRIMARY KEY(NUM_VENDA),
        FOREIGN KEY (NUM_EMPL) REFERENCES EMPLEATS (NUM_EMPL),
        UNIQUE (NUM_EMPL, CLIENT));

CREATE TABLE PRODUCTES_VENUTS
        ( NUM_VENDA INTEGER,
        PRODUCTE CHAR(20),
        QUANTITAT INTEGER DEFAULT 1 NOT NULL,
        IMPORT INTEGER,
        PRIMARY KEY(NUM_VENDA,PRODUCTE),
        FOREIGN KEY (NUM_VENDA) REFERENCES VENDES (NUM_VENDA));
--------------------------------------------------------
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 DEPARTAMENTS VALUES(1, 'TPZARAGOZA', 0, 'INDEPENDENCIA', 'ZARAGOZA');
INSERT INTO PROJECTES VALUES(2, 'TTPP', 'VODAFONE', 2500000);
INSERT INTO EMPLEATS VALUES(21, 'ALEXANDRE', 25000, 'ZARAGOZA', 1, 2);
INSERT INTO EMPLEATS VALUES(21, 'MARTA', 45000, 'BARCELONA', 1,2);
--------------------------------------------------------
Doneu una seqüència d'operacions d'algebra relacional per obtenir el nom del departament on treballa i el nom del projecte on està assignat l'empleat número 2.

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

Nom_dpt     Nom_proj
MARKETING       IBDVID
---------------------
A = EMPLEATS(num_empl = 2)
B = A*DEPARTAMENTS
C = B*PROJECTES
Z = C[Nom_dpt, Nom_proj]
--------------------------------------------------------
Doneu una seqüència d'operacions de l'àlgebra relacional 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

---------------------
A = EMPLEATS[NUM_DPT, CIUTAT_EMPL, NUM_EMPL]
B = A{NUM_DPT->NUM_DPTA, CIUTAT_EMPL->CIUTAT_EMPLEA, NUM_EMPL->NUM_EMPLEA}
C = B[NUM_DPTA = NUM_DPT, CIUTAT_EMPLEA <> CIUTAT_EMPL, NUM_EMPLEA <> NUM_EMPL]A
D = C[NUM_DPT]
E = D*DEPARTAMENTS
Z = E[NUM_DPT, NOM_DPT]
--------------------------------------------------------
Doneu una seqüència d'operacions d'algebra relacional 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

---------------------
A = EMPLEATS(CIUTAT_EMPL <> 'MADRID')
B = A[NUM_DPT]
C = DEPARTAMENTS[NUM_DPT]
D = C-B
E = D*EMPLEATS
F = E*DEPARTAMENTS
Z = F[NUM_DPT, NOM_DPT]
--------------------------------------------------------
Doneu una seqüència d'operacions d'algebra relacional per obtenir els números i els noms dels departament situats a MADRID, que tenen algun empleat que guanya més de 200000.

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

Num_dpt     Nom_dpt
5       VENDES

---------------------
A = DEPARTAMENTS(CIUTAT_DPT = 'MADRID')
B = EMPLEATS(SOU > 200000)
C = A*B
Z = C[NUM_DPT, NOM_DPT]
--------------------------------------------------------
Doneu una seqüència d'operacions d'àlgebra relacional per obtenir els noms i números dels projectes que no tenen cap empleat assignat.

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

Nom_proj        Num_proj
IBDTEL      1

---------------------
A = EMPLEATS[NUM_PROJ]
B = PROJECTES[NUM_PROJ]
C = B-A
D = C*PROJECTES
Z = D[NOM_PROJ, NUM_PROJ]
--------------------------------------------------------
Doneu una seqüència d'operacions d'algebra relacional 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:

NOM_empl        NUM_empl
ROBERTO     3

---------------------
A = EMPLEATS{NUM_DPT-> NUM_DPTA}
B = A[NUM_DPTA = NUM_DPT, CIUTAT_EMPL = CIUTAT_DPT]DEPARTAMENTS
Z = B[NOM_EMPL, NUM_EMPL]
--------------------------------------------------------