SQL Guide Contents Page

SQL Select Command

Used to retrieve selected data. Syntax: 

SELECT [ALL | DISTINCT] columnname1 [,columnname2]
FROM tablename1 [,tablename2]
[WHERE condition] [ and|or condition...]
[GROUP BY column-list]
[HAVING "conditions]
[ORDER BY "column-list" [ASC | DESC] ]

The sections between the brackets [] are optional. A simpler syntax statement is:

select columnname1 [,columnname2] from tablename [where condition];

A "*" may be used to select all columns. The where clause is optional and only one column name must be specified.

The Where Clause

This clause is used to specify which columns and values are returned. Where conditions specify an OPERATOR to use for comparison. OPERATORs include:

  • = - Equal
  • < - Less than
  • > - Greater than
  • <= - Less than or equal
  • >= - Greater than or equal
  • <> - Not equal
  • LIKE - Allows the wildcard operator, %, to be used to select items that are a partial match. An example is:

    select city, state from towntable where state LIKE 'north%';

    This allows selection of all towns in states that begin with the word "north" allowing states like North Dakota and North Carolina to be selected.

The GROUP BY Clause

This "GROUP BY" clause allows multiple columns to be grouped so aggregate functions (listed below) may be performed on multiple columns with one command.

Aggregate function keywords:

Example: 

SELECT MAX(population)
FROM citylist;
WHERE state = 'Indiana';

Example using the GROUP BY clause which gets the smallest population of each city in every state: 

SELECT MIN(population)
FROM citylist;
GROUP BY state;

The HAVING Clause

Allows selection of set test criteria on rows. You can display average size of towns whose population is less than 100.

The ORDER BY Clause

This clause lets results be displayed in ascending or descending order. Keywords:

Other Keywords

Example

SELECT city, state FROM towntable WHERE population > '100000';

SQL Guide Contents Page