136 lines
3.0 KiB
SQL
136 lines
3.0 KiB
SQL
DROP TABLE IF EXISTS MovieCharacter;
|
|
DROP TABLE IF EXISTS MovieGenre;
|
|
DROP TABLE IF EXISTS Movie;
|
|
DROP TABLE IF EXISTS Person;
|
|
DROP TABLE IF EXISTS Genre;
|
|
DROP TABLE IF EXISTS Counter;
|
|
|
|
CREATE TABLE Person (
|
|
PersonID LONG PRIMARY KEY,
|
|
Name VARCHAR(100),
|
|
Sex char
|
|
);
|
|
|
|
CREATE TABLE Genre (
|
|
GenreID LONG PRIMARY KEY,
|
|
Genre VARCHAR(100)
|
|
);
|
|
|
|
CREATE TABLE Movie (
|
|
MovieID LONG PRIMARY KEY,
|
|
Title VARCHAR(100),
|
|
Year INT,
|
|
Type CHAR
|
|
);
|
|
CREATE TABLE MovieGenre (
|
|
MovieID LONG,
|
|
GenreID LONG,
|
|
PRIMARY KEY (MovieID, GenreID),
|
|
FOREIGN KEY (MovieID)
|
|
REFERENCES Movie(MovieID)
|
|
ON DELETE CASCADE,
|
|
FOREIGN KEY (GenreID)
|
|
REFERENCES Genre(GenreId)
|
|
ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE MovieCharacter (
|
|
MovCharID LONG PRIMARY KEY,
|
|
Character VARCHAR(100),
|
|
Alias VARCHAR(100),
|
|
Position INT,
|
|
PlayedBy LONG,
|
|
MovieID LONG,
|
|
FOREIGN KEY (PlayedBy)
|
|
REFERENCES Person(PersonID)
|
|
ON DELETE CASCADE,
|
|
FOREIGN KEY (MovieID)
|
|
REFERENCES Movie(MovieID)
|
|
ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE Counter (
|
|
TableName VARCHAR(100) PRIMARY KEY,
|
|
Counter LONG
|
|
);
|
|
|
|
INSERT INTO `Person` VALUES
|
|
(0, 'Peter Fritz Willi Lustig', 'M'),
|
|
(1, 'Ryan Reynolds', 'M'),
|
|
(2, 'Tom Holland', 'M'),
|
|
(3, 'Robert Downey Jr', 'M'),
|
|
(4, 'Emma Watson', 'F'),
|
|
(5, 'Daniel Radcliffe', 'M'),
|
|
(6, 'Benedict Cumberbatch', 'M'),
|
|
(7, 'Martin Freeman', 'M');
|
|
|
|
INSERT INTO `Counter` VALUES ('Person', 8);
|
|
|
|
INSERT INTO `Genre` VALUES
|
|
(0, 'Kinder'),
|
|
(1, 'Comedy'),
|
|
(2, 'Action'),
|
|
(3, 'Superhelden'),
|
|
(4, 'Fantasy'),
|
|
(5, 'Mystery'),
|
|
(6, 'Drama'),
|
|
(7, 'Adventure');
|
|
|
|
INSERT INTO `Counter` VALUES ('Genre', 8);
|
|
|
|
INSERT INTO `Movie` VALUES
|
|
(0, 'Löwenzahn', 1981, 'S'),
|
|
(1, 'Deadpool', 2016, 'M'),
|
|
(2, 'The Proposal', 2009, 'M'),
|
|
(3, 'Spider-Man: Homecoming', 2017, 'M'),
|
|
(4, 'Avengers: Endgame', 2019, 'M'),
|
|
(5, 'Harry Potter and the Philosophers Stone', 2001, 'M'),
|
|
(6, 'Doctor Strange', 2016, 'M'),
|
|
(7, 'The Hobbit', 2012, 'M'),
|
|
(8, 'Sherlock', 2010, 'S');
|
|
|
|
|
|
INSERT INTO `Counter` VALUES ('Movie', 9);
|
|
|
|
INSERT INTO MovieGenre VALUES
|
|
(0, 0),
|
|
(0, 1),
|
|
(1, 1),
|
|
(1, 2),
|
|
(1, 3),
|
|
(2, 6),
|
|
(2, 1),
|
|
(3, 2),
|
|
(3, 3),
|
|
(4, 2),
|
|
(4, 3),
|
|
(5, 4),
|
|
(5, 7),
|
|
(6, 2),
|
|
(6, 3),
|
|
(6, 4),
|
|
(7, 4),
|
|
(7, 7),
|
|
(8, 5),
|
|
(8, 6);
|
|
|
|
INSERT INTO `MovieCharacter` VALUES
|
|
(0, 'Peter Lustig', 'Peter', 0, 0, 0),
|
|
(1, 'Wade Wilson', 'Deadpool', 0, 1, 1),
|
|
(2, 'Andrew Paxton', 'Andrew', 0, 1, 2),
|
|
(3, 'Peter Parker', 'Spider-Man', 0, 2, 3),
|
|
(4, 'Tony Stark', 'Iron-Man', 0, 3, 3),
|
|
(5, 'Peter Parker', 'Spider-Man', 0, 2, 4),
|
|
(6, 'Tony Stark', 'Iron-Man', 0, 3, 4),
|
|
(7, 'Stephen Strange', 'Doctor Strange', 0, 6, 4),
|
|
(8, 'Hermione Granger', 'Hermine', 0, 4, 5),
|
|
(9, 'Harry Potter', 'The boy who lived', 0, 5, 5),
|
|
(10, 'Stephen Strange', 'Doctor Strange', 0, 6, 6),
|
|
(11, 'Bilbo Baggins', 'Bilbo Beutlin', 0, 7, 7),
|
|
(12, 'Sauron', 'Annatar', 0, 6, 7),
|
|
(13, 'Smaug', 'Smaug the Golden', 0, 6, 7),
|
|
(14, 'Sherlock Holmes', 'Consulting Detective Holmes', 0, 6, 8),
|
|
(15, 'John H. Watson', 'Dr. Watson', 0, 7, 8);
|
|
|
|
INSERT INTO `Counter` VALUES ('MovieCharacter', 16);
|