Torniamo a parlare di MySQL dopo qualche anno spiegando step by step un esercizio su “Come Creare un database della propria raccolta ebook”.

Come ulteriore punto di riferimento potete prendere anche la guida di W3School.

ebook è la tabella principale che contiene i seguenti campi:

• ID_Titolo chiave primaria intero auto incrementabile obbligatorio
• Titolo alfanumerico obbligatorio
• Genere testo obbligatorio
• Formato
• ID_Autore chiave esterna
• ID_Editrice chiave esterna

Tra Autore ed ebook c’è una relazione uno a molti, visto che un libro può appartenere a un solo autore ma un autore può aver scritto più libri.

I campi di Autore sono:

• ID_Autore chiave primaria intero auto incrementabile obbligatorio
• Nome alfanumerico obbligatorio
• Cognome alfanumerico obbligatorio
• Data di Nascita data obbligatorio
• Nazionalità alfanumerico obbligatorio

Tra Casa Editrice ed ebook c’è una relazione uno a molti, come vista sopra per Autore.

I campi di Casa Editrice sono:

• ID_Editrice chiave primaria intero auto incrementabile obbligatorio
• Nome alfanumerico obbligatorio
• Luogo alfanumerico obbligatorio

Creare il database

Create database libri_digitali;

Creare le tabelle

Create table ebook 
(ID_Titolo int auto_increment not null primary key, 
Titolo varchar(255) not null, 
Genere varchar(255) not null, 
Formato varchar(4) not null, 
ID_Autore int not null
ID_Editrice int not null);

Create table Autore 
(ID_Autore int not null auto_increment primary key, 
Nome varchar(255) not null, 
Cognome varchar(255) not null, 
Data_Nascita date not null);

Create table Editrice
(ID_Editrice int not null auto_increment primary key,
Edizione varchar(255) not null,
Luogo varchar(255) not null);

Definire le chiavi esterne

Alter table ebook foreign key(ID_Autore) references autore(ID_Autore);
Alter table ebook foreign key(ID_Editrice) references Editrice(ID_Editrice);

Inserire Dati

Insert into autore(Nome, Cognome, Data_Nascita, nazionalita) values("Fedor Michajlovic","Dostoevkij","1821-11-18","Russa"), ("Arthur","Schnitzler","1862-05-15","Austriaco"), ("Giovanni", "Verga","1840-09-02","Italiana"), ("Luigi", "Pirandello","1867-06-28","Italiana"), ("Leonardo","Sciascia","1921-01-08","Italiana"), ("Oscar","Wilde","1854-10-16","Irlandese"),("George","Orwell","1903-06-25","Indiano"),("Victor","Hugo","1802-02-26","Francese"),("Susanna","Tamaro","1957-12-12","Italiana"),("Jane","Austen","1775-12-16","Brittanica"),("S.S.","Van Dine","1888-10-15","Statunitense"), ("Mark","Twain","1835-11-30","Statunitense"),("Primo","Levi","1919-07-31","Italiano");

INSERT INTO `ebook` (`ID_Titolo`, `Titolo`, `Genere`, `Formato`, `Anno`, `ID_Autore`, `ID_Editrice`) VALUES
(1, 'Delitto e castigo', 'Drammatico', 'epub', 1866, 1, 1),
(2, 'Doppio sogno', 'Drammatico', 'epub', 1925, 2, 2),
(3, 'I Malavoglia', 'Drammatico', 'epub', 1881, 3, 1),
(4, 'Il fu Mattia Pascal', 'Grottesco', 'epub', 1904, 4, 3),
(5, 'Il giocatore', 'Drammatico', 'epub', 1866, 1, 3),
(6, 'Il giorno della civetta', 'Giallo', 'epub', 1961, 5, 4),
(7, 'Il ritratto di Dorian Gray', 'Fantasy', 'epub', 1890, 6, 1),
(8, 'L\'enigma dell\'alfiere', 'Giallo', 'epub', 1928, 11, 2),
(9, 'La fattoria degli animali', 'Fantasy', 'epub', 1945, 7, 5),
(10, 'Note-Dame de Paris', 'Storico', 'epub', 1831, 8, 2),
(11, 'Ragione e sentimento', 'Sentimentale', 'epub', 1811, 10, 2),
(12, 'Storia di una capinera', 'Drammatico', 'epub', 1869, 3, 6),
(13, 'Una storia semplice', 'Giallo', 'epub', 1989, 5, 4),
(14, 'Va\' dove ti porta il cuore', 'Melodramma', 'epub', 1994, 9, 7),
(15, 'Il diario di Adamo ed Eva', 'Satira', 'pdf', 1906, 12, 9),
(16, 'Se questo è un uomo', 'Drammatico', 'pdf', 1947, 13, 8);

Selezionare Dati

Alcuni semplici esempi di visualizzare dati in ordine:

1. mostra tutto il contenuto della tabella ebook;
2. mostra solo i campi titolo e genere della tabella ebook;
3. conta numero ebook;
4. mostra i titoli di genere drammatico;
5. mostra titoli drammatici o sentimentali;
6. mostra titoli drammatici nel formato pdf;
7. mostra titoli pubblicati nel XX secolo (1900-1999) e ordina per anno e titolo crescente;
8. mostra tutti i titoli che iniziano con la lettera s;
9. mostra tutti gli autori nati nel XIX secolo (1800-1899);
10. mostra il titolo pubblicato più recentemente;
11. mostra il titolo pubblicato meno recentemente;
12. mostra tutti i titoli e i rispettivi autori;
13. mostra titoli e autori pubblicati nel XIX secolo (1800-1899);
14. mostra ultimi 10 titoli e rispettivi autori inseriti nel database;
15. mostra tutti i romanzi scritti da Pirandello;
16. raggruppa autori italiani nati nel XIX secolo (1800-1899) e indica numero di opere;
17. raggruppa case editrici e indica numero opere di tutte le case editrici con sede a Milano;
18. mostra titolo, genere, anno, nome, cognome, edizione e luogo raggruppa per titolo;
19. mostra titolo delle opere di Verga pubblicate da Garzanti;
20. Calcola la media dell’anno di pubblicazione dalla tabella ebook;
21. Somma tutti gli anni della tabella ebook;

Select * from ebook;

Select titolo, genere from ebook;

Select count(*) as numero_ebook from ebook;

Select titolo from ebook where Genere="Drammatico";

Select titolo from ebook where Genere="Drammatico" OR Genere="Sentimentale";

Select Titolo FROM ebook where Genere="Drammatico" AND Formato="PDF";

Select titolo, anno from ebook where anno BETWEEN 1900 and 1999 order by anno, titolo ASC;

Select titolo from ebook where titolo like"s%";

Select Nome, Cognome, Nazionalita from autore where year(Data_Nascita) between 1800 and 1899;

Select max(Anno) as Ultima_Pubblicazione, Titolo from ebook;

Select MIN(Anno) as Ultima_Pubblicazione, Titolo from ebook;

SELECT ebook.Titolo, autore.nome, autore.Cognome from ebook, autore where ebook.ID_Autore=autore.ID_Autore;

SELECT ebook.Titolo, autore.nome, autore.Cognome from ebook, autore where ebook.ID_Autore=autore.ID_Autore AND ebook.Anno>=1800 AND ebook.Anno<=1899;

SELECT ebook.Titolo, autore.nome, autore.Cognome from ebook, autore where ebook.ID_Autore=autore.ID_Autore order by ebook.ID_Titolo DESC LIMIT 10;

SELECT ebook.Titolo, autore.nome, autore.Cognome from ebook, autore where ebook.ID_Autore=autore.ID_Autore AND autore.Cognome="Pirandello";

SELECT count(*) as N_Opere, autore.Nome, autore.Cognome from autore,ebook where ebook.ID_Autore=autore.ID_Autore AND nazionalita="Italiana" and year(Data_Nascita) BETWEEN 1800 and 1899 GROUP by autore.Cognome;

Select count(*) as N_Opere, editrice.Edizione from editrice, ebook where ebook.ID_Editrice=editrice.ID_Editrice and editrice.Luogo="Milano" GROUP by editrice.Edizione;

Select ebook.Titolo, ebook.Genere, ebook.Anno, autore.Nome, autore.Cognome, editrice.Edizione, editrice.Luogo FROM ebook,autore,editrice where ebook.ID_Autore=autore.ID_Autore AND ebook.ID_Editrice=editrice.ID_Editrice group by ebook.Titolo;

Select ebook.Titolo FROM ebook,autore,editrice where ebook.ID_Autore=autore.ID_Autore AND ebook.ID_Editrice=editrice.ID_Editrice AND autore.Cognome="Verga" AND editrice.Edizione="Garzanti" group by ebook.Titolo;

Select avg(Anno) from ebook;

Select sum(Anno) from ebook;