Esercizio in MySQL

Database in MySQL

CREATE DATABASE Romanzi;  

CREATE TABLE Romanzi (CodiceR varchar(20) PRIMARY KEY UNIQUE NOT NULL, 
Titolo varchar(20) NOT NULL,
NomeAut varchar(20) NOT NULL
	REFERENCES Autori (NomeAut),
Anno integer NOT NULL,
CHECK (Anno>1000)); 

CREATE TABLE Personaggi (NomeP varchar(20) PRIMARY KEY UNIQUE NOT NULL,
CodiceR varchar(20) NOT NULL
	REFERENCES Romanzi (CodiceR),
Sesso char(1) NOT NULL,
Ruolo varchar(10) NOT NULL);

CREATE TABLE Autori (NomeAut varchar(20) PRIMARY KEY UNIQUE NOT NULL,
AnnoN integer NOT NULL,
CHECK (AnnoN>1000), 
Nazione varchar(20) NOT NULL);

CREATE TABLE Film (CodiceF varchar(20) PRIMARY KEY UNIQUE NOT NULL,
Titolo varchar(20) NOT NULL,
Regista varchar(20) NOT NULL,
Produttore varchar(20) NOT NULL,
Anno integer NOT NULL,
CHECK (Anno>1000),
CodiceR varchar(20) NOT NULL
	REFERENCES Romanzi (CodiceR)); 

INSERT INTO Romanzi VALUES ('A1', 'HP', 'JKRowling', '1998');
INSERT INTO Romanzi VALUES('A2', 'SDA', 'Tolkien', '1973');
INSERT INTO Personaggi VALUES('Harry', 'A1', 'M', 'Mago');
INSERT INTO Personaggi VALUES('Frodo', 'A2', 'M', 'Hobbit');
INSERT INTO Autori VALUES('JKRowling', '1968', 'Ing');
INSERT INTO Autori VALUES('Tolkien', '1912', 'Ted');
INSERT INTO Film VALUES('B1', 'HP Film', 'Idontknow', 'Warner Bros', '2000', 'A1');
INSERT INTO Film VALUES('B2', 'SDA Film', 'Nonloso', 'Pixar', '1990', 'A2');

// Il titolo dei romanzi del 20° secolo 
SELECT Titolo
FROM Romanzi
WHERE Anno>1899 AND Anno<1999;

// Il titolo, l’autore e l’anno di pubblicazione dei romanzi di autori inglesi, ordinati per autore e, per
lo stesso autore, ordinati per anno di pubblicazione 
SELECT R.Titolo, R.NomeAut, R.Anno
FROM Romanzi R, Autori A
WHERE A.NomeAut=R.NomeAut AND A.Nazione='Ing' GROUP BY NomeAut, Anno;

// I personaggi principali (ruolo =”Hobbit”) dei romanzi di autori viventi.
SELECT NomeP
FROM Personaggi
WHERE Ruolo='Hobbit';

// I romanzi dai quali è stato tratto un film con lo stesso titolo del romanzo 
SELECT * 
FROM Romanzi R, Film F
WHERE F.CodiceR=R.CodiceR AND R.Titolo=F.Titolo;

// Il titolo, il regista e l’anno dei film tratti dal romanzo “SDA”
SELECT F.Titolo, F.Regista, F.Anno
FROM Film F, Romanzi R
WHERE F.CodiceR=R.CodiceR AND R.Titolo='SDA';

// Per ogni autore inglese, l’anno del primo e dell’ultimo romanzo. 
SELECT A.NomeAut, MAX(R.Anno), MIN(R.Anno)
FROM Autori A, Romanzi R
WHERE A.NomeAut=R.NomeAut AND A.Nazione='Ing'
GROUP BY A.NomeAut;

// I nomi dei personaggi che compaiono in più di un romanzo, ed il numero dei romanzi nei quali
 compaiono
SELECT P.NomeP, COUNT(*) AS Apparizioni
FROM Personaggi P, Romanzi R
WHERE P.CodiceR=R.CodiceR 
GROUP BY P.NomeP
HAVING Apparizioni>=1;

// I romanzi di autori inglesi dai quali è stato tratto più di un film 
SELECT R.Titolo
FROM Romanzi R, Autori A
WHERE A.Nazione='Ing' AND A.NomeAut=R.NomeAut AND 1 < (SELECT COUNT(*) FROM Film F WHERE F.CodR=R.CodR);

// Il titolo dei romanzi dai quali non è stato tratto un film
SELECT R.Titolo
FROM Romanzi R
WHERE NOT EXISTS (SELECT * FROM Film F WHERE F.CodR=R.CodR);