movie database sql queries code example
Example 1: 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 2: movie database sql queries
select director
from movies inner join stars_in_movies
where star_id=%s and movie_id = id;
Example 3: movie database sql queries
select first_name,last_name
from stars_in_movies inner join stars
where movie_id=%s and star_id=id;
Example 4: 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 5: movie database sql queries
select Title from Movie m
inner join (select MovieID,sum(Rating) Rate from Ratings group by movieid having count(UserID)>39 order by sum(Rating) DESC limit 20) tbl
on m.MovieID=tbl.MovieID
order by tbl.Rate desc
Example 6: movie database sql queries
select Title from Movie m
inner join (select top 20 MovieID,sum(Rating) Rate from Ratings group by movieid having count(UserID)>39 order by sum(Rating) DESC) tbl
on m.MovieID=tbl.MovieID
order by tbl.Rate desc
Example 7: movie database sql queries
`select Title
from Movie m
JOIN
(select MovieID, Rating
from Ratings
order by
Rating)
as r on
m.MovieID = r.MovieID
limit 20;`
Example 8: 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
Example 9: 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 10: movie database sql queries
select first_name --All stars who have not acted with star X
from stars
where star_id not in
(
select star_id --All stars who have acted with star X
from stars_in_movies
where movie_id in
(
select movie_id --All movies in which star X has acted
from stars_in_movies
where star_id = "X"
)
)