SQL: The Sequel

During my time at Flatiron, we had a brief section of the course that was focused on SQL, a domain-specific language used to manage data in relational databases. At the time it seemed very abstract and felt like a lot of code had to be written to get the data back I wanted. From there we quickly moved on to learning Active Record with Ruby on Rails which basically did the SQL queries for us. That was exciting, but over the past month I decided it would be beneficial to me to re-learn SQL and fully understand the logic behind the Active Record magic.

To practice some of the basic SQL queries, I downloaded the app called Mimo. If you have ever used Duolingo to learn another language, then you will be familiar with the format of Mimo which basically follows a similar structure to help you learn computer languages. They have a lot of different languages to choose from, but I chose SQL and started learning!

We can use SQL to create new tables/rows in our database and query the database to retrieve the specific data we want. First let’s build a new table by using the CREATE TABLE, INSERT INTO and VALUES clauses. The code below creates a table called ‘disney_characters’ with columns for id (an integer), and a name and franchise (both text fields).

CREATE TABLE disney_characters (
    id INTEGER,
    name TEXT,
    franchise TEXT)

Now we can add some information to the table. The code below specifies which table and row we are submitting new information for and then tells it the values that should be assigned. I am adding two characters to the table each with an ID, name and franchise they belong to.

INSERT INTO disney_characters (id, name, franchise)
VALUES (1, 'Mickey Mouse', 'Mickey & Friends')
INSERT INTO disney_characters (id, name, franchise)
VALUES (2, 'Moana', 'Moana')

To perform a basic query, I can use the SELECT clause and specify which table I want the information to come from. To grab everything from the table I use the * along with the clause.

SELECT * FROM disney_characters
//
id name            franchise
1  Mickey Mouse    Mickey & Friends
2  Moana           Moana

Now that I have some data in our table, I can begin to make more specific queries using the WHERE clause which takes a conditional statement and returns only the items in the table where the condition is true. The example below only returns the Moana row.

SELECT * FROM disney_characters
WHERE name = 'Moana'
//Note: if we only want to return the name and franchise, we use the column names instead of the *
SELECT name, franchise FROM disney_characters
WHERE name = 'Moana'

I can also chain together multiple conditionals by using the AND, OR clauses. This example shows any row with a name of ‘Moana’ or any row that has a franchise of ‘Mickey & Friends’.

SELECT * FROM disney_characters
WHERE name = 'Moana' OR franchise = 'Mickey & Friends'

These types of queries are exactly what Active Record does behind the scenes for us, but its’ extremely useful to study SQL and understand how it works with relational databases. I would highly recommend the Mimo app for a great overall tutorial on the basics. There is also a free course on codecademy which gives you interactive playgrounds to practice making queries and is a great place to start. Hope this helps you get interested in learning the Rails behind the scenes, I know I’m excited to continue learning more!