Learn to filter database rows with WHERE clauses using comparison operators, BETWEEN, IN, and NULL checks, then sort results with ORDER BY. Examples use real tables like customers and movies to show combining conditions with AND/OR and ordering by multiple columns.
Chapters
A main part of querying data is to filter those records which are relevant for a certain research question. For future applications, it is also essential to bring tables in a particular order. When a where clause is added to the SQL statement, certain rows of a table are selected where a selection condition holds. This example query selects all columns from the customers table only for customers from Italy. By using where country is Italy we can see the first few selected rows of the resulting table. Several operators can be used in the where clause, such as comparison operators which compare the value of a column to a given value. These can check if the values are equal or not, or whether one is greater or less than the other. Then there is the between operator, the in operator, and the null operator. Let's illustrate this with some examples. Here are examples of comparison operators in the first example we select all columns from movies where the genre is not drama. In the second example we select those movies with a renting price greater than or equal to 2. Let us now look at the between operator. We select all columns of customers where the date when the account was created is between the beginning of January and the end of September 2018. 1 January and 31 September are included in this selection. Pay special attention to the way dates are used here. Dates can be compared if they are before or after a given date, but it is necessary to use single quotation marks as for strings, unless explicitly specified otherwise in the settings, or only single quotes are accepted in most SQL flavors. Using double quotes will give an error in postgres. With the in operator, we can compare the values of a column to multiple values. Here we compare the nationality of actors to both USA and Australia. If the nationality is either USA or Australia, then the actor is selected. Null values are missing or unknown values in the data. We already know that not every customer gives a rating after watching a movie. Therefore, there are many null values in the column rating. With the statement where rating is null, we select all records where the customers didn't give a rating, whereas with the statement where rating is not null, we select all records where the customers gave a rating with the statement and. You can connect two or more conditions in a where expression. Here we ask for customers who are from Italy and and have created their movienow account between the beginning of January and the end of September 2018. It is also possible to use the Boolean operator or to connect two conditions. Then all rows are selected where one of the two conditions hold. An alternative to connecting conditions in the where clause is is to use the or operator. Here we select all customers who are from Italy or who created their account between the given dates by using the order by statement. SQL allows us to order the results of a query by the values of one or more columns. In this example, we order by rating. By default, the order is ascending. To see the best ratings first, we can change to descending order by using Desk. This shows us that the first five rows all have the best rating. 10. Now it's time to put all this in.
