A Little Bit of SQL using SQLite

Creating a database, tables, and inserting data.

SQL is what allows us to store, retrieve, and manipulate data in database. It stands for Structured Query Language. I will be using SQLite to create a database, tables, and insert data. Here is a link to download SQLite if you’d like.

In the example for this blog, we’ll create a database of animals consisting of two tables. One table will be cats and the other table will be dogs.

SQLite lets us create a database by clicking on “New Database” as seen in the image below.

Creating a Database in SQLite

The following window will appear.

Make sure that you find the directory that is best for you to save and access the database about to be created. I will be using a directory I created called “Database”. I could have called the directory “chicken”, so name it what is best for you. I will save the actual database that will be holding the cats and dogs table as “animals”.

An “Edit table definition” window will appear after you click save. You can close that out since we will be creating our tables through SQL commands.

Image A

Notice in the red box of Image A how we now have animals.db as our database. Currently, we see “Tables (0)”. This means we do not have any tables yet. Now it’s time to create some.

Creating Tables in SQLite

Creating a table in SQL has some direct language. It is best practice to type in SQL commands in uppercase such as CREATE TABLE or SELECT * FROM . Lowercase words represent that names we are creating. The code block below shows the general template to create a table in SQL.

CREATE TABLE table_name (
   data info,
   data info,
   .
   .
   .
   last piece of data info
)

The code block below is what we’ll be using in SQLite. We have a table called dogs that has values of an id that in an integer. The values of name, breed, and sex are strings, which are represented as the data value VARCHAR in SQL. The parenthesis containing a number tells how man characters to store. For example, breed VARCHAR(20) allows us to hold 20 characters to save the breed. sex VARCHAR(6) allows us to hold 6 characters.

CREATE TABLE dogs (
 id INT,
 name VARCHAR(20),
 breed VARCHAR(20),
 sex VARCHAR(6)
)

Let’s add all of that SQL command into SQLite.

Click on “Execute SQL” button. This is where we can use SQL commands to create a table.

After adding the SQL commands in the first box, click the play button. You can see information in the third box that confirms a table as been created as seen in the image below.

If you click back on “Database Structure” you can see we now have “Tables(1)” containing the table “dogs”.

Let’s do the same thing again to make a cats table with the same values. Try it yourself before you scroll on.

Here is the SQL command to create the table for cats.

Here is the current result of the tables we have.

We can see the data we have by clicking on “Browse Data”.

Notice the dogs table is selected and we can see “id”, “name”, “breed”, “sex”. If you select the cats table, you can see the same situation. Both tables do not have data. Now it’s time to insert some data!

Inserting Data into Tables

To insert data into a table, we use the INSERT INTO command. The code block below is the general template.

INSERT INTO table_name VALUES(data_info, data_info,..., data_info)

In our case we have values of “id”, “name”, “breed”, “sex” so we have to make sure to place the appropriate data in the same order within the parenthesis. The image below data being inserted into the dogs table. We can also see the “execution finished without errors” meaning the data was inserted into the table.

We can confirm the data was inserted by clicking on “Browse Data”. We can see all of the data information added into the dogs table for our animals database.

There are times where we don’t have all of the data information, so we can add what we know. Let’s add a dog to the dogs table where we do not know the breed. We have to tell SQL what information we do know which is “id”, “name”, and “sex”. The code block below shows how to add data that we do know.

INSERT INTO dogs(id, name, sex) VALUES (2, 'Poppy', 'Female')

By clicking back to “Browse Data” we can see that the dog data just inserted shows NULL in the “breed” column since we did not have any data to add for that particular column.

Conclusion

We now know how to create a database in SQLite, create tables, and insert data with complete data or partial data. My next blog will be on how to select particular rows so we can then update and/or delete the rows. Feel free to practice creating databases, their tables, and insert data.