SQL Tables

[00:00:00]
>> Brian Holt: Tables, we're doing tables. So if you look at this, we say .tables in, I'm not in my data.db. And thank you for telling me it is .shell clear. And I do tables, you can see those are the tables that I have, but I don't have one that tracks band members.

[00:00:24]
Let's say that suddenly becomes very important to me for whatever reason. Let's go ahead and create a table, I'll show you how to create one. It's not particularly difficult. We're going to say create table, what it's called, and then opening parentheses, and I'm going to do an ID, I'm going to call it an integer, and I'm going to call it a primary key.

[00:00:56]
Okay, I'm going to do a name, text unique. We're insisting, for at least the purpose is this, which is certainly not accurate, that every band member's name must be unique, and we must insist that it is not null. And then we're going to give them a role if they're the guitarist or something like that text, and we'll call it
>> Brian Holt: Close the parentheses and semicolon, and now a have it called M member and i do a schema on bend member, and you can see it's all the stuff that I gave it.

[00:01:50]
>> Brian Holt: So you might be familiar with like Postgres and your ability to say,
>> Brian Holt: Like instead of being text, I can do like, far char,100 right? Then I'm limiting it to 100 characters, you can do this, that would pass SQLite, and want a limit, then you can put two gigabytes of data in there.

[00:02:16]
It just doesn't care. It's like, you want a limit, yeah, I don't care, right? And so that's one of the interesting things about sqlite is there's only four types. Integers, real, which is like a real number, right? So decimals and stuff like that floats, if you will. Text and blob, which is like a big blob, like an image or something like that, right?

[00:02:40]
It's a dynamically type system, so if you like throw numbers or texts and texts at numbers, it really does its best to try and accommodate that, right? If you try and insert a literal number, 100 into a text field, it's like, cool, you now have a text 100, right?

[00:02:56]
And it just kinda does its best to do backflips and make everything work. And again, you can give it like bar chart 255 where in Postgres, it would be cut off after 255 characters, it's just gonna be I don't care. I'm not tracking it, not my problem, your problem, right?

[00:03:13]
>> Brian Holt: All of those are technically valid SQL, right? Or SQL, and so it will respect those like you can give it a tiny int, which is like a small number, you can give it a big int. Those are all valid types in SQL, and it accepts them, and it just then it just was, like, cool, but it's an integer.

[00:03:30]
I don't care, right? So that's something to keep in mind. Some people love that, I think that did I put a link in here to it? I didn't, yeah, no, I did right there. I think, this is gets into like, what is so all of these convert to integers, all of these convert to texts.

[00:03:56]
Blob is a blob. All of these can convert to real numbers. And, these numeric ones, so, like, a good example is there, notice there's no Boolean, right? It just converts a Boolean to one and zero. So you can say, select where true, right? And it's under the hood, just like cool, true is one, and so it is stored as a one.

[00:04:16]
And so if you create a Boolean, feel and then you insert seven into it, which is definitely not Boolean, it's like, cool, here you go. Here's your seven, right? You know, seventh level of truth or whatever that is, it's really interesting. Some people, again, swear by I was looking for a, there's some part of the docs here where it says like, it's a feature, not a bug, right?

[00:04:40]
And they make a big case of why this, like, flexible typing is good. I like JavaScript and it's flexible typing because it helps me be productive. And I feel like this doesn't help me be productive, so I'm not actually like, this is one of the things about SQLite that I don't care as much for.

[00:04:56]
But, you know, I'm not the person that created a database that's in a trillion places. So maybe you don't listen to me. Listen to the guy that did that, so, that's up to you. Yep, we have a table, a band member, a table contains records, you can call them records, rows, whatever you wanna call them.

[00:05:11]
A record can be thought of as a row in a spreadsheet. And that's actually not too far from the truth. You can literally use Google Sheets as a database. And if you're doing something very small, why not? A kind of simple way to use it,
>> Brian Holt: I think this is literally an NPM package, yeah, this is an NPM package where you can use Google spreadsheet as, basically as a database.

[00:05:34]
Some of the ORMs, like, I don't know if drizzle will do it now, but some of the older ORMs, you can literally hook up to spreadsheets as well, which is kind of cool.
>> Brian Holt: Okay, so let's insert a record into our database. I'm just gonna copy and paste this since we already talked about insert into band member.

[00:06:00]
>> Brian Holt: So you insert into band member, name, role, values. I put Thom Yorke, because he's lead singer of Radiohead. And you can see there, he got an ID. It starts from 1, right? Thom Yorke, and he is a singer.
>> Brian Holt: Okay, and now we can select star from band member, pretty cool.

[00:06:35]
>> Brian Holt: So let's add a few more. Again, I'm going to copy and paste this. Because you don't wanna watch me type this, but it's exactly the same thing. Insert into band member name, roll, and then values. And then you just, as long as you come to separate them, you can put as many values in at once.

[00:06:52]
So this is gonna actually create four more rows and you can see here, these are all the ones that it created.
>> Brian Holt: Again, all members of Radiohead, if you were wondering. And now if I select star again, I have the five members of Radiohead. Okay, so let's alter our table, let's say that we wanted to add image to it.

[00:07:17]
So I'm gonna say alter table. Band member and I'm going to say add column.
>> Brian Holt: Image text.
>> Brian Holt: Done.
>> Brian Holt: As you might imagine, you can also drop column.
>> Brian Holt: That's you don't need that last part. You can just say no image, there you go, very easy to alter tables.

[00:07:50]
We didn't put anything in there, so I think that would just go ahead and create all of them, and it would just set them to null, right? They wouldn't have anything. So let's add a. Something that we're with a default value. So we're gonna say alter table band member.

[00:08:07]
We're gonna say add column, text not null, default, and we're just gonna assume that if you don't give us a nationality that you are from the UK, which seems like a definitely not problematic thing to assume. Right, and now if I do select star from radio from band member, what do you expect to say from nationality?

[00:08:31]
For all of the people that are already at the table, that they're from the UK, which in this case happens to be true because we only have Radiohead at the table, and they are all from the UK. I think so I actually didn't bother looking that up, but hopefully that I'm not wrong on that.

[00:08:48]
If so, I deeply apologize to the members of Radiohead. You can also insert think something in there, and if you didn't include it, so if I insert something else. I think it would assume it to be UK as well. Schema band member, yeah, and it would just all fall to UK.

[00:09:18]
>> Brian Holt: Cool, yeah, and if you null and you have things in the table already, you do have to give it a default. Otherwise it's gonna fail because those would be null.
>> Brian Holt: One thing that you cannot do in SQLite that you can do in just about every other database that I know of, is you cannot alter multiple tables at once or multiple columns at once.

[00:09:39]
So you have to have One distinct query for every alter table that you wanna do. In Postgres, if you wanna modify 10 columns at the same time, it just doesn't care. It's like, cool, it'll do all of them in one swoop. SQLite's like, nope, we only do one at a time.

[00:09:53]
So that is something to keep in mind.
>> Brian Holt: Okay, cool, let's say that we're done with band member. Just do drop table, band member, you are done.
>> Brian Holt: So go to work tomorrow and say drop table users and see what happens.
>> Speaker 2: Someone said you forgot to add the column name, but it still worked.

[00:10:26]
>> Brian Holt: It did, that's correct. Yeah, it probably assumed it to be right here at column, I just didn't even call it anything.
>> Brian Holt: Let's see what happened, let's Let's create the table again.
>> Brian Holt: Again, this is kinda like their permissive thing where they're like cool. We'll just assume that you have a null named column.

[00:11:00]
I didn't put in nationality, right? So let's delete that
>> Brian Holt: From band member, yeah, does it have anything in there? But if I do scheme a band member, yeah, see it just doesn't have anything there. I think that's what that column means is a nully named column. Probably not a great idea.

[00:11:31]
But again, this is going to ask you guys like, sure, you wanted that. It's valid SQL, so go for it.