🗃️ Operatori Relazionali

-- Selezione: tutti gli studenti di Roma
SELECT *
FROM studenti
WHERE citta = 'Roma';

-- Selezione con più condizioni (AND): tutti gli studenti che vivono a Roma e hanno più di 21 anni
SELECT *
FROM studenti
WHERE citta = 'Roma' AND eta > 21;

-- Selezione con OR: tutti gli studenti che vivono a Roma o a Milano
SELECT *
FROM studenti
WHERE citta = 'Roma' OR citta = 'Milano';

-- Selezione con IN (più elegante): tutti gli studenti che vivono a Roma o a Milano
SELECT *
FROM studenti
WHERE citta IN ('Roma', 'Milano');

-- L'obiettivo è mostrare solo nome e città di tutti gli studenti
-- Nascondiamo id ed età perché non servono per questa visualizzazione
SELECT nome, citta
FROM studenti;

-- Per eliminare duplicati (come in algebra relazionale)
SELECT DISTINCT citta
FROM studenti;
-- Risultato: Roma, Milano, Napoli (3 righe, non 4)

-- Combinazione Selezione + Proiezione
-- σ città='Roma' e poi π nome
SELECT nome
FROM studenti
WHERE citta = 'Roma';

-- Prodotto Cartesiano Puro (tutte le combinazioni)
SELECT * 
FROM studenti 
CROSS JOIN esami;

-- INNER JOIN: mostra solo studenti che hanno almeno un esame
-- L'obiettivo è abbinare ogni studente ai suoi esami (se esistono)
SELECT *
FROM studenti s
INNER JOIN esami e ON s.id = e.id_studente;

-- Equivalente: JOIN senza INNER produce lo stesso risultato
-- Mostra gli stessi studenti con i loro esami
SELECT *
FROM studenti s
JOIN esami e ON s.id = e.id_studente;

-- L'obiettivo è ottenere un report completo: nome studente, corso frequentato e voto ricevuto
-- Servono 3 tabelle perché ogni tabella contiene informazioni diverse che vanno combinate
SELECT 
    s.nome,
    c.nome_corso,
    c.crediti,
    e.voto
FROM studenti s
JOIN esami e ON s.id = e.id_studente
JOIN corsi c ON e.id_corso = c.id;

-- L'obiettivo è mostrare: chi ha comprato cosa, quanto, e il totale speso (quantità × prezzo)
SELECT 
    c.nome AS cliente,
    c.città,
    p.nome AS prodotto,
    o.quantità,
    p.prezzo AS prezzo_unit,
    (o.quantità * p.prezzo) AS totale
FROM clienti c
JOIN ordini o ON c.id = o.id_cliente
JOIN prodotti p ON o.id_prodotto = p.id;

-- L'obiettivo è trovare il turno esatto per ogni dipendente
-- Serve il match su DUE colonne per evitare errori 
--(es. non assegnare il turno di Milano a chi lavora a Roma)
SELECT 
    d.nome,
    d.reparto,
    d.sede,
    t.giorno,
    t.orario
FROM dipendenti d
JOIN turni t 
    ON d.reparto = t.reparto
    AND d.sede = t.sede;

-- Cosa succede se usi solo UNA colonna? (ERRATO!)
SELECT d.nome, d.reparto, t.giorno, t.orario
FROM dipendenti d
JOIN turni t 
    ON d.reparto = t.reparto;
-- Risultato SBAGLIATO: Anna (Vendite-Milano) avrebbe anche il turno di Roma!

-- Join con condizioni multiple (AND) - Esempio generico
SELECT *
FROM tabella1 t1
JOIN tabella2 t2 
    ON t1.colonna_a = t2.colonna_a
    AND t1.colonna_b = t2.colonna_b;

-- Theta Join con condizione >=: l'obiettivo è trovare prodotti che il cliente può permettersi
-- Uniamo clienti e prodotti dove il budget del cliente è maggiore o uguale al prezzo del prodotto
SELECT 
    c.nome AS cliente,
    c.budget,
    p.nome AS prodotto,
    p.prezzo
FROM clienti c
JOIN prodotti p ON c.budget >= p.prezzo
ORDER BY c.nome, p.prezzo;

-- Altro esempio di Theta join: trovare prodotti in una fascia di prezzo
SELECT *
FROM fasce_prezzo f
JOIN prodotti p 
    ON p.prezzo >= f.min_prezzo 
    AND p.prezzo <= f.max_prezzo;

-- Esami con voto >= 28 di studenti il cui nome inizia per 'M'
SELECT 
    s.nome,
    e.corso,
    e.voto
FROM studenti s
JOIN esami e ON s.id = e.id_studente  -- fin quì esami con voto di tutti gli studenti
WHERE s.nome LIKE 'M%'           -- filtro studente
  AND e.voto >= 28;                -- filtro voto

-- Ordini del 2024 con importo > 100€
SELECT 
    c.nome,
    o.data_ordine,
    o.importo
FROM clienti c
JOIN ordini o ON c.id = o.id_cliente
WHERE YEAR(o.data_ordine) = 2024
  AND o.importo > 100
ORDER BY o.data_ordine DESC;

-- LEFT JOIN: mostra TUTTI gli studenti, anche quelli senza esami
-- L'obiettivo è visualizzare chi ha esami e chi no (NULL = nessun esame)
SELECT s.id, s.nome, i.id_corso, i.voto
FROM studenti s
LEFT JOIN iscrizioni i ON s.id = i.id_studente;

-- Trovare studenti SENZA esami (filtro su NULL)
-- L'obiettivo è trovare chi NON ha sostenuto alcun esame
SELECT s.*
FROM studenti s
LEFT JOIN iscrizioni i ON s.id = i.id_studente
WHERE i.id_studente IS NULL;
-- Risultato: solo Anna (non ha esami)

-- RIGHT JOIN: mostra TUTTI i corsi, anche quelli senza studenti iscritti
-- L'obiettivo è visualizzare quali corsi hanno iscritti e quali no (NULL = nessuno iscritto)
SELECT c.nome_corso, i.id_studente, i.voto
FROM iscrizioni i
RIGHT JOIN corsi c ON i.id_corso = c.id_corso;

-- Risultato: Yoga appare con NULL perché nessuno è iscritto
-- Database      | 1    | 28
-- Database      | 2    | 25
-- Programmazione| 1    | 30
-- Reti          | 3    | 27
-- Yoga          | NULL | NULL

-- Equivalente: LEFT JOIN invertendo l'ordine delle tabelle
-- Produce lo stesso risultato della query precedente ma usando LEFT JOIN
SELECT c.nome_corso, i.id_studente, i.voto
FROM corsi c
LEFT JOIN iscrizioni i ON c.id_corso = i.id_corso;

-- Inseriamo dati per creare uno scenario con "orfani" in entrambe le tabelle
-- Studente senza iscrizioni (Marco) e iscrizione senza studente (corso 104)
INSERT INTO studenti (id, nome, citta, eta) VALUES
    (5, 'Marco', 'Torino', 24);  -- Studente senza iscrizioni

INSERT INTO iscrizioni (id_studente, id_corso, voto) VALUES
    (NULL, 104, NULL);  -- Iscrizione senza studente associato

-- FULL JOIN simulato in MariaDB: UNION di LEFT e RIGHT JOIN
-- L'obiettivo è ottenere TUTTI i record da entrambe le tabelle,
-- incluse le righe che non hanno corrispondenza nell'altra tabella
SELECT s.id, s.nome, i.id_studente, i.id_corso, i.voto
FROM studenti s
LEFT JOIN iscrizioni i ON s.id = i.id_studente

UNION

SELECT s.id, s.nome, i.id_studente, i.id_corso, i.voto
FROM studenti s
RIGHT JOIN iscrizioni i ON s.id = i.id_studente;

-- NOTA: In altri DBMS (PostgreSQL, SQL Server) si usa direttamente FULL OUTER JOIN
-- SELECT * FROM studenti s FULL OUTER JOIN iscrizioni i ON s.id = i.id_studente;

-- NATURAL JOIN: unisce automaticamente le tabelle sulle colonne con lo stesso nome
-- L'obiettivo è collegare studenti e voti senza specificare ON (trova 'id' in comune)
SELECT *
FROM studenti
NATURAL JOIN voti;  -- Equivale a: JOIN voti ON studenti.id = voti.id

SELECT *
FROM dipendenti
NATURAL JOIN progetti;

 La query equivale a: SELECT * 
 FROM dipendenti JOIN progetti 
 ON dipendenti.id_dip = progetti.id_dip
 AND dipendenti.nome = progetti.nome  ← PROBLEMA

SELECT d.id_dip, d.nome AS dipendente, 
       p.nome AS progetto, d.stipendio, p.budget
FROM dipendenti d
JOIN progetti p ON d.id_dip = p.id_dip;

SELECT *
FROM ordini
NATURAL JOIN clienti;
-- Oggi funziona: unisce solo su id_cliente ✓

SELECT *
FROM ordini
NATURAL JOIN clienti;

-- ORA equivale a: SELECT *
FROM ordini JOIN clienti 
ON ordini.id_cliente = clienti.id_cliente
AND ordini.note = clienti.note   ← DISASTRO! 



-- "Urgente" ≠ "VIP", "Standard" ≠ "Nuovo"
-- Risultato: ZERO righe! La query si rompe silenziosamente!

SELECT *
FROM prodotti
NATURAL JOIN categorie;

-- Unisce su: id_cat AND stato
-- Il Mouse ha stato='sospeso', la categoria ha stato='attivo'
-- → Mouse ESCLUSO dal risultato!