Database 5 esercizi con SQL

Database 5 esercizi con SQL

Di Giuseppe Piccininni


 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

Venditore_id

Nome_venditore

Città

Commissione

5001

Mario Rossi

Napoli

0.15

5002

Franco Neri

Palermo

0.13

5005

Gigi Proietti

Roma

0.11

5006

Michele Chetone

Treviso

0.14

5007

Davide Guidi

Pordenone

0.13

 

Nome tabella: cliente

Cliente_id

Nome_cliente

Città

Categoria

Venditore_id

3002

Giuseppe Verdi

Roma

100

5001

3007

Max Angioni

Roma

200

5001

3005

Nicola di Bari

Bari

200

5002

3008

Antonio Clerici

Napoli

300

5002

3004

Simone Vespa

Venezia

300

5006

3009

Ezio Greggio

Treviso

100

5007

 

  1. 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

Cliente_id

Nome

Città

Categoria

Venditore_id

3002

Giuseppe Verdi

Roma

100

5001

3007

Max Angioni

Roma

200

5001

3005

Nicola di Bari

Bari

200

5002

3008

Antonio Clerici

Napoli

300

5002

3004

Simone Vespa

Venezia

300

5006

3009

Ezio Greggio

Treviso

100

5007

 

  1. 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.
  2. 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

Dipendente_id

Nome_dipendente

e-mail_id

100

Giuseppe Verdi

giu.verdi@abc.com

101

Paolo Limiti

limiti@abc.com

102

Davide Guidi

guidi@abc.com

103

Antonio Clerici

clerici@abc.com

104

Simone Vespa

vespa@abc.com

 

  1. 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

Esame_id

Materia_id

Anno_esame

Num_studenti

71

201

2017

5146

71

201

2018

3545

71

202

2018

5945

71

202

2019

2500

71

203

2017

2500

72

201

2018

3500

72

202

2017

3651

73

201

2018

2647

 

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;

 

  1. 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;

 

  1. 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;

 

  1. 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;
  1. 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.

Impara a programmare in 3 mesi con il Corso di Coding Hackademy su Laravel PHP

Diventa Sviluppatore web in 3 mesi

Scopri il coding bootcamp Hackademy

Programma Completo