Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
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