Which films has customer Sam Raimi not watched? List the films along with how
many times everyone else has watched the films
which genres of films have been watched the least in postcode
3052? List the bottom 5 genres for that postcode.
Which films are not making a profit for NetFilms? (Profit is the net takings from
viewings minus the wholesale cost of the film.)
List who has acted in the most films and who has acted in the least films?
the fourth question ive wrote a query to get the most films out
select FirstName, LastName, count() as totalcount
from CastAndCrew group by FirstName, LastName
having count() = (select max(totalcount) from (select FirstName, LastName, count(*) as totalcount from CastAndCrew
where Id in
(select CastAndCrew from Production where Role in
(select Id from Role where Name = 'Actor')) group by FirstName, LastName)CastAndCrew);
the questions are these above
and im given the tables shown in the figure
can anyone help with these questions they are so so hard all of my queries don't even work
This looks like some kind of homework. Rather than just ask for the answers, why not provide the queries you've tried for questions 1-3 like you did with 4? That way you can improve your understanding.
I agree, this is pretty obviously homework.
But we can still go thru the thought process of how to approach writing the query.
Let's take the first q, for example:
OK, what tables do we need? We're talking about listing films, so we'll need the Film table.
We're talking about not watched / watched, so we'll need the Watch table.
We're talking about a customer, so we'll need the Customer table.
Now, using those tables, how do you get the list of films that Raimi has not watched? Hmm, seems hard. How about we get the list of films Raimi has watched?
SELECT Film
FROM Watch
WHERE Customer = (SELECT Id FROM Customer WHERE UserName = 'Sam Raimi')
Now, how can you use that result to list the other films in the Film table, that is, the ones he did not watch?
select Title, count() everyOneElse
from Film inner join Watch on Film.Id = Watch.Film inner join Customer on Watch.Customer = Customer.Id
where FirstName<>'Sam' and LastName <> 'Raimi'
group by Title having count()>=0 order by count(*) desc;
select Title, Genre, Postcode, dayname(WhenViewed)
from Film inner join Watch on Film.Id = Watch.Film inner join Customer on Watch.Customer = Customer.Id
where Postcode = 3052 group by Genre, WhenViewed having dayname(WhenViewed) = 'Saturday' or dayname(WhenViewed) = 'Sunday';
select Title, count() totalcount
from Film inner join Watch on Film.Id = Watch.Film inner join Customer on Watch.Customer = Customer.Id
group by Title having count()>=0;
