SQL Command Reference  

Looking for a SQL command reference? Our free online SQL tutorial provides examples of SQL commands and SQL syntax. Bookmark our site and use our SQL tutorial as a SQL command reference.

Table of Contents

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

Examples:

Retrieve all vendors located in Chicago.

  1. In the SQL text box, type:

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

  1. 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;

  1. 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
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.

  1. In the SQL text box, type:

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

  1. 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;

  1. 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;

  1. 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;

  1. 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
Table of Contents

Legal Dot Privacy