/*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 28
    SELECT cognome, professione
    FROM dipendenti
    WHERE cognome >= 'A' AND cognome < 'N';

.print
--Punto 29
    SELECT cognome, nome, professione
    FROM dipendenti
    WHERE reddito_lordo BETWEEN 22500.00 AND 31000.00;

.print
--Punto 30
    SELECT professione, COUNT(*) AS num_dip
    FROM dipendenti d, aziende a
    WHERE d.cod_azienda=a.cod_azienda
    AND settore IN ("Metalmeccanico", "Informatica", "Alimentare", "Edilizia", "Trasporti")
    GROUP BY professione;

.print
--Punto 31
    SELECT denominazione, AVG(importo) AS media_versamenti
    FROM aziende a, versamenti v, dipendenti d
    WHERE v.matricola=d.matricola
    AND d.cod_azienda=a.cod_azienda
    GROUP BY denominazione
    HAVING media_versamenti>275.25;