Re-Learning Basics for SQL

During my time at the Flatiron School, we briefly had a section in the curriculum that went over SQL and had a couple of labs that utilized it’s functionality. However, since I do not properly remember that section, I decided to work on solving database word problems on LeetCode and take a refresher course on W3Schools. Some of the problems I found interesting in the W3Schools Quiz on SQL will be studied and explained in another blog. But first, lets talk about what SQL is. SQL stands for Structured Query Language. SQL stores data in a table, similarly like on a spreadsheet. Columns are defined on the table and details what the data is in the row(s). From this database, we can do a number of actions in order to grab and access the information presented, and or add or change the data that is currently in the table. We use statements in order to show what we want to gather or modify within the table. First let’s start by creating a database. Within our terminal you will type sqlite3 pet_database.db, this creates our database. Next, we must create our table. When we create database tables, we need to specify some column names, along with the type of data we are planning to store in each column. So far example, we would type the following:

1. sqlite> CREATE TABLE cats (
2. id INTEGER PRIMARY KEY,
3. name TEXT,
4. age INTEGER
5. );

In order to properly provide an id in the table, we use PRIMARY KEY in order to index our database using an integer. Numbering our table rows makes our data that much easier to access, update, and organize. Let’s say we want to add in another column to our existing table, we would then have to type the following: sqlite> ALTER TABLE cats ADD COLUMN breed TEXT;

This ALTER TABLE statement lets us change the cats table and then by typing ADD COLUMN, we are stating that we are adding a new column to our cat table and in this case we are adding a breed column and the type of data we are storing is TEXT. After we do this, we can check the schema to see the layout of our table. To do so we just need to type sqlite> .schema .

The output from this is the following:

1. sqlite> .schema
2. CREATE TABLE cats(
3. id INTEGER PRIMARY KEY,
4. name TEXT,
5. age INTEGER,
6. breed TEXT
7. );

Lastly, if we wanted to delete this table, we would then just need to type sqlite> DROP TABLE cats; With this action, we delete the cats table in its entirety. These are the basics of SQL, in my next blog I will go over some of the interesting problems dealing with SQL and some more commands.