Connecting to a SQL Database

So the first thing is going to be connecting into our database. So I'm going to show you how to do this, and then afterward there's an exercise in your book that's written down step by step of how to do this. But I want you to see it so you can understand what's going on, and then afterward you can go through this exercise that we've written down so that you can repeat this yourself. Now on my computer here in my Database Navigator here, so this is first of all the DBeaver application.

Here it looks like a little beaver, and when you first launch it there might be some daily tip or something that opens up. You can close that daily tip; you don't have to worry about that. And over here is a Database Navigator. It's how I can navigate or explore a database. Notice there are no databases listed over there.

Think of putting a database over here as kind of like bookmarking a website. If you bookmark a website, you could just click on that bookmark and you can go back to that website. I want to create a bookmark, if you will, for a database.

I want it to store my login information so that I can come back to this more easily in the future. So there's a database menu here and I can create a database connection. As I mentioned, you don't have to follow along.

I'm just going to show you this first so you can see what we're doing, and then afterward there's going to be an exercise in the book. I'm actually just going to look at that exercise so I can remember the IP address of our server because I don't remember the IP address of our server. So I'm just going to take a look at that here.

So I want to create a new database connection which is going to store that over here in my navigator. So I'm going to create a new database connection and it wants to know out of all the different types of databases what kind of database am I connecting to. PostgreSQL here is the elephant.

Right now I'm in the All section here, which shows me all of them. It's possible you might start in the Popular section so you won't have as many of these, and PostgreSQL is one of the most popular ones here. Microsoft SQL being another one of those, but I'm going to choose PostgreSQL because that's the kind of database that we're using.

And then this is the information of how to connect to that. This kind of information would be given to you by the company that you're working for. So in our case, just consider that you're working for us and we're giving you the information to connect to our server that we have created.

Typically there's an IP address or a named address of how you get there. So this IP address will be in your exercise that you're going to do. So I just type in that IP address and that's how to get to our server.

That's the location of our server. The database is the name of the database. This is chosen by your company, and we created a database called company_data.

That's the name of the database that we're connecting into. But we actually have more than just that one database, so I want to show all of the databases, not just the one that I'm connecting into. Even though there is a primary singular database that I'm connecting into, if I didn't check that on, I'd show that one database, but I want to see them all.

So I want to make sure I check that on. Not anybody can connect into our database. We need to have a username and password for security.

We are Student as a username, and in your exercise it'll say that the password is noble-student. I want to make sure that I didn't make any typos here. So instead of just hitting Finish I'm going to hit Test Connection.

That's going to make sure that I didn't make any typos. Now, every once in a while there will be a driver that you need to install or to update. If you ever see these drivers and it says download, make sure you download those because without the drivers you won't be able to connect to the database.

This is generally just a once-every-once-in-a-while thing. Normally you don't see that—maybe the first time or every once in a while they might update those drivers—but most of the time you don't see that. But if you ever do, make sure you download those drivers.

Here it says Connected, which just means I didn't make any typos. But if you did make a typo—maybe in the wrong username or something—it'll say, you know, password authentication failed. So it just says, "Hey, check out your username and password to make sure that those things are correct." But if you typed everything in right, then you should get a Connected, and that means you typed everything in correctly. When I hit Finish, it then stores that information over here, and I can open it up and I can take a look that there are databases, and here's that company_data database as well as the other databases.

If I ever do want to go back in and edit any of the information that's here—maybe I made a typo or something—I can always right-click on this and edit that connection, and in that Edit dialog, I get all of those settings back if I ever need to edit any of those things. Also, if I ever want to delete this connection I can also right-click on it and I can delete it and start over again. The idea is that over here you list all of the databases that you want to connect into, and so you can connect into one company's, you can connect into another one, and you just choose to browse to the database that you want to be querying at that moment.

So this is going to be the first step here that we're going to do, so this is Exercise 1a. If you look at the top right corner of the page there'll be an Exercise 1a. So look at that top right corner. This is just a one-page exercise, so let's all go through Exercise 1a. Okay, it looks like this is what the PDF looks like. All right, there you go. So the host is that 34.74.232, company_data, and then to the right of that check on Show All Databases. Okay, now instead of username PostgreSQL, type in Student for the username, lowercase s, and then the password is noble, n-o-b-l-e-student. Then click on Test Connection at the bottom left of that window. Okay, good, you are connected. So hit OK and then hit Finish at the bottom right of that window. Over there on the left, notice how it says company_data in the Database Navigator. Click the little arrow there. Yeah, give it a second to connect, then open up Databases. Use the little arrow to the left. Also, just something came up with a few people. Some people have messed up the interface—like they minimize things and close things—and they kind of messed up the interface. So in the Window menu there's a Reset Perspective. This is just a bad name for the interface or workspace that you're in. If you ever Reset the Perspective it just puts the interface back to the normal way. If you ever accidentally close things and mess up the interface and do that yourself, then anytime you want to kind of just reset the interface back to the normal stock interface, Reset the Perspective. It's a horrible name for it. They should call it like Workspace or Interface. That is what that means, and if you reset, it just goes back to normal. So just good to know that if you ever mess anything up that you can always do that.

All right, so essentially what did we just do there? Our computer, which has DBeaver installed on it—our laptop, our desktop, whatever—is going over to the server, which is running some type of database—in our case a PostgreSQL Database. So whenever you're connecting into a company's server or database, they will tell you what kind of database you're connecting into and they will give you all of that information. On your computer, you need some application that you install on your computer to browse that database. We're using DBeaver, but there can also be the SQL Server Management Studio being made by Microsoft. We're using this app kind of like we do a web browser to browse the internet, right? So what is a database exactly? It's where the database—or where the data—is stored essentially. It's stored in tables. If you've ever used Excel with rows and columns that's basically what a table is. So a database—a singular database of which we connected into one, but we said to show all of them—a server itself can contain multiple databases. We split our information into different kinds of information so we have different potential databases, and within one database we can split those into tables. So on a high level we have a server, a server that we're connecting into. We're just connecting into one server. It can contain multiple databases and each database can contain multiple tables. This is what a table looks like. So in a certain database you can have multiple tables, and we organize different types of information into different tables.

For example, here is a list of products. We have a Title for the product, and we have a Price for the product. What's really important in SQL is Rows versus Columns. Whenever I talk about Rows versus Columns, pay particular attention to Rows versus Columns. Various features in SQL will specifically do something for rows or for columns, so that's a very important thing. Let's think about what that means. A column is a kind of information. So, for example, Title—those are all titles. In this case, they're text data. Prices—those are all numbers with two decimal places. Are you ever going to have a price with three decimal places? No, so it's a kind of data, right? There are many prices, there are many titles, but they are one kind of data. The Product ID is a unique number: Product Number one, Product Number two, Product Number three. It is a whole number, not a decimal, and it's a unique number—a way to identify a particular item that we're selling. So each row is all related to each other. It's one product. This product is Product Number one; it has a Title, it has a Price. So each row is what we call a record of a particular product. So everything in a row is related to each other, right? Different types of information, but for one product. Another product is another row. If we counted the number of rows, we would know how many products we have because each row is a product. Does that make sense? So columns are kinds of information. They're going to have many rows potentially, and one row is everything related to each other together.

When we put lots of tables together, the way that they relate to each other is called a Schema, and it defines the relationships between tables. Because we organize our tables into different tables, but they relate to each other. So this is the database that we're going to be working with a lot here. We have Users, and those users place orders on our website or application. So for a user, I want to know their name, their email. They have an account on our website or application, and they have a password. They live in a certain place. Now when somebody places an order, do they have to ship it to where they live? No, each order could be shipped to a different place. So there's a User Address, which is where the user lives, but every time they place an order we know who placed that order by their unique User ID. We know when that order was created, and we know where it's going to be shipped to, which might be the same as where they live but it might be different. Now, the order information here does not say what was ordered. That is stored in Line Items. If you think about an invoice or an order, can you place an order for multiple items? Yes. So each item is called a Line Item in this case. So what was the product, what was the price, what was the quantity, and what was the status? We don't have order status; we have Item Status, because when you place an order, do you have to return everything in the order, or can you just return one item? You can just return one item. So it's actually item by item that you want a status. One status might be backordered, another status might be shipped, another status might be returned—all on the same order. We don't track status by order; we track status by each individual Line Item. But in here we associate all those line items together by the Order ID. So Order ID is a unique way to identify a specific individual order. We also track the product that was purchased by the Product ID. So, for example, the Title of the product, the normal Sale Price, tags for finding products—those are stored in a Products table. So all of these tables kind of refer to each other, and together the relationships there are called Schema. And we're going to see more about that later. When we first get started we're just going to look at one table at a time, but later we'll see how we can start to combine tables and work with all the information across these tables. We are using DBeaver. So our Database Navigator over here on the left—this is where we browse and see what's in our database. And this is pretty much true of all Database Applications. Whether you're using DBeaver or not, there's going to be a place that is where you write your SQL code, and then when you run your SQL code there's going to be a place to view the results. If you look at other apps such as Microsoft's SQL Server Management Studio—surprise—it looks basically the same. There's a place where you browse your database, there's a place where you write your code, and a place where you view the results. If in your job you are using another application that's different from DBeaver, it's going to be basically the same. There's always a place to view your databases and your tables and things, a place to write your code, and a place to view the results. Even if they move the explorer to the right-hand side, it's still fundamentally the same thing. That's pretty much all Database Applications.