Lecture 7, This lecture covers the WHERE clause for filtering data.So far, we can select all data or specific columns from our table. But the real power of a database is finding very specific pieces of information. The WHERE clause is how we do this.
The WHERE clause is added to a SELECT statement to filter the results and show only the rows that match a certain condition.
Finding a Specific Friend
Imagine we want to find the phone number for our friend John. We don’t need to see the data for Sarah or Maria. We can use the WHERE clause to specify that we only want the row where the `Name` is ‘John‘.
SELECT PhoneNumber FROM Friends WHERE Name = 'John';Let’s analyze this command:
SELECT PhoneNumber: We are asking for the `PhoneNumber` column.FROM Friends: We are looking in the `Friends` table.WHERE Name = 'John': This is the filter. It tells the database to only consider rows where the value in the `Name` column is exactly equal to ‘John’.
The result of this query will be a single value: ‘555-2222’.
Using Other Operators
The equals sign (=) is not the only operator you can use. You can also use others, like:
<>or!=: Not equal to>: Greater than<: Less than>=: Greater than or equal to<=: Less than or equal to
These are most useful with numeric data. For example, let’s find all friends with an ID greater than 2.
SELECT * FROM Friends WHERE ID > 2;This query will return only the row for Maria, since her ID is 3.
Combining Conditions
You can even combine multiple conditions using AND and OR. For example, to find a friend named ‘John’ AND whose ID is 2, you would write:
SELECT * FROM Friends WHERE Name = 'John' AND ID = 2;In the Next Lecture…
We’ve seen the basic data types like TEXT and INTEGER. In the next lecture, we will take a more detailed look at the different data types available in SQLite and when to use each one.
