Basics of SQL and Relational DatabasesJackie H.
BlockedUnblockFollowFollowingJan 27SQL stands for structured query language, and it is a language that manages data in a database.
Many large websites have databases with a lot of user data, and SQL allows us to parse through the data to get exactly what we want.
Below, I have created a bears database in my terminal for the purposes of this blogpost.
The first column of our bears database is “id INTEGER PRIMARY KEY” which designates a unique id to each bear created.
Primary keys are auto incrementing and each new bear will have its own id number starting from 1.
We have also created columns that are “TEXT” as well as a “BOOLEAN” datatype for the column “alive”.
creating a table for our bears databaseOnce our database is created, we can use “.
schema” to make sure that the database is structured the way we want, and check that the data types are correct.
checking the schema of our bears tableour databaseAfter using INSERT INTO to add bears to our database, we can use SELECT * FROM bears to look at all the bears we have.
The * symbol allows us to choose everything.
Now we can begin to manipulate the data and see the information that most interests us.
Say that I wanted to have the names of the bears in order of their age from youngest to oldest.
I would follow the following format:SELECT (desired_column_name) FROM (table) ORDER BY (column_to_sort_by);SELECT name FROM bears ORDER BY age;names in order of age, ascending which is the defaultselecting all the information of the bear that is the oldestIn the excerpt above, we have selected all the information we have in the database on the oldest bear.
We have DESC after ORDER BY age because the default of ORDER BY is to order the database rows by ascending order.
LIMIT 1 shows us just the bear at the top.
If we wanted to see the oldest two bears, we could have done LIMIT 2.
selecting just the name of the oldest bearIn the example above, we are just looking for the name of the oldest bear.
In the example below, we use the keyword BETWEEN to specifically get the names and genders of the bears in our database within a specific age range of 1–3.
selecting name and gender from bears between the ages of 1 and 3We can also use SQL to get the count of something in our database.
In this example, we have chosen to count the number of bears with a ‘goofy’ temperament and return that count.
selecting the count of bears that have a goofy temperamentIn this example, our SQL command returns a count of how many bears have each color.
We group the bears by color, and then count the number of bears in each color category, and return that answer with the colors of the bears.
selecting the color and getting the color count of our bears databaseIn this example, the end result is the most common color among the bears and the count of how many bears had that color.
First, we group the bears by color and order them by the count, and then just return the color that is most frequent in the database.
We ‘SELECT’ color so the color blue and the count of 2 are returned.
selecting the most common color among our bear databaseAll databases have a primary key for each row instance, but in cases where two databases relate to each other, there might also be a foreign key.
Let’s say that there is a database of artists, and a database of fans, and you worked for a company that wanted to know how many fans an artist had, or which artist a particular person was a fan of.
In this case, you would want a relational database!In our “fan” database, each fan would have an ID number as a PRIMARY KEY, but they would also have a FOREIGN KEY of “artist ID”.
The foreign key would be the primary key of the artist on the artist table.
The system of using id numbers and primary/foreign keys might seem more complicated than just entering the name of the artist the person was a fan of, but using this system prevents future troubles.
For example, if an artist decided to go by a stage name, you wouldn’t have to change each instance of the artist name in the fan database.
You could just change the artist name in the artist database, and because the foreign keys are integers, there would be no problem on the fan database side.
In my next blog post, I will be covering SQL joins!.. More details