SQL for Data Practitioners: Introduction & Setup

0:07Hello and welcome. My name is Jonathan Barrios and I'm super excited to be your instructor for the SQL for Data Practitioners course. Let's take a look at what you're gonna learn in these set of videos. So the first section is an introduction, and then we'll dive into the setup. So let's talk about those individually. All right, so first, we're gonna dive into SQL, tables and spreadsheets or databases.

0:39Rather, we're gonna use spreadsheets as a way to onboard into tables and databases, and we'll talk about SQL as well. And since we're on SQL, we're talking about installing and kind of a little bit of a description of PostgreSQL or Postgres SQL, and that's the flavor of SQL that we will be using in this course, and we'll go through installing that together. And then we'll also install and talk a little bit more about PgAdmin, which is the user interface.

1:09And that runs in your browser. It's pretty cool. So we'll install that and get that up and running. And these two are basically gonna be your development environment. However, if you're already working with Jupyter Notebook and you're already a data analyst, data scientist, or a data engineer, I've included this optional video so that you can use Jupyter Notebook alongside PgAdmin so that you can query the database using Jupyter Notebook and Python. And I think it was just such a great tool to be able to use Python and Jupyter Notebook together.

1:44And that's optional. So that's only if you're already using that tool, but I thought I'd just include that because it's kind of fun to do that. And then finally, we will talk about the SQL Challenge. So at the end of every set of videos, there's gonna be a coding challenge, right? And so in this case, the Coding Challenge is gonna be SQL, and there'll be a series of questions where you answer questions about the database. And so that's kind of cool. So you test what you learn by actually performing the tasks that you would do in a job or in a role if you're working with SQL.

2:18So there'll be some questions, and then you'll figure out how to turn that into a SQL query to get the answers back from the database. All right, so let's go ahead and jump to the next section and surf the web, where we look at each one of these tools together. Okay, so here is PostgreSQL. It's been around for 30 years of active development. It's reliable, feature robust and very performant. And the reason that we're using PostgreSQL or Postgres SQL is because it works really well with Python and Jupyter Notebooks and the library Psycopg2, and we'll talk more about that in the Jupyter section.

2:59But as a high level overview, Python is the lingua franca of machine learning and data science, and that's why you see them associated together. So PostgreSQL and Python play well together, and SQL is just another added sort of tool for data science and data analysis and data engineering, and actually data practitioners and anyone that works with data. And let's jump to pgAdmin. Oops.

3:30So pgAdmin is the user interface that we're gonna be using to actually perform the SQL queries. And when we jump into Jupyter Notebook for, if you want to use that tool, well, you would be connecting to this user interface and asking questions over in this background where the server's running. So pgAdmin is where you type your SQL queries in, and you get your answers back. So it looks a little overwhelming, but don't worry, we're gonna cover this step by step and we'll install it and get started using the query tool inside of pgAdmin.

4:04And here we have a spreadsheet. So a spreadsheet is basically a really nice way to onboard you into understanding what a table is and what a database is. Spreadsheets and tables are very similar, and databases have multiple tables inside. And so this is what we're gonna talk about at the high level overview of those spreadsheets, tables and databases. And then we'll dive into Jupyter Notebook. Here, if you're already familiar with Jupyter Notebook, I'm gonna show you how to use the Psycopg2 package to connect to pgAdmin so that you can query the database.

4:43All right, so that's what we're gonna learn in these set of videos. I hope you're excited. I know that I am. This whole course is gonna be really great, and these set of videos are going to kick this journey, this SQL journey off. And so yeah, let's get started. I hope this has been informative, and I'd like to thank you for viewing.

0:07Hello and welcome back. In this video, we're gonna talk about SQL tables and databases at a high-level overview and talk about how they work together. In future videos, we're gonna install all of the development environment tools and then finally talk about the code challenges that we're gonna use. But for now, let's start off with what is SQL? So SQL is a programming language used for managing databases, and SQL stands for Structured Query Language.

0:43And it is a programming language. It's a full programming language, not just a tool. And it's, again, for managing databases. And so what does managing databases mean? Well, you can read, write, and manipulate data in a database. And so, that's, I think, at a high-level overview, that's a good description of managing a database. Furthermore, SQL gives you special functions that help you query and transform data inside of databases.

1:15So for example, you can ask for a count, and if you're familiar with Python and any other programming language, you'll see that those functions are very similar in this case. And if you don't have that experience, no worries. We're gonna go over that together. Okay, so what is a spreadsheet? So we talked about this a little bit, and this is an important concept because if you don't have an understanding of what a table is and what a database is, this is a great way to onboard to those higher concepts.

1:47So again, we're staying at the high-level overview, so let's take a look. Oops. All right, so let's take a look. So this is a table, actually, this is a spreadsheet, and they're very similar to tables. So, they're pretty much interchangeable as far as the concept goes. Let's talk about this. So we have these columns here and we have rows. And so, this is what's called tabular data, right?

2:20So, a spreadsheet is something that was very popular and designed for people that don't have any training. You pretty much open it and you can see what's happening. You can see, well, this column right here is some kind of an ID. I don't know what kind of ID, but that could actually be something like customer_ID or user ID, things like that. So, that's the only part that kinda doesn't make sense. You're not really sure what that ID is for. However, just to plant a seed, that ID is actually gonna be pivotal in relational concepts or how does this table relate to that table, right?

2:59So these IDs are a very popular way to connect those tables. So just a little side note there, planting a seed. So let's continue. Furthermore, everything else kinda makes sense, right? Someone without training say, "Well, I know what a first name, last name, and email is, and I know what the company is, and an IP address." Well, maybe you don't know what an IP address is, but you could Google it and figure it out. I think it's pretty common, but if not, it's not too hard. Generally, you can understand what you're reading here. And if you say, well, who is user number eight, or what's on row number eight?

3:33Well, you just look at eight and you can see it's Karen, Karen Commander, interesting name. And they work at Blogtags, right? And this is their IP address. So this is very easy to identify if you're looking at that row. Okay, so let's kinda jump to what a database is. So what is a database? Well, here is a database, and that's gonna be right here.

4:03And so, that's just kind of an image of a disc. Traditionally what databases used to look like. And so, these kinda look like spreadsheets, right? Well, these are tables. And so we were talking about how they're very similar. They're tabular data. And a database is a collection of one or more of these tables. So these tables are inside of this, and there's gonna be relationships between these tables, okay? And we'll talk a little bit more about the relationships later. But a database allows for data integrity, automation, and is great for large amounts of data.

4:40So, when you're working with a table or a spreadsheet, there's data automation and integrity and large data are gonna be issues. For example, with large amounts of data, a spreadsheet has a finite number of rows and columns that you can use. And when you start working with large data, spreadsheets tend to get a little bit slow and buggy, right? It'll be kind of a, you have to wait a while for it to load and you'll see a white screen. It's not really designed for big data, whereas databases are, okay?

5:13So, just planting a seed there. And databases are used with apps and websites. We talked about MySQL, and that's a very popular flavor of SQL used with apps and websites. And again, you would create, read, and update and delete, sort of manage that the data inside of a table, inside of a database. And an important concept is this last two right here. So not all databases are SQL databases, right? This was created in the 1970s.

5:45So, it's been around, you know, for a while. And it was kind of created when we didn't really have the internet that we do now. Everybody's on the internet, there's large amounts of data, and we could stream videos, and that was unheard of in the 70s. So, things have changed. And so, now we have new kind of databases called NoSQL. And a NoSQL database doesn't mean it doesn't use SQL at all. It just means not only SQL, right? So it's a hybrid. So some of them might not use SQL at all.

6:16Some of them might allow you to use SQL and some other kind of language. But there's two. The takeaway here is that there's two kinds of databases. Think of it like SQL databases and not only SQL databases. And these are more modern sort of like databases that you would see sort of come up in the last several years, maybe a little bit older. And there's some new databases coming out that are hybrids, and there's some really interesting things happening in this space.

6:47So this is a high-level overview of databases. And so, let's talk about the different kinds of databases, right? These are all SQL databases. And we'll talk a little bit more about the flavors of SQL. The one we're using in this course is PostgreSQL, which is free and open source, whereas something like Oracle, you have to pay for that. And I believe you have to pay for SQL Server as well. MySQL is very popular for websites and web applications.

7:20Okay, so, we talked a little bit about relational databases. This is an important concept because we're gonna be using this relationships and side of PostgreSQL and pgAdmin. So let's take a look. Okay, so relational databases. First, relational databases are based on the relational model, so that's why they're called relational databases. And that was proposed by E.F. Codd in the 70s, again, this has been around since the 70s.

7:54And it's a system that manages relational databases called a Relational Database Management System or RDBS for short. And the relationships are going to be with either a, let's say, like, some sort of an ID column where they match here and here. And this is gonna be like a parent child to a child. So, like, this is a parent and this would be a child table. Maybe this could be another relationship here.

8:26And this would be another child table. And the way that we are creating these relationships, and this is very important, is by using some sort of a key, which is called a primary key. So this one would be a primary key here. You'll often see as related or notated as PK for primary key and FK for foreign key. And so, this is a basically a high-level overview of how these relationships work, okay? And we'll dive into this a little bit deeper later.

8:59But all you need to know is that inside of a database, you have multiple tables. And if it's a relational database, they have some sort of relationship that is made possible by primary keys and foreign keys, also known as parent and child tables. So here, again, the same image, but now you can see that these are actually Relational Database Management Systems. So different kinds. Again, this is the one that we're using. And these are all different, RDBMSs.

9:30All right, so that covers it for the high-level overview of spreadsheets and then tables and then databases. In the upcoming videos, we're gonna install our applications in our development environment. And then at the end of this series of videos, we're gonna dive into our first code challenge. The first code challenge will be a little easy, but more on that later. I hope this has been informative, and I'd like to thank you for viewing.

0:07All right, so in this video we're gonna actually install our development environment. So what does that mean? First we're gonna install PostgreSQL, and that's the version of SQL, the relational database management system, the RDBMS. And then we're gonna install pgAdmin, which is our, the user interface that runs in the browser. And that's where we're gonna open a query tool and then actually write our SQL queries inside there. And it's also important to check out the GitHub Repo, which will contain all of the code examples that we're gonna use throughout all of the videos.

0:43And there's an important file in there that you need to install so that you can create the DVD rental database, which is the database that we're gonna be using throughout these videos. All right, so let's get started. So first what we need to do is search for PostgreSQL. And let's see what we get. Here's some ads. Okay, so here we go. And the URL address is postgreSQL.org.

1:14Click on that and we're gonna click on download. And here we're gonna see a bunch of different operating systems. I'm going to be using Mac since I'm on a Mac, but I'm also gonna point out the pain points for either of these, things to keep in mind. Okay, so let's go ahead and click on macOS. And here it is showing you the versions that you should download depending on the operating system that you're using. So in my case, I'm gonna be, I'm using an Apple M1 chip.

1:47So this version is gonna be 14 for me. But if you have a different version, like 10.11, then you would use version 10, right? So that's pretty much what it says. And I want to go back to the previous page and show you what it looks like for Windows as well. So 32 bit installations is not very common. It's much more common to have a 64 bit installation of Windows. And here you can just check out your details for your OS. And then again, make sure that you're selecting the correct version.

2:19For example, if this is your version of Windows, then you would use 14. But if you have an older version of Windows, then you might consider using a different version, like an older version if you have any conflicts. Okay, so let's go back to the macOS section. I've already installed this, so I'm only going to kind of just guide you through this. It's pretty straightforward though. So then you wanna click on download the installer. So let's do that. And I know that it's version 14 that I need, so I'm just gonna click on the latest version 14, and then I would click on this and download that.

3:01It might take a second. There we go. And we're gonna download that. Okay, great. I've already downloaded that, so I'm just going to bring it up my downloads folder and I'm going to double click on that. Okay, so here it is. Here's installer. So let's go ahead and double click on that. And I need to enter my password first. Okay, so it's starting up in the other window.

3:31Let me go ahead and drag that over here so that I can show you what's happening. Okay, so here's the setup wizard. Leave these modules clicked, we're gonna use all of those. And this is a little hard to see, but it's basically talking about the default locations. So I'm gonna leave that default and this is gonna be the default as well for the configuration. So just leave that. Go to next, and then this will show you all of your options. And then click next to install. I've already installed this, so I'm not gonna install this, but I will point out some important notes.

4:05For example, when you install this, you're going to, it's gonna ask you for a password. And it's really important that, let me close this first. And it's really important when you create the password, it's gonna prompt you to create a password. Definitely write that down because if you lose that password, you're gonna have to reinstall PostgreSQL. There's just no way around that. Since this is an educational sort of environment, I'm using the lowercase password.

4:37And you'll also need a password for pgAdmin. So that's needs to, for me, I'm using the same password for both, lowercase password. If this was a production environment, then you would definitely not want to do that. You would wanna use a secure password. But for, you know, while you're learning, it's okay to use that kind of a password. But whatever password that you use, depending on the level of security that you need, definitely write it down. So that's really, really important. Okay, so let's continue.

5:08So once you've done that, we're not going to launch PostgreSQL, so we're gonna just go ahead and install. Once that's done, you're gonna install pgAdmin. So let's go ahead and for pgAdmin. And it's the first one, which is pgadmin.org. Okay. And just one thing about PostgreQL, usually it'll give you a port number. In my case it's 5432.

5:38So if your port number is different, I wouldn't worry about that, just go ahead and use that. Okay. Okay, so now that we're here, so we're going to go click on the download button, let's see where that is, right here. And I'm going to click on macOS. You could do the same thing for, so here's macOS, the same thing for Windows. And let's look at Windows. It's gonna show you the different versions that you have. I'm gonna click on the newest one.

6:09And you just wanna make sure that if you have an older version of Windows, you might need to use an older version of pgAdmin. But I'm on a Mac, so I'm gonna click on macOS. And I'm just gonna click on the latest version here. And you'll see multiple files here. And the one that you want to install, if you're on a Mac, it's gonna be the executable file. And you're gonna see that it's gonna be the large one. So not 138 bytes, 176 megabytes. So I'm just gonna click on this and that'll prompt me to download that.

6:40I've already downloaded this, but let me just walk you through some important steps. And when you, so when you run this, if you're in a Mac, you're gonna need to drag this into your applications folder. Otherwise, you're gonna need to download it every time you run it. So make sure that you drag it into your applications folder. And then that's it. So we're ready to go. You don't need to run it right now. We're gonna go ahead and run this later. And next we need to talk about GitHub. So let's go ahead and go to the GitHub page and it is github.com/cbtn-data-science-ml.

7:19There's gonna be a link for this above the video. And here is where we need to, once you're here, you wanna click on SQL for Data Practitioners. Let's do that. And here is the file that you're gonna need to download. The SQL queries are here, and here's a Jupyter Notebook. So if you wanted to check out some code, maybe you're getting an error or something, always feel free to click on this and you'll see all of the different sort of sections, right? So statements, filtering, and so on and so forth. So 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, you want to download this. Let me go ahead and click on this and click download. And when you download this, you don't want to execute or open the file directly, right? So let's go ahead and save this. Once you've downloaded the file, it's important not to open it directly. We're gonna do that together in pgAdmin. But first it's important after you install PostgreSQL and pgAdmin to go ahead and restart your computer.

8:24So do that now and I'll see you when you're done with installing or restarting your computer. And now we're going to go ahead and open pgAdmin. Lemme just, so I'm gonna go to pgAdmin. Here it is. PostgreQL is already installed. And when you first open pgAdmin, it's gonna ask you for this, okay? And this is the master password. So if you've already, it's required to unlock and passwords and to connect.

8:56So remember your passwords, I used "password" lowercase. Oh great. And then now we want to click on servers. And you see PostgreSQL. If you click on PostgreSQL database, you may need to enter that password. And that's that password that you created when you installed that. So make sure that that is installed. Okay, so now we're connected to the database and we need to add the database, the DVD rental database that you downloaded, the .tar file.

9:31So make sure that you didn't click on that. If so, you need to delete it and then start again and download it again. So what we're gonna do is right click on PostgreSQL, or we're gonna right click on database actually and create and go to database. And we're gonna name this DVD rental, DVD rental. And we're gonna save that. Okay and here is our DVD rental. Cool.

10:03And now we need to restore the database. So we're gonna use a .tar file. So we're gonna right click on DVD rental and click on restore here. And we need to grab that file. So what I'm gonna do is I have it in my downloads folder, see where we are. Well, there it is. I see it right here. So you may need to select show hidden files. And if you need to see where that, where you are in your directory, I'm here, so I already know that, but just remember where you downloaded it.

10:40Maybe you need to figure out what the directory is and then just paste that in here so you can see it. I can see that this is the file that I'm looking for. And sometimes you need to make sure that this is selected to all files 'cause you might not be able to see it if it's on only SQL files or something like that. So make sure that you're looking at all files. I have the right one selected. And let's select that. And next we're gonna go into, so we wanna click on the data objects. And here we want to click on or select pre-data, data, and post data, and go back to general.

11:18And then we're gonna click on restore. And if you get any errors here, don't worry. In this case it says, successfully completed. Okay, so cool. So again, ignore any fail errors that might show up. And let's close this and we're going to refresh this. Okay and let's see if everything worked. And the way we're gonna do that is click on schemas and let's see if we can find tables right here.

11:52And you can see all of these tables. Okay, great. So what we're gonna do is right click on DVD rental and select a query tool. All right, this query tool is where you're gonna enter your SQL commands. And so the first thing we're gonna do is just select, so this is, you're gonna become very familiar with this syntax, but for now, select star, which means everything and from the film table. So let me show you where that is. So here's the, this is the database, the DVD rental.

12:26And so I'm already in there 'cause you can see here where it says DVD rental. And we're gonna select the film table. So it's this table right here. So select everything from film and you need to click on this play button here to run that query. All right, so it seems like everything worked and now we're set up with pgAdmin and PostgreSQL with our DVD rental database installed. Okay, so the last thing is you can go to file and then preferences.

12:58And here are a bunch of preferences that you can change. And you also have, in case you need to do that or you wanna explore this, and you'll also have the ability to go the online help or the apps, the website to check out the documentation. So let's go to the website and here's the documentation right here and make sure that you have the correct version of pgAdmin. So let's click on this one here, the latest version. So this is pgAdmin 4, I believe. Yeah.

13:31So the latest version I think is the one to use. So this is the documentation. So definitely get familiar with the documentation and deploying. If you have any errors you can look at deploying and this is just a useful sort of resource to have. All right, so hopefully everything ran smoothly. If not, definitely use the documentation or Google for help because you're not alone. If there's any issues, it's gonna be, somebody else has already posted a question and somebody's answered it. But generally this is pretty straightforward.

14:03The only two things to keep in mind is that it will ask you for a password for Postgres and pgAdmin. You have to create that and save that. And then you enter those passwords. I like to use the same password for both, especially when I'm just learning about it. But in a production environment, you could use a much more complicated password, obviously. But once you have those two passwords, the next pain point is to not click on the .tar file, the DVD rentals.tar file directly. That's only done, you only use that file to restore the database like we did.

14:35So hopefully you have everything installed and ready to go. So let's go ahead and dive into the next video where we're gonna talk about our coding challenge. I hope this has been informative and I'd like to thank you for viewing.

0:07Welcome back! In this video, we're gonna talk about Jupyter Notebook and how to use that tool to interact with the database going through PG admin. So you need to install some packages, and more importantly, you should be familiar with Jupyter Notebooks. If not, then go ahead and skip this video. This video is for those of you who are data analysts, data scientists, data engineers, or anyone who's already working with Jupyter Notebook and is familiar with conda environments and working with the terminal. So again, if this is all sounds foreign to you, just go ahead and skip this video.

0:42Not a big deal. This is just optional for those of you who are familiar with this tool and would like to use that tool because it is advantageous to perform SQL queries in a database and start working with that in Jupyter Notebook. All right, so let's get started. So first you want to head to... There's a link above this video, but you can also head to the GitHub repository, and click on Jupyter Notebooks and then you can download this notebook. And it's called Connecting_to_PgAdmin.ipynb.

1:15And, okay, so that's pretty much how you would download it here and this is a little bit explaining how to get started, but I'm gonna show you that here right now. So I've already downloaded that file and I've navigated to the folder where I stored that, which is SQL_Jupiter, and I've created a conda environment. And my conda environment is SQL_Jupiter. I've activated that. So let's see what I have in here. And I do have the PG admin.

1:46So I need to install Jupyter Notebook. So I'm gonna do that real quick. So conda install Jupyter, and this will take a minute. I'm gonna click yes to proceed, and then it is going to install Jupyter Notebook into this new Conda environment that I just created for this video. Once this is done executing, then I'm going to type in Jupyter Notebook to open that notebook, and then we'll take a look at the Jupyter Notebook file.

2:16So Jupyter Notebook, and this will open... Make sure that you're in the same directory as where that Jupyter Notebook file is located, right? And here it is. Let me just drag this over. It opened in another window. Here it is. Okay, so let me make this a little bit shorter so we can see everything. Okay, so let me click on and that's why it's important that you're in the right directory because when you open this, it's going to open wherever that folder is.

2:48So instead of having to navigate, it's kind of nice just to see it there and click on it. Alright, so basically you need to.. Let's go through this one by one. So you need to import all of these packages to get started. And then here you're gonna enter, when you execute this field, it's gonna ask you for your password. And then when you do enter that password, it'll save it so that you can't see it, right? So let's go ahead and just run this together here.

3:21Okay, so I have to install these. So I'm gonna have to do this in a new terminal. So here's a new terminal and I'm gonna make sure that I'm activated. Let's see where the activation was it. Okay, so it's Conda Activate, and the name was SQL Jupiter.

3:52Okay, so I've activated that and now I'm just going to install psycopg2 inside of this virtual environment, condo environment. Now I can click yes to continue. I couldn't do that before. Okay, it's done. And I'm gonna do the same here for pandas. I'm gonna grab this one for the password. Click yes.

4:25And the last one is get pass. Okay, it seems that we're ready to go. All right, so we have everything installed, so we don't need this anymore. And now we can import these. So if we run this and import them, we will be able to, because we've installed them. Okay, so that worked, took a minute.

4:56So let's go ahead and run this first cell, and then we're gonna type in password, because that's my password. And once we are done running that, I'm not gonna save that. And then we're gonna connect to DVD rental and then the user is Postgres and the password is secret. And so I've saved that password as secret here. So let's go ahead and run this and that ran very quickly. And so we're gonna create a connection here and save that as cursor or CUR. Let's go ahead and run that.

5:26And here we're gonna look at the and select everything from the actor table and see that is actor's table and then actor's first name. So let's go ahead and run this. Okay, so now we have our statements saved. So let's execute actor's first name. Okay and so here we're going to fetch one item, and let's go ahead and run data and see what we get.

5:57We get back Penelope. You can also do fetch all or fetch many. Let's do fetch many and we'll do 10. And let's just go ahead and run this again. And then we get the first 10. If you do fetch all, you're gonna get all of them. So, use that with caution. It can be quite a... Depending on the size of the records and the rows, it could be quite a lot so keep that in mind when running this for the first time. All right, so that's how we do that.

6:27And then when you want to close the connection when you're done, so go ahead and just run this, and this is how you close that connection. And right here I've taken... So this is how you would create a data frame. And since we already have the variable data, let's go ahead and try to run this. And then this one here, and now we have, we've already saved it as the... So we have just 10. So we just have the first 10 rows. But let's say you wanted to grab everything, right?

7:01So what we're gonna do is we're gonna execute, let's see where we open. Okay, so look, we have to... It's already closed. We have to open the connection. So let's go ahead and execute that connection here. Nope. Let's see. So let's do this connection here and then the the cursor. And then we're gonna just run this one. And then here we're executing this command. Now we're gonna say fetch all.

7:34So we're gonna grab everything and save it into data. So let's go to run this. I'm not gonna run this because it's gonna list it, but actually let's just do it. It sort of truncates it so it's not too bad, but there's a lot of records. And then you're gonna close that connection. And then here we we're gonna create a data frame saved as df. And we're passing in data that we already stored and then let's return that data frame. And so we have 200 rows of actors, and it's kind of truncated that for us. And so here, if you wanted to take a look at this, so create a new data frame.

8:10And so we're only looking at, in this case, the first name. But if you wanted to do select everything, so we could do that, and that way you'd have a complete table that would look something like this. Okay, so that's pretty much it. This should get you started. So whenever you need to run your SQL queries, what you're going to do is use this and define them as variables. So whatever you want to do, so if you wanna work with a whole table, just select that whole table and turn it into a data frame, and then you can start exploring your data inside of Jupyter Notebook.

8:48I hope this has been informative, and I'd like to thank you for viewing.

0:07Welcome back. In this video we're gonna talk about coding challenges. We don't have a coding challenge yet in this set of videos because we are just talking about high level overview of spreadsheets, tables and databases. And then we covered installing PostgreSQL, and then we install pgAdmin. Then we talked about Jupyter Notebook. So we really haven't done anything except for using one select statement to return the film table. And so we can't really do a code challenge, but this is a great opportunity to introduce how it would work.

0:44So let's say you're in the next set of videos where you're gonna encounter your first coding challenge. What you wanna do is just answer each question using SQL. And so let's go through an example together, right? So let's go ahead and open pgAdmin. Okay, so here's pgAdmin. And what you wanna do is if you don't have this code editor or this query tool, let's just close that and don't save not a big deal. And all I'm gonna do is head to the DVD rental. Let's close this so that it, maybe it's totally closed.

1:17So we can use this as a introduction on how to open the query tool, which we will cover in the next set of videos. So here's PostgreSQL, here's databases. We have two databases. We have a Postgres database, that's our user database, and that's created for us. And this is the one that we created. So let's click on that and expand it. And all, you don't even need to expand it, you can just right click on it and then go to query tool. And let's say the quiz was asking us is, like, what is the statement that you would use to return the film table?

1:52Everything from the film table. And this is not a code challenge, this is just an introduction. And if you don't recall this, just follow along. Basically just open your query tool and just enter what I enter, and I'm gonna show you everything. So you don't need to, it's not a really a code challenge, but just an introduction to a code challenge. All right, so let's write some SQL code or some SQL queries together. So let's say one of the questions was, what is the query to return everything from the film table inside of the DVD rental database?

2:29So I'm just gonna share it with you here. It's select everything and you could write it like this, and select everything from film. And you would, the best practice is to put this on its own line, and at the very end add a semicolon. So this is the query. So you would, you know, maybe it's multiple choice, and you would see answer A, B, C, or D. And if one says select star from film semicolon, then you would select that and submit it. And that's pretty much how you do that.

3:00And then the video section, I would go over it and talk about it. For example, let's say you selected the wrong answer and it was just missing the semicolon. So I just changed that semicolon right here. I just took it off and it will still work, So let me show you that. If I run this, it took a second, but yeah, it returned everything. So it'll work with or without a semicolon. But the correct answer is with a semicolon because it's a best practice. I'm not gonna be that tricky and work with those kind of details. I'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 without having sort of shown you any SQL queries just yet. And you know, just for your exploration right now, since we're talking about it, here are all the different tables that you can access. So instead of film, let's say you want to look at the actor table. This is great practice. And so all you would do is just go ahead and click on this play button here and you would get all the actors, right? So the code challenge in this section is not necessarily a code challenge, it's just gonna ask you questions about SQL, pgAdmin, and what a table and database and spreadsheet are, things like that.

4:10So it's really easy on this first time, but at least it's introducing you to the flow, right? So again, a little bit about my teaching style. We talk about, we have a conversation in the very beginning. We have a conversation in the very beginning and we talk about high level concepts. Maybe we'll look at some slides where we look at syntax, and then we go into code examples where we write SQL queries together, and then at the very end we have a code challenge. And maybe there'll be some questions that I'll ask you to write something.

4:41And then you would enter everything into the code, to the query tool to see if you get the correct answer and then you would mark your correct answer based on what you get back from the database. So it's this really sort of interactive coding experience and I think that you'll really like it. Okay, so that's it for code challenges. Again, it's super easy in this case because there's no real code challenge. You just have some multiple choice quizzes. But in the next set of videos, you will have a code challenge and that'll continue all the way to the end of this course.

5:14So high level discussion code examples, and then a code challenge. All right, I hope this has been informative and I'd like to thank you for viewing.