WK4 Basic filters on SQL queries

Question 1

Filtering

Answer 1

Filtering is selecting data that match a certain condition

Question 2

Operators

Answer 2

An operator is a symbol or keyword that represents an operation.

Question 3

Operator…

=

Answer 3

if we wanted to find all records that have USA in the country column, we use

country = ‘USA’

Question 4

Operator…

WHERE

Answer 4

To filter a query in SQL, we simply add an extra line to the SELECT and FROM statement we used before. This extra line will use a WHERE clause.

In SQL, WHERE indicates the condition for a filter. After the keyword WHERE, the specific condition is listed using operators.

So if we wanted to find all of the login attempts made in the United States, we would create this filter. In this particular condition, we’re indicating to return all records that have a value in the country column that is equal to USA.

We’re going to start with selecting all the columns FROM the log_in_attempts table. And then add the WHERE filter. Don’t forget the semicolon! This tells us we finished the SQL statement. Now, let’s run this query! Because of our filter, only the rows where the country of the log-in attempt was USA are returned.

Question 5

SQL Filtering

Searching for a pattern instead of an exact word

Answer 5

We can also make our conditions more complex by searching for a pattern instead of an exact word. For example, in the employees table, we have a column for office. We could search for records in this column that match a certain pattern. Perhaps we might want all offices in the East building.

To search for a pattern, we used the percentage sign to act as a wildcard for unspecified characters. If we ran a filter for…
‘East%’,

this would return all records that start with East – for example, the offices East-120, East-290, and East-435.

Question 6

Operator…

LIKE

Answer 6

When searching for patterns with the percentage sign, we cannot use the equals operator. Instead, we use another operator, LIKE. LIKE is an operator used with WHERE to search for a pattern in a column.

Since LIKE is an operator, similar to the equal sign, we use it instead of the equal sign. So, when our goal is to return all values in the office column that start with the word East, LIKE would appear in a WHERE clause.

Some entries use US while others use USA…

SELECT*
FROM log_in_attempts
WHERE country LIKE ‘US%’;