Selecting Records in SQL with WHERE clause

The WHERE clause is used to retrieve specific rows from tables. The WHERE clause can contain one or more conditions that specify which rows should be returned.

SELECT column, column
FROM table
WHERE conditions

SQL Symbol Operators

The following table shows the symbolic operators used in WHERE conditions.

1. Use a WHERE clause to limit the rows that are returned to the user.

There are many variations in the SELECT statement syntax. Below are five simple versions of the SELECT statement. In the examples below, we will use a WHERE clause to limit the rows that are returned to the user.

1. Use a WHERE clause to limit the rows that are returned to the user.

/// 1

2. Checking for Equality

2. Retrieve product details for product model 6

/////////////////

WHERE and ORDER BY

When using WHERE and ORDER BY together, the WHERE clause must come before the ORDER BY clause.

1.

///////// Practice Exercise //////////////// Using WHERE and ORDER BY Together In this exercise, you will practice writing SELECT statements that use both WHERE and ORDER BY. 1. Create a report that shows the company name, contact name and fax number of all customers that have a fax number. Sort by company name. 2. Create a report that shows the city, company name, and contact name of all customers who are in cities that begin with “A” or “B.” Sort by contact name in descending order. ////////// Solution ///////////////// SELECT CompanyName, ContactName, Fax FROM Customers WHERE Fax IS NOT NULL ORDER BY CompanyName; SELECT City, CompanyName, ContactName FROM Customers WHERE City < 'C' ORDER BY ContactName DESC; ///////////////////////////////////////////////////////// /// 2

2. Checking for Inequality

///////// Practice Exercise //////////////// Using the WHERE Clause to Check for Equality or Inequality In this exercise, you will practice using the WHERE clause to check for equality and inequality. 1. Create a report showing all the CompanyName, FirstName, and LastName of AdventureWork’s customers who's Title is Mr. SELECT CompanyName, FirstName, LastName FROM SalesLT.Customer WHERE Title = 'Mr.'; /////////////// 2. Create a report showing the product name, list price, standard cost and weight of all products that weigh 1016.04. SELECT Name, ListPrice, StandardCost, Weight FROM SalesLT.Product WHERE Weight=1016.04; ///////////////////// 3. Create a report showing the order date, shipped date, customer id, and freight of all orders placed on June 1, 2008. SELECT OrderDate, ShipDate, CustomerID, Freight FROM SalesLT.SalesOrderHeader WHERE OrderDate = '01-June-2008'; or SELECT OrderDate, ShipDate, CustomerID, Freight FROM SalesLT.SalesOrderHeader WHERE OrderDate = '2008-06-01'; Oracle users use following date format: 'dd-mmm-yyyy' (e.g, '01-June-2008'). MySQL users use following date format: 'yyyy-mm-dd' (e.g, '2008-06-01'). Microsoft users may use either of the preceding formats. /////////// 4. Create a report showing the first name, last name, and country of all employees not in the United States. SELECT AddressLine1, City, CountryRegion FROM SalesLT.Address WHERE CountryRegion <> 'United States'; SELECT AddressLine1, City, CountryRegion FROM SalesLT.Address WHERE CountryRegion = 'United States'; //////////Solutions ///////////////// /// 3

Checking for Greater or Less Than

The less than (<) and greater than (>) signs are used to compare numbers, dates, and strings.

3.

Using the WHERE Clause to Check for Greater or Less Than

In this exercise, you will practice using the WHERE clause to check for values greater than or less than a specified value. //////////////////////////////////////// The following query uses the not equal (<>) operator to return all products with a ProductModelID other than 6. SELECT Name, Color, Size
FROM SalesLT.Product
WHERE ProductModelID <> 6
ORDER BY Name; //////////////////////////////////////// The following query returns all products with a ListPrice greater than 1000.00. SELECT Name, ListPrice
FROM SalesLT.Product
WHERE ListPrice > 1000.00
ORDER BY ListPrice; ///// Exercise 5: Using the WHERE Clause to Check for Greater or Less Than 1. Create a report that shows the customer id, sales order id, due date, ship date and total due of all sales orders that were shipped later than they were required. SELECT CustomerID, SalesOrderID, DueDate, ShipDate, TotalDue FROM SalesLT.SalesOrderHeader WHERE ShipDate > DueDate; 2. Create a report that shows the firstname, lastname and company name of all customers who's last names begin with “A” or “B.” SELECT FirstName, LastName, CompanyName FROM SalesLT.Customer WHERE LastName < 'C'; 3. Create a report that shows all sales orders that have a freight cost of more than $500.00. SELECT SalesOrderID, OrderDate, Freight FROM SalesLT.SalesOrderHeader WHERE Freight > 500; 4. Create a report that shows the product name, list price, standard cost, and color of products that are listed for equal or more than standard cost of the product. SELECT Name, ListPrice, StandardCost, Color FROM SalesLT.Product WHERE StandardCost <= ListPrice; /// 4 //// Using WHERE and ORDER BY Together In this exercise, you will practice writing SELECT statements that use both WHERE and ORDER BY. 1. Create a report that shows the company name, contact name and fax number of all customers that have a fax number. Sort by company name. 2. Create a report that shows the city, company name, and contact name of all customers who are in cities that begin with “A” or “B.” Sort by contact name in descending order. SELECT CompanyName, ContactName, Fax 2. FROM Customers 3. WHERE Fax IS NOT NULL 4. ORDER BY CompanyName; 5. 6. SELECT City, CompanyName, ContactName 7. FROM Customers 8. WHERE City < 'C' 9. ORDER BY ContactName DESC;

.

4.

.

.

.

////////////////////////////////////

Filter results with the WHERE clause

Most queries for application development or reporting involve filtering the data to match specified criteria. You typically apply filtering criteria as a predicate in a WHERE clause of a query. In Azure Data Studio, replace the existing query with the following code: //////////////////////////////////////// ////////////// Done Emeka /////////////////// More Exercises /////////////////////// //Exercise: Using the WHERE clause to check for equality or inequality Create a report showing all the company names and contact names of Northwind’s customers in Buenos Aires. Create a report showing the product name, unit price and quantity per unit of all products that are out of stock. Create a report showing the order date, shipped date, customer id, and freight of all orders placed on May 19, 1997. Create a report showing the first name, last name, and country of all employees not in the United States. //////////////////////////////////////// The LIKE operator enables you to match string patterns. The % character in the predicate is a wildcard for one or more characters, so the query returns all rows where the Name is HL Road Frame followed by any string. SELECT Name, ListPrice
FROM SalesLT.Product
WHERE Name LIKE 'HL Road Frame %'; /// Filter products by product number: SELECT ProductNumber, Name, ListPrice
FROM SalesLT.Product
WHERE ProductNumber LIKE 'BK-%'; //////////////////////////////////////// The following query returns products with a ProductNumber that matches patterns similar to FR-xNNx-NN (in which x is a letter and N is a numeral). SELECT Name, ListPrice
FROM SalesLT.Product
WHERE ProductNumber LIKE 'FR-_[0-9][0-9]_-[0-9][0-9]'; //// Retrieve specific products by product number: SELECT ProductNumber, Name, ListPrice
FROM SalesLT.Product
WHERE ProductNumber LIKE 'BK-[^R]%-[0-9][0-9]'; //////////////////////////////////////// The following query filters data based on NULL values. In such a case you must use IS NULL (or IS NOT NULL). Note: You cannot compare a NULL value using the = operator. SELECT Name, ListPrice
FROM SalesLT.Product
WHERE SellEndDate IS NOT NULL; //////////////////////////////////////// The following query uses the BETWEEN operator to define a filter based on values within a defined range. This query retrieves products with a ProductCategoryID value that is in a specified list. SELECT Name
FROM SalesLT.Product
WHERE SellEndDate BETWEEN '2006/1/1' AND '2006/12/31'; //////////////////////////////////////// This query returns products that the company stopped selling in 2006 by retrieving products with a ProductCategoryID value that is in a specified list. SELECT ProductCategoryID, Name, ListPrice
FROM SalesLT.Product
WHERE ProductCategoryID IN (5,6,7); //////////////////////////////////////// This query uses the AND operator to combine two criteria. SELECT ProductCategoryID, Name, ListPrice, SellEndDate
FROM SalesLT.Product
WHERE ProductCategoryID IN (5,6,7) AND SellEndDate IS NULL; Filter products by color and size: SELECT ProductNumber, Name
FROM SalesLT.Product
WHERE Color IN ('Black','Red','White') AND Size IN ('S','M'); //////////////////////////////////////// This query filters the results to include rows that match one (or both) of two criteria. SELECT Name, ProductCategoryID, ProductNumber
FROM SalesLT.Product
WHERE ProductNumber LIKE 'FR%' OR ProductCategoryID IN (5,6,7); ////////////////////////////////// //// 5 //////////////////Page Ends Here///////////////////////// ////////////////////////////////////

Checking for NULL

When a field in a row has no value, it is said to be NULL. This is not the same as having an empty string or a 0. Rather, it means that the field contains no value at all. When checking to see if a field is NULL, you cannot use the equals sign (=); rather, use the IS NULL expression.

5.

6.

///////// Practice Exercise //////////////// Checking for NULL In this exercise, you will practice selecting records with fields that have (or do not have) NULL values. 1. Create a report that shows the company name, contact name and fax number of all customers that have a fax number. 2. Create a report that shows the first and last name of all employees who do not report to anybody. //////////////////// Solution ///////////////////// SELECT CompanyName, ContactName, Fax FROM Customers WHERE Fax IS NOT NULL; SELECT FirstName, LastName FROM SalesLT.Customer WHERE ReportsTo IS NULL; ////////////////////// ///////////////////////Copy Code ////////////////// ///////////////////////////// /////////From Microsoft site ///// emeka add Products table diagram - change to Northwind

Checking Multiple Conditions with Boolean Operators - AND

AND can be used in a WHERE clause to find records that match more than one condition.

AND

Checking Multiple Conditions with Boolean Operators - OR

OR can be used in a WHERE clause to find records that match at least one of several conditions.

OR

Order of Evaluation

By default, SQL processes AND operators before it processes OR operators. To illustrate how this works, take a look at the following example.

.

Notice that Laura Callahan is returned by the query even though she is not a sales representative. This is because this query is looking for employees from Seattle OR sales representatives from Redmond. This can be fixed by putting the OR portion of the clause in parentheses.

.

The parentheses specify that the OR portion of the clause should be evaluated first, so the preceding SELECT statement will return the same results minus Laura Callahan.

** It’s always a good idea to use parentheses whenever the order of precedence might appear ambiguous.

.

/////////////////////Check this section for duplicates /////////////////// //////////////////////////////////

SQL Word Operators

The following table shows the word operators used in WHERE conditions.

The BETWEEN Operator

The BETWEEN operator is used to check if field values are within a specified inclusive range.

.

Note that a person with the last name “M” would be included in this report.

////Stopped at page 45 //// SQL 101 -1.7.7.php_ini_loaded_file ////The IN Operator The IN operator is used to check if field values are included in a specified comma-delimited list. /* 2. Create a report showing the title of courtesy and the first and 3. last name of all employees whose title of courtesy is "Mrs." or "Ms.". 4. */ 5. 6. SELECT TitleOfCourtesy, FirstName, LastName 7. FROM SalesLT.Customer 8. WHERE TitleOfCourtesy IN ('Ms.', 'Mrs.'); 9. 10. -- The above SELECT statement is the same as the one below 11. 12. SELECT TitleOfCourtesy, FirstName, LastName 13. FROM SalesLT.Customer 14. WHERE TitleOfCourtesy = 'Ms.' OR TitleOfCourtesy = 'Mrs.';

.

/* Create a report showing the title of courtesy and the first and last name of all employees whose title of courtesy begins with "M". */ SELECT TitleOfCourtesy, FirstName, LastName FROM SalesLT.Customer WHERE TitleOfCourtesy LIKE 'M%';

.

The Underscore (_) The underscore (_) is used to match any single character. /* 2. Create a report showing the title of courtesy and the first and 3. last name of all employees whose title of courtesy begins with "M" and 4. is followed by any character and a period (.). 5. */ 6. 7. SELECT TitleOfCourtesy, FirstName, LastName 8. FROM SalesLT.Customer 9. WHERE TitleOfCourtesy LIKE 'M_.';

.

The NOT Operator The NOT operator is used to negate an operation. /* 2. Create a report showing the title of courtesy and the first and last name 3. of all employees whose title of courtesy is not "Ms." or "Mrs.". 4. */ 5. 6. SELECT TitleOfCourtesy, FirstName, LastName 7. FROM SalesLT.Customer 8. WHERE NOT TitleOfCourtesy IN ('Ms.', 'Mrs.');

.

Exercise 9: More SELECTs with WHERE 5 to 15 minutes In this exercise, you will practice writing SELECT statements that use WHERE with word operators. 1. Create a report that shows the first and last names and birth date of all employees born in the 1950s. 2. Create a report that shows the product name and supplier id for all products supplied by Exotic Liquids, Grandma Kelly's Homestead, and Tokyo Traders. Hint: you will need to first do a separate SELECT on the Suppliers table to find the supplier ids of these three companies. You will need to escape the apostrophe in “Grandma Kelly's Homestead” in the first separate SELECT. To do so, place another apostrophe in front of it. 3. Create a report that shows the shipping postal code, order id, and order date for all orders with a ship postal code beginning with “02389”. 4. Create a report that shows the contact name and title and the company name for all customers whose contact title does not contain the word “Sales”. Solution: SimpleSelects/Solutions/WordOperators.sql 1. /****************************** 2. For the first problem, both of the solutions below will work in SQL Server 3. 4. Oracle Solution 5. ******************************/ 6. SELECT FirstName, LastName, BirthDate 7. FROM SalesLT.Customer 8. WHERE BirthDate BETWEEN '1-Jan-1950' AND '31-Dec-1959'; 9. 10. /****************************** 11. MySQL Solution 12. ******************************/ 13. SELECT FirstName, LastName, BirthDate 14. FROM SalesLT.Customer 15. WHERE BirthDate BETWEEN '1950-01-01' AND '1959-12-31 23:59:59'; 16. /* The result of the following "HINT" query must be obtained first in order to write the solution query for #2 */ 17. 18. select supplierid 19. from suppliers 20. where companyname in ('Exotic Liquids', 21. 'Grandma Kelly"s Homestead', 22. 'Tokyo Traders'); 23. 24. 25. SELECT ProductName, SupplierID 26. FROM Products 27. WHERE SupplierID IN (1, 3, 4); 28. 29. SELECT ShipPostalCode, OrderID, OrderDate 30. FROM Orders 31. WHERE ShipPostalCode LIKE '02389%'; 32. 33. SELECT ContactName, ContactTitle, CompanyName 34. FROM Customers 35. WHERE NOT ContactTitle LIKE '%Sales%'; ////////////////////////////// ///////////////////////Copy Code ////////////////// ///////////////////////////// ///////////////////New Copy Code ////////////////////////// ///////////////////////////////////////

To delete limited records in the database, use the WHERE clause along with DELETE command. For example:

Breakdown of your Risk Level

FICO® Scores generally range from 300 to 850, with higher scores demonstrating lower credit risk and lower scores demonstrating higher credit risk.

Related Content