views.sql

DROP VIEW NEWEST_MOVIES;
DROP VIEW SHORTEST_MOVIES;
DROP VIEW AGE18;
DROP VIEW TOP_MOVIES;
DROP VIEW TOP_ACTORS;

SELECT *
FROM NEWEST_MOVIES
WHERE ROWNUM <= 3;

SELECT *
FROM SHORTEST_MOVIES
WHERE ROWNUM <= 3;

SELECT * FROM MOVIES_BY_AGELIMITS;

SELECT *
FROM AGE18;

SELECT *
FROM TOP_MOVIES
WHERE ROWNUM <= 3;
SELECT *
FROM TOP_ACTORS
WHERE ROWNUM <= 3;

-- Newest movies
CREATE OR REPLACE VIEW NEWEST_MOVIES AS
  SELECT
    m.MOVIE_TITLE,
    m.MOVIE_RELEASE_DATE
  FROM MOVIE m
  ORDER BY m.MOVIE_RELEASE_DATE DESC;

-- Shortest movies
CREATE OR REPLACE VIEW SHORTEST_MOVIES AS
  SELECT
    m.MOVIE_TITLE,
    m.MOVIE_LENGTH
  FROM MOVIE m
  ORDER BY m.MOVIE_LENGTH ASC;

CREATE OR REPLACE VIEW MOVIES_BY_AGELIMITS AS
  SELECT
    m.MOVIE_TITLE,
    al.AGELIMIT
  FROM AGELIMIT al, MOVIE m
  WHERE al.AGELIMIT_ID = m.MOVIE_ID
  ORDER BY al.AGELIMIT ASC;

-- Age limitation 18+
CREATE OR REPLACE VIEW AGE18 AS
  SELECT
    m.MOVIE_TITLE,
    al.AGELIMIT
  FROM AGELIMIT al, MOVIE m
  WHERE al.AGELIMIT_ID = m.MOVIE_ID AND al.AGELIMIT_ID > 17;


CREATE OR REPLACE VIEW TOP_MOVIES AS
  SELECT
    mr.MOVIE_ID,
    SUM(mr.MOVIE_RATING) / COUNT(mr.MEMBER_ID) AS AVRG
  FROM MOVIE m, MOVIE_RATING mr
  GROUP BY mr.MOVIE_ID
  ORDER BY AVRG DESC;

-- Top actors
CREATE OR REPLACE VIEW TOP_ACTORS AS
  SELECT
    a.ACTOR_NAME,
    SUM(cr.RATE) / COUNT(cr.USER_ID) AS AVRG
  FROM ACTOR a, CREW_RATING cr
  WHERE a.CREW_ID = cr.CREW_ID
  GROUP BY d.ACTOR_ID
  ORDER BY AVRG DESC;