Lesson 11: Retrieving Specific Rows in SQL (Logical Operators)

So far, you have been retrieving all of the rows in the table. You can, however, specify which rows you wish to retrieve. For example, you could retrieve only those vendors who are in Chicago.


Syntax:

Select */fieldname ...
from tablename
where fieldname =/!=/<>/>/>=/</<=/in/not in/between/not between/begins with/contains/not contains/ is null/is not null/like/not/like value
order by fieldname <desc>...

Explanation:

• You can use any of the following logical operators in your Where clause to restrict the rows you retrieve.

Logical Operators
=	Equal to
!= or <>	Not equal to
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to
in	Equal to any item in a list
not in	Not equal to any item in a list
between	Between two values, greater than or equal to one and less than or equal to the other
not between	Not between two values
begins with	Begins with specified value
contains	Contains specified value
not contains	Does not contain specified value
is null	Is blank
is not null	Is not blank
like	Like a specified pattern. % means any series of characters. _ means any single character.
not like	Not like a specified pattern. % means any series of characters. _ means many single character.

• In the Where clause, when referring to variables in character fields, you must enclose the values in singlequotes.

Example:
where City = 'Chicago'  

• Variables that refer to numeric fields should not be enclosed in quotes.

Example:
where CurrBal > 1200

Examples:Retrieve all vendors located in Chicago.

1. In the SQL text box, type:

Select City, Name, VendId
from TrnVendor
where City = 'Chicago';'  

2. Execute the SQL statement.

Results:
 

City	Name	VendId
Chicago	BedMakers Linen	TV003
Chicago	Music Maker	TV016
Chicago	Distant Horizons	TV028
Chicago	Paltry Play	TV029
Chicago	Make Shift Tilts	TV030

---

Retrieve all vendors who are not located in Chicago. Order the results by city.

1. In the SQL text box, type:

Select City, Name, VendId
from TrnVendor
where City <> 'Chicago'
order by City;

2. Execute the SQL statement.

Results:
 

City	Name	VendId
Bayshore	Bayshore Consulting	TV019
Burlington	BayCon Group	TV020
Dallas	Cooperative Operatives	TV021
Dallas	Clampett Oil	TV026
Detroit	Ray Block	TV005
Detroit	Spot Out	TV007
Freeport	Food Four	TV017
Freeport	World Wide Learning U	TV023
Freeport	Enterprise Transport	TV024
Houston	Legal Lookup	TV022
Mercer	Bed Room Furniture, Inc.	TV004
Mercer	The Freelance	TV011

Retrieve all vendors in Mercer, New York, or Park Ridge.

1. In the SQL text box, type:

Select City, Name, VendId
from TrnVendor
where City in ( 'Mercer', 'New York', 'Park Ridge')
order by City;

2. Execute the SQL statement.

Results:
 

City	Name	VendId
Mercer	Bed Room Furniture, Inc.	TV004
Mercer	The Freelance	TV011
New York	Paper People	TV012
Park Ridge	Mosquito No Bite	TV006
Park Ridge	No Waste Disposal	TV015

---

Retrieve all vendors whose names begin with "Co."

1. In the SQL text box, type:

Select Name, City, VendId
from TrnVendor
where Name begins with 'Co'
order by Name;

2. Execute the SQL statement.

Results:
 

Name	City	VendId
Computer Bytes	Youngstown	TV018
Cooperative Operatives	Dallas	TV021
Counter Productive	Toledo	TV014

---

Retrieve all vendors whose city ends with the letters "do."

1. In the SQL text box, type:

Select Name, City, VendId
from TrnVendor
where City like '%do'
order by City;

2. Execute the SQL statement.

Results:
 

Name	City	VendId
The Soda Factory	Orlando	TV010
Against the Tide	Orlando	TV025
Wet Off Towels	Toledo	TV001
Hit the Deck	Toledo	TV009
Counter Productive	Toledo	TV014
Narrow Nest	Toledo	TV027

---

Retrieve all vendors with a current balance between 500 and 1000 dollars.

1. In the SQL text box, type:

Select VendId, Name, CurrBal [ZZ,ZZZ.99]
from TrnVendor
where CurrBal between 500 and 1000
order by CurrBal;

2. Execute the SQL statement.

Results:
 

VendId	Name	CurrBal
TV003	BedMakers Linen	500
TV012	Paper People	617
TV017	Food Four	642.98
TV023	World Wide Learning U	771.06
TV006	Mosquito No Bite	967.24
TV029	Paltry Play	1,000