SQL Part 5

Let us continue with a few more logical operators in this blog post.

OR

The OR logical operator in SQL allows you to select rows that meet at least one condition out of multiple conditions. It works similarly to the AND logical operator, where multiple conditions must be met. Let us try out the OR logical operator on the ramen database.

SELECT *
     FROM ramenratings
     WHERE stars = 5
     OR country = ‘Thailand’

The query above selects rows where the ramen is given a rating of 5 or from Thailand. The OR operator can be used in conjunction with other operators including the AND operator to write queries with more specifications.

SELECT *
    FROM ramenratings
    WHERE stars = 5
    AND (Brand LIKE ‘C%’ OR Brand LIKE ‘Z%’)

The above query selects ramens with a rating of 5 and begins with either the letter C or Z.

NOT

The NOT logical operator can be put in front of any conditional statements to select rows for which the condition is false.

SELECT *
    FROM ramenratings
    WHERE stars NOT between 2 AND 3

The above statement selects ramens where the ratings are over 3 and under 2. Using > and < operators along with the NOT logical operator is not done because the opposite operator can just be used.

NOT and LIKE can be used together to filter out specific data from the table. For example the query below.

SELECT *
    FROM ramenratings
    WHERE brands NOT LIKE ‘N%’

The query filtered out ramens made by brands starting with the letter N. It can also be used to select rows that don’t contain NULL values by writing IS NOT NULL.

ORDER BY

The ORDER BY clause is already shown in the previous blog posts. It is used to sort the data by a specific column. By default, it will order the rows in ascending order. In order to sort it by descending order, you have to write DESC after the clause.

SELECT *
      FROM ramenratings
      ORDER BY brands DESC

Multiple columns could be used to order the rows. The query below ordered the rows first by the country in descending order and then by the stars rating in ascending order.

SELECT brand,
       style,
       country,
       stars
       FROM ramenratings
       ORDER BY country DESC, stars

When the names of the columns are long and you are ordering by multiple columns, the query can become very long to write out. To make things easier, you can simply substitute the names of the columns with corresponding numbers as shown below.

SELECT brand,
       style,
       country,
       stars
       FROM ramenratings
       ORDER BY 3 DESC, 4

The 3 and 4 in the above query are simply referring to the corresponding order of columns being called. The 3 refers to country and the 4 refers to stars.

When ORDER BY clause is used together with LIMIT, the order query is executed first before the results are limited to a specified number of rows.

In the next blog post, I will be going over aggregate functions available in SQL. The functions are the same ones you would find in other analytics software and programs including Excel.