DROP TABLE DVD;
DROP TABLE rating;
DROP TABLE genre;
DROP TABLE DVD_genre;

CREATE TABLE DVD (
  DVDID integer primary key,
  name varchar(30),
  ratingID integer
);

INSERT INTO DVD VALUES(0, 'Barneys big adventure', 0);
INSERT INTO DVD VALUES(1, 'Barney returns- the horror movie', 3); 

SELECT * FROM DVD;

CREATE TABLE rating (
  ratingID integer primary key,
  name varchar(20)
);

INSERT INTO rating VALUES (0, 'G');
INSERT INTO rating VALUES (1, 'PG');
INSERT INTO rating VALUES (2, 'PG-13');
INSERT INTO rating VALUES (3, 'R');

SELECT * FROM rating;

SELECT
  DVD.name as 'video',
  rating.name as 'rating'
FROM
  DVD, rating
WHERE
  DVD.ratingID = rating.ratingID;

CREATE TABLE genre (
  genreID integer primary key,
  name varchar(30)
);

INSERT INTO genre VALUES (null, 'Family');
INSERT INTO genre VALUES (null, 'Comedy');
INSERT INTO genre VALUES (null, 'Musical');
INSERT INTO genre VALUES (null, 'Horror');

SELECT * FROM genre;

CREATE TABLE DVD_genre (
  DVD_genreID integer primary key,
  DVDID integer,
  genreID integer
);

INSERT INTO DVD_genre VALUES (null, 0, 1);
INSERT INTO DVD_genre VALUES (null, 1, 1);
INSERT INTO DVD_genre VALUES (null, 1, 2);
INSERT INTO DVD_genre VALUES (null, 1, 3);
INSERT INTO DVD_genre VALUES (null, 1, 4);

SELECT * FROM DVD_genre;

SELECT
  DVD.name as 'film',
  genre.name as 'genre'
FROM
  DVD, genre, DVD_genre
WHERE
  DVD.DVDID = DVD_genre.DVDID
AND
  genre.genreID = DVD_genre.genreID;