Coding truck

Sql select with multiple criteria

Previously we talked about the sql where clause and using it to filter selected data. In this post we will talk about how to specify multiple criteria in your where clause. We will use the Coding Truck menu practice.

SQL to create the table

Here is a link to the sql to create and populate the menu on SQL Fiddle

Create table Menu (
 Dish varchar(50)
 ,Price decimal(5,2)
);


Insert Into Menu(Dish, Price) Values ('Asada Fries', 0.99);
Insert Into Menu(Dish, Price) Values ('Barbacoa', 1.99);
Insert Into Menu(Dish, Price) Values ('Carnitas', 2.99);
Insert Into Menu(Dish, Price) Values ('Double Cheeseburger', 3.99);
Insert Into Menu(Dish, Price) Values ('Egg Rolls', 4.99);
Insert Into Menu(Dish, Price) Values ('Frites', 5.99);
Insert Into Menu(Dish, Price) Values ('Garlic Naan', 6.99);
Insert Into Menu(Dish, Price) Values ('Hamburger', 7.99);
Insert Into Menu(Dish, Price) Values ('Ice cream', 8.99);
Insert Into Menu(Dish, Price) Values ('Jalapeno Poppers', 9.99);
Insert Into Menu(Dish, Price) Values ('Kimchi', 9.99);
Insert Into Menu(Dish, Price) Values ('Lasagna', 0.99);
Insert Into Menu(Dish, Price) Values ('Milkshake', 1.99);
Insert Into Menu(Dish, Price) Values ('Noodles!', 2.99);
Insert Into Menu(Dish, Price) Values ('Octopus', 3.99);
Insert Into Menu(Dish, Price) Values ('Peach Cobbler', 4.99);
Insert Into Menu(Dish, Price) Values ('Quinoa Salad', 5.99);
Insert Into Menu(Dish, Price) Values ('Roast Beef Sandwich', 6.99);
Insert Into Menu(Dish, Price) Values ('Sausage on a Stick', 7.99);
Insert Into Menu(Dish, Price) Values ('Tacos', 8.99);
Insert Into Menu(Dish, Price) Values ('Udon', 9.99);
Insert Into Menu(Dish, Price) Values ('Vanilla Wafers', 0.99);
Insert Into Menu(Dish, Price) Values ('Water', 1.99);
Insert Into Menu(Dish, Price) Values ('Xiaolongbao', 2.99);
Insert Into Menu(Dish, Price) Values ('Yogurt (frozen)', 3.99);
Insert Into Menu(Dish, Price) Values ('Zirisk Pollo', 4.99);

Remember that you can use the following sql to view the table and check to make sure that it was created correctly.

Select * from Menu

Greater than, less than and equals

Before we talk about chaining criteria, let's talk about some new operators. So far we have compared values using equals, but equals is far from the only operator available to us. We also have greater than which returns values larger than a specified value. Let's look at menu items that cost more than $5.99.

Select * 
From Menu
Where
  Price > 5.99

As you can see, the database returned the 9 items that cost more than $5.99.

Dish Price
Garlic Naan 6.99
Hamburger 7.99
Ice cream 8.99
Jalapeno Poppers 9.99
Kimchi 9.99
Roast Beef Sandwich 6.99
Sausage on a Stick 7.99
Tacos 8.99
Udon 9.99

Similarly, the less than operator allows us to specify criteria that are smaller than a specified value. Let's look at menu items that cost less than $5.99.

Select * 
From Menu
Where
  Price < 5.99

Sure enough the database gave us the 15 items that cost less than $5.99

Dish Price
Asada Fries 0.99
Barbacoa 1.99
Carnitas 2.99
Double Cheeseburger 3.99
Egg Rolls 4.99
Lasagna 0.99
Milkshake 1.99
Noodles! 2.99
Octopus 3.99
Peach Cobbler 4.99
Vanilla Wafers 0.99
Water 1.99
Xiaolongbao 2.99
Yogurt (frozen) 3.99
Zirisk Pollo 4.99

Greater than and less than are useful, but let's not forget about specifying exact values. Equals is still super useful to return exact matches. Let's query the menu items that cost $5.99.

Select * 
From Menu
Where
  Price = 5.99

Notice that we didn't see these when we looked for items greater than or less than $5.99.

Dish Price
Frites 5.99
Quinoa Salad 5.99

So if you want to include the value in your results you can request rows with a value greater than or equal to a value. Here we request rows greater than or equal to our price point, $5.99.

Select * 
From Menu
Where
  Price >= 5.99

Notice our 11 results include the values from our greater than query and equals query.

Dish Price
Frites 5.99
Garlic Naan 6.99
Hamburger 7.99
Ice cream 8.99
Jalapeno Poppers 9.99
Kimchi 9.99
Quinoa Salad 5.99
Roast Beef Sandwich 6.99
Sausage on a Stick 7.99
Tacos 8.99
Udon 9.99

Notice how the operators can build on each other? We used greater than or equal to instead of having to combine the results of two queries. Finally, we will look at another combination. When you request items that are less than or greater than a value, the values you get are the ones that are not equal. Now, let's ask for the values that are not equal to $5.99.

Select * 
From Menu
Where
  Price <> 5.99

The database is happy to return all the rows in the menu whose price is not $5.99.

Dish Price
Asada Fries 0.99
Barbacoa 1.99
Carnitas 2.99
Double Cheeseburger 3.99
Egg Rolls 4.99
Garlic Naan 6.99
Hamburger 7.99
Ice cream 8.99
Jalapeno Poppers 9.99
Kimchi 9.99
Lasagna 0.99
Milkshake 1.99
Noodles! 2.99
Octopus 3.99
Peach Cobbler 4.99
Roast Beef Sandwich 6.99
Sausage on a Stick 7.99
Tacos 8.99
Udon 9.99
Vanilla Wafers 0.99
Water 1.99
Xiaolongbao 2.99
Yogurt (frozen) 3.99
Zirisk Pollo 4.99

Yes, and...

Now that we have looked at some other operators, let's look at how to combine criteria. The first way to combine criteria is to chain them together with the and keyword. The and takes two criteria and tells the database that both must be true. Let's look at items in the menu whose price is greater than $3.99 and less than $6.

Select * 
From Menu
Where
  Price > 3.99
  and Price < 6

The database is more than happy to show us the menu for items between $3.99 and $6.

Dish Price
Egg Rolls 4.99
Frites 5.99
Peach Cobbler 4.99
Quinoa Salad 5.99
Zirisk Pollo 4.99

Unfortunately, I'm not a fan of quinoa salad so I want to remove that from my results. I will use the and keyword to add an additional criteria that will exclude quinoa salad.

Select * 
From Menu
Where
  Price > 3.99
  and Price < 6
  and Dish <> 'Quinoa Salad'

The quinoa salad is gone from our results.

Dish Price
Egg Rolls 4.99
Frites 5.99
Peach Cobbler 4.99
Zirisk Pollo 4.99

Yes, or...

The and keyword is great for filtering down results because it requires both of the criteria to be true. The or keyword more relaxed. It is perfectly happy to return results if either of the associated criteria are true. Let's return menu items that or $8.99 or are asada fries.

Select * 
From Menu
Where
  Price = 8.99
  or Dish = 'Asada Fries'

The or keyword works like a charm. We can see that it returned ice cream and tacos (delicious!) because they cost $8.99. It also returned asada fries (also delicious) even though they are $0.99 because we also asked for them.

Dish Price
Asada Fries 0.99
Ice cream 8.99
Tacos 8.99

The and and or keywords are super helpful for getting very specific with the criteria you use in your sql statements. They allow you to hone in on exactly the data you are interested in.

Take a minute to play with them and see what you can do. How many criteria can you chain together? Did you get the results you expected?

Next