Getting Started & Your First SQL Query

All right, so I'm going to show you first how to do this. And then there's going to be an exercise for you to do this. And it's going to walk you through all the steps that I'm going to show.

Repetition is good for learning. So kind of seeing it and then doing it yourself helps to drill into your brain. So no need to follow along during this first demonstration here.

You're going to have a step-by-step exercise that's going to walk you through all of these steps. So here in DBeaver, I've got my database connection over here. Now it named my database connection, company underscore data.

Why did it name my connection that? Because that's the database that I told it to connect to. But technically, think of this database connection more as a server. So for example, if I right-clicked on this and I renamed it.

Now the exercise isn't going to make you rename it. You don't have to rename it, but I like to do this just to say, imagine this being the Noble Desktop server, just so we understand what we're seeing here. This is our server.

Our server can contain multiple databases. In that list of databases, company data is the one that we're primarily going to work with. In there, there are schemas or groupings of tables.

Schemas are a way to group and relate tables to each other. So I'm going to go into there and there's one called public, but there might be different groupings of tables. I just have one grouping of tables and here are the tables that are in there.

This is the data that I can work with. This is given to me by the company. I've got user data, users, place orders, which are made up of line items and they purchase products.

I've also got some employee data in here as well. Employees work in various departments. I know this because I've seen this so many times before, but if you're looking at this, you're like, what's this? What is in users? Well, I can open up users and I can see the columns, which are the kinds of information.

So I can see the names, the cities, the states. And notice there's a little one, two, three versus an A-to-Z. One, two, threes tell me that those are numbers. A-to-Z tells me that that is text and a little clock tells me that that's time.

So it kind of tells me the kind of information that is stored there. And if I were to click on the users table, double-click on it. Double-click opens up a tab here and I can look at the data that's actually in there and it will show me the actual data.

It'll show me the names, the emails, the encrypted passwords, which doesn't really help me to decrypt those. The users address, city, state and zip code. Notice that things like here, for example, with user states, notice how it says varchar.

So I can see that there are two. Varchar is short for variable length characters. Two says that there are two characters.

So I know when I look at this, that there are two characters. So these states are not New York written out, they're NY. It's not Vermont written out, it's VT.

So even looking at the kind of data here tells me what's here. These can have up to 255 characters. Right? Timestamps.

So these are time related information. So I know when these user accounts were created. How do I get this view over here? Yeah, when we want to open that up, we double click on the table and then initially, it's gonna be here in properties and there's a data tab.

So, I click on the data tab to actually see the data. Now, if I open up the users table here, I can also open up, let's say, the orders table. Each time I double click on the table, it'll open up a new tab for each table here.

So, here I can see the data that's in the orders table and here I can see the data that's in the users table. If you're done looking at a table, I can close these things up so they don't take up space over here on the right. It's perfectly fine to just get a sense of the data, but I might not need to actually see the actual data.

Maybe just looking at the columns is enough to give me an idea of what kind of data I am looking at. I'm looking at user IDs, order IDs, when the orders were placed, the names, addresses, cities, states, and zip codes where we shipped those orders. So, this is a way for me to first get to know the data here and start to see what I can actually query.

Now, when I'm actually ready to write a query and I want to maybe see this users table, not by double-clicking and using the interface here, but by actually writing code to do that because I have a lot more control with the code. I want to get a text file where I can write my text for writing my code, and that's the SQL editor. I can create a new SQL script, and this is where I can write my text over here to write my code, and this is a little bit small here.

So, I can use command plus and minus to make that bigger or smaller. If I'm on Windows, that would be CTRL MINUS (-) (-) or CTRL PLUS (+) (+) to make it bigger or smaller, and I can say, I want to select all the columns from—and notice that this table is called users. So, I'm gonna type in users and a semicolon.

So, I want to select all of those columns from that users table and I want to run this code. There's a play button here to execute this code, and down here, it runs that code and gives me the resulting data from this particular query. So, I just ran some SQL code.

Now, if I want to see the data that's in the orders table, I can replace this with orders, and now I can run this, and now I'm seeing the results from that. So, whenever you get that play button over here, that is going to run your code. What's unusual about SQL is that when you run code here, let's say you have two pieces of code.

What is it going to do? Is it going to run all of the code? Is it going to run just the code that you've clicked on? So, for example, if I click on the orders line here and I run that, this is the order information being shipped to. If I click on this query and I run that, it's only running one of these queries. It's actually only running the selected query.

Now, there's two ways I can select things. I could just click on it. That's good enough, or if I select all of the code, SQL executes only the selected query or queries.

See how this ran two different queries as two different tabs here. So, if I select all the code, it will run all the code. However, if I only click on one query, I'm only running that one query.

If I click on another query, I'm only running that one query. This is very unusual compared to most coding languages, where the entire file runs. In SQL, it's what you have selected, and what I like about DBeaver is it's smart enough to know that simply clicking on something selects it.

I don't actually have to highlight all of the code that's in there, but if I were to select all the code, I could run all of those queries, and I would get one tab for orders, one tab for the users, but normally, we don't run multiple queries. Normally, we just run one. So, we just click on that one and we just run that one query, or I click on another query and I just run that one query.

Now, I don't know if you noticed, but when I was typing this in, I was typing this in all caps, and see how it's switched to lowercase. That's weird. It's a weird default of DBeaver, and I like to change it because I like them to be uppercase queries.

In fact, if I started typing, I would like it to uppercase those queries for me. I'd like it to do that as a default preference. So, luckily, we can change our preferences, and I'm going to recommend you do that in the exercise.

So, this will all be in the exercise you're going to do, and we have in window, we have some preferences that we can set, and there's not too many preferences that I would set, but in the exercise, one of them is going to be to uppercase those keywords, and so that is under editors, and then SQL editor, and then formatting. So, here, keyword case is set to default. By default, it's lowercase.

That's just a weird default. Normally, in database documentation, it's always uppercase. So, personally, I would recommend uppercase.

Now, if you really like it in lowercase, you can keep the defaults, but personally, I would recommend doing uppercase. So, that way, from now on, it'll always uppercase those keywords for me. That is my personal preference, and I think that's what most people do, but that is your preference.

Technically, you can do whatever you want. So, that's one preference. So, now, if I hit apply and close, now in the future, as I'm typing, see now how it uppercases.

Now, I don't even need to uppercase it myself. See how they convert to uppercase? So, that preference is now set. You only have to set that once and then you never have to worry about it again.

Another preference is I'd like some line numbers over here because when I'm talking to other people, it's nice to refer to a line number over here. It also can be a nice way that I can click to select lines. So, I like to have line numbers, and also one other thing, if you have long things, I don't like to have to side scroll like this.

So, I like that text will wrap to the next line when we have really long lines. So, I like text wrap to be turned on. So, those are the only other two preferences that I like to set.

Again, all of these will be in the exercise, and this one is under text editors. So, if I just click on the word text editors, "show line numbers" is an option that's normally not turned on, but I can turn it on, and also "enable word wrap when opening an editor." So, in the future, from now on, every time I open a new editor, it'll turn word wrap on.

So, I don't have to scroll horizontally ever. It'll just wrap the long lines. So, I don't have to scroll horizontally.

Technically, it won't apply to this one, but from now on, every time I open up new editors in the future. So, from now on, when I have really long lines, it will wrap to the next line. So, I don't have to scroll side to side.

Also, notice now that I have line numbers. So, I can talk to other people and say, "Oh, hey, on line five, I've got such and such an issue, " and it makes it nicer to work with other people. All right, if I'm done with a file, let me start typing a query here.

Select everything from users. If I wanted to come back to this code later on, I could save my file. So, I could select Save As.

Not that I need to come back to this file, but if I wanted to, I could, and I could say, "Hey, let me put this into level one here, " which is where I'm working, and I could say, "This is Dan's first query." It's a.SQL file, which is just the file extension for SQL code. It's fundamentally just a plain text file because it just contains text.

In the future, if I ever want to open that back up again, I could just open it like I normally would. I could say "Open File, " browse to wherever I saved that file, and open it back up again. Notice it's 21 bytes.

There's no data in there other than just the text. I just get the text again so that I can run this, and that means I do need to have a connection to my database for that query to work.

So, these are just plain text files. It's just so you can save your code so you don't have to write it again in the future to save yourself time. But they're basically just plain text files.

Also, one last thing here before you guys do your exercise. Down here, notice there is a number here. Let's say that I look at my orders table here.

So, say I look at orders and I run this query here. So, notice it says "200+" over here. "200+" means how many rows? DBeaver does not retrieve all of the rows of a table.

Think about it. If you were on the Amazon website or the Amazon database and you query the orders table, how many billions of orders would there be? Would you want to wait for billions of orders to load? Would you want to download billions of orders to your computer? No, you'd only want a few to get a sample of the data to see a little bit of the information. So, by default, DBeaver loads 200 rows at a time.

So, it has more than 200 rows right now, but we're only seeing the first 200. Now, as I scroll down, because it only downloaded the first 200, I can see the first 200, and when I get closer to the 200th row, it'll load another 200 at that moment. So, when I get down to 200, it says, "Oh wait, I need to load another 200."

So, now I have loaded 400, but there are more. So, it loads in increments of 200. This is how it keeps DBeaver fast because if there were a million orders, it would take a long time to download a million orders, but it just downloads the first 200, and then it could download another 200.

Normally, you don't need to see them all to get an idea of the data. You're getting a sampling of the data. It's perfectly fine to just get a sense of the data, so you can get a sampling to start to know what kind of stuff you have.

All we're doing right now is getting a sampling of the data to try to understand what we can work with, and normally, your queries are not going to say, "Give me it all, " because you're trying to find something in the table, not the whole thing, right? Normally, you're going to filter it down. So, the whole class as we start to learn things is going to be: let's take all of the data—which we don't want to see all of—and let's start filtering it down piece by piece so that we get less and less, because we're trying to look for something in all of that vast data. We're not really trying to see it all at the same time.

Okay, it's just enough to get us started here. In the workbook, there is exercise 1B.

So, let's go through that exercise 1B. It'll walk you through browsing the database over here, creating your first little file over here, writing your code, setting those preferences, and this is that exercise 1B in the PDF workbook.