So we made it through all of that crud.
It's sort of a joke.
Anyways, we're now talking about string functions, so basically we've seen how to interact with our
database, create stuff, insert it, update, delete all of that.
But now we're going to focus more on select or reading the data out, because what we've been doing
right now is just reading the data directly as it is.
So I could say I select breed and name from cats and that's it.
I get it back and that's great.
But there are things that I could do with that data.
So with numbers I might want to add them together or I might want to, I don't know, reverse a string
or replace all spaces with a certain character or conjoin first name and last name or conjoin.
Two pieces of data together.
All of these are different operations, different ways of printing our data out that we can do.
And so we're going to start focusing on them in this section.
We're talking about string functions.
So there's going to be a bunch of things that we see.
I already mentioned some of them.
We can concatenate words, we can take certain parts of a string and leave other parts out.
We can reverse, we can uppercase, lowercase, replace characters, all sorts of things.
But before we get there, the first thing I want to do is show you something that I think is pretty
useful that we'll use pretty often from here on out.
So up until this point, all of the SQL that we've been writing has been manually entered into the command
line into the CLI here.
So if I want to create a table, I have to type create table and then the name of the table cat.
But let's say that I mess it up.
So let's say I typed singular cat, which technically isn't a problem per se, but it's a good idea
to have things pluralist it won't break.
But so let's say I don't notice for a little bit though so I create a table cat and we want name is
bar char 100 age is ten what am I doing ages int just like that.
Let's say we leave it at that.
Now this isn't a great example because this is a short line, but if I messed it up, I have to go all
the way back here and edit it.
Right.
And do cats like this?
But let's now suppose that I have something even longer and I want to put it on separate lines and format
it nicely like this great table cats.
And then we have parentheses there and we'll do name is bar chart and let's say I misspell this like
that and age is an int.
And then I realized, oh shoot, I totally messed up.
I don't have a way of getting back there.
I don't have an easy way of getting here.
So I have to retype the whole line or copy it.
There's no simple way of editing it.
So what I want to show you is that we can run code from a file and that's really, really useful running
SQL files that we're going to talk about here.
So basically by the end, we will be working with files that look like this.
They're nicely formatted, they have nice syntax highlighting, we can edit them, make changes and
so on, and run them when we're ready rather than typing things manually into the command line.
That doesn't mean that we won't be typing things here because it's a very useful tool.
Oh, go away.
Sorry.
It's a very useful tool.
I should probably edit that out.
Well, anyways, it's a useful tool that allows us to explore things, try things out.
If they're short lines, it's great.
But if we're really trying to do more serious stuff, it's much easier and better to work in a file.
You can make edits, you can share it with people.
So that's what we're going to do.
So let's go ahead, let me get out of this and I'll just hit enter and get an error and that's fine.
So right now I'm in Kat's database, and if I do show tables, we don't Oh, I do have a cats table,
but if I look at it, it's empty.
So what I'm actually going to do is drop that cat's table, drop table cat.
So what I want to do is remake the cat's table.
And what I could do is what I just did here, create table cat and do the whole thing.
But rather than that, I'm going to do it in a file.
And then all I'll have to do here is run that file, execute it with one short line.
So up here I have a new file again.
You can do file, new file.
You can use the shortcut shown here.
You can right click and do new file as well.
Whatever we do though, we need a new file and I'm just going to put something in there.
So create table and I am showing you creating a table, but we'll be putting any SQL query in here,
creating a table, dropping tables, inserting data, selecting any sort of query we want.
So create table cats and it's so easy now to format it nicely.
We'll have name, which is var cha 100.
Let's do an ID as well.
See how easy that was.
If I made a mistake, cat id will be an int.
Not no auto increment comma.
Name var chart and then we'll do.
Age is an int and we'll do primary key is cat id.
So this should all be review.
But what's nice is I have this query like this.
I need to save the file.
So I did command s, but you can also go file save and we need to give it a name.
Any name will do.
I'll just call it first file.
And this is the most important part, SQL.
So that's what identifies the contents of the file as SQL code.
So we need that click save.
Make sure it changed up there.
Also, you can see we got nice syntax highlighting now, which is also very useful.
So I'm sure by now you can see the value in doing this.
You have a history of your work, you can edit it, share it with people.
It's just a much better way of doing it.
So how do we actually run it?
Well, this is the magic source and then a file name.
So in our case, our file name is first file SQL.
So we just need source file name.
SQL.
So I'm going to actually quit out of the class.
And the first thing I want to show you is that it matters where you are when you initialize your CLI.
So when I type LZ here and if you're not familiar with that, it just list the current files.
So what this shows me is that I have book data, first file and problems.
All right, so we want first file.
Don't worry about these other two.
So we are in the correct directory to access that file.
So if I start up the CLI with my SQL Ctrl space cli.
And I use the cat app database just like that.
I'll make some space.
Now, all I have to do is source first file dot, SQL, just like that.
And we'll see what happens.
It says Query OC.
So now how do we know what works?
Well, we could do a describe cat and here's our table.
So that's all that there is to actually executing the file.
It's just source and the file name.
But what I want to show you two things.
I'll go ahead and make a new directory.
You don't have to do this, but I'm just going to call it testing.
And then instead of testing, I'll make a new file.
And what I'll put inside of that file is a select statement select star from Cat.
Actually, let's do an insert insert into cats and we'll just do what do we have here?
Name and age, which I'm just realizing and capitalized.
So we could obviously go back and change that here and rerun it, but we'll leave it.
So insert into cat's name and age just like that.
Values and name will be, let's do Charlie and age will be let's do an old cat 17.
Save the file.
And we'll do file.
Save.
And make sure the file has SQL in it.
So we'll do rename and I'll just call this insert dot SQL.
Let's actually just to show you, we can do more than one thing at a time.
Let's insert both or let's insert two and this one will be called.
I think we already have a Cyndi.
Let's do a Connie who is ten.
And then at the end we'll also do select star from Cats just like that.
So these three things will be executed at once if we do it correctly.
So now to reference this file, our CLI is running in this folder.
So we can't just say insert SQL.
If we did that source insert SQL, it tells us it can't find that file.
So what we need to do is do a source testing slash, insert SQL, and there we go.
So that's important that you have to access the correct path.
You have to refer to this file by its full path to get there.
So if you're in this folder, all that we can access are these.
But if we drill to this folder, then we have access.
And obviously you could extrapolate that to as many layers as you need.
So now let's make sure it worked.
You can see after it worked, we had query.
Okay.
One row affected.
That was our first insert.
And then the same thing, our second insert.
And then our select statement gave us this right here.
So that's all there is to run in code from a file.
It's really, really useful.
We'll be doing it pretty much non-stop.
And the first thing that we're going to do is actually get some new data into our database using this
in the next video.