/*Autore: Zagarella Nadia*/
.header on
.mode column

-- CREAZIONE TABELLE

CREATE TABLE aziende (
    cod_azienda   INTEGER PRIMARY KEY,
    denominazione TEXT NOT NULL,   -- max 20
    indirizzo     TEXT,            -- max 30
    citta         TEXT,            -- max 30
    tel           TEXT,            -- max 15
    settore       TEXT             -- max 25
);

CREATE TABLE dipendenti (
    matricola     TEXT PRIMARY KEY,  -- max 5
    cognome       TEXT NOT NULL,     -- max 20
    nome          TEXT NOT NULL,     -- max 20
    indirizzo     TEXT,              -- max 30
    citta         TEXT,              -- max 30
    professione   TEXT,              -- max 20
    reddito_lordo REAL,
    trattenute    REAL,
    n_familiari   INTEGER,
    cod_azienda   INTEGER NOT NULL REFERENCES aziende(cod_azienda)
);

CREATE TABLE versamenti (
    cod_versamento INTEGER PRIMARY KEY AUTOINCREMENT,  -- numerazione automatica
    data_vers      TEXT NOT NULL,                      -- formato aaaa-mm-gg
    importo        REAL NOT NULL,
    matricola      TEXT NOT NULL REFERENCES dipendenti(matricola)
);

-- INSERIMENTO DATI D'ESEMPI

-- AZIENDE (6 record)
INSERT INTO aziende (cod_azienda, denominazione, indirizzo, citta, tel, settore) VALUES
(1, 'Rossi Meccanica',   'Via Roma 12',        'Torino',  '011234567', 'Metalmeccanico'),
(2, 'Verdi Informatica', 'Corso Italia 45',    'Milano',  '027654321', 'Informatica'),
(3, 'Bianchi Alimentari','Via Garibaldi 8',    'Roma',   '052198765', 'Alimentare'),
(4, 'Neri Costruzioni',  'Viale Europa 101',   'Roma',    '065544332', 'Edilizia'),
(5, 'Gialli Logistica',  'Via dei Mille 33',   'Bologna', '051223344', 'Trasporti'),
(6, 'Blu Servizi',       'Piazza Duomo 5',     'Firenze', '055112233', 'Servizi');

-- DIPENDENTI (10 record)
INSERT INTO dipendenti (matricola, cognome, nome, indirizzo, citta, professione, reddito_lordo, trattenute, n_familiari, cod_azienda) VALUES
('D0001', 'Esposito',  'Marco',    'Via Mazzini 3',      'Torino',  'Operaio',        26000.00, 5200.00, 3, 1),
('D0002', 'Ferrari',   'Giulia',   'Via Verdi 21',       'Milano',  'Programmatore',  34000.00, 8500.00, 1, 2),
('D0003', 'Russo',     'Luca',     'Via Dante 7',        'Milano',  'Analista',       39000.00, 10200.00, 2, 2),
('D0004', 'Romano',    'Elena',    'Via Cavour 15',      'Roma',   'Impiegata',      27500.00, 6000.00, 4, 3),
('D0005', 'Colombo',   'Paolo',    'Via Manzoni 9',      'Roma',    'Geometra',       31000.00, 7400.00, 2, 4),
('D0006', 'Ricci',     'Sara',     'Via Leopardi 18',    'Bologna', 'Operaio',        24500.00, 4900.00, 0, 5),
('D0007', 'Marino',    'Andrea',   'Via Foscolo 40',     'Torino',  'Tecnico',        29000.00, 6700.00, 3, 1),
('D0008', 'Greco',     'Chiara',   'Via Petrarca 6',     'Firenze', 'Segretaria',     25500.00, 5100.00, 1, 6),
('D0009', 'Bruno',     'Davide',   'Via Carducci 27',    'Roma',    'Muratore',       23000.00, 4300.00, 5, 4),
('D0010', 'Gallo',     'Francesca','Via Pascoli 11',     'Firenze', 'Magazziniera',   22500.00, 4000.00, 2, 5);

-- VERSAMENTI (12 record; cod_versamento è generato automaticamente)
INSERT INTO versamenti (data_vers, importo, matricola) VALUES
('2026-01-15', 250.00, 'D0001'),
('2026-02-15', 250.00, 'D0001'),
('2026-01-20', 400.50, 'D0002'),
('2026-03-10', 150.00, 'D0003'),
('2026-02-05', 320.75, 'D0004'),
('2026-04-12', 500.00, 'D0005'),
('2026-03-18', 180.00, 'D0006'),
('2026-05-22', 275.25, 'D0007'),
('2026-06-30', 220.00, 'D0008'),
('2026-07-14', 610.00, 'D0009'),
('2026-08-03', 195.50, 'D0010'),
('2026-09-09', 300.00, 'D0003');

.print
--Punto 32
    SELECT a.denominazione, COUNT(*) AS num_dip
    FROM dipendenti d, aziende a
    WHERE d.cod_azienda=a.cod_azienda
    AND d.professione="Operaio"
    GROUP BY a.denominazione
    HAVING num_dip>10;

.print
--Punto 33
    SELECT professione
    FROM dipendenti
    GROUP BY professione
    HAVING AVG(reddito_lordo)>25000;

.print
--Punto 34
    SELECT a.cod_azienda, a.denominazione, COUNT(*) AS totale_dip
    FROM dipendenti d, aziende a
    WHERE d.cod_azienda=a.cod_azienda
    GROUP BY a.cod_azienda, a.denominazione
    HAVING totale_dip>20;

.print
--Punto 35
    SELECT cognome, nome, professione
    FROM dipendenti
    WHERE reddito_lordo=(SELECT MAX(reddito_lordo) 
                            FROM dipendenti);

.print 
--Punto 36
    SELECT cognome, nome, cod_azienda
    FROM dipendenti
    WHERE reddito_lordo=(SELECT MIN(reddito_lordo)
                            FROM dipendenti
                            WHERE professione="Operaio");

.print
--Punto 37
    CREATE VIEW numero_dipendenti AS
    SELECT a.cod_azienda, denominazione, COUNT(*) AS numero_dip
    FROM aziende a, dipendenti d
    WHERE a.cod_azienda=d.cod_azienda
    GROUP BY denominazione;

    SELECT denominazione, cod_azienda
    FROM numero_dipendenti
    WHERE numero_dip=(SELECT MAX(numero_dip)
                        FROM numero_dipendenti);