A Journey Mans Guide to Programming- — SQL

Recently I’ve embarked on this journey(going on two months now) of learning how to program. Spending countless hours scouring the web via YouTube, Udemey, Free Code Academy, W3Schools and endless other resources to get an understanding of a this world I have always had a fascination for but now have a new found respect. Dabbling into multiple different language such as JavaScript, Python and Ruby; I always wondered what allowed all these characters strung along together to communicate to the user and allow them to store information that can then be retrieved and reused.

Thru this journey I have been fortunate enough to be accepted into a great coding boot camp. Its helped to keep me grounded, but also to take advantage of having instructors help guide me along this ever emerging world that is programing. As an outsider to coding I always pictured Frank Morgan (from the Wizard of Oz) for those young ins out there, pushing all the buttons and pulling all the right levers to create the magic that could only be captured on the internet. Only to find that’s not the case at all, in a sense I feel that I have pulled back the curtain that once stood ambiguous and isolated to only discover the hive-mind that is open-source.

Speaking of programming, lets talk SQL (Structured Query Language) commonly refer a sequel…and were not talking about those never ending Fast and Furious movies who’s non stop action plots will most certainly lead them to drag racing on the moon(thanks a lot Elon Musk). No were talking SQL, sequel, the relational database management system. Used by thousands of companies worldwide, implementing various versions such as MySQL, Microsoft SQL, PostgreSQL among others. Everything from the National Audubon Society(protecting and advocating for birds) to Oracle, who sells database driven software solutions that nets billions in profits annually. The world of SQL is vast and expanding but at the basic level they are all about one thing, the CRUD.

CRUD

At bare minimum we expect our databases to allow us to store and alter data at will. This is whats known as CRUD. Create, Read, Update and Delete. With these basic building blocks we can use various tools at our disposal such as the terminal and SQLite to help build and alter our databases. Lets get going by building something simple in our terminal to see what the power of databases are all about.

To get started lets make sure we have the tools we need. First lets check to see if we have the capability to access SQLite via terminal. We can test this by typing in our terminal:

which sqlite3

If install already you should receive the following response:

/usr/bin/sqlite3

If not, depending on what type of system you are running you may need to install this manually, there is a great walk-thru on tutorialspoint.com that will take you step by step. Feel free to click on the link below for more information.

SQLite - Installation

Alright now that we got what we need, lets get building! In order to create our database we need information worth storing and who doesn’t enjoy a good laugh. In the following example I will use comedians, but this tutorial should work with any information you would like to use to create relationships with.

Database

Time to get down to syntax. To get started building our database lets run some commands in our terminal:

sqlite3 comedians_database.db

You should receive something along the lines of:

SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.

Create

Now that we have our database set up to receive information, lets go ahead and start by adding a table. The table in a database is made up of column names and rows that helps store the relationships between them all.

example of a table storing the relationships between staff and student.

Let’s go ahead and implement this method to help create our table:

sqlite> CREATE TABLE comedians(
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER
);

Lets parse thru this to see whats really going on here. As we can see the main components being used are in capital text in order to create the table and the columns that will be used.

The CREATE TABLE command is doing just that, creating a table and for our example a table of comedians. With in our parentheses we put our id INTEGER PRIMARY KEY, this allows us to access our table columns index to manipulate our data. Next, we have name TEXT, age INTEGER. This sets up the name of our columns (name and age) with the type of information that will be inserted such as TEXT or INTEGER(number). One thing to note is the use of the semi-colon(;) at the end our parentheses. This is our ending point, without this we will be unable to create or update our data. To ensure that your database has been created you can use:

.schema

This should return your schema or your database structure that will be used to house your information.

Update

Lets say we want to update or alter our table in order to add a new column to provide new information. To do this we can use ALTER TABLE:

sqlite> ALTER TABLE comedians ADD COLUMN hometown TEXT; 

To see the changes we can run our schema again:

sqlite> .schema
CREATE TABLE comedians(
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER,
hometown TEXT);

Delete

Lastly we will look at delete, in our case DROP TABLE which allows up to to delete the table we created.

DROP TABLE comedians

BOOM! there goes all our hard work.

This is a very basic introduction of building a table in the terminal, being able to read it, edit it and then delete it with a few commands. To get into more functionality we can actually use the SQLite application but with these in your toolkit we are well on the way to storing the information needed to build the next Oracle.