RMiL5NUt

1# change this to use your own database
2use cbourke;
3
4# Exercise: create table(s) to store information on films, including directors and actors
5
6drop table if exists FilmActor;
7drop table if exists Actor;
8drop table if exists Film;
9drop table if exists Director;
10
11create table Director (
12  directorId integer primary key not null auto_increment,
13  firstName varchar(100),
14  lastName varchar(100)
15);
16
17insert into Director (directorId, firstName, lastName) values (10, 'David', 'Lynch');
18insert into Director (directorId, firstName, lastName) values (20, 'Tim', 'Miller');
19
20create table if not exists Film (
21  filmId integer primary key not null auto_increment,
22  title varchar(255),
23  releaseDate varchar(50) default '0000-00-00',
24  grossEarnings double default 0,
25  imdbRating double not null default 5,
26  eidr varchar(100) not null,
27  directorId integer not null,  
28  #TODO: consider genre varchar(50), probably 2-3 more tables!
29  #enforce via a constraint, a uniqueness for eidr values:
30  constraint uniqueEdir unique(eidr),
31  # these are nice, but not enforced:
32  constraint nonNegativeImdbRating check (imdb >= 0),
33  foreign key (directorId) references Director(directorId)
34  
35);
36
37insert into Film (directorId, title, eidr) values (
38  (select directorId from Director where firstName = 'David' and lastName = 'Lynch'), 
39  'Dune', '10.5240/7ECC-15E0-7585-62DA-DA8C-V');
40
41insert into Film (directorId, title, eidr, imdbRating) values (20, 'Deadpool', '10.5240/704E-D123-15DA-29AB-7FBD-G', -10.5);
42
43create table Actor (
44  actorId integer primary key not null auto_increment,
45  firstName varchar(100),
46  lastName varchar(100)
47);
48
49insert into Actor (actorId, firstName, lastName) values (1, 'Ryan', 'Reynolds');
50insert into Actor (actorId, firstName, lastName) values (2, 'Morena', 'Baccarin');
51insert into Actor (actorId, firstName, lastName) values (3, 'Brianna', 'Hildebrand');
52
53insert into Actor (actorId, firstName, lastName) values (4, 'Kyle', 'MacLachlan');
54insert into Actor (actorId, firstName, lastName) values (5, 'Patrick', 'Steward');
55
56insert into Actor (actorId, firstName, lastName) values (100, 'Nick', 'Cage');
57
58insert into Actor (firstName, lastName) values ('Tom', 'Hardy');
59
60insert into Actor (firstName, lastName) values ('Peter', 'O\'Toole');
61
62select * from Actor;
63create table FilmActor (
64  filmActorId integer primary key not null auto_increment,
65  actorId integer not null,
66  filmId integer not null,
67  foreign key (actorId) references Actor(actorId),
68  foreign key (filmId) references Film(filmId)
69);
70
71insert into FilmActor (actorId, filmId) values (1, 2); #Ryan Reynolds -> Deadpool
72insert into FilmActor (actorId, filmId) values (2, 2); #Baccarin -> Deadpool
73insert into FilmActor (actorId, filmId) values (3, 2); #Hildebrand -> Deadpool
74
75insert into FilmActor (actorId, filmId) values (4, 1); #Hildebrand -> Deadpool
76insert into FilmActor (actorId, filmId) values (5, 1); #Hildebrand -> Deadpool
77
78update Actor set lastName = 'Stewart' where actorId = 5;
79
80select * from Director d
81  join Film f on f.directorId = d.directorId
82  join FilmActor fa on fa.filmId = f.filmId
83  join Actor a on a.actorId = fa.actorId;