Jose: Hi Guys, and welcome back.
In this video, we're going to learn
about the CREATE TABLE command
that we've already seen in the SQLite Viewer.
We're going to explore what it means
and how we can code our own CREATE TABLE commands.
So the CREATE TABLE command is pretty straightforward,
all things considered.
We've got CREATE TABLE accounts in this case,
and that tells Postgres that we want to create a new table,
and the new table name should be accounts.
Then we give Postgres
the columns between brackets.
We give each column name
and the type of data that we want to store in that column.
So for example, TEXT or INTEGER.
Notice that the column name and data types
are separated by commas.
So here we're creating two columns
with two distinct data types.
In this case, they're the same data type,
but they could be different.
Finally, we've got this semicolon at the end
to signal the end of the query.
This is quite important,
and most queries are going to have a semicolon at the end.
There are other types of data
and every RDBMS has different types of data,
although they're often quite similar.
And a lot of the different data types
that an RDBMS can have,
sometimes are quite similar as well.
So you have to sort of be careful with which one you pick.
But SQLite is pretty straightforward.
There's only five major types of data in SQLite.
There's the INTEGER for numbers,
There's TEXt for text.
There's BLOB for binary data,
or data that doesn't really have any structure.
There's REAL and NUMERIC for other types of number as well.
Now in CREATE TABLE,
we've also got a clause that's pretty important,
the IF NOT EXISTS clause.
So if you're trying to create a table,
but it already exists,
you're always gonna get an error.
But if we use the IF NOT EXISTS clause,
then we won't get an error.
So this is what that looks like.
CREATE TABLE IF NOT EXISTS,
then the table name and then the columns.
So you can see that this is starting
to not look like English at all.
Remember at the start of this section
we said that SQL looks a bit like English,
I wouldn't really say IF NOT EXISTS in normal conversation.
But nonetheless, this is how the command is written.
All right, let's go over to the editor
and look at writing this code ourselves in there.
So here we are in the data.db file,
and I've deleted all the tables and everything else.
So we're going to go ahead and execute some SQL,
and we're going to type away our CREATE TABLE command.
This is just to show you how it works,
how you can do this in your own data viewer.
And so the CREATE TABLE commands always start
with CREATE TABLE.
Now, once we've got that,
we type the table name,
which is gonna be users in this case,
and then between a pair of brackets,
we're going to type the columns of the table.
So here we're gonna have first underscore name,
and then the data type which is TEXT,
and then surname and the datatype,
which is TEXT.
Remember there's a comma separating the columns.
So here we've got two distinct columns.
Alright, now we can run this query.
And we should see that the result
is that the query executed successfully.
Now, because it executed successfully,
we have a table created.
If we go over to database structure,
we will see that the table now exists.
Let's go back to execute SQL and try to run this again.
And now you'll see that we get an error.
It says, "Result: table users already exists."
So the error is pretty nice.
And it tells you where the error happened
or the start of the query that caused this error to happen.
So it says the CREATE TABLE users is the start of the error.
Again, as we saw in the presentation,
we can say CREATE TABLE IF NOT EXISTS.
And then we can run this and there'll be no error.
You can see that the query now executed successfully.
And it doesn't tell us that it didn't do anything.
It just said that it worked.
What you asked for happened,
which is that we created the table if it didn't exist,
and it just so happened that he did exist in this occasion.
All right, that is how you can run these commands here
on DB Browser for SQLite.
And remember to keep practising these commands,
and don't forget them so that later on
when you do need to use them in a proper application,
you'll remember how to do that.
Thanks for joining me.
I'll see you in the next video.