SQL Basics— Let’s Play with Pokémon Data!
Phase 2 of the Flatiron journey slowly unfolds and I’m hit with SQL. I was so excited to see it on the curriculum but when I looked at the first lesson, I already wanted to exit out of the browser.
So I was just staring at the page and the page was staring back at me, and no progress was made. If you’re a newbie like me, you’ve probably only seen it in job postings and have no idea what SQL is or what it does.
Let’s slowly unfold! (Flatiron uses the SQLite data management system so the commands will mostly only be relevant to databases that are managed with SQLite.)
What is SQL?
The world runs on data! And how do we, as people of the modern age, store data? ONLINE! Data is stored in… databases! Data is knowledge and to be able to obtain such knowledge, we must acquire power! The power of SQL!
SQL stands for Structured Query Language. Some people pronounce it as “sequel” while others just pronounce it by saying each letter, “S.Q.L.”
Data is stored in a warehouse called a database. Unfortunately, the warehouse is locked. SQL will be the key to accessing databases and acquiring the specific data that we need. With SQL, we can create, read, update, and delete data from databases. Data is stored in tables, similar to the ones from Microsoft Excel or Google Sheet.
Let’s say we have this table in a database file called pokemon.db.
This table has records of Pokémon. Convention is to name the table as the lowercase plural of what the table contains, so it would still be pokemon… Okay, let’s try a better example! If a table were to have data on fruits, then the table would be called fruits.
The table below would have fruits as its name.
But I really like Pokémon, so we will stick with the first table.
How to execute commands?
There are a few ways to execute SQL commands. One way is to open up the database file and then just run the commands in the terminal while you’re still in the database file. The second way is to write all of your codes in a file (I’m currently learning Ruby so let’s use the .rb extension on the file, but the extension does not matter). Then you use the following to invoke all the SQL commands saved in list-of-commands-file.rb:
pokemon.db < list-of-commands-file.rb
For this method, it is recommended that you separate the file where you create the table, insert the data, and write the queries.
Or you can write the code in the text editor and then just copy and paste the commands to the terminal (once you’re inside the database file).
How to create a table?
So let’s go back to the Pokémon example. Let’s start with creating a table to store information.
CREATE TABLE pokemon (
id INTEGER PRIMARY KEY,
name TEXT,
type TEXT
);The CREATE TABLE pokemon command is creating a table called pokemon. This table will have three columns: id, name, and type. It’s typically convention to type the column names in lower case and the commands in UPPER CASE. It’s also that way to prevent you from confusing yourself into thinking a command/syntax is part of a string or input.
The first column id, takes in an integer as the input. The id will also often be called the primary key of the table. Each table has its own primary key. Think of it like a person’s tax ID. Everyone will have a different tax ID just like how every Pokémon record will have a different id. A record is every row of information within the table.
The second column name, takes in text as the input. Text is inputted with quotes “”. The third column type, is just like the second column.
The very last part of the syntax is the semi-colon ;. Without the semi-colon, SQLite won’t know that your command is at its end.
Okay, so once we have executed the SQL command, we should have an empty table in the pokemon.db file with just the column names.
How will we know if the table was actually created?
I present to you, the .tables command!
Type .tables into your terminal (make sure you’re in the level of your database file). You should get all the tables that you have created so far! So you should see pokemon listed.
To check the format of your pokemon table, use the .schema command. It will return something like this:
CREATE TABLE pokemon (
id INTEGER PRIMARY KEY,
name TEXT,
type TEXT
);…Wait. Isn’t that just our CREATE TABLE commands? Yes. Yes it is.
Let’s add some Pokémon now!
How to insert data?
To add a Pokémon, we will do the following:
INSERT INTO pokemon (name, type) VALUES
("Bulbasaur", "grass"),
("Ivysaur", grass);Or we could do:
INSERT INTO pokemon (name, type) VALUES ("Bulbasaur", "grass");
INSERT INTO pokemon (name, type) VALUES ("Ivysaur", "grass");But wait! Didn’t I create three columns?!
Yes. Yes I did. So where is the id part of the input?
Well, do ever just do so much data entry that sometimes you forget the last thing you just entered? Or do you ever forget why you go into a room? Was it to get the phone charger…? Was it to get a towel…?
The magic of SQLite is that we will never have to worry about manually counting how many Pokémon we have in the table already. SQLite will help us keep track. The first record, Bulbasaur, will have an id of 1. The second record will have an id of 2, and so on. The id value of every record will be its most unique characteristic.
Sure you can argue that no two Pokémon will have the same name. But what if one day, Nintendo decides to play with us and just create a whole set of Pokémon with the same names as previous Pokémon? No way…right?
Well, they did. And they just added the region’s name in front of the Pokémon’s name. So, Meowth is still Meowth. Pikachu is still Pikachu. Trainers won’t be saying, “Alolan Meowth, use Scratch!” That is just silly.
With SQLite magic, we won’t have to worry about the new Pokémon having the same name as the older ones. We can just query with their unique attribute, id. SQLite automatically adds in the id attribute for us because we named the column as PRIMARY KEY. SQLite will count the id and auto-increment it for us! Awesome! One less human error to worry about.
How to query?
So we just inserted two Pokémon into our table. Let’s retrieve all the data related to all the Pokémon that we have in the table.
SELECT * FROM pokemon;Let’s break down the SELECT command.
SELECT means we want the following values to the returned to us.
* is something you may recognize from other database searches such as Google. It it somewhat of a wildcard. SQLite will recognize that as returning the values from every column.
FROM is the keyword for SQLite to look for the table name that comes right after this.
pokemon is the name of the table that we’re selecting from.
; is to end the query.
The query would return something like this:
1|Bulbasaur|grass
2|Ivysaur|grassWow. Isn’t that just ugly? Let’s make our results a bit prettier.
Let’s use the .headers on command to show us what the column names are!
id|name|type
1|Bulbasaur|grass
2|Ivysaur|grassA lot better but still…ugly. If only the results would space each other out…
Let’s try the .mode column command. This command will enable us to adjust the width of the results. Let’s stick with .width auto.
Let’s see the results now!
id name type
--------- ----------- ------
1 Bulbasaur grass
2 Ivysaur grassSo much better!
So what if we only want the type of the Pokémon with the id of 1? The command would be:
SELECT pokemon.type FROM pokemon WHERE pokemon.id = 1;Let’s break this down.
SELECT means we want the item(s) to the right of this keyword to be returned to us.
pokemon.type means we will be going into the table of pokemon and we want the value of the type column. Generally, databases will have more than one table. To better organize our list of Pokémon, we could technically create tables for every new generation, right? But they should all belong to the same file because they’re all Pokémon. So then the next table, say pokemon-gen-2, will also have a name column and a type column. We would want to use the table-name.column-name notation to distinguish which table we’re selecting from.
FROM pokemon means we will be looking at the pokemon table.
WHERE is the keyword to set our filters.
pokemon.id = 1 is our filter.
; is to end the query.
All together is that we only want the type of the Pokémon from the pokemon table that has the id of 1.
You can always use other filters, but the id is unique to each record so using the id filter is extremely useful when you only want a specific return.
How to update table/data?
If memory serves me right, Ash had a Bulbasaur in the first season. So now I want to add a trainer column to my table. Let’s update the schema of our table and then check the schema with .schema!
ALTER TABLE pokemon ADD COLUMN trainer TEXT;.schema would return something like this:
CREATE TABLE pokemon (
id INTEGER PRIMARY KEY,
name TEXT,
type TEXT
, trainer TEXT);Yes the comma looks awkward but this is the correct schema of our pokemon table!
Let’s break down the ALTER TABLE command.
ALTER TABLE means we will be changing a table. But we have to let SQLite know which table, right?
pokemon is the name of the table that we’re changing.
ADD COLUMN tells SQLite how we want to alter the table.
trainer is the column name that we will be adding.
TEXT is the data type of the inputs in the trainer column.
; is to end the command.
So now that we have our trainer column, let’s update our Bulbasaur record to include its trainer!
This command would allow us to update an existing record:
UPDATE pokemon SET trainer = “Ash” WHERE pokemon.id = 1;(You can also use WHERE pokemon.name = “Bulbasaur”;)
Let’s break this down.
UPDATE is the keyword to update an existing record.
pokemon is the name of the table we’re updating.
SET is the keyword to set a new value.
trainer = is how we would want to change a value in the trainer column.
“Ash” is the value we’re inserting into the trainer column.
WHERE sets the filter.
pokemon.id = 1 is the filter. We will only be changing the trainer value for this Pokémon.
; ends the command.
You will get no returns. You would have to manually query for that specific Pokémon’s data with something like this to see the data for Bulbasaur:
SELECT pokemon.trainer FROM pokemon WHERE pokemon.id = 1;This would return:
trainer
----------
AshIt looks all pretty because we used .headers on, .mode column and, .width auto earlier.
Remember the mistake in the first table? Let’s bring that table back. Charmander is a fire type! Let’s update the table.
Earlier we updated a “cell” that was empty. We can also update a “cell” that already has data with the same UPDATE command!
UPDATE pokemon SET type = “fire” WHERE pokemon.id = 4;How to delete table/data?
What if one day you get tired of Pokémon and want to move on to Digimon? (You are allowed to enjoy both, of course.) So then you decide to delete everything about Pokémon.
This is how you would delete a table:
DROP TABLE pokemon;Pretty simple enough.
But what if you just really dislike one particular Pokémon and want to delete that data? Let’s pretend we had inserted all the Pokémon data up to 151.
DELETE FROM pokemon WHERE pokemon.id = 150;So now the last two ids of the pokemon table would be 149 and 151. The next Pokémon you create will not have an id of 150. It will have an id of 152. Remember how id is unique? SQLite will not repeat a previous id once a new row is added.
However, what if I were to delete the Pokémon with an id of 151 and the current table only has 151 records? In that case, SQLite will reuse id 151.
Conclusion
SQL is not that scary! It’s okay if you forget some commands here and there. That is why we have Google! I had to rely on my notes when I was writing this post because my memory is just that bad.
So now you have learned or reviewed to how to:
- create/delete/update tables
- insert/update/delete records!
There are way more commands out there but these are the basics. So now that you are equipped with some knowledge, let’s go query them all!
