Filtering Your Data

Episode #3 of the course Intro to SQL by Mike Schmitt

 

We now know how to view the data in our database tables, but what if we don’t need every record returned with a query? What if we only need a portion of the records on the table? With SQL, we can filter what is returned with a WHERE clause.

Let’s use our online retailer example and look at their inventory stored on the following Inventory table:

A handy and common feature on many websites is a search bar. A search function allows you to find out whether an item you’re looking for is in inventory and available for purchase. For example, let’s say we are interested in purchasing pants, so we type ‘Pants’ in our search. Behind the scenes, an SQL query is run against the database that looks something like this:

SELECT * FROM Inventory WHERE Type=’Pants’;

We are familiar with the first half of the syntax, which is our basic SELECT, but there is now an additional WHERE clause following the table name. This acts as a filter to limit the returned results to what matches the filtering criteria. The results are limited to those where the value in the Type column is equal to ‘Pants.’ Our display will look like so:

Note that the value in our query is contained in single quotes. This is required for any data type aside from a number, so any text or dates.

Now what if we need to further refine our search with additional criteria? Not a problem! You can append any number of additional filtering statements by adding the additional criteria separated by an AND. Here’s an SQL example that further limits our results to those with a color of ‘Gray’:

SELECT * FROM Inventory WHERE Type=’Pants’ AND Color=’Gray’;

And the query result set:

Now we know how all those websites can bring up exactly what we’re looking for!

Explore on your own with an SQL Fiddle of today’s lesson! The database has already been built for you, so you can test your queries in the panel on the right-hand side!

 

Recommended book:

“SQL: The Ultimate Beginner’s Guide” by Andrew Johansen

 

Share with friends