SQL for Data Practitioners: Introduction & Setup

0:07<v ->Hello and welcome.</v>

0:09My name is Jonathan Barrios

0:10and I'm super excited to be your instructor

0:12for the SQL for Data Practitioners course.

0:15Let's take a look at what you're gonna learn

0:17in these set of videos.

0:21So the first section is an introduction,

0:24and then we'll dive into the setup.

0:27So let's talk about those individually.

0:30All right, so first,

0:33we're gonna dive into SQL,

0:35tables and spreadsheets or databases.

0:39Rather, we're gonna use spreadsheets

0:41as a way to onboard into tables and databases,

0:45and we'll talk about SQL as well.

0:47And since we're on SQL,

0:49we're talking about installing

0:51and kind of a little bit of a description

0:53of PostgreSQL or Postgres SQL,

0:57and that's the flavor of SQL

0:58that we will be using in this course,

1:01and we'll go through installing that together.

1:04And then we'll also install

1:05and talk a little bit more about PgAdmin,

1:07which is the user interface.

1:09And that runs in your browser. It's pretty cool.

1:12So we'll install that and get that up and running.

1:14And these two are basically

1:16gonna be your development environment.

1:19However, if you're already working with Jupyter Notebook

1:23and you're already a data analyst,

1:24data scientist, or a data engineer,

1:27I've included this optional video

1:29so that you can use Jupyter Notebook alongside PgAdmin

1:33so that you can query the database

1:35using Jupyter Notebook and Python.

1:38And I think it was just such a great tool

1:40to be able to use Python and Jupyter Notebook together.

1:44And that's optional.

1:45So that's only if you're already using that tool,

1:48but I thought I'd just include that

1:49because it's kind of fun to do that.

1:51And then finally, we will talk about the SQL Challenge.

1:54So at the end of every set of videos,

1:58there's gonna be a coding challenge, right?

2:00And so in this case, the Coding Challenge is gonna be SQL,

2:03and there'll be a series of questions

2:05where you answer questions about the database.

2:08And so that's kind of cool.

2:10So you test what you learn

2:11by actually performing the tasks

2:14that you would do in a job

2:16or in a role if you're working with SQL.

2:18So there'll be some questions,

2:19and then you'll figure out how to turn that into a SQL query

2:23to get the answers back from the database.

2:27All right, so let's go ahead and jump to the next section

2:31and surf the web,

2:32where we look at each one of these tools together.

2:35Okay, so here is PostgreSQL.

2:38It's been around for 30 years of active development.

2:41It's reliable, feature robust and very performant.

2:45And the reason that we're using PostgreSQL or Postgres SQL

2:49is because it works really well with Python

2:52and Jupyter Notebooks and the library Psycopg2,

2:56and we'll talk more about that in the Jupyter section.

2:59But as a high level overview,

3:02Python is the lingua franca

3:03of machine learning and data science,

3:05and that's why you see them associated together.

3:08So PostgreSQL and Python play well together,

3:11and SQL is just another added sort of tool

3:15for data science and data analysis and data engineering,

3:19and actually data practitioners

3:21and anyone that works with data.

3:24And let's jump to pgAdmin.

3:29Oops.

3:30So pgAdmin is the user interface

3:32that we're gonna be using

3:33to actually perform the SQL queries.

3:35And when we jump into Jupyter Notebook for,

3:38if you want to use that tool,

3:40well, you would be connecting to this user interface

3:43and asking questions over in this background

3:46where the server's running.

3:47So pgAdmin is where you type your SQL queries in,

3:51and you get your answers back.

3:52So it looks a little overwhelming,

3:54but don't worry, we're gonna cover this step by step

3:57and we'll install it

3:59and get started using the query tool inside of pgAdmin.

4:04And here we have a spreadsheet.

4:06So a spreadsheet is basically a really nice way

4:10to onboard you into understanding what a table is

4:13and what a database is.

4:16Spreadsheets and tables are very similar,

4:18and databases have multiple tables inside.

4:21And so this is what we're gonna talk about

4:23at the high level overview of those spreadsheets,

4:27tables and databases.

4:29And then we'll dive into Jupyter Notebook.

4:31Here, if you're already familiar with Jupyter Notebook,

4:34I'm gonna show you how to use the Psycopg2 package

4:38to connect to pgAdmin so that you can query the database.

4:43All right, so that's what we're gonna learn

4:45in these set of videos.

4:46I hope you're excited.

4:47I know that I am.

4:48This whole course is gonna be really great,

4:50and these set of videos are going to kick this journey,

4:53this SQL journey off.

4:55And so yeah, let's get started.

4:57I hope this has been informative,

4:58and I'd like to thank you for viewing.

0:07<v ->Hello and welcome back.</v>

0:09In this video,

0:10we're gonna talk about SQL tables and databases

0:14at a high-level overview

0:16and talk about how they work together.

0:19In future videos,

0:19we're gonna install all of the development environment tools

0:22and then finally talk about the code challenges

0:25that we're gonna use.

0:27But for now, let's start off with what is SQL?

0:32So SQL is a programming language

0:37used for managing databases,

0:40and SQL stands for Structured Query Language.

0:43And it is a programming language.

0:46It's a full programming language, not just a tool.

0:51And it's, again, for managing databases.

0:53And so what does managing databases mean?

0:56Well, you can read, write,

0:58and manipulate data in a database.

1:01And so, that's, I think, at a high-level overview,

1:05that's a good description of managing a database.

1:08Furthermore, SQL gives you special functions

1:11that help you query

1:12and transform data inside of databases.

1:15So for example, you can ask for a count,

1:18and if you're familiar with Python

1:20and any other programming language,

1:22you'll see that those functions

1:23are very similar in this case.

1:25And if you don't have that experience, no worries.

1:27We're gonna go over that together.

1:31Okay, so what is a spreadsheet?

1:33So we talked about this a little bit,

1:35and this is an important concept

1:37because if you don't have an understanding

1:39of what a table is and what a database is,

1:43this is a great way to onboard to those higher concepts.

1:47So again, we're staying at the high-level overview,

1:49so let's take a look.

1:52Oops.

1:54All right, so let's take a look.

1:58So this is a table,

2:01actually, this is a spreadsheet,

2:03and they're very similar to tables.

2:06So, they're pretty much interchangeable

2:09as far as the concept goes.

2:11Let's talk about this.

2:12So we have these columns here and we have rows.

2:16And so, this is what's called tabular data, right?

2:20So, a spreadsheet is something that was very popular

2:24and designed for people that don't have any training.

2:26You pretty much open it and you can see what's happening.

2:29You can see,

2:30well, this column right here is some kind of an ID.

2:33I don't know what kind of ID,

2:35but that could actually be something

2:36like customer_ID or user ID, things like that.

2:42So, that's the only part that kinda doesn't make sense.

2:46You're not really sure what that ID is for.

2:48However, just to plant a seed,

2:50that ID is actually gonna be pivotal in relational concepts

2:56or how does this table relate to that table, right?

2:59So these IDs are a very popular way to connect those tables.

3:03So just a little side note there, planting a seed.

3:05So let's continue.

3:07Furthermore, everything else kinda makes sense, right?

3:11Someone without training say,

3:12"Well, I know what a first name, last name, and email is,

3:15and I know what the company is, and an IP address."

3:18Well, maybe you don't know what an IP address is,

3:20but you could Google it and figure it out.

3:22I think it's pretty common, but if not, it's not too hard.

3:25Generally, you can understand what you're reading here.

3:27And if you say, well, who is user number eight,

3:31or what's on row number eight?

3:33Well, you just look at eight and you can see it's Karen,

3:36Karen Commander, interesting name.

3:39And they work at Blogtags, right?

3:43And this is their IP address.

3:45So this is very easy to identify

3:48if you're looking at that row.

3:51Okay, so let's kinda jump to what a database is.

3:57So what is a database?

3:59Well, here is a database, and that's gonna be right here.

4:03And so, that's just kind of an image of a disc.

4:06Traditionally what databases used to look like.

4:10And so, these kinda look like spreadsheets, right?

4:14Well, these are tables.

4:15And so we were talking about how they're very similar.

4:18They're tabular data.

4:20And a database is a collection

4:21of one or more of these tables.

4:24So these tables are inside of this,

4:26and there's gonna be relationships

4:27between these tables, okay?

4:29And we'll talk a little bit more

4:31about the relationships later.

4:33But a database allows for data integrity, automation,

4:38and is great for large amounts of data.

4:40So, when you're working with a table or a spreadsheet,

4:44there's data automation and integrity

4:47and large data are gonna be issues.

4:51For example, with large amounts of data,

4:53a spreadsheet has a finite number of rows

4:57and columns that you can use.

4:58And when you start working with large data,

5:01spreadsheets tend to get a little bit slow and buggy, right?

5:04It'll be kind of a, you have to wait a while for it to load

5:06and you'll see a white screen.

5:08It's not really designed for big data,

5:10whereas databases are, okay?

5:13So, just planting a seed there.

5:16And databases are used with apps and websites.

5:20We talked about MySQL,

5:21and that's a very popular flavor of SQL used

5:24with apps and websites.

5:26And again, you would create, read, and update and delete,

5:30sort of manage that the data inside of a table,

5:33inside of a database.

5:35And an important concept is this last two right here.

5:39So not all databases are SQL databases, right?

5:42This was created in the 1970s.

5:45So, it's been around, you know, for a while.

5:48And it was kind of created

5:49when we didn't really have the internet that we do now.

5:53Everybody's on the internet,

5:55there's large amounts of data, and we could stream videos,

5:58and that was unheard of in the 70s.

5:59So, things have changed.

6:01And so, now we have new kind of databases called NoSQL.

6:05And a NoSQL database doesn't mean it doesn't use SQL at all.

6:09It just means not only SQL, right? So it's a hybrid.

6:13So some of them might not use SQL at all.

6:16Some of them might allow you to use SQL

6:19and some other kind of language.

6:21But there's two.

6:22The takeaway here is that there's two kinds of databases.

6:25Think of it like SQL databases and not only SQL databases.

6:30And these are more modern sort of like databases

6:34that you would see sort of come up

6:36in the last several years, maybe a little bit older.

6:40And there's some new databases coming out that are hybrids,

6:44and there's some really interesting things

6:46happening in this space.

6:47So this is a high-level overview of databases.

6:52And so, let's talk about the different kinds

6:53of databases, right?

6:55These are all SQL databases.

6:57And we'll talk a little bit more about the flavors of SQL.

7:01The one we're using in this course is PostgreSQL,

7:05which is free and open source,

7:07whereas something like Oracle,

7:08you have to pay for that.

7:10And I believe you have to pay for SQL Server as well.

7:13MySQL is very popular for websites and web applications.

7:20Okay, so, we talked a little bit about relational databases.

7:23This is an important concept

7:25because we're gonna be using this relationships

7:28and side of PostgreSQL and pgAdmin.

7:32So let's take a look.

7:40Okay, so relational databases.

7:42First, relational databases

7:44are based on the relational model,

7:46so that's why they're called relational databases.

7:49And that was proposed by E.F. Codd in the 70s,

7:52again, this has been around since the 70s.

7:54And it's a system that manages relational databases

7:58called a Relational Database Management System

8:01or RDBS for short.

8:03And the relationships are going to be with either a,

8:07let's say, like, some sort of an ID column

8:09where they match here and here.

8:12And this is gonna be like a parent child to a child.

8:15So, like, this is a parent and this would be a child table.

8:20Maybe this could be another relationship here.

8:26And this would be another child table.

8:29And the way that we are creating these relationships,

8:32and this is very important,

8:33is by using some sort of a key,

8:36which is called a primary key.

8:38So this one would be a primary key here.

8:40You'll often see as related or notated as PK for primary key

8:46and FK for foreign key.

8:50And so, this is a basically a high-level overview

8:53of how these relationships work, okay?

8:56And we'll dive into this a little bit deeper later.

8:59But all you need to know is that inside of a database,

9:01you have multiple tables.

9:03And if it's a relational database,

9:05they have some sort of relationship

9:07that is made possible by primary keys and foreign keys,

9:11also known as parent and child tables.

9:17So here, again, the same image,

9:19but now you can see that these are actually

9:21Relational Database Management Systems.

9:24So different kinds.

9:25Again, this is the one that we're using.

9:27And these are all different, RDBMSs.

9:30All right, so that covers it for the high-level overview

9:34of spreadsheets and then tables and then databases.

9:38In the upcoming videos,

9:39we're gonna install our applications

9:41in our development environment.

9:42And then at the end of this series of videos,

9:44we're gonna dive into our first code challenge.

9:47The first code challenge will be a little easy,

9:49but more on that later.

9:51I hope this has been informative,

9:52and I'd like to thank you for viewing.

0:07<v ->All right, so in this video</v>

0:09we're gonna actually install our development environment.

0:11So what does that mean?

0:13First we're gonna install PostgreSQL,

0:15and that's the version of SQL,

0:16the relational database management system, the RDBMS.

0:22And then we're gonna install pgAdmin,

0:24which is our, the user interface that runs in the browser.

0:27And that's where we're gonna open a query tool

0:30and then actually write our SQL queries inside there.

0:34And it's also important to check out the GitHub Repo,

0:38which will contain all of the code examples

0:40that we're gonna use throughout all of the videos.

0:43And there's an important file in there that you need

0:45to install so that you can create the DVD rental database,

0:49which is the database that we're gonna be using

0:51throughout these videos.

0:53All right, so let's get started.

0:56So first what we need to do is search for PostgreSQL.

1:05And let's see what we get. Here's some ads.

1:08Okay, so here we go.

1:10And the URL address is postgreSQL.org.

1:14Click on that and we're gonna click on download.

1:19And here we're gonna see a bunch of

1:21different operating systems.

1:23I'm going to be using Mac since I'm on a Mac,

1:26but I'm also gonna point out the pain points

1:28for either of these, things to keep in mind.

1:31Okay, so let's go ahead and click on macOS.

1:34And here it is showing you the versions

1:37that you should download depending on the operating system

1:41that you're using.

1:42So in my case, I'm gonna be,

1:44I'm using an Apple M1 chip.

1:47So this version is gonna be 14 for me.

1:51But if you have a different version, like 10.11,

1:55then you would use version 10, right?

1:57So that's pretty much what it says.

1:58And I want to go back to the previous page

2:01and show you what it looks like for Windows as well.

2:04So 32 bit installations is not very common.

2:07It's much more common to have a 64 bit

2:09installation of Windows.

2:10And here you can just check out your details for your OS.

2:15And then again, make sure that you're selecting

2:17the correct version.

2:19For example, if this is your version of Windows,

2:23then you would use 14.

2:24But if you have an older version of Windows,

2:26then you might consider using a different version,

2:28like an older version if you have any conflicts.

2:34Okay, so let's go back to the macOS section.

2:38I've already installed this,

2:39so I'm only going to kind of just guide you through this.

2:42It's pretty straightforward though.

2:44So then you wanna click on download the installer.

2:46So let's do that.

2:47And I know that it's version 14 that I need,

2:50so I'm just gonna click on the latest version 14,

2:54and then I would click on this and download that.

3:01It might take a second. There we go.

3:05And we're gonna download that. Okay, great.

3:07I've already downloaded that,

3:08so I'm just going to bring it up my downloads folder

3:13and I'm going to double click on that.

3:18Okay, so here it is. Here's installer.

3:20So let's go ahead and double click on that.

3:24And I need to enter my password first.

3:29Okay, so it's starting up in the other window.

3:31Let me go ahead and drag that over here

3:33so that I can show you what's happening.

3:36Okay, so here's the setup wizard.

3:38Leave these modules clicked,

3:40we're gonna use all of those.

3:41And this is a little hard to see,

3:42but it's basically talking about the default locations.

3:47So I'm gonna leave that default

3:49and this is gonna be the default as well

3:51for the configuration.

3:54So just leave that.

3:55Go to next, and then this will show you all of your options.

3:58And then click next to install.

3:59I've already installed this, so I'm not gonna install this,

4:02but I will point out some important notes.

4:05For example, when you install this,

4:07you're going to, it's gonna ask you for a password.

4:11And it's really important that, let me close this first.

4:16And it's really important when you create the password,

4:19it's gonna prompt you to create a password.

4:21Definitely write that down

4:22because if you lose that password,

4:24you're gonna have to reinstall PostgreSQL.

4:27There's just no way around that.

4:29Since this is an educational sort of environment,

4:35I'm using the lowercase password.

4:37And you'll also need a password for pgAdmin.

4:43So that's needs to, for me, I'm using the same password

4:46for both, lowercase password.

4:48If this was a production environment,

4:50then you would definitely not want to do that.

4:51You would wanna use a secure password.

4:54But for, you know, while you're learning,

4:55it's okay to use that kind of a password.

4:58But whatever password that you use,

5:00depending on the level of security that you need,

5:02definitely write it down.

5:04So that's really, really important.

5:07Okay, so let's continue.

5:08So once you've done that,

5:10we're not going to launch PostgreSQL,

5:14so we're gonna just go ahead and install.

5:17Once that's done, you're gonna install pgAdmin.

5:20So let's go ahead and for pgAdmin.

5:25And it's the first one, which is pgadmin.org.

5:31Okay.

5:32And just one thing about PostgreQL,

5:34usually it'll give you a port number.

5:36In my case it's 5432.

5:38So if your port number is different,

5:41I wouldn't worry about that, just go ahead and use that.

5:43Okay.

5:45Okay, so now that we're here,

5:46so we're going to go click on the download button,

5:49let's see where that is, right here.

5:53And I'm going to click on macOS.

5:55You could do the same thing for, so here's macOS,

5:58the same thing for Windows.

6:00And let's look at Windows.

6:01It's gonna show you the different versions that you have.

6:05I'm gonna click on the newest one.

6:09And you just wanna make sure

6:10that if you have an older version of Windows,

6:12you might need to use an older version of pgAdmin.

6:16But I'm on a Mac, so I'm gonna click on macOS.

6:19And I'm just gonna click on the latest version here.

6:23And you'll see multiple files here.

6:24And the one that you want to install, if you're on a Mac,

6:27it's gonna be the executable file.

6:29And you're gonna see that it's gonna be the large one.

6:32So not 138 bytes, 176 megabytes.

6:36So I'm just gonna click on this

6:39and that'll prompt me to download that.

6:40I've already downloaded this,

6:42but let me just walk you through some important steps.

6:46And when you, so when you run this,

6:49if you're in a Mac, you're gonna need to drag this

6:51into your applications folder.

6:53Otherwise, you're gonna need to download it

6:54every time you run it.

6:55So make sure that you drag it into your applications folder.

6:58And then that's it. So we're ready to go.

7:00You don't need to run it right now.

7:02We're gonna go ahead and run this later.

7:04And next we need to talk about GitHub.

7:07So let's go ahead and go to the GitHub page

7:11and it is github.com/cbtn-data-science-ml.

7:19There's gonna be a link for this above the video.

7:21And here is where we need to, once you're here,

7:24you wanna click on SQL for Data Practitioners.

7:27Let's do that.

7:28And here is the file that you're gonna need to download.

7:31The SQL queries are here, and here's a Jupyter Notebook.

7:34So if you wanted to check out some code,

7:37maybe you're getting an error or something,

7:38always feel free to click on this

7:40and you'll see all of the different sort of sections, right?

7:45So statements, filtering, and so on and so forth.

7:48So let's go back here and we're gonna click on this.

7:50And what you wanna remember here is that you don't want to,

7:55you want to download this.

7:56Let me go ahead and click on this and click download.

8:01And when you download this, you don't want to execute

8:05or open the file directly, right?

8:08So let's go ahead and save this.

8:11Once you've downloaded the file,

8:12it's important not to open it directly.

8:15We're gonna do that together in pgAdmin.

8:18But first it's important after you install PostgreSQL

8:21and pgAdmin to go ahead and restart your computer.

8:24So do that now and I'll see you when you're done

8:26with installing or restarting your computer.

8:30And now we're going to go ahead and open pgAdmin.

8:33Lemme just, so I'm gonna go to pgAdmin. Here it is.

8:37PostgreQL is already installed.

8:41And when you first open pgAdmin,

8:46it's gonna ask you for this, okay?

8:49And this is the master password.

8:51So if you've already, it's required to unlock

8:54and passwords and to connect.

8:56So remember your passwords, I used "password" lowercase.

9:04Oh great. And then now we want to click on servers.

9:06And you see PostgreSQL.

9:09If you click on PostgreSQL database,

9:12you may need to enter that password.

9:14And that's that password that you created

9:16when you installed that.

9:17So make sure that that is installed.

9:20Okay, so now we're connected to the database

9:22and we need to add the database,

9:27the DVD rental database that you downloaded, the .tar file.

9:31So make sure that you didn't click on that.

9:32If so, you need to delete it and then start again

9:35and download it again.

9:37So what we're gonna do is right click on PostgreSQL,

9:42or we're gonna right click on database actually

9:45and create and go to database.

9:49And we're gonna name this DVD rental, DVD rental.

9:57And we're gonna save that.

10:00Okay and here is our DVD rental. Cool.

10:03And now we need to restore the database.

10:05So we're gonna use a .tar file.

10:07So we're gonna right click on DVD rental

10:10and click on restore here.

10:16And we need to grab that file.

10:19So what I'm gonna do is I have it in my downloads folder,

10:25see where we are.

10:26Well, there it is. I see it right here.

10:28So you may need to select show hidden files.

10:33And if you need to see where that,

10:35where you are in your directory,

10:36I'm here, so I already know that,

10:38but just remember where you downloaded it.

10:40Maybe you need to figure out what the directory is

10:43and then just paste that in here so you can see it.

10:45I can see that this is the file that I'm looking for.

10:50And sometimes you need to make sure

10:51that this is selected to all files

10:53'cause you might not be able to see it

10:54if it's on only SQL files or something like that.

10:57So make sure that you're looking at all files.

10:59I have the right one selected.

11:02And let's select that.

11:04And next we're gonna go into,

11:07so we wanna click on the data objects.

11:09And here we want to click on or select pre-data,

11:13data, and post data, and go back to general.

11:18And then we're gonna click on restore.

11:23And if you get any errors here, don't worry.

11:25In this case it says, successfully completed.

11:29Okay, so cool.

11:31So again, ignore any fail errors that might show up.

11:35And let's close this and we're going to refresh this.

11:40Okay and let's see if everything worked.

11:43And the way we're gonna do that is click on schemas

11:48and let's see if we can find tables right here.

11:52And you can see all of these tables. Okay, great.

11:55So what we're gonna do is right click on DVD rental

11:58and select a query tool.

12:01All right, this query tool is where you're gonna enter

12:04your SQL commands.

12:05And so the first thing we're gonna do is just select,

12:08so this is, you're gonna become very familiar

12:10with this syntax,

12:12but for now, select star, which means everything

12:16and from the film table.

12:19So let me show you where that is.

12:21So here's the, this is the database, the DVD rental.

12:26And so I'm already in there

12:28'cause you can see here where it says DVD rental.

12:31And we're gonna select the film table.

12:33So it's this table right here.

12:35So select everything from film

12:38and you need to click on this play button here

12:40to run that query.

12:43All right, so it seems like everything worked

12:45and now we're set up with pgAdmin and PostgreSQL

12:49with our DVD rental database installed.

12:53Okay, so the last thing is you can go to file

12:56and then preferences.

12:58And here are a bunch of preferences that you can change.

13:01And you also have, in case you need to do that

13:04or you wanna explore this,

13:05and you'll also have the ability to go the online help

13:11or the apps, the website to check out the documentation.

13:15So let's go to the website

13:18and here's the documentation right here

13:20and make sure that you have the correct version of pgAdmin.

13:24So let's click on this one here, the latest version.

13:27So this is pgAdmin 4, I believe. Yeah.

13:31So the latest version I think is the one to use.

13:34So this is the documentation.

13:36So definitely get familiar with the documentation

13:38and deploying.

13:39If you have any errors you can look at deploying

13:41and this is just a useful sort of resource to have.

13:47All right, so hopefully everything ran smoothly.

13:50If not, definitely use the documentation or Google for help

13:53because you're not alone.

13:55If there's any issues,

13:57it's gonna be, somebody else has already posted a question

14:00and somebody's answered it.

14:01But generally this is pretty straightforward.

14:03The only two things to keep in mind is

14:05that it will ask you for a password for Postgres

14:08and pgAdmin.

14:09You have to create that and save that.

14:11And then you enter those passwords.

14:13I like to use the same password for both,

14:15especially when I'm just learning about it.

14:17But in a production environment,

14:18you could use a much more complicated password, obviously.

14:22But once you have those two passwords,

14:24the next pain point is to not click on the .tar file,

14:27the DVD rentals.tar file directly.

14:30That's only done, you only use that file

14:33to restore the database like we did.

14:35So hopefully you have everything installed and ready to go.

14:38So let's go ahead and dive into the next video

14:42where we're gonna talk about our coding challenge.

14:45I hope this has been informative

14:46and I'd like to thank you for viewing.

0:07<v ->Welcome back!</v>

0:08In this video, we're gonna talk about Jupyter Notebook

0:11and how to use that tool to interact with the database

0:15going through PG admin.

0:17So you need to install some packages, and more importantly,

0:20you should be familiar with Jupyter Notebooks.

0:22If not, then go ahead and skip this video.

0:25This video is for those of you who are data analysts,

0:28data scientists, data engineers,

0:30or anyone who's already working with Jupyter Notebook

0:33and is familiar with conda environments

0:36and working with the terminal.

0:37So again, if this is all sounds foreign to you,

0:40just go ahead and skip this video.

0:42Not a big deal.

0:43This is just optional for those of you

0:44who are familiar with this tool

0:46and would like to use that tool because it is advantageous

0:49to perform SQL queries in a database

0:52and start working with that in Jupyter Notebook.

0:54All right, so let's get started.

0:56So first you want to head to...

0:59There's a link above this video,

1:01but you can also head to the GitHub repository,

1:04and click on Jupyter Notebooks

1:06and then you can download this notebook.

1:10And it's called Connecting_to_PgAdmin.ipynb.

1:15And, okay, so that's pretty much

1:17how you would download it here

1:18and this is a little bit explaining how to get started,

1:21but I'm gonna show you that here right now.

1:25So I've already downloaded that file

1:27and I've navigated to the folder where I stored that,

1:31which is SQL_Jupiter,

1:34and I've created a conda environment.

1:37And my conda environment is SQL_Jupiter.

1:40I've activated that.

1:41So let's see what I have in here.

1:43And I do have the PG admin.

1:46So I need to install Jupyter Notebook.

1:48So I'm gonna do that real quick.

1:50So conda install Jupyter,

1:55and this will take a minute.

1:57I'm gonna click yes to proceed,

1:59and then it is going to install Jupyter Notebook

2:01into this new Conda environment that I just created

2:04for this video.

2:06Once this is done executing, then I'm going to type in

2:10Jupyter Notebook to open that notebook,

2:12and then we'll take a look at the Jupyter Notebook file.

2:16So Jupyter Notebook, and this will open...

2:21Make sure that you're in the same directory

2:22as where that Jupyter Notebook file is located, right?

2:27And here it is. Let me just drag this over.

2:30It opened in another window. Here it is.

2:34Okay, so let me make this a little bit shorter

2:36so we can see everything.

2:37Okay, so let me click on

2:39and that's why it's important

2:41that you're in the right directory

2:42because when you open this,

2:45it's going to open wherever that folder is.

2:48So instead of having to navigate,

2:49it's kind of nice just to see it there and click on it.

2:53Alright, so basically you need to..

2:56Let's go through this one by one.

2:58So you need to import all of these packages to get started.

3:02And then here you're gonna enter,

3:05when you execute this field,

3:08it's gonna ask you for your password.

3:10And then when you do enter that password, it'll save it

3:13so that you can't see it, right?

3:16So let's go ahead and just run this together here.

3:21Okay, so I have to install these.

3:24So I'm gonna have to do this in a new terminal.

3:28So here's a new terminal

3:30and I'm gonna make sure that I'm activated.

3:37Let's see where the activation was it.

3:40Okay, so it's Conda Activate,

3:44and the name was SQL Jupiter.

3:52Okay, so I've activated that

3:54and now I'm just going to install psycopg2

4:00inside of this virtual environment, condo environment.

4:05Now I can click yes to continue. I couldn't do that before.

4:08Okay, it's done.

4:10And I'm gonna do the same here for pandas.

4:16I'm gonna grab this one for the password. Click yes.

4:25And the last one is get pass.

4:32Okay, it seems that we're ready to go.

4:42All right, so we have everything installed,

4:43so we don't need this anymore.

4:46And now we can import these.

4:47So if we run this and import them, we will be able to,

4:50because we've installed them.

4:53Okay, so that worked, took a minute.

4:56So let's go ahead and run this first cell,

4:59and then we're gonna type in password,

5:02because that's my password.

5:03And once we are done running that,

5:05I'm not gonna save that.

5:08And then we're gonna connect to DVD rental

5:10and then the user is Postgres and the password is secret.

5:13And so I've saved that password as secret here.

5:16So let's go ahead and run this and that ran very quickly.

5:20And so we're gonna create a connection here

5:22and save that as cursor or CUR.

5:25Let's go ahead and run that.

5:26And here we're gonna look at the

5:29and select everything from the actor table

5:33and see that is actor's table and then actor's first name.

5:37So let's go ahead and run this.

5:40Okay, so now we have our statements saved.

5:43So let's execute actor's first name.

5:48Okay and so here we're going to fetch one item,

5:54and let's go ahead and run data and see what we get.

5:57We get back Penelope.

5:58You can also do fetch all or fetch many.

6:03Let's do fetch many and we'll do 10.

6:08And let's just go ahead and run this again.

6:10And then we get the first 10.

6:12If you do fetch all, you're gonna get all of them.

6:14So, use that with caution.

6:16It can be quite a...

6:17Depending on the size of the records and the rows,

6:19it could be quite a lot so keep that in mind

6:22when running this for the first time.

6:26All right, so that's how we do that.

6:27And then when you want to close the connection

6:29when you're done, so go ahead and just run this,

6:31and this is how you close that connection.

6:33And right here I've taken...

6:38So this is how you would create a data frame.

6:41And since we already have the variable data,

6:44let's go ahead and try to run this.

6:46And then this one here, and now we have,

6:49we've already saved it as the...

6:52So we have just 10. So we just have the first 10 rows.

6:56But let's say you wanted to grab everything, right?

7:01So what we're gonna do is

7:03we're gonna execute, let's see where we open.

7:05Okay, so look, we have to...

7:08It's already closed. We have to open the connection.

7:11So let's go ahead and execute that connection here.

7:15Nope. Let's see.

7:19So let's do this connection here and then the the cursor.

7:23And then we're gonna just run this one.

7:25And then here we're executing this command.

7:29Now we're gonna say fetch all.

7:34So we're gonna grab everything and save it into data.

7:37So let's go to run this.

7:38I'm not gonna run this because it's gonna list it,

7:40but actually let's just do it.

7:42It sort of truncates it so it's not too bad,

7:45but there's a lot of records.

7:46And then you're gonna close that connection.

7:49And then here we we're gonna create a data frame

7:51saved as df.

7:53And we're passing in data that we already stored

7:55and then let's return that data frame.

7:58And so we have 200 rows of actors,

8:00and it's kind of truncated that for us.

8:02And so here, if you wanted to take a look at this,

8:07so create a new data frame.

8:10And so we're only looking at, in this case, the first name.

8:16But if you wanted to do select everything,

8:19so we could do that,

8:20and that way you'd have a complete table

8:23that would look something like this.

8:25Okay, so that's pretty much it. This should get you started.

8:28So whenever you need to run your SQL queries,

8:31what you're going to do is use this

8:33and define them as variables.

8:36So whatever you want to do,

8:37so if you wanna work with a whole table,

8:39just select that whole table and turn it into a data frame,

8:44and then you can start exploring your data

8:46inside of Jupyter Notebook.

8:48I hope this has been informative,

8:50and I'd like to thank you for viewing.

0:07<v ->Welcome back.</v>

0:08In this video we're gonna talk about coding challenges.

0:12We don't have a coding challenge yet in this set of videos

0:15because we are just talking about high level overview

0:19of spreadsheets, tables and databases.

0:23And then we covered installing PostgreSQL,

0:26and then we install pgAdmin.

0:28Then we talked about Jupyter Notebook.

0:30So we really haven't done anything except for using one

0:33select statement to return the film table.

0:37And so we can't really do a code challenge,

0:40but this is a great opportunity to introduce

0:43how it would work.

0:44So let's say you're in the next set of videos

0:47where you're gonna encounter your first coding challenge.

0:50What you wanna do is just answer each question using SQL.

0:54And so let's go through an example together, right?

0:58So let's go ahead and open pgAdmin.

1:01Okay, so here's pgAdmin.

1:03And what you wanna do is if you don't have this code editor

1:07or this query tool, let's just close that

1:09and don't save not a big deal.

1:11And all I'm gonna do is head to the DVD rental.

1:13Let's close this so that it, maybe it's totally closed.

1:17So we can use this as a introduction on how

1:19to open the query tool,

1:22which we will cover in the next set of videos.

1:25So here's PostgreSQL, here's databases.

1:28We have two databases.

1:29We have a Postgres database, that's our user database,

1:32and that's created for us.

1:33And this is the one that we created.

1:35So let's click on that and expand it.

1:38And all, you don't even need to expand it,

1:39you can just right click on it and then go to query tool.

1:43And let's say the quiz was asking us is,

1:47like, what is the statement that you would use

1:50to return the film table?

1:52Everything from the film table.

1:54And this is not a code challenge,

1:56this is just an introduction.

1:58And if you don't recall this, just follow along.

2:01Basically just open your query tool

2:03and just enter what I enter,

2:05and I'm gonna show you everything.

2:07So you don't need to, it's not a really a code challenge,

2:09but just an introduction to a code challenge.

2:11All right, so let's write some SQL code

2:13or some SQL queries together.

2:17So let's say one of the questions was,

2:20what is the query to return everything from the film table

2:27inside of the DVD rental database?

2:29So I'm just gonna share it with you here.

2:32It's select everything

2:34and you could write it like this,

2:35and select everything from film.

2:37And you would, the best practice is

2:40to put this on its own line,

2:42and at the very end add a semicolon.

2:44So this is the query.

2:45So you would, you know, maybe it's multiple choice,

2:49and you would see answer A, B, C, or D.

2:52And if one says select star from film semicolon,

2:56then you would select that and submit it.

2:58And that's pretty much how you do that.

3:00And then the video section,

3:01I would go over it and talk about it.

3:03For example, let's say you selected the wrong answer

3:06and it was just missing the semicolon.

3:08So I just changed that semicolon right here.

3:10I just took it off and it will still work,

3:13So let me show you that.

3:14If I run this, it took a second,

3:17but yeah, it returned everything.

3:18So it'll work with or without a semicolon.

3:21But the correct answer is with a semicolon

3:23because it's a best practice.

3:25I'm not gonna be that tricky

3:27and work with those kind of details.

3:29I'm just gonna see if you know how to return it.

3:31So, but that's the only example I could kind of come up with

3:35without having sort of shown you any SQL queries just yet.

3:40And you know, just for your exploration right now,

3:42since we're talking about it,

3:43here are all the different tables that you can access.

3:46So instead of film, let's say you want

3:48to look at the actor table.

3:50This is great practice.

3:51And so all you would do is just go ahead

3:52and click on this play button here

3:55and you would get all the actors, right?

3:58So the code challenge in this section is not necessarily a

4:02code challenge, it's just gonna ask you questions about SQL,

4:05pgAdmin, and what a table and database

4:08and spreadsheet are, things like that.

4:10So it's really easy on this first time,

4:13but at least it's introducing you to the flow, right?

4:16So again, a little bit about my teaching style.

4:18We talk about, we have a conversation in the very beginning.

4:23We have a conversation in the very beginning

4:25and we talk about high level concepts.

4:27Maybe we'll look at some slides where we look at syntax,

4:31and then we go into code examples

4:33where we write SQL queries together,

4:35and then at the very end we have a code challenge.

4:38And maybe there'll be some questions

4:40that I'll ask you to write something.

4:41And then you would enter everything into the code,

4:45to the query tool to see if you get the correct answer

4:48and then you would mark your correct answer

4:50based on what you get back from the database.

4:52So it's this really sort of interactive coding experience

4:55and I think that you'll really like it.

4:59Okay, so that's it for code challenges.

5:01Again, it's super easy in this case

5:03because there's no real code challenge.

5:04You just have some multiple choice quizzes.

5:07But in the next set of videos,

5:08you will have a code challenge

5:10and that'll continue all the way to the end of this course.

5:14So high level discussion code examples,

5:17and then a code challenge.

5:19All right, I hope this has been informative

5:21and I'd like to thank you for viewing.