SQL Database queries question: Consider the following database which has the fol
ID: 3888663 • 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:
2. (a) Find names of movie stars who only star in movies produced by producers who have a less than average networth.
(b) Find years where more than 3 horror movies were released
(c) Find names of movie execs who produce movies longer than 120 minutes or star “Kit Harrington”
Explanation / Answer
Solution:
2.
(a)
Answer:
Select Stars.starName From Stars
inner join Movies on Movies.title = Stars.movieTitle
inner join MovieExec on Movies.producer_id = MovieExec.id
Where MovieExec.networth < avg(networth);
(b)
Answer:
select year, count(genre) > 3 from Movies where genre = 'horror';
(c)
Answer:
select MovieExec.name from MovieExec
inner join Movies
inner join Stars
where Movies.length > 120 or Stars.starName = 'Kit Harrington';
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.