Lesson 2 – Creating Tables in a Database

In the last lesson, you saw how to set up the environment to run the SQLite database. In this lesson, you will see how to add tables to an existing database.

Adding “products” Table

Tables are the entities where your data is actually stored. Let’s create a table “products” in our “RetailDB” that you created in the last lesson. To do so, open the DB Browser for SQLite, load the “RetailDB” as you did in the last lesson and then click on “ExecuteSQL” tab from the top. Execute the following query inside the text area:

CREATE TABLE products(
id INTEGER PRIMARY KEY,
name varchar,
price INTEGER,
instock varchar,
category_id
);

For reference, look at the following screenshot. After entering your query, click on the play button at the top to execute the query.

To create a table in a database, the CREATE command is used. After the CREATE command, you have to specify the keyword TABLE followed by the name of the table, which is “product” in our case. Next, you have to write opening and closing brackets. Inside the brackets, you add the columns for the tables. The columns to enter depend upon the type of data that you want to store. We will store the id, name, price, instock, and category_id. Hence we have created the corresponding columns. While adding a column, you have to specify the column name and the type of data that will be stored in the column. For instance, the “id” column in our data will store integer values. We have also imposed a PRIMARY KEY constraint on “id” column which means that we can only have unique integers in this column. The “id” column will be used to uniquely identify the records in the “products” table.

Adding “categories” Table

In the same way, we can add “categories” table. The categories table will contain information about the different categories that our products may belong to. The following script creates “categories” table.

CREATE TABLE categories(
id INTEGER PRIMARY KEY,
name varchar,
current_total INTEGER
);

Now if you click on the “Database structure” tab, you should see the “categories” and “products” under the “Tables” heading as shown in the following screenshot.

What’s Next?

In this lesson, you saw how to create tables in a database, in the next lesson I will show you how to insert records in a table.