WK4 The WHERE clause and basic operators

Question 1

WHERE

Answer 1

WHERE indicates the condition for a filter.

SELECT firstname, lastname, title, email
FROM employees
WHERE title = ‘IT Staff’;

WHERE clause instructs SQL to return only those that contain ‘IT Staff’ in the title column. It uses the equals sign (=) operator to set this condition.

Question 2

Semicolon

;

Answer 2

Place at the end of a SQL query

Question 3

Filtering for patterns

Answer 3

You can also filter based on a pattern. For example, you can identify entries that start or end with a certain character or characters. Filtering for a pattern requires incorporating two more elements into your WHERE clause:

• a wildcard
• the LIKE operator

Question 4

wildcard

Answer 4

A wildcard is a special character that can be substituted with any other character. Two of the most useful wildcards are the percentage sign (%) and the underscore (_):

The percentage sign substitutes for any number of other characters.

The underscore symbol only substitutes for one other character.

These wildcards can be placed after a string, before a string, or in both locations depending on the pattern you’re filtering for.

The following table includes these wildcards applied to the string ‘a’ and examples of what each pattern would return.

Question 5

wildcard table

e.g to the string ‘a’

Answer 5

Pattern. .Results to be returned

‘a%’ apple123, art, a

‘a_’ as, an, a7

‘a__’ ant, add, a1c

‘%a’ pizza, Z6ra, a

‘_a’ ma, 1a, Ha

‘%a%’ Again, back, a

‘a’ Car, ban, ea7

Question 6

LIKE

Answer 6

To apply wildcards to the filter, you need to use the LIKE operator instead of an equals sign (=). LIKE is used with WHERE to search for a pattern in a column.

For instance, if you want to email employees with a title of either ‘IT Staff’ or ‘IT Manager’, you can use LIKE operator combined with the % wildcard:

SELECT lastname, firstname, title, email
FROM employees
WHERE title LIKE ‘IT%’;

As another example, if you want to search through the invoices table to find all customers located in states with an abbreviation of ‘NY’, ‘NV’, ‘NS’ or ‘NT’, you can use the ‘N_’ pattern on the state column:

SELECT firstname,lastname, state, country
FROM customers
WHERE state LIKE ‘N_’;

Key takeaways

Filters are important when refining what your query returns. WHERE is an essential keyword for adding a filter to your query. You can also filter for patterns by combining the LIKE operator with the percentage sign (%) and the underscore (_) wildcards.