Retrieving Specific Rows
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 single quotes.
Example:
where City = 'Chicago'
- Variables that refer to numeric fields should not be enclosed in quotes.
Example:
where CurrBal > 1200
Retrieve all vendors located in Chicago.
- In the SQL text box, type:
Select City, Name, VendId
from TrnVendor
where City = 'Chicago';'
- Execute the SQL statement.
| 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.
- In the SQL text box, type:
Select City, Name, VendId
from TrnVendor
where City <> 'Chicago'
order by City;
- Execute the SQL statement.
| 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 |
| Monroe | Big Tree Landscaping | TV008 |
| Monroe | Softer Software | TV013 |
| New York | Paper People | TV012 |
| Orlando | The Soda Factory | TV010 |
| Orlando | Against the Tide | TV025 |
| Park Ridge | Mosquito No Bite | TV006 |
| Park Ridge | No Waste Disposal | TV015 |
| Toledo | Wet Off Towels | TV001 |
| Toledo | Hit the Deck | TV009 |
| Toledo | Counter Productive | TV014 |
| Toledo | Narrow Nest | TV027 |
| Warren | The Games All Here | TV002 |
| Youngstown | Computer Bytes | TV018 |
Retrieve all vendors in Mercer, New York, or Park Ridge.
- In the SQL text box, type:
Select City, Name, VendId
from TrnVendor
where City in ( 'Mercer', 'New York', 'Park Ridge')
order by City;
- Execute the SQL statement.
| 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."
- In the SQL text box, type:
Select Name, City, VendId
from TrnVendor
where Name begins with 'Co'
order by Name;
- Execute the SQL statement.
| 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."
- In the SQL text box, type:
Select Name, City, VendId
from TrnVendor
where City like '%do'
order by City;
- Execute the SQL statement.
| 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.
- In the SQL text box, type:
Select VendId, Name, CurrBal [ZZ,ZZZ.99]
from TrnVendor
where CurrBal between 500 and 1000
order by CurrBal;
- Execute the SQL statement.
| 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 |
| Table of Contents |
