Movies

Aus ProgrammingWiki

Spielwiese: Movies

Quellen http://www.imdb.com/, http://sqlzoo.net/data/datadist.cgi?linenumber=1&linecount=50&repeatpost=on&target=&engine=mysql&tabname=createmovie&tabname=tabactor&tabname=tabmovie&tabname=tabcasting&

DROP TABLE IF EXISTS personen;
CREATE TABLE personen (
  id INTEGER NOT NULL, 
  vorname VARCHAR(35),
  nachname VARCHAR(35),
  geschlecht VARCHAR(1),
  PRIMARY KEY (id)
);

INSERT INTO personen VALUES (1, 'Sean', 'Connery', 'm');
INSERT INTO personen VALUES (2, 'Alec', 'Baldwin', 'm');
INSERT INTO personen VALUES (3, 'John', 'McTiernan', 'm');
INSERT INTO personen VALUES (4, 'Robert', 'Redford', 'm');
INSERT INTO personen VALUES (5, 'Sydney', 'Pollack', 'm');
INSERT INTO personen VALUES (6, 'Faye', 'Dunaway', 'w');
INSERT INTO personen VALUES (7, 'Craig', 'Sheffer', 'm');
INSERT INTO personen VALUES (8, 'Brad', 'Pitt', 'm');
INSERT INTO personen VALUES (9, 'Kathryn ', 'Bigelow', 'w');
INSERT INTO personen VALUES (10, 'Jeremy', 'Renner', 'm');
INSERT INTO personen VALUES (11, 'Anthony', 'Mackie', 'm');
INSERT INTO personen VALUES (12, 'Clint', 'Eastwood', 'm');
INSERT INTO personen VALUES (13, 'Meryl', 'Streep', 'w');
INSERT INTO personen VALUES (14, 'Hilary', 'Swank', 'w');



DROP TABLE IF EXISTS filme;
CREATE TABLE filme (
  id INTEGER NOT NULL,
  titel VARCHAR(70),
  jahr DECIMAL(4) ,
  bewertung FLOAT,
  anzahl_bewertungen INTEGER,
  regisseur INTEGER,
  PRIMARY KEY (id),
  FOREIGN KEY (regisseur) REFERENCES personen(id)
);

INSERT INTO filme VALUES (1, 'The Hunt for Red October', 1990, 7.6, 56882, 3);
INSERT INTO filme VALUES (2, 'Three Days of the Condor', 1975, 7.5, 14777, 5);
INSERT INTO filme VALUES (3, 'A River Runs Through It', 1992, 7.1, 20429, 4);
INSERT INTO filme VALUES (4, 'The Hurt Locker', 2008, 7.8, 92696, 9);
INSERT INTO filme VALUES (5, 'The Bridges of Madison County', 1995, 7.8, 22833, 12);
INSERT INTO filme VALUES (6, 'Million Dollar Baby', 2004, 8.2, 151594, 12);


DROP TABLE IF EXISTS rollen;
CREATE TABLE rollen (
  film_id INTEGER NOT NULL,
  schauspieler_id INTEGER NOT NULL,
  rollen_vorname VARCHAR(35),
  rollen_nachname VARCHAR(35),
  reihenfolge INTEGER,
  PRIMARY KEY (film_id, schauspieler_id),
  FOREIGN KEY (film_id) REFERENCES filme(id),
  FOREIGN KEY (schauspieler_id) REFERENCES personen(id)
);

INSERT INTO rollen VALUES (1, 1, 'Marko', 'Ramius', 1);
INSERT INTO rollen VALUES (1, 2, 'Jack', 'Ryan', 2);
INSERT INTO rollen VALUES (2, 4, 'Joseph', 'Turner', 1);
INSERT INTO rollen VALUES (2, 6, 'Kathy', 'Hale', 2);
INSERT INTO rollen VALUES (3, 7, 'Norman', 'Maclean', 1);
INSERT INTO rollen VALUES (3, 8, 'Paul', 'Maclean', 2);
INSERT INTO rollen VALUES (4, 10, 'William', 'James', 1);
INSERT INTO rollen VALUES (4, 11, 'JT', 'Sanborn', 2);
INSERT INTO rollen VALUES (5, 12, 'Robert', 'Kincaid', 1);
INSERT INTO rollen VALUES (5, 13, 'Francesca', 'Johnson', 2);
INSERT INTO rollen VALUES (6, 12, 'Frankie', 'Dunn', 1);
INSERT INTO rollen VALUES (6, 14, 'Maggie', 'Fitzgerald', 2);

Alle Einträge anzeigen

SELECT * FROM personen;
SELECT * FROM filme;
SELECT * FROM rollen;

Movies

Spielwiese: Movies

Alle Einträge anzeigen

Suche