SELECT

[00:00:00]
>> Brian Holt: So, let's hop back into our class here, we're on basic SQL here and SELECT statements.
>> Brian Holt: So, I am a big fan of learning SQL by doing and not by sitting here and breaking the grammar down, which is how a bunch of other people choose to teach this.

[00:00:18]
Of like, you run this query and it breaks down into this grammar, which is then parse this way and run in this order. I just like learning by doing, so that's the way I'm gonna teach you. SQL has a very structured, I mean, it's literally in the name, way of parsing queries and running queries and things like that.

[00:00:39]
And I'm gonna say 99% of the time it really doesn't matter. The only time it really kind of matters is you have to do SELECT first, and then FROM here and WHERE here. The order of those clauses matters, but you kind of just get used to it. And for the most part, it's pretty intuitive, I will show you the one part where you kinda have to think about what order things happens in, otherwise it's just kinda intuitive.

[00:01:10]
I will say that for this course, I am gonna give you a little brief intro to SQL enough for you to just kinda be dangerous in SQL. But I don't go as much into depth in it, as I do in this course, the complete intro to SQL and Postgres, I go deeper in this one.

[00:01:25]
A lot of those concepts, in fact, almost all those concepts apply to SQLite as well. I wanna to be a lighter course, so we're talking a little bit less about it.
>> Brian Holt: And most of this is built around the idea of development, and not necessarily around the idea of being a database administrator, because I am a developer and I'm not a database administrator.

[00:01:48]
So I'm not gonna necessarily give you the best ops tips, but I am gonna give you the best tips I know around writing apps with SQL. The first thing I'll just mention is that SQL is a defined language. There's a standards body, and everyone kinda has to agree on how SQL works.

[00:02:09]
And that is then applied to Snowflake, and Oracle, and DB2, and SQLite, and Postgres, and MySQL. They all have to have to kind of adhere to this one standard, which was great about that, because this SELECT FROM Artist now works across basically any SQL database that you choose to write against, right?

[00:02:32]
If you learn a bunch of stuff about SQLite, and then you hop over to Postgres, things are gonna feel really familiar. In fact, almost all of it that I'm teaching you today will apply to Postgres, and the couple of times that it doesn't, I'll highlight, because it's interesting.

[00:02:46]
They do have some very key differences in terms of how they do things, but for the most part, you kinda get to treat them all as the same, which is pretty cool. The first thing we're gonna do is we're gonna learn about how to read from a database, which is probably most of the time what you're gonna be doing.

[00:03:04]
So if you have your database open here, let's go ahead and just write a query right away and say, SELECT * FROM Artist. None of this capitalization matters. You can write this as silly as you want to.
>> Brian Holt: This is hard for me to write.
>> Brian Holt: The one thing you'll find about SQLite, it does not care about capitalization at all in almost any case.

[00:03:42]
Yeah, that's a nice dad joke there for you [LAUGH]. I entertain myself sometimes. So let's talk about the query that we just wrote, so, SELECT * FROM Artist. You'll see that I tend to capitalize the grammar words. When I started writing code 15 years ago, well, actually, longer than that, professionally, 15 years ago.

[00:04:11]
This is what most people did all the time, it was like a way to indicate this part of the grammar does this. You'll see in a lot of tutorials, people still do it. You'll see that I mostly still do it because it is so ingrained in me. You don't have to, and I would say a lot of people don't these days, right?

[00:04:28]
You will see a lot of people just do SELECT * FROM Artist, or even just artist like this. And to be honest with you, when I'm just writing it for myself and not for anybody else, this is what I tend to do. So the SELECT part of this means that we are going to be reading from a database.

[00:04:47]
The star means give me back everything in table. FROM just tells what table, right? So, we can change to be select * from Genre, right, which is another table that we have in here, and you'll see that it gives me everything in the table.
>> Brian Holt: All right, so star, just as it means across many of much of computing, means everything.

[00:05:12]
When you're just there by yourself and you're querying, I do a lot of SELECT * just to see like what's in the table, how is the data structured, it kinda gives you a nice visualization. But when you're writing queries for your apps, be specific of what you want out of the tables.

[00:05:26]
You wanna do something like SELECT name FROM Artists, right? Notice this is just giving me the name of the artist. Notice I don't have the ID that I got when I did SELECT *.
>> Brian Holt: All right, numbers' here now, which I think they call ArtistId.
>> Brian Holt: Yep, and so you can see I'm getting just the number now.

[00:05:56]
And it does give it back in the order that you ask it for. So if you wanted to do name first and then ArtisId, you can see now it's in the other order. So the number comes second, and the artist comes first.
>> Brian Holt: So when you are writing code in SQL and any variant of SQL, be specific of the field that you're asking for.

[00:06:20]
It makes your code a lot more readable. And later you might alter your table and add more columns and you might not need those columns. It's always a good idea for the code that you're intending to live for a while to be specific about the things that you want back and not just a star.

[00:06:37]
If you need help with what's in a table, I do .schema Artist a lot. And then you can see here I have an ArtistId which is an integer. I have a name which is a text, right? And then we'll talk about constraints later, but it means that the ArtistId is unique for the table, right?

[00:06:55]
It's the primary key for the table. Honestly, this is probably the most useful thing you'll learn all day is just how to select something from a table cuz it's mostly what you do.
>> Brian Holt: So every time that I do that SELECT, I think I'm getting like 300 something artists or something like that because there's a lot in this table, right?

[00:07:16]
But you might imagine, what happens if you do SELECT posts FROM Facebook? You're gonna get a trillion rows back, I don't know, but a very much many large amount of rows, right, which you don't wanna do. Typically, you're looking for a row or a group of rows. So let's do that first.

[00:07:34]
Let's do SELECT name FROM Artist, and then we're gonna do a WHERE, which is basically, say, limit this down to a specific subsection. I'm going to do ArtistId =, and then just give it a number, I think it's 1 to 275, it looks like. I did 174, I don't remember what that is, The Postal Service, which not intentionally, but that is the hoodie I am wearing today [LAUGH].

[00:08:10]
>> Brian Holt: Okay, I think this makes sense, intuitively. You're saying, I want everything where this particular little clause is true, right? As you might imagine, if I wanted to say SELECT ArtistId FROM Artist WHERE name =, single quote, very important here, single quote. We'll talk about that in a second, The Postal Service.

[00:08:42]
>> Brian Holt: And you can see there I got the,
>> Brian Holt: 174 back. Now, if I actually remember correctly, I think that's actually a Postgres thing. Double quotes actually might work here. Nope, okay, I was right. It is important that you only use single quotes there. SQLite is really permissive, that's actually one of their core tendencies to be permissive and try and make sense out of anything that you give it, even if it's wrong, right?

[00:09:06]
This is wrong SQL, and I thought, I was like, they might handle it, but they don't. Single quotes in SQL, and this applies across databases, means string literal or an actual value was being put in there. Or you can just think of a string, right? Double quotes means a table-level value, right?

[00:09:29]
So this is actually going to look for a column called The Postal Service, which doesn't exist, right? I could actually come in here and I could put name in quotes, and this will work.
>> Brian Holt: Right, because that actually is the name of the table-level value, same thing here with Artists, right?

[00:09:48]
Artists can be put in double quotes, you don't need to because that's kinda nonsensical to put it there, but you could, right?
>> Speaker 2: How do you get SQL syntax highlighting in your terminal? Has anybody gotten that set up?
>> Brian Holt: I intentionally didn't get that set up because I expected most people to not have that.

[00:10:10]
Really, to be super honest with you, the way I do all of this stuff is I go into VS Code, I do, Change Language Mode to SQL, and then I do SELECT * FROM blah, and then I start writing in here. Because, as you'll see later in the course, our queries get quite long and then I'll copy and paste them in there.

[00:10:32]
It's hard to edit all this stuff on the terminal because I am not a Vim slash terminal purist, as you can see. So that's why I never bothered to set it up, is cuz I always write them in VS Code and then I move them over. There's even VS Code extensions here where you can just directly do all of this stuff in VS Code, which works really well.