/*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 7
    SELECT cognome, nome, professione, reddito_lordo
    FROM dipendenti
    WHERE professione = 'Segretaria'
        AND reddito_lordo < 29000.00;

.print
--Punto 8
    SELECT cognome, nome
    FROM dipendenti
    WHERE cod_azienda = 1
        AND professione = 'Tecnico';

.print
--Punto 9
    SELECT professione
    FROM dipendenti
    WHERE cod_azienda = 3;

.print
--Punto 10
    SELECT DISTINCT professione
    FROM dipendenti
    WHERE reddito_lordo > 26000.00;

.print
--Punto 11
    SELECT cod_versamento, data_vers, importo
    FROM versamenti, dipendenti
    WHERE cod_azienda = 2
        AND dipendenti.matricola=versamenti.matricola;

.print
--Punto 12
    SELECT reddito_lordo * 23 / 100 AS imposta_lorda
    FROM dipendenti
    WHERE professione = 'Geometra';