# CREATE TABLE: new tables with SQL
Run through and play with the interactive database for this lecture to learn about this better!
In the last chapter we learned how to create a table using DB Browser. Now we'll learn how to create another table using only SQL with SQLite.
We will be creating an accounts table, which will have two fields:
number. Both will be text fields.
Why is the account number, the
number field, a text field?
You should use numeric field types, such as
INTEGER, for data on which you may perform mathematical operations. An account number is not likely to be one such datum, so we'll keep it as text.
First, open the "Execute SQL" tab in DB Browser.
Then, we'll type out our command:
CREATE TABLE accounts (name TEXT, number TEXT);
There are three parts to this command:
CREATE TABLE accountstells SQL we want to create a new table, and its name name will be
(name TEXT, number TEXT)tells SQL we want to create two columns in the table. Inside brackets and separated by commas we can add many columns following this format:
column_name column_type. So here the
namecolumn has a type of
TEXT, and the
numbercolumn has a type of
- Finally, we include a semicolon:
;. This tells SQL we have reached the end of the query.
# Other types of data in SQLite
Every RDBMS has different types of data, but some of them are similar across RDBMS's.
The SQLite types
TEXT also exist in PostgreSQL. In other RDBMS's though, they may be called differently but be used for the same purposes.
# Running the command multiple times
If you run the
CREATE TABLE command again, you'll get an error because the table already exists. Often you'll want to create a table if it doesn't exist yet. Fortunately, SQL allows us to do that by adding
IF NOT EXISTS to the query:
CREATE TABLE IF NOT EXISTS accounts (name TEXT, number TEXT);
I believe this is not an English-like as some other parts of SQL, but nonetheless that's how it goes!
Since now we've created two tables, let's learn to insert data into them in the next chapter!