SQL Database queries question: Consider the following database which has the fol
ID: 3888661 • Letter: S
Question
SQL Database queries question:
Consider the following database which has the following relations:
Movies ( title, year, length, genre, studioName, producer_id )
Stars ( movieTitle, movieYear, starName )
MovieStar ( name, address, gender, birthdate )
MovieExec ( name, id, address, networth )
Studio ( name, address, president_id )
Where movie executives can be producers or studio presidents and they have unique id numbers. Assume that names of people are unique, there is one producer of each movie, and each studio has one president.
Express the following queries using SQL:
1. (a)Find the names of presidents of studios that released a movie in 2017.
(b)Find movies which star more than 10 movie stars.
(c) Find movie stars who only star in movies produced by “Guillermo del Toro”
(d)Find the titles of movies that have been used for two or more movies
Explanation / Answer
Hi,
1. to get movies release in 2017, we can do select * from movies where year=2017, but we are only interested in their producers hence
select name from Studio where president_id in (select producer_id from movies where year=2017);
2.select movieTitle from Stars where count(distinct starName )>10 group by movieTitle;
since we are interested in movies having more than 10 stars
3. select * from MovieStar where name in (select starName from Stars where movieTitle in (select title from movies where studioName='Guillermo del Toro'));
in this, first getting all movie titles produced by Guillermo del Toro, then getting the starnames in them, then getting their details from moviestart table
4. select title from Movies group by title having count(*)>=2;
here first grouping by title to get same movie title movies and checking if there are 2 or more.
Thumbs up if this was helpful, otherwise let me know in comments.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.