SQL Series Pt. 2
This is the second part in my series teaching the basics of SQL. As a reminder in Part 1 we went over the a few of the most common keywords that are used to query a database. These keywords are SELECT, FROM, LIMIT, and COUNT, if a refresher is needed please follow the link at the bottom of this page to review those keywords.
WHERE and Comparison operators
We will now take a step deeper into SQL by introducing the concept of filtering. Say for example we wanted to take our database from part 1 (the New York Mets roster) and find all of the players who were over the age of 25, how would we go about doing this? For this we will have to introduce the WHERE keyword, which will allow us to filter our results based on text and numeric values. We will also have to introduce a few of the basic comparison operators which will need to be used in conjunction with the WHERE keyword, these are as follows.
- = equal to
- != not equal to
- < less than
- > greater than
- <= less than or equal to
- >= greater than or equal to
Back to our example, to filter our database to any player over the age of 25 we will first select the columns we want to display, in our case we will use * to choose all columns. Then we will choose what database we will choose the columns from, which in our case is the baseball database, and finally we will use WHERE, the age column, and the greater than operator to filter the results that will be displayed. Putting this all together the following code will return all players that are over the age of 25, remember to always use WHERE after the FROM keyword.
The above code will filter our NY Mets table and display only the players that are over the age of 25, which can be see below. Notice that we had a player that was 25, if we had wanted to include that player in our findings we simply would have changed the greater than operator to a greater than or equal to operator. We can also use WHERE to filter based on text values. Say we wanted to display players whose first name was Pete, we would follow the same steps as before but in our WHERE clause we would use the equal to operator and put in the text we wanted to compare to. It is very important to make sure when we are comparing text values that we must wrap the text in single quotes or else we will not get the correct result.
AND and OR
Many times we will want to select data based on multiple conditions, we can do this by using the AND keyword. This is a pretty simple step forward but will allow us to build upon our WHERE queries by adding an extra condition to filter on or as many as we car to type out. The only thing we need to worry about when we use the AND keyword is that we will have to specify each time the column name that we want to compare, even if two conditions use the same column. For example if we wanted to find all of the players whose age was over 25 and under 35 our code would look as follows.
BETWEEN
The above query was simple enough to type out but we can make this type of query even easier by utilizing the BETWEEN keyword. We can accomplish the same result as above with the following code. It’s important to note that when we use BETWEEN the results are inclusive, meaning that whatever the beginning and ending values are will be shown in our result.
Conclusion
Filtering our tables to display only certain things that we need is an essential part of utilizing this language. We have added four more keywords that can be used to query our databases which are as follows, WHERE, AND, OR, BETWEEN, as well as basic comparison operators that are used in conjunction with the WHERE keyword. We can already see that with just a few keywords we can do so many different things with SQL. In my next post we will learn about aggregate functions as well as sorting and grouping our results.
