**Course Title:** SQLite Mastery: Lightweight Database Management **Section Title:** Basic Data Retrieval and Filtering **Topic:** Filtering data with WHERE, AND, OR, and NOT ### Introduction to Filtering Data In the previous topic, we learned how to retrieve data from SQLite databases using SELECT statements. However, sometimes we need to retrieve specific data based on certain conditions. This is where filters come in. In this topic, we will cover how to use WHERE, AND, OR, and NOT to filter data in SQLite. ### Using the WHERE Clause The WHERE clause is used to filter data based on a condition. The basic syntax of the WHERE clause is: ```sql SELECT column1, column2, ... FROM tablename WHERE condition; ``` In the condition, you can use operators such as: - = (equal to) - != (not equal to) - > (greater than) - < (less than) - >= (greater than or equal to) - <= (less than or equal to) **Example 1:** Retrieve all rows from the `employees` table where the `age` column is greater than 30. ```sql SELECT * FROM employees WHERE age > 30; ``` **Example 2:** Retrieve all rows from the `employees` table where the `name` column is equal to 'John Smith'. ```sql SELECT * FROM employees WHERE name = 'John Smith'; ``` ### Using the AND Operator The AND operator is used to combine multiple conditions. All conditions must be true for the row to be included in the result set. **Example 1:** Retrieve all rows from the `employees` table where the `age` column is greater than 30 and the `salary` column is greater than 50000. ```sql SELECT * FROM employees WHERE age > 30 AND salary > 50000; ``` **Example 2:** Retrieve all rows from the `employees` table where the `name` column is equal to 'John Smith' and the `department` column is equal to 'Sales'. ```sql SELECT * FROM employees WHERE name = 'John Smith' AND department = 'Sales'; ``` ### Using the OR Operator The OR operator is used to combine multiple conditions. Only one condition needs to be true for the row to be included in the result set. **Example 1:** Retrieve all rows from the `employees` table where the `age` column is greater than 30 or the `salary` column is greater than 50000. ```sql SELECT * FROM employees WHERE age > 30 OR salary > 50000; ``` **Example 2:** Retrieve all rows from the `employees` table where the `name` column is equal to 'John Smith' or the `department` column is equal to 'Sales'. ```sql SELECT * FROM employees WHERE name = 'John Smith' OR department = 'Sales'; ``` ### Using the NOT Operator The NOT operator is used to negate a condition. The row is included in the result set if the condition is not true. **Example 1:** Retrieve all rows from the `employees` table where the `age` column is not greater than 30. ```sql SELECT * FROM employees WHERE NOT age > 30; ``` **Example 2:** Retrieve all rows from the `employees` table where the `name` column is not equal to 'John Smith'. ```sql SELECT * FROM employees WHERE NOT name = 'John Smith'; ``` ### Conclusion In this topic, we learned how to use the WHERE, AND, OR, and NOT operators to filter data in SQLite databases. We saw how to use these operators to retrieve specific data based on conditions and how to combine multiple conditions using the AND and OR operators. ### Key Takeaways - The WHERE clause is used to filter data based on a condition. - The AND operator is used to combine multiple conditions. All conditions must be true for the row to be included in the result set. - The OR operator is used to combine multiple conditions. Only one condition needs to be true for the row to be included in the result set. - The NOT operator is used to negate a condition. The row is included in the result set if the condition is not true. ### Next Steps In the next topic, we will cover how to sort data using the ORDER BY clause. If you have any questions or need help with the material, please comment below. References: * SQLite Official Documentation: [https://www.sqlite.org/docs.html](https://www.sqlite.org/docs.html) * SQL Tutorial by W3Schools: [https://www.w3schools.com/sql/default.asp](https://www.w3schools.com/sql/default.asp) **Leave a comment or ask for help below if you have any questions.**