OverviewTeaching: 20 min
Exercises: 10 minQuestions
How can I filter data?Objectives
Write queries that
SELECTdata based on conditions, such as
Understand how to use the
WHEREclause in a statement.
Learn how to use comparison keywords such as
LIKEin a statement.
SQL is a powerful tool for filtering data in databases based on a set of conditions. Let’s say we only want data for a specific ISSN, for instance, for the Acta Crystallographica journal from the
articles table. The journal has an ISSN code
2056-9890. To filter by this ISSN code, we will use the
SELECT * FROM articles WHERE ISSNs='2056-9890';
We can add additional conditions by using
NOT. For example, suppose we want the data on Acta Crystallographica published after October:
SELECT * FROM articles WHERE (ISSNs='2056-9890') AND (Month > 10);
Parentheses are used merely for readability in this case but can be required by the SQL interpreter in order to disambiguate formulas.
If we want to get data for the Humanities and Religions journals, which have ISSNs codes “2076-0787” and “2077-1444”, we can combine the tests using OR:
SELECT * FROM articles WHERE (issns = '2076-0787') OR (issns = '2077-1444');
When you do not know the entire value you are searching for, you can use comparison keywords such as
IS NULL. For instance, we can use
LIKE in combination with
WHERE to search for data that matches a pattern.
For example, using the
articles table again, let’s
SELECT all of the data
Subject contains “Crystal Structure”:
SELECT * FROM articles WHERE Subjects LIKE '%Crystal Structure%';
You may have noticed the wildcard character
%. It is used to match zero to many characters. So in the SQL statement above, it will match zero or more characters before and after ‘Crystal Structure’.
Let’s see what variations of the term we got. Notice uppercase and lowercase, the addition of ‘s’ at the end of structures, etc.
To learn more about other comparison keywords you can use, see Beginner SQL Tutorial on SQL Comparison Keywords.
Write a query that returns the
Yearfor all papers where
Subjectscontains “computer” and that have more than 4 citations.
SELECT Title, First_Author, Subjects, ISSNs, Month, Year FROM articles WHERE (Subjects LIKE '%computer%') AND (Citation_Count > 4);
You can continue to add or chain conditions together and write more advanced queries.
WHEREto filter and retrieve data based on specific conditions.
AND, OR, and NOTto add additional conditions.
Use the comparison keyword
LIKEand wildcard characters such as
%to match patterns.