Queries (Program 3)
1. List the titles
of all movies directed by ‘Hitchcock’.
SELECT
Mov_title
FROM
MOVIES
WHERE
Dir_id IN (SELECT Dir_id
FROM DIRECTOR
Where Dir_name='Hitchcock');
OR
SELECT Mov_title
FROM
MOVIES M, DIRECTOR D
WHERE
M.Dir_id=D.Dir_id and Dir_name=’Hitchcock’;
2. Find the movie
names where one or more actors acted in two or more movies.
SELECT
Mov_title
FROM MOVIES M,MOVIE_CAST
MC
WHERE
M.Mov_id=MC.Mov_id AND Act_id IN ( SELECT Act_id
FROM
MOVIE_CAST
GROUP
BY Act_id
HAVING
COUNT(ACT_ID)>1);
3. List all actors
who acted in a movie before 2000 and also in a movie after 2015 (use JOIN
operation).
SELECT
A.Act_name
FROM ACTOR A
INNER JOIN MOVIE_CAST MC1 ON
MC1.Act_id=A.Act_id
INNER JOIN MOVIE_CAST MC2 ON
MC2.Act_id=A.Act_id
INNER JOIN MOVIES M1 ON
MC1.Mov_id=M1.Mov_id
INNER JOIN MOVIES M2 ON
MC2.Mov_id=M2.Mov_id
WHERE
M1.Mov_year<2000 AND M2.Mov_Year>2015;
OR
SELECT
A.Act_name
FROM ACTOR A
JOIN MOVIE_CAST MC1 ON
MC1.Act_id=A.Act_id
JOIN MOVIE_CAST MC2 ON MC2.Act_id=A.Act_id
JOIN MOVIES M1 ON
MC1.Mov_id=M1.Mov_id
JOIN MOVIES M2 ON
MC2.Mov_id=M2.Mov_id
WHERE
M1.Mov_year<2000 AND M2.Mov_Year>2015;
4. Find the title
of movies and number of stars for each movie that has at least one rating and
find the highest number of stars that movie received. Sort the result by movie
title.
SELECT
Mov_title,MAX(Rev_stars),SUM(Rev_stars)
FROM MOVIES
M,RATING R
WHERE
M.Mov_id=R.Mov_id
GROUP BY
Mov_title
ORDER BY
Mov_title;
OR
SELECT Mov_title,Rev_stars
FROM MOVIES
M,RATING R
WHERE
M.Mov_id=R.Mov_id
Order by
Mov_title;
5. Update rating
of all movies directed by ‘Steven Spielberg’ to 5.
UPDATE RATING
SET Rev_stars=5
WHERE Mov_id
IN(SELECT Mov_id
FROM MOVIES
WHERE Dir_id IN(SELECT Dir_id
FROM DIRECTOR
WHERE Dir_name='Steven Spielberg'));
OR
UPDATE RATING
SET Rev_stars=5
WHERE Mov_id
IN(SELECT Mov_id
FROM MOVIES M,DIRECTOR D
WHERE M.Dir_id =D. Dir_id
and Dir_name='Steven Spielberg');
No comments:
Post a Comment