SQLite3 Demo

In this blog post I’m going to be doing a small demo, creating a SQLite3 database. This is something I’ve been meaning to do for myself to practice and get a refresher on SQL. I’m basing this post on Flatiron’s curriculum on learn.co as I thought it was the most helpful. Hopefully this blog post will help and/or inspire others to do the same!

Pronounced SEQUAL- What Is It?

SQL stands for Structured Query Language. Unlike other languages that are out there, SQL’s only purpose is for talking to databases. This language can also be referred to as a “domain specific” programming language. There are many database systems that SQL interacts with such as MySQL, PostgreSQL and SQLite. I’m going to be doing the demo in SQLite.

Set Up

1. In the terminal write: which sqlite3 => return should be /usr/bin/sqlite3

This means SQLite3 is already on your computer!

2. If not, go to SQLite Download Page here.

Continuing after downloaded…

3. Paste: sqlite3 test_sqlite.db

b. Paste: create table test_table(id); => we’re creating a database called “Test Table”

** ; ** is highly important to terminate the session, otherwise you will be trapped!! You can also do .quit

It happened to me doing this just now. The .quit will also take you back to the directory but just don’t forget the ;. You can add it on the next line also in case you did forget it the first time around!

Creating the table

We are create a dogs table because I love dogs and based on my previous post about my dog, Benny I thought I would continue the theme.

1. So to get back into the database you should see this. **pay no mind to my other projects but you can see it at the bottom! Now you can’t cd into it so instead run sqlite3 whatever_table_name.db in this case test_sqlite.db

2. Now that we’re in SQLite database let’s create a table for dogs. I’m giving the attributes name, age and breed. **Notice the error- it is because we need to specify some column names. You can see the specifics that I say integer for age and text for name, which is important.

3. To check what we have so far you can run the .tables and .schema command. See below on what we have so far!

Writing in a Text Editor!

1. I’ve put everything in a folder for convenience so you can start that by writing in your terminal mkdir folder_name. Then create a file by writing the command touch file_name.

2. It should look like so! You can see by this line “sqlite3 test_sqlite.db < 01_add_columns_to_dogs.sql” connects the two.

Adding Data!!

1. Finally we can start adding data. We can do this in our terminal and text editor. I will demonstrate both using INSERT INTO

As you can see now we have some doggies in our database!!! Now when using your text editor based on the example make sure you do separate INSERT INTO’s. Also because we had id as primary key it automatically assigns it for us, which is why you don’t need to add it yourself.

OKAY ONTO THE FUN STUFF!

I’ve quickly created an owners table so that we can see the joins and how they work! Below is a quick visual of the updated schema and the owners table with name and age.

updated dog table

THE JOINS

Below is an overview of the joins and it is what I’m going to be demonstrating with the tables we have created.

Inner Join

Boiler plate

Our example that returned the data with the specified columns

If our dog didn’t have an owner id assigned or had an id of an owner that didn’t exist in our owners table they would not show up in this inner join. INNER JOIN returns all the data where the statement is TRUE.

Left Join

Boiler Plate

Now, say that we had a pet that didn’t have an owner. In the INNER JOIN method the dog would be excluded but in the LEFT JOIN method it would return the dog regardless — it would just leave an empty space where the owners name would be

*RIGHT JOIN and FULL JOIN are not supported by SQLite but I have posted the boiler plate of what they would look like.

Right Join

Now if we created another owner that didn’t have a pet and we did RIGHT JOIN we would see the owner’s name but with no pet information.

Full Join

The FULL JOIN would include both the dog without the own and the owner without the dog. To sum it up ALL THE DATA.

Conclusion

This was super helpful for me and I hope it helps others! Again, the resources I used were from Flatiron Schools Learn.co.There’s a lot more to do with SQL and other databases, which I may do in my next blog post. Happy Coding!