/*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 13
    SELECT COUNT(*) AS num_dipendenti
    FROM dipendenti, aziende
    WHERE aziende.cod_azienda = dipendenti.cod_azienda
    AND denominazione= 'Rossi Meccanica';

.print
--Punto 14
    SELECT MIN(reddito_lordo) AS reddito_min, MAX(reddito_lordo) AS reddito_max
    FROM dipendenti;

.print
--Punto 15
    SELECT AVG(reddito_lordo) AS reddito_medio
    FROM dipendenti
    WHERE professione = 'Operaio';

.print
--Punto 16
    SELECT SUM(importo) AS versamenti_tot
    FROM versamenti, dipendenti
    WHERE dipendenti.matricola=versamenti.matricola
    AND cod_azienda = 1;

.print
--Punto 17
    SELECT cognome, nome
    FROM dipendenti
    ORDER BY cognome ASC, nome ASC;

.print
--Punto 18
    SELECT denominazione
    FROM aziende
    ORDER BY denominazione;