0503 | More SQL Filters

Filter dates and numbers

• example | SELECT * FROM log_in_attempts WHERE time > '18:00';

String data

• data consisting of an ordered sequence of characters
• characters could be numbers, letters, or symbols

Numeric data

• data consisting of numbers
• mathematical operations can be used on numeric data, like multiplication or addition

Date and time data

• data representing a date and/or time

Common operators

• =; >; <; <> (not equal to) or !=; >=; <=

BETWEEN

• an operator that filters for numbers or dates within a range
• used for numeric data as well as date and time data
• inclusive operator
• example | SELECT * FROM machines WHERE OS_patch_date BETWEEN '2021-03-01' AND '2021-09-01';

Operators for filtering dates and numbers

Exclusive operator

• an operator that does not include the value of comparison
• example | >

Inclusive operator

• an operator that includes the value of comparison
• example | >=

Filters with AND, OR, and NOT

AND

• specifies that both conditions must be met simultaneously
• example | SELECT * FROM machines WHERE operating_system = 'OS 1' AND email_client = 'Email Client 1';

OR

• specifies that either condition can be met
• example | SELECT * FROM machines WHERE operating_system = 'OS 1' OR operating_system = 'OS 3';

NOT

• negates a condition
• only works on a single condition, and not on multiple ones
• example | NOT | SELECT * FROM machines WHERE NOT operating_system = 'OS 3';
• example | <> | SELECT * FROM machines WHERE operating_system <> 'OS 3';
• example | != | SELECT * FROM machines WHERE operating_system != 'OS 3';

More on filters with AND, OR, and NOT

Logical operators

• AND, OR, and NOT allow you to filter your queries to return the specific information

Combining logical operators

• logical operators can be combined in filters
• example | SELECT firstname, lastname, email, country FROM customers WHERE NOT country = 'Canada' AND NOT country = 'USA';