The Movie Database Exercise

[00:00:00]
>> This is a relatively unoptimized system, some of these cores can be really slow, we're gonna talk about optimizing queries later. But in the meantime I'm gonna ask you to, I think this is a useful exercise for you, you have all of the tools already to answer all these questions.

[00:00:16]
They're gonna require you to kind of explore the data, look at the tables, figure out how the tables connect. This is all stuff that I did. But I've taught you everything that you need to do here. It just might be hard to put it together, but that's hard, struggling will teach you.

[00:00:34]
So, I'm gonna give you some time here, but these are the questions that I want you to answer. Which movie made the most money? ORDER BY is gonna be your friend here, particularly is descending order and some of these have no revenues. You can use coalesce, column name and zero to say if it's null, give me zero or you can also say ORDER BY, I think it's revenue is the name of the.

[00:01:02]
And then you can put nulls last, it'll actually incorporate that into your ORDER BY. This one, there's no joins here, you're gonna do an ORDER BY basically. How much revenue did the movies Keanu Reeves act in make? So casts is the name of the connecting table between movies and people.

[00:01:32]
You're gonna have to do multiple joins here and you're also gonna have to use something that we haven't used so far called sum. So, we have select count star, which we did, right? We can also do select, Sum of revenue. This is how much money all movies have made in this database, that's what sum does.

[00:02:05]
I think if we do average, it might get upset. No, this is the average as well. This is not gonna be totally accurate, because a bunch of these movies don't have revenue information associated with them, but, Another aggregation function like count, you treat it exactly like count. Which 5 people were in the movies that had the most revenue?

[00:02:31]
The answer probably won't surprise you. So, these two are really similar. But it's kind of inverting, one's looking at Keanu Reeves specifically so you're driving that from the people table, whereas the other one's driving it from the movies table, right? Which 10 movies have the most keywords? So there is a movie keywords table.

[00:02:58]
There is a categories table that names what's the movie keywords, because if you look at movie keywords, it has a movie ID and a category ID. It actually doesn't tell you what the name of the category is, the name of the category exists in the categories table. So you got to join multiple times here, right?

[00:03:17]
It's a many to many relationship. And then which category is associated with the most movies? Another thing to know about this database, There is German, English, Spanish and French on this database as well. So, this one for example, I think there's actually quite a bit of German stuff in here.

[00:03:50]
The most common one might actually be German, so don't be surprised if that's the case. Bonus points if you can help me with the English ones, or the German ones, I don't care. So hopefully the brain is going, you're thinking about this. Just start typing some queries, see what happens.

[00:04:07]
If you click here on the next one, all of the answers are on the next page. So again, I just want you to learn, I don't care if you get it right or whatever the case, just care that you're learning about it. So, do whatever you have to do to get there.