WK4 Filters with AND, OR and NOT

Question 1

Logical operators

Answer 1

AND, OR, and NOT allow you to filter your queries to return the specific information that will help you in your work as a security analyst. They are all considered logical operators.

Question 2

AND

Answer 2

AND is used to filter on two conditions. AND specifies that both conditions must be met simultaneously.

SELECT firstname, lastname, email, country, supportrepid
FROM customers
WHERE supportrepid = 5 AND country = ‘USA’;

Question 3

OR

Answer 3

OR operator also connects two conditions, but OR specifies that either condition can be met. It returns results where the first condition, the second condition, or both are met.

SELECT firstname, lastname, email, country
FROM customers
WHERE country = ‘Canada’ OR country = ‘USA’;

Note: Even if both conditions are based on the same column, you need to write out both full conditions. For instance, the query in the previous example contains the filter WHERE country = ‘Canada’ OR country = ‘USA’.

Question 4

NOT

Answer 4

Unlike the previous two operators, the NOT operator only works on a single condition, and not on multiple ones. The NOT operator negates a condition. This means that SQL returns all records that don’t match the condition specified in the query.

For example, if a cybersecurity issue doesn’t affect customers in the USA but might affect those in other countries, you can return all customers who are not in the USA. This would be more efficient than creating individual conditions for all of the other countries. To use the NOT operator for this task, write the following query and place NOT directly after WHERE:

SELECT firstname, lastname, email, country
FROM customers
WHERE NOT country = ‘USA’;

Pro tip: Another way of finding values that are not equal to a certain value is by using the <> operator or the != operator. For example, WHERE country <> ‘USA’ and WHERE country != ‘USA’ are the same filters as WHERE NOT country = ‘USA’.

Question 5

Combining logical operators

Answer 5

Logical operators can be combined in filters. For example, if you know that both the USA and Canada are not affected by a cybersecurity issue, you can combine operators to return customers in all countries besides these two. In the following query, NOT is placed before the first condition, it’s joined to a second condition with AND, and then NOT is also placed before that second condition. You can run it to explore what it returns:

SELECT firstname, lastname, email, country
FROM customers
WHERE NOT country = ‘Canada’ AND NOT country = ‘USA’;

Key takeaways

Logical operators allow you to create more specific filters that target the security-related information you need. The AND operator requires two conditions to be true simultaneously, the OR operator requires either one or both conditions to be true, and the NOT operator negates a condition. Logical operators can be combined together to create even more specific queries.