--  M:N Example movie and actors

drop database if exists Mar5MNExample;
create database if not exists Mar5MNExample;
use Mar5MNExample;

create table movie
(
	movieID			int			primary key,
	movieTitle		varchar(25),
	movieYearRel	year
);

insert into movie values
	(101,'Postcards', 1990),
	(102,'Dangal', 2016),
	(103,'Star Wars', 1977),
	(104,'Fargo', 2014),
	(105,'PK',2014),
	(106,'Mystery Men',1991);


create table actor
(
	actorID			int			primary key,
	actorFName		varchar(25),
	actorLName		varchar(25)
);

insert into actor values
	(11, 'Carrie','Fisher'),
	(22, 'Harrison', 'Ford'),
	(33, 'Aamir', 'Khan'),
	(44, 'William','Macy');


create table movieactor
(
	movieID		int,
    actorID		int,

    primary key (movieID, actorID),

    foreign key (movieID)
		references movie(movieID),
	foreign key (actorID)
		references actor(actorID)
);

INSERT INTO movieactor values
(101, 11),
(102, 33),
(103, 11),
(103, 22),
(104, 44),
(105, 33),
(106, 44);

select movieTitle, movieYearRel, actorFName, actorLName
FROM movieactor ma
join movie m
	on ma.movieID = m.movieID
join actor a
	on ma.actorID = a.actorID
ORDER BY actorLName;