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 var car.

So I can see that there are two. Var car 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? Uh 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 wanna 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 uh 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. So, this is a way to explore the actual data but I might not I might not need to actually see the actual data.

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

Now, when I'm actually ready to write a query and I wanna maybe see this users table not by double clicking and using the interface here but by actually writing code to do that cuz I have a lot more control with the code. I wanna 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 wanna 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 wanna 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 gonna run all of code? Is it gonna run just the code that you've clicked into? So, for example, if I click into the orders line here and I run that, this is the order information being shipped to. If I click into 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 into it. That's good enough or if I select all of the code, SQL runs what you select.

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

If I click into 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 DB is it's smart enough to know that just clicking into something is enough to select 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 the order, one tab for the users but normally, we don't run multiple queries. Normally, we just run one. So, we just click into that one and we just run that one query or I click into another query and I just run that one query.

Now, I don't know if you notice but when I was typing this in, I was typing this in in all caps and see how it's switched to lowercase. That's weird. It's a weird default of DB 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 gonna recommend you do that in the exercise.

So, this will all be in the exercise you're gonna 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 gonna be to uppercase those keywords and so that is under editors and then SQL editor and then formatting. So, here, keyword, the keyword case, default. By default, it's lowercase.

That's just a weird default. I've never seen like normally in in a database documentation, it's always an 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's also can be a nice way that I can click to select lines. So, I like to have a line number 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 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 so 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 side scroll ever. It'll just wrap the long lines. So, I don't have to side scroll.

Technically, we won't apply to this one but from now on, every time I open up new editors in the future. So, from now on, now, 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 how I have line numbers there. 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. Alright, if I'm done with a file here, so let me start typing in 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 say, 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 dot 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 wanna open that back up again, I could just open it like I normally would. I could say, open file, browse to wherever I save that file, and open it back up again. Notice it's 21 bytes.

There's no data in there other than just the text. So, it's not like these SQL files contain data. Uh I just get the text again so that I can run this and that means that 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 that 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, here, notice over here, it says 200 plus. 200 plus means how many rows? DBeaver does not retrieve all of the rows of a table.

Think about orders. 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 like billions of orders to load? Would you wanna download to your computer billions of orders? No, you'd only want a few to get a sample of the data to see a little bit of the information. So, DBeaver by default 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. So, that's nice to know that it doesn't retrieve all of the stuff.

So, it keeps your queries executing quickly. If you keep scrolling down, eventually, when it says 500, well, it loads in increments of 200. So, 200,200, and if there were 200, it would have loaded another 200 but there are only 500 orders here.

So, it loaded 200, then it loaded another 200, and then it loaded the final 100. So, this particular table only has 500 rows but it keeps the database fast so that we only load increments of 200. Perfectly fine just to 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 can we work with and normally, your queries are not gonna say, give me it all because you're trying to find something in the table, not the whole thing, right? Normally, you're gonna filter it down. So, the whole class as we start to learn things is all gonna be, let's take all of the data which we don't wanna see it all 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. So, it's just enough just to kinda get us started here. In the book, there's 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.