Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Qüestió 1
- SELECT num_dpt, nom_dpt
- FROM departaments d
- WHERE EXISTS (SELECT *
- FROM empleats e1
- WHERE e1.num_dpt = d.num_dpt)
- AND NOT EXISTS (SELECT *
- FROM empleats e2
- WHERE e2.num_dpt = d.num_dpt
- AND e2.ciutat_empl != 'MADRID');
- -- Qüestió 2
- SELECT DISTINCT d.num_dpt, d.nom_dpt
- FROM departaments d, empleats e
- WHERE e.num_dpt = d.num_dpt
- GROUP BY e.ciutat_empl, d.num_dpt
- HAVING COUNT(e.ciutat_empl) > 1
- -- Qüestió 3
- INSERT INTO cost_ciutat (ciutat_dpt, cost)
- SELECT d.ciutat_dpt, SUM(e.sou)
- FROM departaments d, empleats e
- WHERE e.num_dpt = d.num_dpt
- GROUP BY d.ciutat_dpt
- -- Qüestió 4
- CREATE TABLE FRANGES_HORARIES
- ( INSTANT_INICI INTEGER,
- INSTANT_FI INTEGER CHECK (INSTANT_FI > INSTANT_INICI + 180),
- NUM_EMPL INTEGER NOT NULL,
- 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));
- -- JosepRivaille
Add Comment
Please, Sign In to add comment