/* \i 'H:\\0-BasidiDati_2024-25\\Moodle_2-SoluzioniDiEsercizi\\Azienda-1-CanaB_CreateAlterInsert.sql' */
/* Progettato da: Leonardo Joao Fabbro e altri */

DROP DATABASE IF EXISTS azienda;
CREATE DATABASE azienda;

\c azienda

CREATE TABLE DIPARTIMENTI(
    NomeDipa CHAR(2) PRIMARY KEY,
    Citta CHAR(2) NOT NULL,
    MatriImpieCassiere INTEGER
);

CREATE TABLE IMPIEGATI(
    MatriImpie INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    Cognome VARCHAR NOT NULL,
    Nome VARCHAR NOT NULL,
    Citta CHAR(2) NOT NULL,
    Stipendio INTEGER NOT NULL,
    NomeDipa VARCHAR(2) REFERENCES DIPARTIMENTI(NomeDipa) ON UPDATE CASCADE ON DELETE SET NULL,
    Ufficio INTEGER,
    MatriImpieCapo INTEGER REFERENCES IMPIEGATI(MatriImpie) ON DELETE SET NULL
);

/* Aggiungo adesso un vincolo di integrita' referenziale perche' il vincolo e` verso una tabella che non era ancora creata! */
ALTER TABLE DIPARTIMENTI ADD FOREIGN KEY (MatriImpieCassiere) REFERENCES IMPIEGATI(MatriImpie) ON DELETE SET NULL;

/* Inserimento dei dipartimenti */
INSERT INTO DIPARTIMENTI VALUES ('HW', 'MI', NULL);
INSERT INTO DIPARTIMENTI VALUES ('SW', 'PD', NULL);
INSERT INTO DIPARTIMENTI VALUES ('CE', 'PA', NULL);

/* Inserimento degli impiegati */
INSERT INTO IMPIEGATI(Cognome, Nome, Citta, Stipendio, NomeDipa, Ufficio, MatriImpieCapo)
  VALUES ('A', 'X', 'PD', 50, 'SW', 1, NULL);
INSERT INTO IMPIEGATI(Cognome, Nome, Citta, Stipendio, NomeDipa, Ufficio, MatriImpieCapo)
  VALUES ('A', 'Y', 'VI', 80, 'SW', 2, NULL);
INSERT INTO IMPIEGATI(Cognome, Nome, Citta, Stipendio, NomeDipa, Ufficio, MatriImpieCapo)
  VALUES ('B', 'X', 'MI', 15, 'HW', 2, 1);
INSERT INTO IMPIEGATI(Cognome, Nome, Citta, Stipendio, NomeDipa, Ufficio, MatriImpieCapo)
  VALUES ('C', 'Y', 'MI', 60, NULL, NULL, 1);
INSERT INTO IMPIEGATI(Cognome, Nome, Citta, Stipendio, NomeDipa, Ufficio, MatriImpieCapo)
  VALUES ('C', 'Y', 'PD', 20, 'SW', 2, 2);
INSERT INTO IMPIEGATI(Cognome, Nome, Citta, Stipendio, NomeDipa, Ufficio, MatriImpieCapo)
  VALUES ('D', 'Z', 'PD', 90, NULL, NULL, NULL);
INSERT INTO IMPIEGATI(Cognome, Nome, Citta, Stipendio, NomeDipa, Ufficio, MatriImpieCapo)
  VALUES ('E', 'A', 'VI', 90, 'SW', 1, 4);

/* Inserisco adesso i cassieri perche' questi sono stati inseriti dopo i dipartimenti. */
UPDATE DIPARTIMENTI
  SET MatriImpieCassiere = 3
  WHERE NomeDipa = 'HW';
UPDATE DIPARTIMENTI
  SET MatriImpieCassiere = 1
  WHERE NomeDipa = 'CE';

\qecho '\nTabella DIPARTIMENTI'
SELECT *
FROM DIPARTIMENTI;

\qecho 'Tabella IMPIEGATI'
SELECT *
FROM IMPIEGATI;