When it comes to developing applications, there is no doubt that being able to store data is a necessity. Whether you're trying to implement a log-in system, or you want to create personalized music playlists, or you want to create a program that takes care of all your haircut appointments. Whatever the case maybe, this data has to be stored somewhere. This is where databases come in handy. These databases allow you to store data in a way where if the program is to be stopped, and restarted, you can pick up exactly where you left off. If you decide to create a log-in system, the username in password has to be stored somewhere. Today I will be giving a step by step guide for how to create a simple database using SQL.

Setting Up Environment

Before we can get into any actual programming, we must make sure that you have installed the required packages to actually use SQL. If you are using Mac, there is a chance that you might have it installed. Nonetheless, you can check by navigating to your VS Code terminal and entering:

which sqlite3

If you see something like this, then that means that it is already installed. If not, you can continue on to install.

/usr/bin/sqlite3

If you are using windows, skip to the "Installing on Windows WSL" section to install sqlite.

Installing on Mac OSX

Assuming you already have homebrew installed on your environment, you can type this into the command line to install sqlite3.

brew install sqlite

Installing on Windows WSL

Once you have navigated to your VS Code terminal, you are going to type 3 different commands in order. Give it a little time in between each command as you don't want to interrupt the installation process.

sudo apt update
sudo apt install sqlite3
sqlite3 --version

Now that your environment is now completely set up, you can start coding.

Using SQLite3

Once your environment is set up, you can navigate to your terminal once again and type this command to enter the SQL interpreter.

sqlite3

Once you're in, you will see something like this:

SQLite version 3.38.2 2022-03-26 13:51:10
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> 

If you see this, everything is up and running which means you are good to go.

Creating a Table

Now that we are in the correct environment, we can start creating tables. Say that we want to open up a small car dealership and we want to store all of our cars in this SQL table. The first thing we would need to do is create our table. We can do this using this code:

CREATE TABLE cars (
    id INTEGER PRIMARY KEY,
    make TEXT,
    model TEXT,
    year INTEGER,
    price INTEGER
);

What this code is saying is:

Create a table called "cars" with 4 columns, one with an id that is an integer, another with the make which is a text value, another with the model which is also a text value, another with the year as an integer, and the price which is also an integer.

Based on the structure, we can see that each column is defined by the name of the column, then the data type for which this column will accept.

We can also see that with the id, we included "PRIMARY KEY" which means that an id will automatically be assigned when a new car is input into the table. This will make it so that the first car will have an id of "1", and the next car will have an id of "2" and so on and so forth. This number will just keep incrementing for however many cars are input.

Inserting Values into the Table

Once we have our table ready, we can start inserting values into it. Say for example, our finds us a 2015 Toyota Camry at a junkyard. If we wanted to input this new car into our database, we would type:

INSERT INTO cars 
    (make, model, year, price)
VALUES 
    ('Toyota', 'Camry', 2015, 10000);

What this code is saying is:

Insert this new car into the table 'cars'. We are going to set the make value to 'Toyota', the model value to 'Camry', the year to 2015, and the price will be set at $10,000.

Notice how we did not include the id column, because remember that the id is automatically assigned. Since this is the first car we are adding, this will automatically be assigned an id value of 1. Go ahead and try to add a 2023 Nissan GTR priced at $150,000. (Keep in mind, integer values must not contain commas or dollar symbols)

Checking Your Tables

If we want to see our tables, we can type this into the terminal to see which tables have been created.

.tables

Furthermore, if we want to see our newly created 'cars' table, we can use this code.

SELECT * FROM cars;

What this code is saying is:

Show me every column from the table 'cars'.

If we simply wanted to see a table of only the makes of the car, we could type:

SELECT make FROM cars;

This works for every column.

In addition, the table itself might seem a little ugly, so let's go ahead and fix that. Type this code:

.headers on
.mode columns

Now when you display your table, it'll be easier to read.

Congratulations!

Congratulations on using SQL to create your very first table! You now have the fundamentals of coding in SQL to create databases. As you can imagine, we can use this for real world applications at a large scale. This was a very basic example, but I hope that you may feel a little more comfortable starting to learn SQL. Challenge yourself to create something BIG. Happy Coding!