Esercizio database gestione spese | Esercizi SQL

Creare un database che consenta di gestire una base dati relativa alle spese personali. Ogni spesa appartiene ad una categoria e ogni spesa può appartenere a più tags. Dopo aver fatto una adeguata analisi, modellizzare il database creando lo schema concettuale e logico. Scrivere le query necessarie per:

Query di creazione (CREATE):

• inserire una nuova spesa
• inserire una nuova categoria
• inserire un nuovo tag
• inserire una nuova spesa con una nuova categoria e nuovi tags
• inserire una nuova spesa con una categoria e tags già esistenti
• inserire una nuova spesa con una categoria esistente e nuovi tags
• inserire una nuova spesa con una categoria esistente e tags già esistenti

Query di modifica (UPDATE):

• modificare la descrizione di una spesa
• modificare la categoria di una spesa
• modificare il tag di una spesa
• modificare la categoria e il tag di una spesa
• modificare la descrizione, la categoria e il tag di una spesa
• modificare la descrizione e la categoria di una spesa
• modificare la descrizione e il tag di una spesa

Query di cancellazione (DELETE):

• cancellare una spesa
• cancellare una categoria
• cancellare un tag
• cancellare una spesa con una categoria e tags già esistenti

Proiezioni (SELECT):

• Seleziona tutti i campi dalla tabella “spese”.
• Seleziona solo il nome della categoria dalla tabella “categorie”.
• Ottieni una lista delle descrizioni delle spese dalla tabella “spese”.
• Quali sono i nomi dei tag dalla tabella “tags”?
• Mostra l’importo e la data di tutte le spese.
• Quali sono gli ID delle spese dalla tabella “spese”?
• Ottieni una lista di tutte le categorie dalla tabella “categorie”.
• Mostra solo le date delle spese dalla tabella “spese”.

Selezioni Semplici (WHERE):

• Seleziona tutte le spese con un importo maggiore di $100.
• Quali spese sono state effettuate il 15 ottobre 2023?
• Mostra le spese nella categoria “Alimentari”.
• Quali spese sono state effettuate prima del 1º gennaio 2023?
• Seleziona le spese con una descrizione che contiene la parola “ristorante”.
• Quali spese sono state fatte con un tag chiamato “Viaggio”?
• Mostra le spese effettuate da un’utente specifico (supponiamo un campo “utente_id” nella tabella “spese”).

Selezioni Complesse (JOIN):

• Quali spese sono state fatte il 2 marzo 2023 e appartengono alla categoria “Intrattenimento”?

• Elencare le spese effettuate con il tag “Viaggio” in una categoria diversa da “Vacanze”.

• Mostra le spese con un importo superiore a $50 fatte dopo il 1º gennaio 2023 appartenenti alla categoria “Alimentari”.

• Quali spese hanno lo stesso tag di un’altra spesa ma un importo diverso?

• Elencare le spese con importi superiori alla media delle spese.

• Quali spese appartengono a categorie con almeno 10 spese registrate?

• Mostra le spese che non hanno tag associati.

Possibile soluzione

CREATE TABLE categorie (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nome VARCHAR(255) NOT NULL
);

CREATE TABLE tags (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nome VARCHAR(255) NOT NULL
);

CREATE TABLE spese (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    descrizione VARCHAR(255) NOT NULL,
    importo DECIMAL(10,2) NOT NULL,
    data DATE NOT NULL,
    categoria_id INTEGER NOT NULL,
    FOREIGN KEY (categoria_id) REFERENCES categorie(id)
);

CREATE TABLE spese_tags (
    spesa_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    FOREIGN KEY (spesa_id) REFERENCES spese(id),
    FOREIGN KEY (tag_id) REFERENCES tags(id)
);

INSERT INTO categorie (nome) VALUES ('Alimentari');
INSERT INTO categorie (nome) VALUES ('Intrattenimento');
INSERT INTO categorie (nome) VALUES ('Vacanze');
INSERT INTO categorie (nome) VALUES ('Abbigliamento');
INSERT INTO categorie (nome) VALUES ('Bollette');
INSERT INTO categorie (nome) VALUES ('Altro');

INSERT INTO tags (nome) VALUES ('Viaggio');
INSERT INTO tags (nome) VALUES ('Cena');
INSERT INTO tags (nome) VALUES ('Cinema');
INSERT INTO tags (nome) VALUES ('Caffè');
INSERT INTO tags (nome) VALUES ('Auto');
INSERT INTO tags (nome) VALUES ('Benzina');
INSERT INTO tags (nome) VALUES ('Treno');
INSERT INTO tags (nome) VALUES ('Aereo');
INSERT INTO tags (nome) VALUES ('Hotel');
INSERT INTO tags (nome) VALUES ('Noleggio');
INSERT INTO tags (nome) VALUES ('Elettronica');
INSERT INTO tags (nome) VALUES ('Libri');
INSERT INTO tags (nome) VALUES ('Musica');
INSERT INTO tags (nome) VALUES ('Giochi');
INSERT INTO tags (nome) VALUES ('Sport');
INSERT INTO tags (nome) VALUES ('Palestra');
INSERT INTO tags (nome) VALUES ('Luce');
INSERT INTO tags (nome) VALUES ('Gas');

INSERT INTO spese (descrizione, importo, data, categoria_id) VALUES ('Spesa al supermercato', 50.00, '2023-10-01', 1);
INSERT INTO spese (descrizione, importo, data, categoria_id) VALUES ('Cena al ristorante', 30.00, '2023-10-02', 2);
INSERT INTO spese (descrizione, importo, data, categoria_id) VALUES ('Biglietto del cinema', 10.00, '2023-10-03', 2);
INSERT INTO spese (descrizione, importo, data, categoria_id) VALUES ('Caffè al bar', 1.00, '2023-10-04', 2);
INSERT INTO spese (descrizione, importo, data, categoria_id) VALUES ('Benzina', 50.00, '2023-10-05', 5);
INSERT INTO spese (descrizione, importo, data, categoria_id) VALUES ('Biglietto del treno', 100.00, '2023-10-06', 3);
INSERT INTO spese (descrizione, importo, data, categoria_id) VALUES ('Biglietto aereo', 200.00, '2023-10-07', 3);
INSERT INTO spese (descrizione, importo, data, categoria_id) VALUES ('Noleggio auto', 50.00, '2023-10-08', 3);

INSERT INTO spese_tags (spesa_id, tag_id) VALUES (1, 1);
INSERT INTO spese_tags (spesa_id, tag_id) VALUES (2, 2);
INSERT INTO spese_tags (spesa_id, tag_id) VALUES (3, 3);
INSERT INTO spese_tags (spesa_id, tag_id) VALUES (4, 4);
INSERT INTO spese_tags (spesa_id, tag_id) VALUES (5, 5);
INSERT INTO spese_tags (spesa_id, tag_id) VALUES (6, 6);
INSERT INTO spese_tags (spesa_id, tag_id) VALUES (7, 7);
INSERT INTO spese_tags (spesa_id, tag_id) VALUES (8, 8);
INSERT INTO spese_tags (spesa_id, tag_id) VALUES (8, 9);
INSERT INTO spese_tags (spesa_id, tag_id) VALUES (8, 10);