· 8 years ago · Mar 19, 2018, 06:16 PM
1-- M:N Example movie and actors
2
3drop database if exists Mar5MNExample;
4create database if not exists Mar5MNExample;
5use Mar5MNExample;
6
7create table movie
8(
9 movieID int primary key,
10 movieTitle varchar(25),
11 movieYearRel year
12);
13
14insert into movie values
15 (101,'Postcards', 1990),
16 (102,'Dangal', 2016),
17 (103,'Star Wars', 1977),
18 (104,'Fargo', 2014),
19 (105,'PK',2014),
20 (106,'Mystery Men',1991);
21
22
23create table actor
24(
25 actorID int primary key,
26 actorFName varchar(25),
27 actorLName varchar(25)
28);
29
30insert into actor values
31 (11, 'Carrie','Fisher'),
32 (22, 'Harrison', 'Ford'),
33 (33, 'Aamir', 'Khan'),
34 (44, 'William','Macy');
35
36
37create table movieactor
38(
39 movieID int,
40 actorID int,
41
42 primary key (movieID, actorID),
43
44 foreign key (movieID)
45 references movie(movieID),
46 foreign key (actorID)
47 references actor(actorID)
48);
49
50INSERT INTO movieactor values
51(101, 11),
52(102, 33),
53(103, 11),
54(103, 22),
55(104, 44),
56(105, 33),
57(106, 44);
58
59select movieTitle, movieYearRel, actorFName, actorLName
60FROM movieactor ma
61join movie m
62 on ma.movieID = m.movieID
63join actor a
64 on ma.actorID = a.actorID
65ORDER BY actorLName;