Day 2 – Filter Your Query using Where

For test exercise we’ll use the PizzaDB. You can find link to the database script on GIT.

You don’t need to have a database installed to do these exercise, just use the prompts below.

In these exercise we’ll write some query using the Employee table. It has an EmployeeID columns in addition to the FirstName and LastName columns.

Exercise #1

Using the Customer table, display the CustomerId, LastName, City for all Customers in the city Plainwill.

/* Answer */
select CustomerID, LastName, City
from Customer
where City = 'Plainwill'

Hint: You can use the WHERE clause to filter on City = ‘Plainwill’

The first is the column list and the table.

The second is the WHERE clause.

Exercise #2

You can use comparison operators on text values as well. 

Using the Customer table, find all customers having a last name that is greater than or equal to L.  Display the CustomerID and LastName.

/* Answer */
select CustomerID, LastName
from Customer
where LastName >= 'L'

Hint: You can use >= for the comparison operator.  It means greater than or equal to.

Exercise #3

Using the Customer table,

display the CustomerID, LastName, and PhoneNumber for all customer within the 249 area code.

Note:  The phone numbers are stored as text and formatted like:  123-456-7890

where 123 is the area code.

The phone number is a text column so you can use the LIKE clause.

/* Answer */
select CustomerID, LastName, PhoneNumber
from Customer
where PhoneNumber like '249%'

This would work:  LIKE ‘249%’

Coding instructions hereDisplay the ProductID, ProductName, ProductType, and Price for all product table items having a price NOT BETWEEN 5.00 and 11.00 dollars

/* Answer */
select ProductID, ProductName, ProductType, Price
from Product
where price not between 5.00 and 11.00

Display the ProductID, ProductName, ProductType, and Price

for all Product table items having a price NOT BETWEEN 5.00 and 11.00 dollars

whose ProductType is either P or B.

/* Answer */
select ProductID, ProductName, ProductType, Price
from Product
where price not between 5.00 and 11.00
and ProductType in ('P', 'B')

You’ll want to test for two items:

1.   Whether the price NOT BETWEEN 5.00 AND 11.00

AND

2.  ProductType IN (‘P’,’B’)