Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
This code-along will have us using ChatGPT to quickly generate SQL commands from natural language. We’ll use the terminal and sqlite3 to demonstrate and test this capability.
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
[MUSIC]
0:00
Hi, I'm Rachel,
a Python teacher here at treehouse.
0:09
I love databases and
working with them using SQL.
0:13
But sometimes it's a little challenging
to remember the correct SQL command,
0:17
especially if your database is huge, and
all the tables have multiple columns.
0:22
In this video, I'm excited to share with
you how I've been using ChatGPT to help
0:26
me generate SQL commands to
interact with my databases.
0:31
We've got an exciting task
ahead of us in this video,
0:35
building a database to manage
the pets of treehouse.
0:38
We'll start off with
a pretty simple table, and
0:42
then we'll query some data,
change some data, and
0:45
we'll even split our data into multiple
tables for a better organization.
0:48
Okay, so I've got ChatGPT open to
the left, and terminal open to the right.
0:53
I'll be using terminal for
this workshop, but
0:58
please feel free to use whatever terminal
equivalent you're most comfortable with.
1:01
I'm also going to be using SQLite3
because it's one of the more accessible
1:06
database engines and it's perfect for
something small like this.
1:10
You probably already
have SQLite installed.
1:14
You can check this by going to your
terminal and typing SQLite3 and
1:17
pressing Enter.
1:22
If your screen looks like mine,
then you're ready to go.
1:24
Otherwise, check the teacher's notes for
some installation instructions.
1:27
Alright, let's get straight
to creating the table.
1:30
So let's tell ChatGPT we're creating
a table called pets using SQLite3.
1:33
It should have the columns name,
color, breed,
1:40
species, and owner name or just the owner.
1:46
Okay, let's see what it comes up with.
1:51
All right, perfect.
1:54
So it gives us the command and
1:55
it also explains things a little
bit if you really want to read it.
1:57
So, let's pop that right in.
2:01
Okay, nothing happens.
2:03
That's fine, no errors happen.
2:05
That's the important thing.
2:06
Okay, so let's start putting in our pets.
2:08
So we'll start with mine
because I'm absolutely biased,
2:10
so Rubik is a tabby colored
domestic shorthair cat.
2:16
I also have Pickle, who is the same
except his color is tuxedo tabby.
2:21
Okay, let's see.
2:29
Okay, so it's given us the commands and
because I never provided my name,
2:34
it said, your name.
2:39
We'll go with that anyway, and
then we'll ask it to change it.
2:41
So, let's insert everything there.
2:44
We'll have a quick look.
2:46
And it's got Rubik, the correct colors,
breed, species, and then my name.
2:49
So, let's get that changed.
2:55
Oops, I forgot to put in my name,
2:56
command for changing the name to Rachel.
3:00
Okay, so it says to update pets and
3:09
set the owner to Rachel where
the name is Rubik or Pickle.
3:11
So, I know this will work.
3:17
But I also know that because I'm
the only owner in the database,
3:17
we can actually just do this section.
3:21
I think, let's find out.
3:24
So no errors, which is great,
we'll check it out.
3:25
And yes, it's changed your name to Rachel.
3:29
So, here's an example of maybe just
not taking the full SQL command if
3:32
we do have an understanding of what
it's giving us, and what it's doing.
3:37
We don't have to take everything
that it gives us at face value,
3:42
we can can use some discernment.
3:47
Now let's move on to Laura's pets.
3:50
Now, she's got a lot of pets.
3:51
So I'm just gonna copy and
paste what she gave me,
3:52
like the message that she sent me.
3:55
So I'll just grab all of that.
3:58
So, the cats and then the dog.
4:00
So, Laura's pets.
4:03
Okay, so it's giving us
4:08
everything here, fantastic.
4:12
Let's pop that into the pets table, and
4:17
then press up,
I should have just typed it.
4:21
Everyone, is that fantastic?
4:25
Okay, so now that we know that it can
take an entire paragraph of data and
4:27
then extrapolate that data
into separate little commands,
4:32
let's give it the rest of our pets
in one fell swoop, one big message.
4:36
So, let's do Travis's dog is gunner,
is a pitbull, and white.
4:41
Jesse has two Bengal cats.
4:48
Hawtin is brown, and Cajmere is choco.
4:51
Kari's got a beta fish called Jaques,
4:56
he is a black crown tail.
5:02
And finally, Dustin's cat Link
looks exactly like Pickle.
5:07
Not even kidding, they could be twins.
5:13
So we'll see if ChatGPT can
extrapolate what we've given
5:15
it before about Pickle, and
then populate Link's data.
5:19
Let's see what it does.
5:24
Okay, so there's Gunner, Hawtin, Cajmere,
5:27
Jaques, that's interesting.
5:31
That's cool.
5:39
Okay, so, what it's done for
Link is that it's selecting the name,
5:40
color, breed, species, of Pickle.
5:47
So this is not quite right because
the owner is no longer your name,
5:51
it's changed to Rachel.
5:54
And it's also taking Pickle's name,
[LAUGH] So that's probably not gonna work.
5:56
But everything else does look quite good.
6:00
So we'll grab Gunner, Hawtin,
Cajmere, and Jacques, and
6:02
pop them into the database, and
we'll deal with Link in a second.
6:06
From pets, yep, everyone is there.
6:10
Okay, so, let's have a look.
6:12
So replace your name, obviously.
6:16
The statement selects the information
of Dustin's cat link,
6:18
which looks like Pickle, and inserts.
6:23
Okay, so
it thinks that Dustin also has Pickle.
6:25
I think, [LAUGH] So let's give
it a bit more data to work with.
6:29
Dustin's cat is named Link.
6:33
He just looks like Pickle.
6:37
Let's see.
6:43
Okay.
6:44
Dear, okay,
[LAUGH] This is assuming that we've
6:47
already run the command that
ChatGPT gave us last time.
6:52
Link isn't in the database yet, we just
need a fresh insert command for him.
6:58
Let's try that.
7:06
Dear, [LAUGH] Okay, so it's still
not really taking that into account.
7:09
So what we're gonna do instead,
instead of fighting it,
7:12
we're just going to grab,
come up here and grab Pickle's data.
7:18
I'll pop it here coz we can't
unedit in terminal quit well.
7:24
So we'll just pop it in here.
7:28
We'll change that to Link and
we'll change that to Dustin.
7:29
[LAUGH] It's no point fighting.
7:32
Okay, let's check it out.
7:36
Select all from pets, and there we go.
7:38
Link looks just like Pickle,
except he's called Link.
7:41
[LAUGH] Already, okay?
7:45
So now, if you know much about databases,
you're probably thinking at this point we
7:47
should have more than one table
to store all of this data.
7:52
And you're absolutely correct.
7:55
I agree with you.
7:57
So what we're gonna do is we're going to
split the owners into their own table.
7:57
And this will be great later
if we flesh out the owners'
8:02
table with contact details or
preferred vet or suburb.
8:07
But for
now we're just gonna split the name off,
8:11
because it's a very database
structure type thing to do.
8:14
So let's ask ChatGPT to help us with that.
8:17
So, let's separate the owner into its
own table and link it back with an ID.
8:19
Now, something that I prefer not to
use in this section is foreign keys.
8:28
So, in my experience with ChatGPT, working
with SQL commands, it's not the best with
8:34
foreign keys and starting new tables and
closing all tables.
8:39
So, I'm just gonna specify right now,
don't use foreign foreign keys.
8:43
Let's see what it comes up with Okay,
that's pretty cool.
8:49
Yep, it is really explaining everything,
which is great.
8:58
Okay, we'll scroll up
here while it does that.
9:05
So, what are we looking at?
9:07
We're creating a new owner's table,
which is good.
9:09
It's got a primary key and
text, that's correct.
9:11
And then, it's getting us to
create a second pets table.
9:14
This may be a problem because
we already have a pets table.
9:17
And I don't think it realizes that.
9:21
So what we're gonna do is we're gonna
drop the pets table first before we
9:23
create this one.
9:27
Otherwise, there's gonna be some issues.
9:27
However, we got to make sure that
we don't lose any of our data.
9:30
So, I'll scroll down here to
this section where we re-insert
9:33
everyone's data, and
just make sure everyone is here.
9:38
Look, Link is here.
9:42
Now, how did you not give me
the [LAUGH] right thing before?
9:43
So let's just make sure everyone's here.
9:47
Looks like it, okay.
9:50
So all the data is here ready to go,
so let's safely come back here and
9:51
we'll start with creating
the owners table.
9:57
And then now before we create this pets
table, let's drop the one that we have.
10:03
And the reason for
10:06
this is because a SQLite3 can't
actually just delete a column.
10:07
And later on,
you can't actually just delete a column.
10:10
And later on, we would need
to delete the owner's column.
10:12
Because there's no need for
10:15
the owner's column if we have an owner's
ID column that links to another table.
10:16
So let's just drop table pets,
and that should be gone.
10:20
And then we can recreate it
with the owner ID column.
10:24
Perfect, no errors so
far, so that's great.
10:28
And now we've got this nice big chunk of
columns we can just copy and paste in.
10:31
Let's have a look.
10:37
So select all from pets,
we have everyone here.
10:38
And then select all from owners, perfect.
10:43
Okay, so now that we've got both
tables sort of showing in our SQLite,
10:49
let's ask ChatGPT to show us what
it should look like right now.
10:53
So what should my database
look like right now?
10:57
Okay, so we've got Rachel, Laura, Travis.
11:04
Yeah, and everyone is there.
11:08
So that's a good sign, because ChatGPT's
database looks just like ours.
11:13
So we are on the right track.
11:19
So now once he's done typing,
let's start querying the table.
11:22
And querying is great.
11:26
We've been doing it so
far with the select all from owners, but
11:27
that's a very simple query.
11:31
So let's try and
do something a bit more not that simple.
11:32
[LAUGH] So let's have a look.
11:36
Let's grab all the cats.
11:40
Command to grab all the cats.
11:42
Okay, great, super simple query.
11:48
Let's check it out.
11:51
Okay, so we've got all the cats,
we've got a lot of cats, but
11:53
we don't see the owner's name.
11:56
So, let's ask for that as well.
11:57
So, that command for
all the cats plus owners.
12:00
If you've worked with databases before,
12:04
you know that this involves some sort of
join command, which can get quite long.
12:07
I don't really wanna think about it right
now so let's get ChatGPT to help us.
12:13
Okay, so as I said, there is a join
command where species is cat.
12:18
So where this number is, the owner ID,
should be replaced with the owner name.
12:23
So, we'll give that a go.
12:29
Perfect, we've got everyone this time.
12:31
Now, I just noticed that we're missing
our headers, so let's turn them on.
12:33
Headers on, and we'll try that again.
12:39
Can't just up these, we're gonna
paste the whole thing, fantastic.
12:41
Why do we have the owner ID as well?
12:45
That's okay.
12:48
[LAUGH] Let's ask it.
12:49
Can we not include the owner ID,
we don't need to see that.
12:52
Give that a go.
13:03
All right.
We filtered out the owner ID coz we really
13:06
don't need the owner ID and the owner
name in the same sort of return method.
13:09
Okay, great.
13:14
So, let's give it something that's
a little bit more advanced,
13:15
not super advanced,
a little bit more advanced.
13:18
So you'll notice that we have tabbies and
tuxedo tabbies.
13:21
Now if you don't know what tuxedo cats
are, it's just that original color,
13:22
which is tabby plus
enough white on the body.
13:27
And that's what makes it a tuxedo.
13:30
So, let's grab all the cats
that have at least tabby,
13:33
like a bit of tabby in their coat.
13:36
So we would include the tabbies and
the tuxedo tabbies.
13:39
Grab all the cats and the owners if they
13:42
are tabby, including the tuxedos.
13:48
So how do you spell tuxedos?
13:53
Okay, let's see what it says.
13:57
Okay, so it's using a like method,
or it's like tuxedo.
14:01
Okay, cool.
14:07
Let's have a look.
14:09
Okay, so, fantastic.
14:15
So it looks like it's grabbed the tabby
and the tuxedo tabby, and a tuxedo grey.
14:17
So Toph is a tuxedo as well.
14:23
Now I only wanted to grab
the ones with tabbies.
14:24
Any ones that are tabbies?
14:31
Okay, so it should just have
the like tabby one, yes.
14:40
Fantastic, so it should just be Pickle,
Rubik, and Link.
14:47
Because they're the only ones that
have tabbies in their coloring.
14:49
Okay, so let's move on.
14:54
So I just realized that Gunner
is actually not a pitbull,
14:55
Gunner is a Queensland heeler.
14:58
So, lets ask ChatGPT for modification.
15:00
Gunner isn't a pitbull,
he's a Queensland Heeler.
15:04
All right.
15:14
So, it says to update pets,
15:15
set the breed to Queensland Heeler
where the name is Gunner.
15:16
That should absolutely work.
15:19
Let's check it out.
15:21
From pets.
15:22
And Gunner is now Queensland Heeler,
fantastic.
15:25
Let's move on.
15:27
So, let's have a look at what
the database should look like now.
15:29
Since we've got it here,
let's just check the pets table.
15:33
Because we haven't really done
much with the owners' table.
15:37
Let's have a look.
15:44
Yep, everyone is there.
15:47
The IDs are definitely matching up,
and Hawtin, Cajmere,
15:50
Jaques, and Link, fantastic.
15:54
Okay, so let's add a few
more pets into the database.
15:56
I'm currently fostering two rabbits,
so let's put them in.
16:00
I'm fostering two rabbits.
16:04
Snowball is a white Angora, and
Charcoal is a black Netherland Dwarf.
16:09
Okay Perfect.
16:18
Let's see.
16:23
And still my name isn't in here.
16:23
[LAUGH] Okay.
16:25
And it does say make sure you
insert your own owner ID.
16:28
So let's just do that ourselves,
we don't need to ask them.
16:33
Okay, let's pop them in,
let's check them out.
16:38
Perfect, there they are.
16:42
They are rabbit, and then Angora.
16:44
Awesome.
16:45
Now, because I am fostering them, the idea
is that they do get adopted one day.
16:46
So let's say tomorrow they get adopted.
16:52
Hurray!
the rabbits got adopted.
16:55
Can we remove them from the database?
16:58
And, pretty good.
17:05
This is pretty specific.
17:08
It's selecting Snowball and
the breed has to be Angora, and
17:11
it's selecting Charcoal and
the breed has to be Netherlands Dwarf.
17:15
So this is actually pretty good in
a bigger more filled out database.
17:18
You probably want to be
a bit more specific, so
17:24
you don't accidentally deleting
all the Snowballs in the database.
17:27
If it were me I would have just deleted
the rabbits because there are only two
17:33
rabbits in the database.
17:36
But ChatGBT thinks like a computer,
and that's why we use it.
17:37
So let's delete our two rabbits,
and have a look at the pets now.
17:41
And they should be gone.
17:46
Now, just to finish up,
17:48
let's have a look at what our full
database looks like, and then compare it.
17:50
So, what does my database look like now?
17:56
Okay, our pets table should have Rubik,
Pickles, Katara.
18:05
Yes, we've seen all this before, great.
18:08
And yes,
it mentions that the rabbits are gone,
18:13
what about the owners table?
18:20
[LAUGH] I didn't ask for
the database, right?
18:24
Yes, I did.
18:27
That's okay.
18:27
And it says it didn't show us the owners
coz the owners haven't changed.
18:30
That is fair.
18:34
Okay, so everything does match up.
18:35
Pretty cool.
18:37
As you can see, ChatGPT was a fantastic
help in our journey to creating
18:39
a database, and then adding and reading
and modifying and deleting data from it.
18:43
There were a few instances where we
had to double-check the commands, but
18:50
we should always be doing that,
no matter where we source our help from.
18:54
So, this video was just a little taster
of how you can get ChatGPT to help you
18:57
with your SQL commands.
19:02
I encourage you to play
around with it more and
19:04
see what kind of support you can get for
your daily workflow.
19:07
So thanks for joining us in this video,
and we'll see you next time.
19:10
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up