Lesson 23

How to Retrieve Filtered Data Using SQL WHERE Clause

Learn how to use Structured Query Language (SQL) to select rows from a table using a filter that matches a certain condition.

You are introduced to the WHERE clause to narrow down the result rows in a query. You can specify the column name, a comparison operator, and the desired value.

You also learn that you can narrow down even more with more conditions using the AND keyword. Later you are also briefly exposed to its counterpart OR keyword.

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72

Summary

Summary of Transcript on SQL Queries

In this discussion, the speaker explains how to perform SQL queries to retrieve specific data from a 'customers' table using the WHERE clause. Here’s a breakdown of the key points:

Using the `WHERE` Clause

The WHERE clause allows filtering of records based on certain conditions.
To find customers with the last name "Dough", you would write:
```
SELECT * FROM customers WHERE last_name = 'Dough';
```
The query returns only the rows where the last name matches "Dough".

Case Sensitivity

The speaker emphasizes that SQL queries are case-sensitive; using "john" instead of "John" will yield no results.

Combining Conditions

To filter results further, you can add additional conditions using AND.
- Example to find a specific person, John Dough:
```
SELECT * FROM customers WHERE last_name = 'Dough' AND first_name = 'John';
```

String Literals

Strings must be enclosed in single quotes to differentiate them from SQL keywords or variables.

Numeric Comparisons

For numeric values, such as customer IDs, quotes are not needed:
```
WHERE customer_id = 3;
```

Multiple Conditions

You can use OR to include multiple possibilities for a condition:
- Example to find either "Dough" or another name:
```
WHERE first_name = 'John' AND (last_name = 'Dough' OR last_name = 'Run');
```

Conclusion

The session concludes by summarizing the ability to filter and combine multiple conditions in SQL queries, highlighting the importance of case sensitivity, string formats, and the correct use of logical operators.

Video Transcript

Okay, let's talk about looking somebody up because so far we're always querying all the rows. And that's not really what we do. Let's say I want to find information about somebody who might have the last name dough. So let me put star back so I want all the columns from customers. And I'm going to teach you about the where clause. So we can add after the from, we can add where if you want to narrow it down to specific rows. Like I want to say where the last name is equal to dough. So what this means, okay, I'm going to select the rows from the table customers, all the columns, but I only want the ones that have last name equal to dough here. Click run. And you can see there's two people, two customers. Okay. You can see their last name is dough. So where is very common. Don't forget, you can just say the column name here and equal sign means to compare. Okay. Some programming languages, this is like assignment, but this is not the case here. This is actual comparison. Okay, but what if I want to look out by first name as well? Okay, I found two customers. Actually I just wanted to find out about John. So how would I narrow down it even more to just a single person? Well I have to say and another comparison. In this case, first name equals to John. Now I'm going to do something here real quick. I'm going to put lowercase j. Let's see what happens. Well I got nobody, no rows. Why? I'm going to type lowercase j here and that's different from uppercase j. So be careful, the case matters in this case. Think about that. Okay, so you can basically follow the same pattern and keep saying and column name equals something and column names equals something. Now I might have noticed I put quotes around the name. All right. Why did I put quotes? It's called the string in programming language. Because I want to literally say the word or the string of characters, d-o-e. If I put it without, it might think it's something else. Usually in programming languages we have the reserved keywords like the select here and the from and where. They're special keywords and if I put it without quotes it's going to try to think it's a special keyword or some kind of variable or something. So let's make sure if you have the literal text you have to add the single quotes around it in the beginning and the end. Okay somebody asked, can you compare by ID? Yes, you can compare anything. If you want to say by ID, you just say customer ID equals some number. Now in the case of number you probably don't need to say the quotes around it. For example, let me just do it real quick. Where the customer ID is, say I want to find the customer ID 3. Let me see. As you can see on the left hand side it tells me what's the kind or type of the column. Customer ID in this case is int. Int is short for integer. So in that case I don't have to have quotes. I had to have quotes for the other one because it's a vercare. Being character varying string of 100. And there you go. In this case because it's a int, a number integer, I don't have to have the quotes. That as you can see I can find by ID. Yes, it's called case sensitive. Somebody said yeah, the case matters. They commented what if you wanted John and John. So if you want these two, so first you would have to say first name John to get both of them right. Now that could be a third John. Do you care if there's a third one? If you care about the third one and you specifically just want these two even though they're by the other John's then you got to narrow it down even more. But you can say like this. Instead of saying n, you can add the, let me show you real quick. That's a little bit nested things. So and the last name is either do or last name is run. Let's see if that works. Same way. So this case I have to say okay, first name is John and something else and the something else is I have to use a r here, which is like the opposite of n in a way. The last name is either do or last name is that one. So that one you just captured those two assuming they're Marjans.