我的数据库中有三个表:
- "Film",其中包含有关电影的详细信息
- "Sale",其中包含关于放映电影的其他地方的详细信息
- "Proiezioni",其中包含有关投影的详细信息,外部引用了前面两个表
这个问题涉及到只在"比萨"城放映的所有电影的片名.
select f.Titolo
from Film f
where not exists(select *
from Sale s, Proiezioni p
where p.xCodSala=s.CodSala AND f.CodFilm=p.xCodFilm AND Citta<>'Pisa');
这个解决方案的问题是,它给我提供了仅在"Pisa"中放映的电影,但也提供了根本没有放映的(未请求的)电影(不在表"Proiezioni"中).
您可以在下面找到表生成和填充(ddl)的代码.
expected output是唯一一行对应于电影标题"I Predatori"的"CodFilm = 'f03'".
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');