The Movie Database Setup

[00:00:00]
>> So the next thing that we're gonna do, I'm gonna make you write a bunch of queries for yourself and they're gonna be hard and it's gonna be fun. So we've been dealing with recipes up until now, most of the rest of the course deals with movies. We'll have a couple of things here and there with the recipes.

[00:00:18]
Because some of the concepts are demonstrated better there. But we're actually just gonna use something called the Open Media database, which is like this project here. See, if it comes up, that it's purely like open source. It's just a community of people that are updating this website with Modern movie information, right?

[00:00:46]
So I found a product here that offers a database dump of it. It's actually specifically called out on the postgres website as a good database ticket, like get started with. It's huge, so it has a half million records in it or something like that. So we can run some really big queries, which is gonna be really cool.

[00:01:06]
I took the database dump and I put it on Docker Hub, which is this one, you probably pulled it earlier. If you wanna see the Docker file, it's here it's relatively simple, it clones it. It runs it in Postgres 14, installs a couple of things. It downloads all of the various different queries, and then it runs the queries.

[00:01:30]
I also put the recipe guru SQL file that really long one that I had you download earlier. The exact same one has been running here. So you still have access to the same data. I didn't put the triggers or functions in there. I also didn't put the JSON B stuff in there.

[00:01:48]
So everything before the JSON based stuff is in there, everything after is not in there. So if you wanted to mess with the JSON B stuff, you'd have to start from there. Okay, so what I want you to do right now, if you're already running the complete intro to SQL container, you are done, you have to do nothing else.

[00:02:09]
If you're still running the PostgreSQL 14.1, I'm gonna give you two options. You can either leave it running, but I think I'm actually already running it. I am, I'm actually already running this one, but I'll show you how to start and stop it. So we called the previous one PG, just say docker kill pg.

[00:02:30]
As long as you're fine deleting that database, you might not be, but we're gonna be done with it, so it's fine. Docker kill pg, I don't have that running already, so that's fine, mine's already gone. But I'm gonna kill my SQL1 as well. So I did docker kill SQL like that.

[00:02:48]
Mine's not running, so it's not there, and then I want you to run this command right there. Docker run -e Postgres password equals lol --name=sql, you can call a PG again if you want. Actually, don't, because we're gonna use compose later, we call this SQL, it'll make a difference.

[00:03:14]
The -d is for detach, -p for port, if you're gonna keep the pg1 running, this won't run, because they're gonna be conflicting on the same port. So if you want to keep the other one running, just put this on 5433 or 534 or something like that. It has to be running on a different port, they can't be bind in the same port.

[00:03:38]
I shut my other one down, so I'm just gonna leave on 5432 --rm, it means delete all the logs when you're done. And then it's bthold/complete-intro-to-sql, which will run the latest version of it, because that's implied in Docker. Okay, you'll get this, which is just the hash of the container that's running.

[00:04:01]
If I put docker ps, you can see here I have this one running, it was created eight seconds ago and it's called sql. Now, I can say docker exec, this one right here. So if you put this /omdb it's going to connect you directly to that database. So go ahead and copy this whole thing.

[00:04:27]
So psql is the name of the command. And then, if you give it a additional command line, parameter there or argument, it's gonna connect to that database. So if you wanna connect directly to recipeguru, that's how you would do that. But I'm gonna connect directly to omdb. And you can see here, it says omdb, that's how I know I clicked to the right database.

[00:04:52]
If I do \d, you can see here, I have a bunch of movie tables. And if I do \l, you can see I should have recipeguru there. So I can do \c to recipeguru, that'll connect me to the recipe database. I can do \d here, you can see my tables are in fact still here.

[00:05:17]
So \c, we're gonna go back to omdb, cuz that's one that we care about. Do Command L, so we saw the databases, we saw the tables. So a little little bit about some of these tables that first of all, a very useful thing for you to see if you do \d.

[00:05:41]
And then the name of the table that you're trying to understand. It'll show you everything about that table. So I put \d movies here, ID, name, kind, series, runtime, so on and so forth. It's get some foreign keys, that's great. I'm gonna say SELECT * FROM movies and I'll say LIMIT 5;.

[00:06:03]
You can see, if you make it smaller it'll be easier to read, but I have a big screen, cuz I'm projecting here. But you can see it, I can see the budget, the revenue, the homepage, Finding Nemo is one of these. And then let's just select COUNT (*) FROM movies.

[00:06:26]
You can see we have a 178,000 movies for us to select from.