MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Film
(
    CodFilm char(6) not null,
    Titolo varchar(30) not null,
    AnnoProduzione int(4) null,
    Nazionalita varchar(20) null,
    Regista varchar(30) null,
    Genere varchar(15) null,
    PRIMARY KEY (CodFilm)
) engine=innodb;

create table Sale
(
    CodSala char(3) not null,
    Posti int(3) not null,
    Nome varchar(10) not null,
    Citta varchar(20) not null,
    PRIMARY KEY (CodSala)
) engine=innodb;

create table Proiezioni
(
    CodProiezione varchar(8) not null,
    xCodFilm char(6) not null,
    xCodSala char(3) not null,
    Incasso real not null,
    DataProiezione date not null,
    PRIMARY KEY (CodProiezione),
    FOREIGN KEY (xCodFilm) references Film (CodFilm) on delete cascade,
    FOREIGN KEY (xCodSala) references Sale (CodSala) on delete cascade
) engine=innodb;
    insert into Film values
    ("f01","Wall Street",1987,"Stati Uniti","Oliver Stone","Drammatico"),
    ("f02","Nothing Hill",1999,"Regno Unito","Roger Michell","Commedia"),
    ("f03","I Predatori",2020,"Italia","Pietro Castellitto","Commedia"),
    ("f04","Fight Club",1999,"Stati Uniti","David Fincher","Drammatico"),
    ("f05","Il mattino ha l'oro in bocca",2008,"Italia","Francesco Patierno","Drammatico"),
    ("f06","Basic Instinct",1992,"Stati Uniti","Paul Verhoeven","Thriller"),
    ("f07","About A Boy",2002,"Regno Unito","Paul Weitz, Chris Weitz","Commedia"),
    ("f08","Troy",2004,"Stati Uniti","Wolfgang Petersen","Azione"),
    ("f09","Platoon",1986,"Stati Uniti","Oliver Stone", "Guerra"),
    ("f10","Seven",1995,"Stati Uniti","David Fincher","Thriller");
    
    insert into Sale values
    ("s01",100,"Cineworld","Roma"),
    ("s02",125,"Madison","Rieti"),
    ("s03",90,"UCI","Milano"),
    ("s04",110,"The Space","Milano"),
    ("s05",50,"Greenwich","Pisa");
    
    insert into Proiezioni values
("p01","f04","s01",250000.50,'2022-01-07'),
("p02","f08","s03",300000.95,'2022-01-07'),
("p03","f08","s05",90000.00,'2022-02-05'),
("p04","f02","s01",18000,'2022-03-18'),
("p05","f02","s02",35775.80,'2022-04-20'),
("p06","f01","s04",92500.40,'2022-05-10'),
("p07","f09","s03",430000.45,'2022-05-13'),
("p08","f09","s03",22000,'2022-05-14'),
("p09","f03","s05",66000,'2022-05-20'),
("p10","f01","s02",280000,'2022-05-22'),
("p11","f01","s02",70000,'2022-05-23'),
("p12","f04","s04",16500,'2022-05-25');
SELECT Film.Titolo
FROM Film
JOIN Proiezioni ON Film.CodFilm = Proiezioni.xCodFilm
JOIN Sale ON Proiezioni.xCodSala = Sale.CodSala
GROUP BY 1
HAVING COUNT(*) = SUM(Sale.Citta = 'Pisa')


Titolo


I Predatori