SQL Tutorial 2 : Additional criterias with the use of OR/IN/AND Functions

In reality, the dataset that we want to obtain often requires the application of much more criterias and filters than just 1. Building on what we explored in Topic 1, we will use new functions such as the “AND/OR/IN” functions to build on our “SELECT FROM WHERE” framework.

“OR” Function

Referring to the same ”world_data” dataset, if we want to extract extract population data of Albania and Angola,
(Assuming that the table name is ”world_data”)

We will write the following query :

SELECT population, name
FROM world_data
WHERE name = “Albania”
OR name = “Angola”

The output will be:

*Do note that it is important to SELECT name as well so that you can distinguish which population number belongs to Albania and which population number belongs to Angola. Otherwise, you will just have an output of 2 population numbers with no associated country, which does not make sense.

“IN” Function

Also, if we require data from multiple countries, we can consider using the “IN” function to replace the “OR” function. The same data can be extracted with the use of the “IN” function as shown below. The “IN” Function allows you to query with multiple criterias within the same column (ie A list of names within the “names” column).

The query will be :

SELECT population, name
FROM world_data
WHERE name IN (“Albania”, “Angola”)

*Think : If we need to manually add a comma to separate each criteria, isn’t it very tedious? Can we employ some other tools such as Excel to reduce the workload in this process?

“AND” Function

As for the use of the “AND” function, we will use the same dataset.
For instance, if we want to find gdp data for countries in Europe, but only for those with gdp above 12000000000.
We will write the following query :

SELECT gdp, name
FROM world_data
WHERE continent = “Europe”
AND gdp > 12000000000

The output will be:

Notice that the data for Andorra was not extracted. Although it fulfilled the criteria  of “Europe”, it did not fulfil the criteria for gdp being greater than 12000000000. This is the key learning point from the use of the “AND” function. This is very important in helping you pinpoint exactly what data to filter out.

Note that the use of quotation marks only applies for the “string” data type. This is why we do not need quotation marks for gdp figures in this case because gdp figures are “integers”. However, the fact that gdp figures are “numbers” does not necessarily mean that it is an “integer” data type, it really depends on how the data was entered and formatted into the database by the administrator. Usually, in the SQL Interface, the Data Type will be stated as shown below so do pay attention to it before querying.

Common Operators used for criteria setting

These are especially useful when you only want to extract the relevant data. For example, the top selling products or the most viewed products by a certain benchmark (ie more than 100 views etc). In a business sense, we often have to track the performance of products. For instance, products that are consistently below a certain value (ie less than 5 sales etc) will usually be highlighted for further action, which makes the use of these Operators in your query even more crucial.

Next :  SQL Tutorial 3 : Aggregation Functions