BD - REPÀS: inserts/deletes/updates/creates

-- 1)

UPDATE PROJECTES p
SET PRESSUPOST = PRESSUPOST+500000
WHERE EXISTS(SELECT *
         FROM EMPLEATS e, DEPARTAMENTS d
         WHERE e.NUM_PROJ = p.NUM_PROJ AND
         e.NUM_DPT = d.NUM_DPT AND
         d.CIUTAT_DPT = 'BARCELONA');

-- 2)

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;

-- 3)

INSERT INTO professors VALUES('123456789', 'Jordi Petit', 56473829, 1234);
INSERT INTO assignacions VALUES ('123456789', 'C1', '119', 3600, 3800);

-- 4)

INSERT INTO professors VALUES('123456789', 'Jordi Petit', 56473829, 1234);
INSERT INTO despatxos VALUES('A', '6',20);
INSERT INTO despatxos VALUES('B', '6',20);
INSERT INTO assignacions VALUES ('123456789', 'A', '6', 100, 120);
INSERT INTO assignacions VALUES ('123456789', 'A', '6', 121, 140);
INSERT INTO assignacions VALUES ('123456789', 'B', '6', 121, 140);

-- 5)

INSERT INTO COST_CIUTAT
SELECT DISTINCT d.CIUTAT_DPT, 0
FROM DEPARTAMENTS d
WHERE NOT EXISTS(SELECT *
             FROM DEPARTAMENTS d1, EMPLEATS e
             WHERE d1.CIUTAT_DPT = d.CIUTAT_DPT
               AND e.NUM_DPT = d1.NUM_DPT);

-- 6)

INSERT INTO DEPARTAMENTS VALUES(1, 'CS', 3, 'OMEGA', 'BARCELONA');
INSERT INTO PROJECTES VALUES(1, 'PROP', 'SUDOKU', 50);
INSERT INTO EMPLEATS VALUES(21, 'Enric Rodríguez', 1234, 'BARCELONA', 1, 1);
INSERT INTO EMPLEATS VALUES(21, 'Salvador Roura', 4321, 'BARCELONA', 1, 1);

-- 7)

CREATE TABLE presentacioTFG(
    idEstudiant CHAR(100) PRIMARY KEY,
    titolTFG CHAR(100) UNIQUE NOT NULL,
    dniDirector CHAR(50) NOT NULL,
    dniPresident CHAR(50) NOT NULL,
    dniVocal CHAR(50) NOT NULL,
    instantPresentacio INTEGER NOT NULL,
    nota INTEGER CHECK(nota >= 0 AND nota <= 10) DEFAULT NULL,
    FOREIGN KEY (dniDirector) REFERENCES PROFESSORS(dni),
    FOREIGN KEY (dniPresident) REFERENCES PROFESSORS(dni),
    FOREIGN KEY (dniVocal) REFERENCES PROFESSORS(dni),
    CHECK(dniDirector != dniPresident AND dniDirector != dniVocal AND dniPresident != dniVocal));

-- 8)

CREATE TABLE FRANGES_HORARIES(
    INSTANT_INICI INTEGER,
    INSTANT_FI INTEGER,
    NUM_EMPL INTEGER,
    PRIMARY KEY(INSTANT_INICI, INSTANT_FI, NUM_EMPL),
    CHECK (INSTANT_FI > INSTANT_INICI + 180),
    FOREIGN KEY (NUM_EMPL) REFERENCES EMPLEATS(NUM_EMPL));

CREATE TABLE TASQUES_REALITZADES(
    NUM_TASCA INTEGER PRIMARY KEY,
    INSTANT_INICI INTEGER NOT NULL,
    INSTANT_FI INTEGER NOT NULL,
    NUM_EMPL INTEGER NOT NULL,
    DESCRIPCIO CHAR(50) NOT NULL,
    FOREIGN KEY (INSTANT_INICI, INSTANT_FI, NUM_EMPL)
    REFERENCES FRANGES_HORARIES(INSTANT_INICI, INSTANT_FI, NUM_EMPL));

-- 9)

CREATE TABLE VENDES(
    NUM_VENDA INTEGER PRIMARY KEY,
    NUM_EMPL INTEGER NOT NULL,
    CLIENT CHAR(30),
    FOREIGN KEY (NUM_EMPL) REFERENCES EMPLEATS(NUM_EMPL),
    UNIQUE(NUM_EMPL, CLIENT));

CREATE TABLE PRODUCTES_VENUTS(
    NUM_VENDA INTEGER,
    PRODUCTE CHAR(20),
    QUANTITAT INTEGER NOT NULL DEFAULT 1,
    IMPORT INTEGER,
    PRIMARY KEY (NUM_VENDA, PRODUCTE),
    FOREIGN KEY (NUM_VENDA) REFERENCES VENDES(NUM_VENDA));

-- JosepRivaille