- 22 hours ago
Create the Database with this script: https://github.com/AlexTheAnalyst/PostgresqlYouTubeSeries
Practice Postgresql here: https://www.analystbuilder.com/questions
In this series I want to teach you Postgresql! It's one of the most popular types of SQL right now and is becoming even more popular with the rise of AI. I'll teach you all the syntax and how to use it as well as go beyond just querying!
____________________________________________
RESOURCES:
💻Analyst Builder - https://www.analystbuilder.com/
📖Take my Full MySQL Course Here: https://bit.ly/3tqOipr
📖Take my Full Python Course Here: https://bit.ly/48O581R
📖Practice Technical Interview Questions: https://bit.ly/46pDqqL
Coursera Courses:
Google Data Analyst Certification: https://coursera.pxf.io/5bBd62
Data Analysis with Python - https://coursera.pxf.io/BXY3Wy
IBM Data Analysis Specialization - https://coursera.pxf.io/AoYOdR
Tableau Data Visualization - https://coursera.pxf.io/MXYqaN
*Please note I may earn a small commission for any purchase through these links - Thanks for supporting the channel!*
____________________________________________
BECOME A MEMBER -
Want to support the channel? Consider becoming a
Practice Postgresql here: https://www.analystbuilder.com/questions
In this series I want to teach you Postgresql! It's one of the most popular types of SQL right now and is becoming even more popular with the rise of AI. I'll teach you all the syntax and how to use it as well as go beyond just querying!
____________________________________________
RESOURCES:
💻Analyst Builder - https://www.analystbuilder.com/
📖Take my Full MySQL Course Here: https://bit.ly/3tqOipr
📖Take my Full Python Course Here: https://bit.ly/48O581R
📖Practice Technical Interview Questions: https://bit.ly/46pDqqL
Coursera Courses:
Google Data Analyst Certification: https://coursera.pxf.io/5bBd62
Data Analysis with Python - https://coursera.pxf.io/BXY3Wy
IBM Data Analysis Specialization - https://coursera.pxf.io/AoYOdR
Tableau Data Visualization - https://coursera.pxf.io/MXYqaN
*Please note I may earn a small commission for any purchase through these links - Thanks for supporting the channel!*
____________________________________________
BECOME A MEMBER -
Want to support the channel? Consider becoming a
Category
📚
LearningTranscript
00:00What's going on, everybody? Welcome back to another video.
00:02Today, we're going to be starting our PostgreSQL series.
00:10Now, PostgreSQL is one of the most popular and one of the best SQL platforms in the entire world.
00:16I've compared in the past to MySQL since they're both open source and really popular tools and platforms.
00:21But in recent years, especially with the rise of AI, more and more companies are starting to use PostgreSQL.
00:27Now, in this series, we're going to have a beginner, an intermediate, and an advanced.
00:31And we're going to do something that I've never done in a previous series before.
00:35We're going to get a little bit outside of just querying data.
00:37So in the beginner series, which is where this video is going to be, we're going to be installing.
00:41We're going to be learning some of the basics of just using PostgreSQL and querying data.
00:45In the intermediate series, we'll get a little bit more advanced.
00:47We'll start joining data.
00:48We might even start looking at window functions and CTEs and things like that.
00:51And then in the advanced, we'll go a little bit beyond what we've done before.
00:54We're going to start incorporating other functions and functionalities within PostgreSQL
00:58that do not involve querying your data.
01:01These may even be things that typically an AE would do, which is an analytics engineer.
01:06But as data professionals, we should absolutely know how to do them, how these things work.
01:10I hope that's okay, that this series is going to get a little bit more advanced than we typically do,
01:14because I just think that's where we are.
01:16We need to go more advanced.
01:18We need to dive into these things.
01:19And we need to know things beyond just querying data these days.
01:22By the end of the series, you're going to be an expert in PostgreSQL.
01:24I have no doubt about it.
01:26But before we actually start doing all those things, we have to install and set up our database.
01:32With that being said, let's jump over to my screen and get started.
01:34All right.
01:35Now, most people are just going to come to Google and they're going to say,
01:37download PostgreSQL.
01:39And I'm going to walk you through how to get there because it's a little bit confusing.
01:42But you can start here and just follow along, or I'll leave a link down in the description
01:45where you can just go to the final link.
01:48Either way is fine.
01:49But let's get started.
01:50Let's go ahead and click on downloads.
01:52You can come over here and select what operating system you are using.
01:56I'm using a Windows.
01:57So I'm going to select Windows.
01:59We're going to come up here to download the installer.
02:02This is by the EDB.
02:03And we're going to click on this.
02:05And now we have our PostgreSQL version.
02:08And you can get it for Linux or Macs.
02:09And here we have the Windows.
02:11So I'm going to leave this link right here in the description down below.
02:15So you can just come right to it.
02:16But there's going to be a lot of people who don't do that.
02:18And I just want to walk through that really quickly.
02:21So let's go ahead and select Windows because that's the one I'm using.
02:24But be sure to get the right one for you.
02:26I'm going to select it.
02:27It's going to start downloading.
02:28And I'm just going to put this in my downloads.
02:31Let's go ahead and save this.
02:33And I've already have this.
02:35But I'm going to get rid of it and replace it again just for you guys.
02:38Let's go ahead and click yes.
02:39Now that that is downloaded, we're just going to click on it.
02:42You can either go to your File Explorer and open it from there.
02:44Or you can just click on it depending on what system you're using.
02:48It's asking me.
02:49You may not have seen that.
02:50But it was asking me for permission.
02:51I just gave it permission to access the computer.
02:54We have our setup window.
02:56And we just need to walk through this very quickly.
02:58And I'm going to make some small notes as we're going through just for you in case you are curious.
03:02Or you want to make sure you do this right.
03:05We're going to say next.
03:07You need to specify where this is going to go by default at least on a Windows machine.
03:11It's going to go into your program files.
03:12I am perfectly fine with that.
03:14Let's go ahead and select next.
03:15Now this part is really important.
03:18I recommend just getting all of them.
03:20But the most important ones are the Postgres SQL Server and the PG Admin 4.
03:25You don't have to have the Stack Builder.
03:27You don't have to have the command line tools.
03:29But it's just best to have it all in my opinion.
03:32Because you never know what you're going to need it for later on.
03:34Let's go ahead and select next.
03:36Now this is going to specify where we're storing our data.
03:39And so this is important because when you start storing data within Postgres SQL and you start putting it in
03:44there,
03:44it has to store it somewhere on your computer to use locally.
03:48So I'm just going to keep it in the default file path.
03:51Let's go ahead and select next.
03:52Now we have to set up a password.
03:55This is very important.
03:56This messes a lot of people up because they forget their password down the line.
03:59Make it really easy.
04:01Just make it something that you are going to remember.
04:03So I'm going to set mine up.
04:09All right.
04:10Almost done.
04:11And now I'm going to select next.
04:14And we're going to select the Port Server.
04:16Now by default, mine is 5432.
04:19And so I'm going to go ahead and select next.
04:20You can put something else in there.
04:21But I'm just going to keep it as the default.
04:23Next, we're just selecting the locale to be used for a new database cluster.
04:27There's a bunch of different options in here.
04:29But we're just going to use default.
04:31That will make the best decision based off the information that it has from your local machine.
04:36And so it usually is best to just use the local one.
04:40Let's go ahead and select next.
04:41These are all the things that they are going to be changing that they're going to be doing on your
04:45computer.
04:46You can go ahead and take a look at those if you would like.
04:48And go ahead and select next.
04:49And now we're ready to actually install this and put it onto our computer.
04:54Let's go ahead and select next.
04:56And now it's going to install.
04:57This takes a couple minutes and then it'll be done.
05:00Once mine finishes up, then we'll get going again.
05:06All right.
05:07PostgreSQL is completely done setting up.
05:09We have the option to launch Stack Builder at exit.
05:12We don't need to do that.
05:13So we're not going to.
05:15Let's go ahead and click finish.
05:17And just like that, we are ready to go.
05:19So what we need to do now is let's come down here and let's type in PostgreSQL.
05:25Nothing comes up.
05:27And that's because you aren't actually going to be queering directly off of it.
05:32You need to go to pgadmin4.
05:34There's our beautiful logo right there for pgadmin4.
05:38That is what we downloaded earlier along with the PostgreSQL server.
05:42But to actually interface with it on a local machine, you need to use pgadmin4.
05:47Let's go ahead and click on this.
05:49Now it just pulled up.
05:50Let's go ahead and expand this really quick.
05:52It says we need to enter our password.
05:55Go ahead and do that now.
05:57I'm going to make sure I got mine in there.
05:58I'm going to save that password.
05:59I'm going to click OK.
06:00I can tell you right off the bat when you're setting this up, you're going to need to do
06:04a few things.
06:04One is you need to come over to this query tool workspace and we need to actually connect
06:10to the server because the server is where the data is going to be stored.
06:15We're going to actually run our queries and things like that.
06:16If it doesn't have it or if it's just nothing there, go ahead and click PostgreSQL 18 or if
06:23you're watching this later and you need to do 19 or 20 or whatever version, go ahead
06:28and do that.
06:29We're going to keep it PostgreSQL 18.
06:31We do have our port there.
06:33We have our database, our user.
06:35It should auto select our password.
06:36We'll see if it does.
06:38But I'm just going to connect and open the query tool.
06:40This is a good sign.
06:41This is some previous stuff that I've done in the past because I was preparing for the
06:47series.
06:47If we come back over here, though, to our default workspace, which is where we do want to be,
06:52let's go ahead and open up our server.
06:54We're going to come down here to our PostgreSQL and we can come down here to our schemas.
06:59If we open up our schemas, this is where most of the time we're going to be living.
07:03All right.
07:03This is our public schema.
07:05This is just by default what it's going to look like.
07:07There's a ton of stuff in PostgreSQL.
07:10We're not going to get into all of it today.
07:12As we work through the series, I'll show you new stuff.
07:15But if we come right down here, we have our tables.
07:18Now, if we open this up, notice we have no tables, right?
07:23But we're going to create those in just a second.
07:25Let's go ahead and open up our query tool.
07:27You can do Alt-Shift-Q, at least on the Windows machine.
07:30But if you have a different one, see what that is.
07:33And this is where we're going to live.
07:35Let's go back to our query.
07:36This is typically what it's going to look like.
07:38Okay, so we have our query right here.
07:40We have our scratchpad over here.
07:43And I usually get rid of this, if I'm being honest.
07:45But then we have our data output messages and notifications down here.
07:49This right here and this over here is usually what you're going to be doing when you're working within PostgreSQL.
07:56Within here, we can write things like our select statements, our from statements, our where, our group by, window functions,
08:03CTEs, all sorts of stuff.
08:04There's a ton of things we're going to learn in this series.
08:08But let's come over here.
08:10And I'm going to, down in the description, in the GitHub, there's going to be a file to create the
08:14database.
08:15I'm going to go ahead and just open this up.
08:17And I'm going to open up the file.
08:19And this right here is what we need to run in order to create our different tables within this public
08:26schema.
08:27Now, this is your first time seeing something like this.
08:30It might be a little bit confusing.
08:32As I go through and explain it to you, it should make a lot of sense.
08:36Now, we have no tables here.
08:37But I know people.
08:39I've done a lot of tutorials like this before.
08:41So I added this at the top because people are going to make some small mistake.
08:45They're going to accidentally create these tables but not others.
08:48And what we're doing here is just dropping the table if it already exists.
08:52So if there's a table in there and you make a mistake, you don't have to write this or know
08:56how to go in and delete it.
08:58But you can just drop the table if it's already there so you can start from scratch.
09:02I did that for you.
09:04Now, what we're doing is we're creating a table.
09:06These are specific commands within PostgreSQL that do specific things.
09:11So all of these kind of red are going to be, you know, specific commands within PostgreSQL.
09:17We're creating this table named ships.
09:19We have a ship ID.
09:20And then right after it, you'll notice we have a data type.
09:24Now, right here, we just have serial primary keys.
09:27That's just going to be 1, 2, 3, 4 without ever creating a duplicate.
09:30That's what a primary key is.
09:32We also have ship name.
09:33And that's just going to be text.
09:35Now, down below, you will see the data that we are going to be inserting into this table.
09:41And so you can see this is a Millennium Falcon.
09:43That's a freighter.
09:44And we're going to insert that into this ships table that we're creating.
09:49So we're going to create it.
09:51We're going to insert it into there.
09:52And then we'll insert the values into that table and everything works well.
09:57Let's go down.
09:58We also have create table for planets.
10:01And again, it's very similar.
10:02We're going to do serial text text with these three column names.
10:05And this is our data at the row level.
10:07And then we have our create table for our character info.
10:11Now, we have something important here just to note.
10:14Is we have our planet ID.
10:15We have our ship ID.
10:17These are what are called foreign keys or references to other tables.
10:22So this planet ID, when we insert this planet ID, 2, 7, 1, 1, this references these planets right here.
10:31And so we need to be able to, and that's because of the serial primary key, just so you know,
10:35the planet ID.
10:36Just adding that in there for extra info.
10:38But we have to specify that those are references.
10:41And just one last thing to note is that this is an integer.
10:44So we have a different type of data type.
10:46We also have a date, a numeric, a character.
10:50These things for you right now are not mega important.
10:53But as you're querying data, as you're creating databases, as you're importing data with thousands and millions of rows, data
11:00types are super, super important.
11:02And so this is just a very brief highlight on this.
11:05Now, let's go ahead and run this.
11:07And you should be able to do this well.
11:08We're just going to execute the entire script.
11:10That means all the code that's in there.
11:12Let's go ahead and run this.
11:13Right here, it's skipping these drop tables because they don't already exist.
11:17It's just giving you this message.
11:19So if you get this message, that's okay.
11:21But it returned successfully.
11:23Let's come down here to tables.
11:24And let's right click and click refresh.
11:28Now we have our character info, our planets and our ships table.
11:32And they should have columns within it.
11:34And you can even come in here and you can look at this.
11:37So we have integer, text, text, integer, text.
11:40Or actually integer, integer.
11:41But these are our column names.
11:43And it also shares the data types with it.
11:45Now what we're going to do very just briefly because we just got into this is all we're
11:50going to do is open up this character info table and take a look at it.
11:53And then the next lesson, we're going to start diving into querying, looking at the basics
11:58of querying our data here.
12:00Now let's come up here.
12:01Let's click on query tools.
12:03Just going to open up a brand new window that we haven't done before.
12:06All you're going to write, this is the only thing we're going to write.
12:08We're going to select everything.
12:11And where are we selecting everything from?
12:13We're going to do from character underscore info.
12:18That's it.
12:18It's all we're doing.
12:19Let's go ahead and run this.
12:21And now we can see we have our data.
12:24It's actually in our table.
12:25So this is something that we're going to highlight and kind of go into a little bit more in the
12:30next lesson.
12:31But now we have our tables and they all should be good to go.
12:35You know what?
12:36Just while we're here, let me go back and or not back.
12:39But let's also look at planets.
12:41Let's make sure this one got in.
12:42So we're going to look at our planets table.
12:44The data is in there.
12:45And let's also look at our ships data or ships table.
12:49And those are in there.
12:50So now we're good to go.
12:53Whoops.
12:54So now we're good to go.
12:55We have all of our data exactly where we want it to be.
12:58And in this series, in the beginner series, we're going to cover a lot of really good examples
13:03for just learning the basics of querying data.
13:05It is 100% a necessary skill to know.
13:08As we get into the intermediate and advanced concepts, especially with learning things
13:12that are kind of outside of what a traditional data analyst would typically work on, I will
13:16be walking you through every single step of the way.
13:18If you've never done this step before, don't worry.
13:21I will walk you through it to make sure that you know it really well by the end.
13:24I absolutely love Sequel.
13:26I'm so excited for this series.
13:27So I hope that you are going to enjoy this just as much as I am.
13:31If you have not already, be sure to like and subscribe.
13:34And I will see you in the next video.
Comments