Intermediate SQL
Last week I wrote an introductory article to learning Structured Query Language. I covered creating tables, updating tables, dropping tables, and methods for executing SQL through a text editor. If you haven’t seen that already, feel free to check it out here.
The main topics I want to touch on today are how to manipulate data into those SQL tables. The methods we will be using are inserting, updating, selecting, and deleting.
Insert
The first thing we need to know is how to insert data, considering we can’t update or select data that doesn’t exist. If you followed along last week, I’ll be using the same example from that article. If you run .schema, your table should look similar to this:
And that’s the table we want. The first thing to note when using an Insert statement is that we want to have a value for every column in the table. Here’s an example of an Insert statement:
It’s very similar to the commands we are already familiar with. Here’s a short breakdown:
- INSERT INTO
- table_name
- (column1_name, column2_name, column3_name, column4_name)
- VALUES
- (column1_value, column2_value, column3_value, column4_value)
;< = never forget the semicolon
Simple enough, right? If you grasp this idea then I have good news for you. The rest of the commands we use follow this formula and aren’t too different from this one. If you can grasp this one, the rest are fairly simple.
Update
The next statement I want to cover is Update. It looks very similar to the Insert statement with very slight differences. Here’s what it should look like:
All this statement does is take the one article in our table and update the title. This one uses a couple of different keywords, but is still pretty straightforward:
- UPDATE
- table_name
- SET row_name =(to) row_value
- WHERE row_name =(is) row_value
Let me start off by saying there’s a reason for the (to) and (is) in there. The fun thing about SQL is that the majority of the commands read like broken English. If you wanted to read it out loud, it’d probably sound like: “Update the articles table by setting the article title to ‘Intermediate SQL’ where it is ‘Intro to SQL’”. It isn’t the most grammatically correct sentence, but reading it out loud to yourself does make the concept easier to grasp. The WHERE keyword is a keyword that we will see again when using Select statements to retrieve data from our table.
Select
We’ve now covered Inserting and Updating data, but now let’s go over how to Select data. I saved Select for last simply because it is the most complex of the three. The structure is still very similar to the others, but given that you can provide different parameters still makes it the most complex. Let’s start with the most basic form of a Select statement:
This basic statement will just pull every entry from the table. Since there’s only one entry so far, you’ll only see the one of course. Feel free to experiment with this by adding more rows into your table from what you learned earlier. Let’s break down how to build a Select statement:
- SELECT
- names_of_values
- FROM
- table_name
See what I mean? It’s not that it’s necessarily more difficult to use Select, it’s just that it has more intricacies. So if I want to see all the article titles with their publications, I’d use this Select statement:
This is the same idea, only we are narrowing down the information we want to see. Now instead of printing out all of the data on all of the articles, we’ll only see their titles and the publications they are associate with. Pretty neat, huh?
Additionally, you can throw in the DISTINCT keyword immediately after SELECT to grab only values that have no duplicates. That means if we had 10 articles total and 2 of them had the same title, using that keyword would only pull 8 articles, omitting the 2 that share the same title.
Now I want to cover using Select statements with the WHERE keyword. We know how to select certain data from the table, but what if we want a specific row data to be returned?
Enter the WHERE clause:
This will return all of the data about an article, but only specifically the article titled “Intermediate SQL”. So now if we combine what we’ve learned, we can have really specific SQL statements like so:
This statement will pull all entries from our table that are part of The Startup publication, showing us the article titles and authors.
You can also use comparison operators <, > to specify your search as well:
This statement will pull all of the article titles in our table that have more than 50 claps.
Delete
I almost forgot about the Delete statement. This is by far the simplest one, especially now that you’re familiar with the others. It helps to Delete based on the id value of something in your table since it’s the only truly unique way to identify different entries, so I’ll be using that as the example:
This statement will simply delete the first article that was saved to the table. Of course, you can modify the values after the WHERE keyword to delete based on title, publication, claps or author, but when it comes to having a larger table, this could cause some complications. Once something is deleted, you have to manually re-enter it, and that can be a pain. I always recommend deleting based on ID if possible.
Conclusion
And that’s all for today! Hopefully after today, you’re a lot more comfortable working with SQL and have a good idea on how to create/update/delete tables and manipulate/retrieve the data stored in them. Thanks for reading and happy coding!
