We can use the WHERE clause to filter information down to the rows that satisfy a certain condition(s). Run the following:
SELECT name,gender FROM biodata WHERE gender = 'Male';
Here only the rows that have the gender being exactly the word “Male” will be returned. By using !=, we can ask for the rows that do not match a certain condition, in this case the records with gender “Female”:
SELECT name,gender FROM biodata WHERE gender != 'Male';
Comparing numerical fields
SQL also allows us to do other comparisons such as $\gt$, $\lt$, $\ge$ and $\le$:
SELECT name,height FROM biodata WHERE height < 150;
SELECT name,height FROM biodata WHERE height >= 150;
The above statements will produce $2$ tables, one with the persons who have height less than $150cm$ and another with the persons whose heights are at least $150cm$.
Working with dates
SQL also allows date comparisons. Get the persons who were born before $1995$:
SELECT name,dob FROM biodata WHERE dob < '1995-01-01';
The default format for dates in SQL is YYYY-MM-DD.
Logical operators
The logical operators are NOT, OR and AND. Get the males who were born before $1995$:
SELECT name,dob FROM biodata WHERE dob < '1995-01-01' AND gender = 'Male';
To get the persons who were born before $1995$ or those who are at least $150cm$ in height:
SELECT name,dob FROM biodata WHERE dob < '1995-01-01' OR height >= 150;
For persons born before $1995$ or those who are not at least $150cm$ in height, we run:
SELECT name,dob FROM biodata WHERE dob < '1995-01-01' OR NOT height >= 150;
Parentheses can be used to specify the order of applying logical operators.
The LIKE operator
Sometimes we will want to have a partial match in a comparison e.g. when we want to get the names which contain a certain letter or a sequence of letters:
SELECT name, gender FROM biodata WHERE name LIKE '%a%';
SELECT name, gender FROM biodata WHERE name LIKE 'Jo%';
Here we get the persons whose names contain the letter ‘a’ and those whose names begin with the string “Jo”. Treat the % like match-all. It allows for any set of characters, zero ($0$) or more, to be used in its place in order for there to be a match. We can use this to get the persons whose birth year is a particular value:
SELECT name, dob FROM biodata WHERE dob LIKE '1999%';
The underscore (_) wildcard character allows for exactly one ($1$) character to be swapped for it.
SELECT name, gender FROM biodata WHERE name LIKE 'J_h%';
Reflection
We looked at:
- Getting rows that match a single condition
- Getting rows that match combinations of conditions
- Applying logical operators to produce complex logical conditions
- Using the LIKE operator to get rows that fulfill partial matches in the data