movie database sql code example

Example 1: movie database sql queries

select Title from Movie m JOIN 
(select MovieID, Rating from Ratings order by Rating DESC limit 20) as r
on m.MovieID = r.MovieID;

Example 2: movie database sql queries

select first_name,last_name
  from stars_in_movies inner join stars
 where (movie_id=%s and movie_id=%y) and star_id=id;

Example 3: movie database sql queries

select director -- directors who have worked with star X
from movies
where movie_id in
(
    select movie_id  --All movies in which star X has acted
    from stars_in_movies
    where star_id = "X"
)

Example 4: movie database sql queries

select A.first_name
from stars A,
(
select star_id
from stars_in_movies
where movie_id = "X"
)B,
(   
select star_id
from stars_in_movies
where movie_id = "Y"
)C
where A.star_id = B.star_id
AND B.star_id = C.star_id