SQL - 13. Movies Database

CREATE DATABASE Movies

USE Movies

CREATE TABLE Directors (
    Id INT PRIMARY KEY IDENTITY,
    DirectorName NVARCHAR(30) UNIQUE NOT NULL,
    Notes NVARCHAR(MAX)
)

CREATE TABLE Genres
(
    Id INT PRIMARY KEY IDENTITY,
    GenreName NVARCHAR(30) UNIQUE NOT NULL,
    Notes NVARCHAR(MAX)
)

CREATE TABLE Categories
(
    Id INT PRIMARY KEY IDENTITY,
    CategoryName NVARCHAR(30) UNIQUE NOT NULL,
    Notes NVARCHAR(MAX)
)

CREATE TABLE Movies
(
    Id INT PRIMARY KEY IDENTITY,
    Title NVARCHAR(50) UNIQUE NOT NULL,
    DirectorId INT FOREIGN KEY REFERENCES Directors(Id),
    CopyrightYear DATE,
    [LENGTH] TIME NOT NULL,
    GenreId INT FOREIGN KEY REFERENCES Genres(Id),
    CategoryId INT FOREIGN KEY REFERENCES Categories(Id),
    Rating DECIMAL(2, 1) NOT NULL,
    Notes NVARCHAR(MAX)
)

INSERT INTO Directors(DirectorName, Notes)
VALUES      ('James Cameron', 'Canada'),
            ('Michael Bay', 'USA'),
            ('Justin Lin', 'Japan'),
            ('Martin Scorcese', 'Italy'),
            ('Dennis Dugan', 'USA')

INSERT INTO Genres(GenreName, Notes)
VALUES      ('Fantasy', NULL),
            ('Drama', NULL),
            ('Action', NULL),
            ('Thriller', NULL),
            ('Comedy', NULL)

INSERT INTO Categories(CategoryName, Notes)
VALUES      ('First', NULL),
            ('Second', NULL),
            ('Third', NULL),
            ('Fourth', NULL),
            ('Fifth', NULL)

INSERT INTO Movies
            (Title, DirectorId, CopyrightYear, [LENGTH], GenreId, CategoryId, Rating, Notes)
VALUES
            ('Avatar', 1, '2009', '02:42:00', 1, 1, 9.5, 'My favourite'),
            ('Pearl Harbor', 2, '2001', '03:05:00', 2, 1, 9.4, 'I love it'),
            ('The Fast and the Furious: Drift Tokyo', 3, '2006', '01:44:00', 3, 2, 8.2, 'Lets drift'),
            ('Cape Fear', 4, '1991', '02:08:00', 4, 5, 7.8, 'Frightening'),
            ('Just Go with It', 5, '2011', '01:57:00', 5, 1, 8.4, 'Fun')