SQL Assignment

-- 3.1
SELECT title, director
FROM movie m
WHERE m.director IN (
    SELECT a.*
    FROM movie m, TABLE(m.actors) a
)
/
-- 3.2
SELECT title, director, genre
FROM movie m, TABLE(m.actors) a
WHERE a.column_value = 'Kevin Spacey'
/
-- 3.3
SELECT l.movie_ref.title AS movie, session_date, session_time
FROM listing l
WHERE l.session_date = '14.06.19'
AND l.cinema_ref.name = 'Cinema Paris'
/
-- 3.4
SELECT l.cinema_ref.name AS cinema, session_date, session_time
FROM listing l
WHERE l.movie_ref.title = 'American Beauty'
/
-- 3.5
SELECT r.reviewer, r.score
FROM movie m, TABLE(m.reviews) r
WHERE m.title = 'American Beauty'
AND r.score = (
    SELECT MIN(r.score)
    FROM movie m, TABLE(m.reviews) r
    WHERE m.title = 'American Beauty'
)
/
-- 3.6
SELECT l.movie_ref.title AS movie, l.cinema_ref.name AS cinema, l.session_date
FROM listing l, listing l2
WHERE l.cinema_ref.name = l2.cinema_ref.name
AND l.session_date = l2.session_date
AND l.movie_ref.title = l2.movie_ref.title
GROUP BY l.movie_ref.title, l.cinema_ref.name, l.session_date
ORDER BY l.movie_ref.title, l.session_date, l.cinema_ref.name
/
-- 3.7
SELECT m.title
FROM movie m
WHERE m.title NOT IN (
    SELECT l.movie_ref.title
    FROM listing l
    GROUP BY l.movie_ref.title
)
/
-- 3.8
SELECT m.title, a.column_value AS actor
FROM movie m, TABLE(m.actors) a
WHERE a.column_value IN (
    SELECT a.column_value
    FROM movie m, TABLE(m.actors) a
    GROUP BY a.column_value
    HAVING COUNT(*) > 1
)
/
-- 3.9
SELECT title, AVG(r.score) AS average_score
FROM movie m, TABLE(m.reviews) r
GROUP BY m.title
HAVING AVG(r.score) IN (
    SELECT MAX(AVG(r.score))
    FROM movie m, TABLE(m.reviews) r
    GROUP BY m.title
)
/
-- 3.10
SELECT a.column_value AS actor, COUNT(*) AS number_of_movies
FROM movie m, TABLE(m.actors) a
GROUP BY a.column_value
HAVING COUNT(*) IN (
    SELECT MAX(COUNT(*))
    FROM movie m, TABLE(m.actors) a
    GROUP BY a.column_value
)
/