Functionalities.txt

    FIND MOVIES OF A PARTICULAR GENRE
SELECT m.Title, m.ReleaseDate, m.Plot, m.Runtime
FROM movies m
INNER JOIN genres g ON m.MID=g.MID
WHERE g.Name='Horror'

    GET ALL REVIEWS OF A PARTICULAR MOVIE
SELECT CONCAT(u.FirstName, ' ', u.LastName) AS User, r.Stars AS Rating, r.Date, r.Content AS Review
FROM reviews r
INNER JOIN movies m ON r.MID=m.MID
INNER JOIN users u ON u.UID=r.UID
WHERE m.Title='The Secret Life'
ORDER BY r.Date DESC

    LIST ALL CAST MEMBERS OF A MOVIE
SELECT CONCAT(p.FirstName, " ", p.LastName) AS `Actor Name`, c.CharacterName AS `Character Name` FROM createdby cb
INNER JOIN movies m ON cb.MID=m.MID
INNER JOIN cast c ON c.MID=cb.MID AND c.PID=cb.PID
INNER JOIN people p ON cb.PID=p.PID
WHERE m.title='The Avengers'

    LIST ALL CREW MEMBERS OF A MOVIE
SELECT CONCAT(p.FirstName, " ", p.LastName) AS `Crew Member Name`, c.Role AS `Character Name`
FROM createdby cb
INNER JOIN movies m ON cb.MID=m.MID
INNER JOIN crew c ON c.MID=cb.MID AND c.PID=cb.PID
INNER JOIN people p ON cb.PID=p.PID
WHERE m.title='The Avengers'

    LIST ALL MOVIES OF FAVORITE GENRES
SELECT m.Title AS Movie, g.Name AS `Genre Match`
FROM movies m
INNER JOIN genres g ON m.MID=g.MID
INNER JOIN favgenres f ON f.Name=g.Name
INNER JOIN users u ON f.UID=u.UID
WHERE u.UID=1

    GET ALL INFORMATION OF A MOVIE
SELECT m.Title, m.ReleaseDate AS 'Release Date', m.Plot, m.runtime, GROUP_CONCAT(g.Name) AS Genres
FROM movies m
INNER JOIN genres g ON m.MID=g.MID
WHERE m.Title = 'The Avengers'
GROUP BY m.MID

    GET MOVIES RELEASED IN A PARTICULAR DATE RANGE
SELECT m.Title, m.ReleaseDate AS 'Release Date'
FROM movies m
WHERE m.ReleaseDate BETWEEN '2017-01-01' AND '2018-01-01'
ORDER BY m.ReleaseDate ASC

    FIND ALL MOVIES A PERSON HAS STARRED IN
SELECT m.Title AS Movie, c.CharacterName as 'Character Name'
FROM movies m
INNER JOIN cast c ON m.MID=c.MID
INNER JOIN people p ON c.PID=p.PID
WHERE p.PID=1

    FIND ALL MOVIES A PERSON HAS WORKED IN AS CREW MEMBER
SELECT m.Title AS Movie, c.Role
FROM movies m
INNER JOIN crew c ON m.MID=c.MID
INNER JOIN people p ON c.PID=p.PID
WHERE p.PID=1

    ALL LIKED MOVIES OF A USER
SELECT m.Title
FROM movies m
INNER JOIN liked l ON m.MID=l.MID
INNER JOIN users u ON u.UID=l.UID
WHERE u.UID=1

    ALL WATCHED MOVIES OF A USER
SELECT m.Title
FROM movies m
INNER JOIN watched w ON m.MID=w.MID
INNER JOIN users u ON u.UID=w.UID
WHERE u.UID=1

    LIST ALL REVIEWS BY A USER
SELECT m.Title AS Movie, r.Date, r.Stars AS Rating, r.Content AS Review
FROM reviews r
INNER JOIN movies m ON r.MID=m.MID
WHERE r.UID=2
ORDER BY r.Date DESC

    ALL CAST MEMBERS OF USER'S LIKED MOVIES
SELECT CONCAT(p.FirstName, ' ', p.LastName) AS Actor, m.Title AS Movie, c.CharacterName AS 'Character Name'
FROM people p
INNER JOIN cast c ON p.PID=c.PID
INNER JOIN liked l ON l.MID=c.MID
INNER JOIN movies m ON c.MID=m.MID
WHERE l.UID=2

    LIST ALL MOVIES WITH A RATING GREATER THAN A PARTICULAR RATING
SELECT DISTINCT m.Title, r.Stars
FROM movies m
INNER JOIN reviews r ON r.MID=m.MID
WHERE r.Stars > 4

    LIST ALL PHOTOS OF A PERSON
SELECT p.FileName
FROM photos p
WHERE p.PID=1

    LIST ALL FAVORITE GENRE
SELECT f.Name AS Genres
FROM favgenres f
WHERE f.UID=1

    LIST ALL GENRES OF A MOVIE
SELECT g.Name AS Genres
FROM genres g
WHERE g.MID=1

    LIST ALL POSTER OF A MOVIE
SELECT p.FileName
FROM posters p
WHERE MID=1

    LIST OF NUMBER OF MOVIES FOR EACH CERTIFICATE
SELECT COUNT(MID) AS 'Number of Movies', Certificate 
FROM movies 
GROUP BY Certificate

    WHICH TYPE OF CERTIFICATE OF MOVIES IS AVAILABLE IN MAJORITY
SELECT COUNT(MID) AS 'Number of Movies', Certificate
FROM movies
GROUP BY Certificate
HAVING COUNT(MID) > (SELECT (COUNT(MID)/2)-1 FROM movies)

    AVERAGE RUNTIME OF MOVIES
SELECT AVG(Runtime) AS 'Average Runtime of Movies'
FROM MOVIES

    SEARCH MOVIE BY SUBSTRING OF TITLE
SELECT *
FROM movies
WHERE Title
LIKE '%ave%'

    LIST MOVIES OF PARTICULAR CERTIFICATES
SELECT *
FROM movies
WHERE Certificate
IN ('PG-13', 'PG')

    LIST ALL NON-ADULT MOVIES
SELECT *
FROM movies
WHERE Certificate
NOT IN ('R')

    LIST ALL GENRES THAT NO USER HAS LIKED A MOVIE OF
SELECT Name AS Genre
FROM genres
WHERE Name
NOT IN (
    SELECT Name 
    FROM favgenres
)

    LIST OF ALL CAST AND CREW MEMBERS
SELECT CONCAT(p.FirstName, " ", p.LastName) AS `Person Name`
FROM createdby cb
INNER JOIN movies m ON cb.MID=m.MID
INNER JOIN cast c ON c.MID=cb.MID AND c.PID=cb.PID
INNER JOIN people p ON cb.PID=p.PID
WHERE m.title='The Avengers'
UNION
SELECT CONCAT(p.FirstName, " ", p.LastName) AS `Crew Member Name`
FROM createdby cb
INNER JOIN movies m ON cb.MID=m.MID
INNER JOIN crew c ON c.MID=cb.MID AND c.PID=cb.PID
INNER JOIN people p ON cb.PID=p.PID
WHERE m.title='The Avengers'

    RUNTIME GREATER THAN A PARTICULAR VALUE
SELECT Title
FROM movies
WHERE Runtime =
ANY (
    SELECT Runtime
    FROM movies
    WHERE Runtime>100
);

    AVERAGE RATING OF A MOVIE
SELECT AVG(Stars) AS Rating
FROM reviews
WHERE MID = 2