hKw3hn4M

1BEGIN TRANSACTION;
2CREATE TABLE IF NOT EXISTS "MovieDimension" (
3	"ID"	INTEGER,
4	"name"	TEXT,
5	"genre"	TEXT,
6	PRIMARY KEY("ID" AUTOINCREMENT)
7);
8CREATE TABLE IF NOT EXISTS "CinemaDimension" (
9	"ID"	INTEGER,
10	"city"	TEXT,
11	"country"	TEXT,
12	PRIMARY KEY("ID" AUTOINCREMENT)
13);
14CREATE TABLE IF NOT EXISTS "CinemaFacts" (
15	"movie_id"	INTEGER,
16	"cinema_id"	INTEGER,
17	"ticketID"	INTEGER,
18	"billedPrice"	NUMERIC,
19	FOREIGN KEY("cinema_id") REFERENCES "CinemaDimension"("ID"),
20	FOREIGN KEY("movie_id") REFERENCES "MovieDimension"("ID")
21);
22INSERT INTO "MovieDimension" VALUES (1,'M','Should not be part'),
23 (2,'Dune','Science Fiction');
24INSERT INTO "CinemaDimension" VALUES (1,'Darmstadt','Germany'),
25 (2,'Paris','France');
26INSERT INTO "CinemaFacts" VALUES (2,2,1,10.5),
27 (1,1,2,5.5),
28 (1,2,123,12312);
29
30CREATE VIEW solution AS
31SELECT m.genre, COUNT(DISTINCT m.id) FROM CinemaFacts cf JOIN MovieDimension m ON cf.movie_id = m.id
32WHERE m.id NOT IN(SELECT cf.movie_id FROM CinemaDimension c JOIN CinemaFacts cf ON c.id = cf.cinema_id WHERE c.country = "Germany") GROUP BY m.genre;
33
34CREATE VIEW basic AS
35SELECT m.genre, count(m.id)
36FROM CinemaFacts cf JOIN CinemaDimension c ON cf.cinema_id = c.id JOIN MovieDimension m ON cf.movie_id = m.id
37WHERE c.country != "Germany"
38GROUP BY m.genre;
39
40CREATE VIEW left_join AS
41SELECT m.genre, count(m.id)
42FROM CinemaFacts cf
43LEFT JOIN CinemaDimension c ON cf.cinema_id = c.id
44LEFT JOIN MovieDimension m ON cf.movie_id = m.id
45WHERE c.country != "Germany"
46GROUP BY m.genre;
47
48COMMIT;
49