Intermediate SQL - Filtering Records Flashcards
Learn about how you can filter numerical and textual data with SQL. Filtering is an important use for this language. You’ll learn how to use new keywords and operators to help you narrow down your query to get results that meet your desired criteria and gain a better understanding of NULL values and how to handle them.
What is the filtering clause keyword called?
WHERE - we may want to select a coat/record from the closet/table where the color is green
What are 6 comparison operators we can use with WHERE to filter numbers?
1) less than (<)
2) less than, equal to (<=)
3) equal to (=)
4) not equal to (<>)
5) greater than, equal to (>=)
6) greater than (>)
What comparison operator can be used with WHERE to filter strings?
= with single quotes around string we want to filter
Does WHERE come before or after the FROM statement?
After
What is the order of execution on this query?
1) FROM
2) WHERE
3) SELECT
4) LIMIT
We go to the closet we want to get the coat from, find where the green coats are, and select five of them.
What are three keywords that allow us to enhance our filters when using WHERE by adding multiple criteria?
1) OR
2) AND
3) BETWEEN
When do we use the OR operator with WHERE?
when we want to filter multiple criteria and only need to satisfy at least one condition (example, green or purple coat options)
True/False - We must specify the field for each OR condition in order for the query to run.
True - in the example, the invalid query has not specified what field or operator should be associated with the year 2000
When do we use the AND operator with WHERE?
when we want to satisfy all criteria in our filter
True/False - We do not need to specify the field for each AND condition in order for the query to run.
False - we must specify the field name separately for each AND condition as with OR
True/False - If a query has multiple filtering conditions, we will need to enclose the individual clauses in parentheses to ensure the correct execution order; otherwise, we ma not get the expected result.
True - enclose individual clauses in parenthesis
What is a operator, other than AND, that we can use to specify values in a specific range?
BETWEEN
The BETWEEN operator is inclusive - this means the results contain what?
results contain the beginning and end values
True/False - Like the WHERE clause, the BETWEEN clause can be used with multiple AND and OR operators.
True
What three operators will help us filter a pattern rather than specific text?
1) LIKE
2) NOT LIKE
3) IN
What is the LIKE operator used for?
used to search for a pattern in a field
What two wildcards are used with LIKE as a placeholder for some other value?
1) % match zero, one, or many characters
2) _ underscore match a single character
What operator can we use to find records that don’t match the specified patter?
NOT LIKE - in this query below, we are looking for people who do not have A. as part of their first name
True/False - Wildcards can be put anywhere and can be combined to find values that start, end, or contain characters in any position, as well as find records of certain length.
True
What will this sample of code return?
all people whose name ends in r
What will this sample of code return?
all records where the third character is t
True/False - We could chain several ORs to the WHERE clause if we want to filter based on many conditions or a range of numbers.
True - but it can get messy doing it this way
What operator allows us to specify multiple values in a WHERE clause, making it easier and quicker to set numerous OR conditions?
IN
True/False - The WHERE IN operator can not be used with text fields.
False
True/False - The COUNT(field_name) includes only non-missing values.
True
True/False - The COUNT(*) does not include missing values.
False - COUNT(*) does include missing values
In SQL, what does NULL represent?
a missing or unknown value
What are 3reasons we may have missing values in a table?
1) human error
2) information not available
3) unknown
What operator can we use with the WHERE clause to quickly see how much of our data is missing?
IS NULL - in the example here, we have checked to see which names do not have a recorded birthdate in our table
What operator can we use with the WHERE clause to quickly see how much of our data is NOT missing?
IS NOT NULL - in the example the query on the right gives the count of all people whose birth dates are not missing in the people table
