Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE genre 
(
  g_id SERIAL NOT NULL,
  g_name TEXT NOT NULL,
  CONSTRAINT genre_pk PRIMARY KEY (g_id)
);
CREATE TABLE movie
(
  m_id SERIAL NOT NULL,
  m_name TEXT NOT NULL, -- can't be a PK or UNIQUE,
                            -- many movies have the same name (i.e. remakes)
  m_director TEXT,  -- could be an INTEGER pointing to a director table?
  --
  -- other stuff... lead actor, NY Times rating... &c..
  --
  CONSTRAINT movie_pk PRIMARY KEY (m_id)
);
CREATE TABLE movie_genre
(
  mg_movie_id INTEGER NOT NULL,
  mg_genre_id INTEGER NOT NULL,
  CONSTRAINT mg_pk       PRIMARY KEY (mg_movie_id, mg_genre_id), -- the PK here is the combination of the movie id and the genre id. You can't have the same movie with
                                                                 -- the same genre more than once!
  CONSTRAINT mg_movie_fk FOREIGN KEY (mg_movie_id) REFERENCES movie (m_id),
  CONSTRAINT mg_genre_fk FOREIGN KEY (mg_genre_id) REFERENCES genre (g_id)
);
INSERT INTO genre 
(g_name) -- note that g_id insertion is automatic
                   -- from the SERIAL type
VALUES
('Horror'),
('Sci-Fi'),
('Action'),
('Rom-Com'),  -- Romantic Comedy
('Comedy');
5 rows affected

INSERT INTO movie
(m_name, m_director)
VALUES
('Rocky Horror', 'R_Horror_Dir'), -- just use placeholder director names!
('2001, A Space Odessy', '2001_Dir'),
('Rambo', 'Rambo_Dir'),
('Terminator', 'Terminator_Dir'),
('Die Hard', 'Die Hard_Dir'),
('When Harry met Sally', 'HmS_Dir'),
('Bridget Jone''s Diary', 'BJD_Dir');
7 rows affected

INSERT INTO movie_genre
VALUES
(1, 1),
(1, 5), -- Rocky Horror Picture Show is both comdy and horror
(2, 2), 
(3, 3),
(4, 3),
(5, 3),
(6, 4),
(7, 4);
8 rows affected

SELECT * FROM movie_genre;


mg_movie_id
mg_genre_id


1
1

1
5

2
2

3
3

4
3

5
3

6
4

7
4


SELECT 
  m.m_name AS "Movie name", -- nice for presentation!
  m.m_director, -- other fields from movie
  -- mg.mg_movie_id, mg.mg_genre_id, -- line 3 - see below.
  g.g_name 
FROM movie m
JOIN movie_genre mg
ON m.m_id = mg.mg_movie_id
JOIN genre g
ON mg.mg_genre_id = g.g_id
ORDER BY m.m_id;

-- I left in line 3 so that you can see how the tables
-- are joined and you can get from movie to genre via
-- the joinig/linking bridging table movie_genre.



Movie name
m_director
g_name


Rocky Horror
R_Horror_Dir
Horror

Rocky Horror
R_Horror_Dir
Comedy

2001, A Space Odessy
2001_Dir
Sci-Fi

Rambo
Rambo_Dir
Action

Terminator
Terminator_Dir
Action

Die Hard
Die Hard_Dir
Action

When Harry met Sally
HmS_Dir
Rom-Com

Bridget Jone's Diary
BJD_Dir
Rom-Com


-- If you don't want multiple lines per film, you can do
-- something like this;

SELECT 
  m.m_name AS "Movie", -- AS "blah" is nice for tidying up the presentation
  m.m_director AS "Director",
  array_agg(g.g_name) AS "Genres"  -- note plural, the Rocky Horror (Picture Show) has two!
FROM movie m
JOIN movie_genre mg
ON m.m_id = mg.mg_movie_id
JOIN genre g
ON mg.mg_genre_id = g.g_id
GROUP BY m.m_name, m.m_director;


Movie
Director
Genres


Terminator
Terminator_Dir
{Action}

Rocky Horror
R_Horror_Dir
{Horror,Comedy}

Die Hard
Die Hard_Dir
{Action}

Bridget Jone's Diary
BJD_Dir
{Rom-Com}

When Harry met Sally
HmS_Dir
{Rom-Com}

2001, A Space Odessy
2001_Dir
{Sci-Fi}

Rambo
Rambo_Dir
{Action}

Movie name	m_director	g_name
Rocky Horror	R_Horror_Dir	Horror
Rocky Horror	R_Horror_Dir	Comedy
2001, A Space Odessy	2001_Dir	Sci-Fi
Rambo	Rambo_Dir	Action
Terminator	Terminator_Dir	Action
Die Hard	Die Hard_Dir	Action
When Harry met Sally	HmS_Dir	Rom-Com
Bridget Jone's Diary	BJD_Dir	Rom-Com

Movie	Director	Genres
Terminator	Terminator_Dir	{Action}
Rocky Horror	R_Horror_Dir	{Horror,Comedy}
Die Hard	Die Hard_Dir	{Action}
Bridget Jone's Diary	BJD_Dir	{Rom-Com}
When Harry met Sally	HmS_Dir	{Rom-Com}
2001, A Space Odessy	2001_Dir	{Sci-Fi}
Rambo	Rambo_Dir	{Action}