Database 5 esercizi con SQL

 Nel mondo dell’informatica i database ricoprono un ruolo cruciale. Per questo il ruolo del programmatore deve avere un’ampia conoscenza dei database e del loro utilizzo. Per interagire con un database, il linguaggio universale adottato è quello SQL, da anni ormai integrato come standard per gestire i database.

Cos’è un database?

Secondo Oracle un database è un insieme di informazioni (o dati) strutturati e archiviati elettronicamente in un sistema informatico. Di solito, il database viene controllato da un sistema DBMS (Database Management System). Si fa riferimento ai dati, al sistema DBMS e alle applicazioni associate come sistema di database, spesso abbreviato solo in database. Un database può essere di vari tipi, il più utilizzato e conosciuto è il database relazionale.

Per prendere dimestichezza con i database bisogna imparare il linguaggio SQL. 

Cos’è SQL?

Il significato di SQL corrisponde ad un acronimo: Structured Query Language, è un linguaggio di programmazione utilizzato da quasi tutti i database relazionali per eseguire query, manipolare e definire i dati, nonché fornire il controllo dell'accesso. Sviluppato per la prima volta negli anni '70 e ancora oggi fondamentale per la gestione dei DB.

Una volta fissati questi concetti, per imparare il linguaggio SQL e la logica dietro i database relazionali, è importante esercitarsi. Fa parte del processo d’apprendimento, come lo è per tutti i linguaggi di programmazione.

Per farlo vedremo una serie di esercizi sul linguaggio SQL, di difficoltà graduale, in modo tale da ricoprire tutte le peculiarità del linguaggio.

1. Scrivi un'istruzione SQL per creare un prodotto cartesiano tra venditore e cliente, ovvero, ogni venditore apparirà per tutti i clienti e viceversa per quel venditore che appartiene a quella città. 

Nome tabella: venditore

Nome tabella: cliente

2. Dalla tabella seguente, scrivi una query SQL per trovare tutti i clienti. Ordina il set di risultati per cliente_id. Restituisci nome_cliente, città, categoria.

Nome tabella: cliente

3. Dalle tabelle riportate nell’esercizio 1, scrivi una query SQL per individuare tutti i clienti e il venditore che lavora per loro. Restituisci il nome del cliente e il nome del venditore.
4. Dalla tabella seguente, scrivi una query SQL per trovare tutte le e-mail duplicate (senza lettere maiuscole) dei dipendenti. Restituisci e-mail_id.

Nome tabella: dipendenti

5. Dalle tabelle seguenti scrivi una query SQL per trovare quegli studenti che hanno ottenuto il 100% dei voti in ogni materia per tutto l'anno. Restituisci l'ID della materia, il nome della materia, gli studenti per tutto l'anno.

Nome tabella: test_esame

Si possono svolgere tantissime operazioni tramite il linguaggio SQL, è possibile incrociare i dati in differenti modi, partendo da semplici tabelle, svolgendo operazioni di calcolo sui dati contenuti, quando possibile, oppure creare nuove viste in base alle informazioni che abbiamo a disposizione. È un requisito fondamentale per la carriera di un programmatore, specialmente per un web developer. È uno dei linguaggi in cui la pratica conta di più, perché soltanto vedendo i risultati di una query si capisce davvero come modellare i dati e come i vari operatori logici (e non) li trattano in SQL. Vediamo insieme le soluzioni:

1. La query da scrivere in questo esercizio è molto basilare. Per iniziare, ti aiuta a capire come funzionano le varie keyword del linguaggio SQL (SELECT, FROM, etc…)

SELECT *
FROM venditore a
CROSS JOIN cliente b
WHERE a.città IS NOT NULL;

2. Spesso ordinare le tabelle aiuta molto nel capire come strutturare la query. In questo esercizio puoi apprendere la funzionalità dell’operatore di raggruppamento ORDER BY.

SELECT nome,città,categoria
FROM cliente
ORDER BY cliente_id;

3. Nel paragrafo precedente abbiamo parlato di “incrociare i dati”. Questo esercizio ti fa capire come farlo, scrivendo una query molto semplice tra due tabelle con delle informazioni in comune. 

SELECT cliente.nome_cliente, nome_venditore
FROM cliente,venditore
WHERE venditore.venditore_id = cliente.venditore_id;

4. Questo esercizio, un po’ più articolato, mette insieme tutti i concetti visti finora, in più ci fa capire come creare una nuova vista (tabella) incrociando i dati di quelle esistenti.

CREATE TABLE IF NOT EXISTS dipendenti(dipendenti_id int, nome_dipendente varchar(255), email_id varchar(255));
TRUNCATE TABLE dipendenti;
INSERT INTO dipendenti (dipendenti_id, nome_dipendente, email_id) VALUES ('100','Giuseppe Verdi', ' giu.verdi@abc.com ');
INSERT INTO dipendenti (dipendenti_id, nome_dipendente, email_id) VALUES ('101','Paolo Limiti', 'limiti@abc.com');
INSERT INTO dipendenti (dipendenti_id, nome_dipendente, email_id) VALUES ('102','Davide Guidi', 'guidi@abc.com');
INSERT INTO dipendenti (dipendenti_id, nome_dipendente, email_id) VALUES ('103','Antonio Clerici', 'clerici@abc.com');
INSERT INTO dipendenti (dipendenti_id, nome_dipendente, email_id) VALUES ('104','Simone Vespa', 'vespa@abc.com');
SELECT * FROM dipendenti;

SELECT email_id FROM
(
SELECT email_id, COUNT(email_id) AS nuOfAppearence
FROM dipendenti
GROUP BY email_id
) AS countEmail
WHERE nuOfAppearence> 1;

5. Infine, ancora un esercizio più completo. In questo caso dovrai lavorare molto sui singoli dati, andando ad utilizzare più operatori, di calcolo, di inserimento e di gestione delle tabelle.

CREATE TABLE test_esame (esame_id int NOT NULL, materia_id int NOT NULL, anno_esame int NOT NULL, num_studenti int,
PRIMARY KEY (esame_id, materia_id, anno_esame));
INSERT INTO test_esame VALUES (71,201,2017,5146);
INSERT INTO test_esame VALUES (72,202,2017,3651);
INSERT INTO test_esame VALUES (73,202,2018,4501);
INSERT INTO test_esame VALUES (71,202,2018,5945);
INSERT INTO test_esame VALUES (73,201,2018,2647);
INSERT INTO test_esame VALUES (71,201,2018,3545);
INSERT INTO test_esame VALUES (73,201,2019,2647);

CREATE TABLE test_materia (materia_id int NOT NULL unique, nome_materia varchar(255));
INSERT INTO test_materia VALUES (201,Matematica);
INSERT INTO test_materia VALUES (202,Fisica);
INSERT INTO test_materia VALUES (203,Chimica);

SELECT s.materia_id, p.nome_materia,
SUM(s.num_studenti) 'Studenti per l’intero anno'
FROM test_esami s
JOIN test_materia p
ON s. materia_id = p. materia_id
GROUP BY s. materia_id;

SELECT s1.materia_id, p.nome_materia,s1.anno_esame as primo_anno, s1.num_studenti
FROM test_esame s1
JOIN test_materia p on s1.materia_id = p.materia_id
JOIN (SELECT materia_id, min(anno_esame) min_anno
FROM test_esame GROUP BY materia_id) s2
ON s1.materia_id = s2.materia_id
AND s1.anno_esame = s2.min_anno;

Svolgere esercizi di coding online per prendere confidenza con i database, le loro tabelle e gli operatori del linguaggio SQL è una fase necessaria per poter gestire i dati senza dover rallentare il proprio percorso di apprendimento.