Plex & SQL Integration: Unlock the Ultimate Media Management

For many, SQL is a formidable language often associated with complex databases and structured data. However, what if I told you that you could unravel the mysteries of SQL by applying it to something you’re truly passionate about — your Plex library?

For simplicity, let’s consider the following schema:

Movies

• MovieID (Primary Key) Auto Increment
• Title
• ReleaseYear
• Genre
• Director
• Duration

TV Shows

• ShowID (Primary Key) Auto Increment
• Title
• Release Year
• Genre
• Director
• NumberOfSeasons

Documentary’s

• DocID (Primary Key) Auto Increment
• Title
• ReleaseYear
• Genre
• Director
• Duration

CREATE THE PLEX DATABASE

CREATE DATABASE IF NOT EXISTS PLEXDB;
USE PLEXDB;

CREATE TABLES

CREATE TABLE Movies (
    MovieID INT PRIMARY KEY AUTO_INCREMENT,
    Title VARCHAR(255) NOT NULL,
    ReleaseYear VARCHAR(4),
    Genre VARCHAR(50),
    Director VARCHAR(100),
    Duration INT
);
CREATE TABLE TVShows (
    ShowID INT PRIMARY KEY AUTO_INCREMENT,
    Title VARCHAR(255) NOT NULL,
    ReleaseYear VARCHAR(4),
    Genre VARCHAR(50),
    Director VARCHAR(100),
    NumberOfSeasons INT
);
CREATE TABLE Docs (
    DocID INT PRIMARY KEY AUTO_INCREMENT,
    Title VARCHAR(255) NOT NULL,
    ReleaseYear VARCHAR(4),
    Genre VARCHAR(50)
    Director VARCHAR(100),
    Duration INT
);

AUTO_INCREMENT attribute is used to automatically generate unique IDs for each entry.

NULL:

When a column is defined as NULL, it means that the column can contain no value, and the absence of a value is represented by NULL. NULL is not the same as an empty string or zero; it represents the absence of data.

NOT NULL:

When a column is defined as NOT NULL, it means that the column must always have a value, and NULL is not allowed. You must provide a value for this column when inserting a new record.

Now run the SQL command show databases;

Now run show tables;

Awesome! You’ve created your database and 3 tables in the PLEX database.

INSERT DATA

You can now insert sample data into your tables using the INSERT INTO statements.

For example:

-- Insert a movie
INSERT INTO Movies (Title, ReleaseYear, Genre, Director, Duration)
VALUES ('Inception', '2010', 'Sci-Fi', 'Christopher Nolan', 148);
-- Insert a TV show
INSERT INTO TVShows (Title, ReleaseYear, Genre, Director, NumberOfSeasons)
VALUES ('Stranger Things', '2016', 'Sci-Fi', 'Duffer Brothers', 4);
-- Insert an documentary
INSERT INTO Docs (Title, ReleaseYear, Genre, Director, Duration)
VALUES ('Free Solo', '2018', 'Adventure', 140);

Enter the SQL command: SELECT * FROM Movies

This serves as an illustrative example within my PLEX setup.

Here are some examples of SELECT statements that you can use to retrieve information from your PLEX database, based on the movie and TV show schema we discussed earlier:

Retrieve Specific Columns for All TV Shows:

SELECT Title, ReleaseYear, Genre, Director, NumberOfSeasons FROM TVShows;

Retrieve Specific Columns for All TV Shows:

SELECT Title, Genre FROM Movies WHERE Genre = 'Action'
UNION
SELECT Title, Genre FROM TVShows WHERE Genre = 'Action';

Retrieve Movies and TV Shows with a Specific Genre:

SELECT Title, NumberOfSeasons FROM TVShows WHERE NumberOfSeasons > 3;

These are just a few examples, and you can customize your SELECT statements based on the specific information you need and the relationships in your database. Always remember to adjust column and table names to match your actual database schema.