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

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

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

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

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

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store