Basics of Learning the SQL Language

So let's learn a little bit more about our SQL code. So basically the format of the rest of today is going to be that I'm going to go through some slides. Repetition is how you help drill code into your brain.

So I'm going to go through slides, showing some code, talk through it so we can try to focus on understanding the code. Then I'm going to live code, show you how some of this stuff works in more detail, kind of expand on it. And then afterwards, you're going to do an exercise where you can code it yourself.

So that's three times, three repetitions. That's how you drill it into your brain. Whenever you're learning a coding language, it takes time for you to be able to remember it off the top of your head.

It's repetition. Now, hey, if you're magical and you can just remember the first time everything and never have to have it repeated, that's wonderful. But most of us have to have it repeated multiple times for us to eventually remember it.

Some things are going to be easier for you to remember. Some things are going to take a little bit longer. Just know that with practice, eventually you'll drill it into your brain so that in the beginning, when you're first learning, you probably have to look it up and be like, oh, wait, what was this code? But eventually, you'll eventually be able to remember them just off the top of your head with enough practice.

So, all right. Columns. In the first example, we just use an asterisk.

And the asterisk meant all columns. And sometimes you want all columns, but sometimes you only want a few. So, knowing the table that we're working in, we can look at the column names and we can specify instead of the asterisk, we can say a list.

And in English, we use commas to separate the items in the list. So, you can say just one column or you can say multiple columns, say as many as you want. And the SELECT portion of this is saying which columns you want to select.

For some reason, when I first got started with SQL, because everybody called them SELECT statements, I was thinking SELECT was kind of the whole thing. But really, SELECT is just one portion. And the job of the SELECT, as opposed to the job of the FROM, the job of the SELECT is to specify which columns I'm selecting from some table.

So, SQL is this structured query language. The structure is that there's a certain order that we have to write things in. And so, unlike other languages where you can kind of mix and match things in kind of whatever order you want, SQL demands a certain order.

You put SELECT first, then you put FROM second. So, we have to think of these as different pieces. Each piece does their own job and we have to put them in the same order.

So, we always put SELECT first. We're selecting certain columns FROM a certain table. As long as your names of your columns do not have a space in them, then you could just put in the name of the column.

But there are, if you did have a space, if whoever named that was not very nice and put a space in the name, you'd have to put quotes around. Or in SQL Server, you could put square brackets around it. Personally, I don't know who would ever create a table with spaces in the column names.

That's not very nice to do to people. But normally, we put things like underscores, for example. We avoid dashes in the names because a dash implies subtraction.

Like this minus this. So, we avoid pluses and minuses and slashes for divisions. So, that's why we use underscores instead of dashes in our names.

Of course, you can't control the names. Whoever creates those names, whoever creates the database, they get to choose those names. But we just put the names as they're listed in the database.

Now, you might not want all of the rows. So, remember how I said, pay attention to rows versus columns? So, SELECT is saying which columns. But what if I don't want all the rows? This assumes I'm going to get all the rows.

So, there are ways that we can limit how many rows we get. And this is the first time we're seeing a difference of PostgreSQL versus SQL Server. In PostgreSQL, we add a LIMIT option at the end.

And we say to limit how many rows. So, we're limiting it to 10 rows. By default, it would just give you all the rows, right? Now, DBeaver only gives you 200 at a time to speed up your queries.

But we're saying maybe we only want the TOP 10 products. And in Microsoft, they actually call it TOP 10 instead of LIMIT 10. And which is very odd, they actually moved the location of the code.

So, instead of putting it at the end, they put it here. They put it into the SELECT, which is kind of weird to break up the SELECT and stick a TOP 10 inside of it. This is kind of more normal.

Like here you have a SELECT, here you have a FROM, here you have a LIMIT. These are three different features. But I guess because it read nicely, because SQL tries to be a language that's kind of like plain English, we're selecting the TOP 10 FROM products, right? They tried to read nicely.

And it does read nicely. I do agree with that. But in PostgreSQL, we're limiting it to 10 rows.

And we put that at the end. So, we couldn't do the TOP 10 if we're executing PostgreSQL like we are. So, we'll just ignore the SQL Server and we're just going to do the PostgreSQL.

Now, when we're limiting how many rows, if we're saying, show me 10 products, which 10 products, how are we sorting those products? So, for that, we need to be able to sort or order our columns. So, think about like if you're in Excel, if you've ever done Excel, you know how you can sort by column and it'll sort all your rows? Same kind of thing in SQL. When you just say to SELECT all the columns FROM products, generally speaking, unless you're familiar with a particular flavor of SQL, you don't necessarily know how they're ordered.

This can be different across different flavors of SQL. PostgreSQL normally orders things based on when they were updated, not based on when they were created. So, for example, if you update something, it'll go down to the bottom.

And so, the bottom-most rows will not necessarily be the later rows; they might be the last updated rows. So, don't just rely on some assumed sort order. If you want to sort your data, specify how you want to sort.

So, we have an ORDER BY. So, notice how there's a SELECT, there's a FROM, and there's now an ORDER BY. So, we're learning different parts of the queries and we can put these together to form bigger queries.

Think of them like building blocks. We're learning different parts that we can put together. And those parts do need to be put in a specific order.

That's the structure part of this query language. So, ORDER BY, and you specify a column because you're sorting on a particular column. So, if you're ordering by price, you're going to have it listed so that all your products are going to go from cheapest to most expensive.

Or, oh, what if you want it from most expensive to cheapest? How do you define which way it goes? For that, we need to be either doing ASCENDING or DESCENDING. There's only two ways you can sort, A-to-Z or Z-to-A, right? So, the default assumed sort order is ASCENDING. You can say ASC for ASCENDING, but you don't ever need to.

And the way that I remember this is, it's just kind of the normal way you do things. When you start doing the alphabet, do you start with Z? No, you start with A. So, the normal default is A, B, C, D. It starts with A and it goes to Z. When you start with numbering, you start counting. You start at one, one, two, three, four.

So, you start small and you get big. Dates, they started in olden times, right? And dates go bigger; they go later. So, you start with old and you go to new.

That's the default normal if you don't specify a sort order. But if you add DESCENDING here, which is part of the ORDER BY, you're ordering by a certain column in DESCENDING. That flips it.

Now, instead of going small to large, now you're going large to small. So, now you're going from 100 down to one. You're going from Z to A. You're going from today and you're reversing time.

You're going backwards, right? So, you only have two choices. It's ASCENDING or DESCENDING. ASCENDING is the default.

And if you don't like it, you add DESCENDING. So, one way to think about it is if you just add ORDER BY, if it's not in the correct sort order, you just flip it by adding DESCENDING. So, you have a 50-50 shot.

It works either normally or DESCENDING. So, if you don't like the default, just add DESCENDING and then you'll like the sort order. Now, as we start to add multiple things, there's a written order and there's an execution or processing order.

And behind the scenes, even though we write things in a certain way, behind the scenes, it's going to execute them in a different order. And this might not seem like an important thing now, but as time goes on, we're going to start to see that this is actually important for understanding how SQL works. So, the way that we write our query is to say which columns we want to SELECT FROM some sort of table.

And then we ORDER BY a certain column. But if you think about it, how do you know that that column exists? Don't you have to go to the table first and then look up to see if a column exists? Like in DBeaver, for example, we go into a table, we go to the table first, and then we see if a column exists in the table. So, you have to start at the table level and then go into the columns.

So, behind the scenes, SQL is going to execute the FROM first, then it's going to execute the SELECT, and then it's going to execute the ORDER BY. Now, again, at this point, you're probably thinking, well, Dan, why do I care? Does it really matter? I have to write it in a certain way. Do I care about how it works on the processing side? It does.

And let me give you one small example. So, let's say I create a new file here for me to start writing some code. If I don't tell it what table I'm in, how does it know which columns to suggest? So, if I'm going to say I want the, let's say I'm in employees.

And I've got the salary. If I start typing in salary, notice how it doesn't know to suggest salary as a name, because it doesn't know what table I'm in. So, yeah, I could just write the whole thing out here, but I'd much rather use my code hinting.

So, if I say SELECT, and maybe I temporarily put an asterisk, and then I come in and I use employees. See how employees is suggested to me? This is called code hinting. To use that code hinting, I can hit TAB.

Now, I know it types in an extra letter there. We'll talk about that more later. For right now, don't worry about it.

I was going to delete it because I don't need it. If I come back in now and I type in salary, oh, look, see how it's code-hinting salary? Why can it code-hint that now and it couldn't code-hint it before? Because it didn't know what table we were in, right? So, if you just start typing and I start typing salary, see how it can't code-hint salary? Because it doesn't know what table you're in because I haven't told it what table I'm in. And what it doesn't want to say is, oh, you can choose salary from the users table, right? Do I have salary in the users table? No, so it does not want to code-hint a column for a table that doesn't contain it.

So, they're like, you need to tell me the table first. I don't know why they chose to put SELECT before FROM when they created SQL. I think it would have made more sense to say FROM employees SELECT salary, because that's the way that the code actually runs.

Maybe they know something better than I do. Maybe there was a reason why they did that, but it's not actually like, I wish they would have reversed them. Now, people say, well, can I reverse it? No, sorry, you can't, because this is a structured query language.

We must write it in the order that they've defined. So, that's what we have to remember is not only what code do we do, but what order do we do it in? Because that's not going to run. Yes, it's the right pieces, but they're not put into the correct order.

They must always be put into the correct order. Okay, so that's just one little kind of clue that there is a difference between the written order and the execution or processing order. And we'll see more of that as we go through the various days, but just kind of keep that in the back of your mind.

Now, what if you have two rows that are the same? You have duplicate rows. You might not want all of those duplicate rows. For example, the prices that my products are sold for.

Yeah, there's like kind of standard prices for things like 199,299,399. What if when I do a list of all the prices, imagine how many products are going to share the same price. There's going to be many products that are all 199 or many products that are all going to be 299.

I might want to just get a list of unique price points because I might want to see what kind of prices do I currently sell products for because I want to decide out of those which ones I want to list my new price at to kind of match one of my current prices. If you say DISTINCT, DISTINCT eliminates duplicate rows. If there were multiple rows of the same 199,199,199, you're just going to see 199 once.

You're not going to see it multiple times. So DISTINCT simply eliminates duplicate rows. Notice how I've emphasized rows, right? We're looking at multiple rows.

So whenever we're doing features, they are targeted either columns or rows.