Complete Intro to SQL & PostgreSQL The Movie Database Solution: Keanu Reeves
Transcript from the "The Movie Database Solution: Keanu Reeves" Lesson
>> Okay, so how much revenue does the movies Keanu Reeves in, act in make? Which I recognize is a weird question but basically what's the box office of all Keanu Reeves movies? So, we're gonna start from movies, right, because we wanna know all of the movies, so that's gonna be our front table.
[00:00:20] So select then we're gonna do a COALESCE here as well, of the (SUM(m.revenue), 0 AS TOTAL I actually don't know if we need that COALESCE, but we'll see.
>> Total to be lower case.
>> So yeah, you're right, I mean it would work it would just be weird.
[00:01:03] Okay, FROM movies. INNER JOIN, we're gonna join on CASTS, right, cuz that's the give me the connection between movies and people. So casts c, m, c and then ON m.id =c.movie id, right? Because that's the connection and cast is just movie id and people id.
>> Don't think you movies as m so.
>> You're correct I did not, so select.
>> Sorry, I found it helpful to write the stuff in a code editor and then paste it in.
>> Yeah, that's typically what I do as well. But we're here, so, we'll do it this way first. As total
>> [INAUDIBLE] Yep.
[00:02:13] All right, maybe we will just do this in my editor. We'll get syntax highlighting as well. All right, here we go, SELECT, COALESCE(SUM ( m.revenue), 0) AS total. I actually don't think we need that COALESCE but I'm gonna leave it in next that's my notes have and then we'll go back and try it without, movies m INNER JOIN casts c On m.id = c.movie.id.
[00:03:09] Then we're gonna INNER JOIN again, people p, ON. C.person_id = p.id. WHERE p.name = Keanu Reeves. All right, let's check that one out. So this is the amount that Keanu Reeves movies has done, let me try just removing this COALESCE and trying to remember exactly why I had to put this in here.
[00:04:02] And it might have not been necessary, it's not. Because that would basically say, if this entire SUM was no then make it zero which is nonsensical in this particular case, so it wasn't necessary. Cool, that sounds good, any questions about this one?
>> I was trying to order by his most popular or his highest revenue movie.
>> Which doesn't make any sense because selling them anyway right?
>> Well no, I misread the question, so instead of SUM, I was just like, hey, what movies did Keanu Reeves, which one of his movies did the best or whatever?
>> So I mean, you could do m.name, m.revenue, I think this will give you back all his movies, right?
[00:05:07] With their revenues, which it does.
>> Yeah,that's where I went bad, yeah.
>> ORDER BY, ORDER By, and revenue descending NULLS LAST. Was it NULLS or NULL LAST? So there you go, that would be that one.
>> [INAUDIBLE] Toy Story 4.
>> Was he? Yes, no, maybe, I guess, according to this database it is, that's year.
>> That's very cool data.
>> Pretty fun, right?