Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Start a free Courses trial
to watch this video
The real power of SQL comes from filtering rows of information. You've filtered columns, now you're going to filter rows.
SQL Used
A WHERE Clause
SELECT <columns> FROM <table> WHERE <condition>;
Equality Operator
Find all rows that a given value matches a column's value.
SELECT <columns> FROM <table> WHERE <column name> = <value>;
Examples:
SELECT * FROM contacts WHERE first_name = "Andrew";
SELECT first_name, email FROM users WHERE last_name = "Chalkley";
SELECT name AS "Product Name" FROM products WHERE stock_count = 0;
SELECT title "Book Title" FROM books WHERE year_published = 1999;
Inequality Operator
Find all rows that a given value doesn't match a column's value.
SELECT <columns> FROM <table> WHERE <column name> != <value>;
SELECT <columns> FROM <table> WHERE <column name> <> <value>;
The not equal to or inequality operator can be written in two ways != and <>. The latter is less common.
Examples:
SELECT * FROM contacts WHERE first_name != "Kenneth";
SELECT first_name, email FROM users WHERE last_name != "L:one";
SELECT name AS "Product Name" FROM products WHERE stock_count != 0;
SELECT title "Book Title" FROM books WHERE year_published != 2015;
See all of the SQL used in SQL Basics in the SQL Basics Cheat Sheet.
Remember, the syntax for retrieving all information from a table, but 0:00 there's more that you can do with the SELECT statement. 0:04 We can search to retrieve the information we want by using a WHERE clause. 0:07 We create a WHERE clause with the word keyword followed by a condition. 0:12 Let's take a closer look at what a condition looks like. 0:16 First, you have the column name, then a special character or 0:20 set of characters called an operator. 0:24 And finally, you have the value. 0:26 Let's take a look at our library example again. 0:29 Let's start with a question. 0:32 What are all the titles and authors of the books in the library published in 1997? 0:34 As you can see, when we run this query J.K. 0:40 Rowling's first Harry Potter book is retrieved. 0:42 Let's take a quick look at what the database did with this query. 0:45 It used the condition in the where clause to see if the first underscore 0:48 published column had the value of 1997. 0:53 If it did, it returned to the title and author. 0:56 Since there is only one entry that matches this condition in the books table, 1:02 we only got one result back. 1:08 We also didn't need to include the first_published column in 1:10 the select section of our query. 1:15 We didn't need to include the column we were testing there. 1:17 This is particularly handy when you're generating reports and 1:21 keeping quota to a minimum. 1:24 Also, keeping the data to a minimum in our reports maximizes the efficiency 1:26 of the report being returned. 1:31 See how we use this equal sign? 1:34 This is known as the equality operator. 1:35 We use that to test of the contents in the column were equal to the value of 1997. 1:38 Note that we didn't include quotes around 1997, since it's a numeric type. 1:45 Numeric types will never have spaces in them, 1:51 so just writing the number is all that's needed. 1:54 Now you might remember we have a date data type even though 1997 is a year, 1:57 we're storing it as an integer here. 2:02 Because we don't need a day, month or 2:05 a specific time to be stored in our database. 2:08 We'll look at a date example shortly. 2:11 Let's take a look at what the database does when running this query. 2:14 Here's all the information in the books table. 2:18 When the query is executed, 2:21 each row is processed to see if it matches the condition after the word keyword. 2:23 Let's see this first row. 2:29 The value in the first_published column happens to be 1997. 2:30 It's a match, so it'll be returned, then every other row is evaluated. 2:35 In this case, all of the rows don't match. 2:41 Only the selected columns from the first row are returned. 2:45 Let's modify this query to answer another question. 2:50 What are all the books authored by J.K Rowling? 2:53 And what year were they first_published? 3:01 Since the author is of a text type, we must use quotes around the value. 3:04 If we didn't include quotes, a syntax error would occur. 3:11 See how this has now retrieved all books by J.K. Rowling? 3:15 She's a busy lady. 3:18 An important thing to realize is that the equality operator is strict. 3:20 Meaning, if I were to search for J.K. Rowling with a lower case 3:25 r, It would return no results. 3:29 In other words, the equal to operator is case sensitive. 3:34 In other programming languages, the equal operator is a double equal sign. 3:39 With SQL, it's just one. 3:44 Let's ask another question. 3:46 Let's bring back books that are not by J.K. Rowling. 3:48 We can use the not equal to or inequality operator to do this. 3:52 We write the inequality operator by using an exclamation mark before the equal sign. 3:57 Let's bring back the title, author and first_published date for 4:02 all books that are not written by J.K. Rowling. 4:08 Wow, J.K. Rowling has a lot of books in our library. 4:12 Other authors haven't been anywhere near as busy. 4:15 We've searched using numbers with the first_published column and 4:18 text with the author. 4:23 The next type we can search by is date. 4:24 In our library database, we have a loans table. 4:27 Let's just do a quick select statement to see what we've got available for columns. 4:30 There are three date columns, loaned_on, return_by and returned_on. 4:39 We can ask all sorts of questions related to a particular date. 4:45 For example, what books are loaned on the 10th of December 2015? 4:50 First, we select the book_id 4:56 FROM the loans table WHERE loaned_on 5:01 = "2015-12-10" 5:07 with quotes around it. 5:13 The query returns the book with the ID of 15, but which book is book 15? 5:17 We can figure it out if 5:23 we SELECT title FROM books 5:28 WHERE id is = 15. 5:34 The book with the id of 15 is Emma. 5:39 Remember, I mentioned before that the ID is a unique identifier for 5:45 a row entry in a table. 5:50 People who design databases often use the ID in other tables, 5:51 these are known as foreign keys. 5:56 Let's look at the loans table again. 5:58 You can see that the book_id and 6:01 the patron_id are being used in the loans table. 6:04 These are foreign keys. 6:07 Instead of duplicating information from the book or the patron tables using the ID 6:09 serves on duplication and disc space, because the data is tightly coupled, 6:14 you'll often hear that these databases are referred to as relational databases, 6:20 because the data has relationships with other data. 6:25 These relationships are where a lot of the power and 6:29 complexity come from when writing more advanced SQL queries. 6:32 We'll take a look at those types of queries in another course. 6:36 So to recap, this is the syntax for 6:40 writing queries for searching for an exact match of a value. 6:42 You do your regular select from query with the WHERE keyword followed by condition. 6:47 The condition is a column name, an operator and a value. 6:53 When you want to find an exact match, we use the equality operator, 6:57 which is represented by an equal sign. 7:01 To find all non-matching entries, you'd use the inequality operator, 7:04 the exclamation mark followed by an equal sign. 7:09 You also don't need to list the column you included in the condition or 7:13 the work course in your columns list. 7:16 Finally, if you're using a numeric value, 7:19 you don't need to use any quote marks for the text types and date types you do. 7:22
You need to sign up for Treehouse in order to download course files.
Sign up