List the titles of all movies directed by ‘Hitchcock’:
SELECT MOV_TITLE
FROM SMOVIES
WHERE DIR_ID IN (SELECT DIR_ID FROM SDIRECTOR WHERE DIR_NAME = ‘HITCHCOCK’);
- Find the movie names where one or more actors acted in two or more movies:
SELECT MOV_TITLE
FROM SMOVIES M, SMOVIE_CAST MV
WHERE M.MOV_ID = MV.MOV_ID AND ACT_ID IN (
SELECT ACT_ID
FROM SMOVIE_CAST
GROUP BY ACT_ID
HAVING COUNT(ACT_ID) > 1
)
GROUP BY MOV_TITLE
HAVING COUNT(*) > 1;
List all actors who acted in a movie before 2000 and also in a movie after 2015 (use JOIN operation):
SELECT A.ACT_NAME, M.MOV_TITLE, M.MOV_YEAR
FROM SACTOR A
JOIN SMOVIE_CAST C ON A.ACT_ID = C.ACT_ID
JOIN SMOVIES M ON C.MOV_ID = M.MOV_ID
WHERE M.MOV_YEAR NOT BETWEEN 2000 AND 2015;
- Find the title of movies and the number of stars for each movie that has at least one rating and find the highest number of stars that the movie received. Sort the result by movie title:
SELECT MOV_TITLE, MAX(REV_STARS)
FROM SMOVIES
INNER JOIN SRATING USING (MOV_ID)
GROUP BY MOV_TITLE
HAVING MAX(REV_STARS) > 0
ORDER BY MOV_TITLE;
Update the rating of all movies directed by ‘Steven Spielberg’ to 5:
UPDATE SRATING
SET REV_STARS = 5
WHERE MOV_ID IN (
SELECT MOV_ID
FROM SMOVIES
WHERE DIR_ID IN (
SELECT DIR_ID
FROM SDIRECTOR
WHERE DIR_NAME = ‘STEVEN SPIELBERG’
)
);
