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)  -- g_id has to be a PK in order for it to be an FK in movie!
);

CREATE TABLE


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_genre INTEGER, -- maybe allow NULLs? Some movies
                -- don't fit into a genre or a single genre?
  m_director TEXT,  -- could be an INTEGER FK into a director table!
  --
  -- other stuff...
  --
  CONSTRAINT movie_pk PRIMARY KEY (m_id),
  CONSTRAINT m_genre_fk FOREIGN KEY (m_genre) REFERENCES genre (g_id)
);

CREATE TABLE


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');

INSERT 0 5


SELECT * FROM genre;  -- good to check that your tables have been created.


g_id
g_name


1
Horror

2
Sci-Fi

3
Action

4
Rom-Com

5
Comedy



SELECT 5


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

INSERT 0 7


SELECT * FROM movie;


m_id
m_name
m_genre
m_director


1
Rocky Horror
1
R_Horror_Dir

2
2001, A Space Odessy
2
2001_Dir

3
Rambo
3
Rambo_Dir

4
Terminator
3
Terminator_Dir

5
Die Hard
3
Die Hard_Dir

6
When Harry met Sally
4
HmS_Dir

7
Bridget Jone's Diary
4
BJD_Dir



SELECT 7


SELECT 
  m.m_name AS "Movie name", -- nice for presentation!
  m.m_director, -- other fields from movie
  g.g_name AS "Genre"  -- only one genre per movie, see https://dbfiddle.uk/?rdbms=postgres_10&fiddle=421f66094c3073bce7d929a61e2b7861 for multiple genres! 
FROM movie m
JOIN genre g
ON m.m_genre = g.g_id
ORDER BY m.m_id;




Movie name
m_director
Genre


Rocky Horror
R_Horror_Dir
Horror

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



SELECT 7

m_id	m_name	m_genre	m_director
1	Rocky Horror	1	R_Horror_Dir
2	2001, A Space Odessy	2	2001_Dir
3	Rambo	3	Rambo_Dir
4	Terminator	3	Terminator_Dir
5	Die Hard	3	Die Hard_Dir
6	When Harry met Sally	4	HmS_Dir
7	Bridget Jone's Diary	4	BJD_Dir

Movie name	m_director	Genre
Rocky Horror	R_Horror_Dir	Horror
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