MyCMS.ca by Victory Design
SQL Queries

SQL (pronounced "ess-que-el") is the acronym for Structured Query Language. SQL is the standard language for communicating with relational database management systems. SQL statements are used to retrieve data from the database as well as perform tasks such as adding updating and deleting the data. Some common relational database management systems that use SQL are: Oracle, Sybase, MS SQL Server,MS Access, MySQL, etc.


Basic Query Structure
SELECT field1 [,"field2",etc]
FROM table
[WHERE "condition"]
[GROUP BY "field"]
[ORDER BY "field"]


[ ] = optional


The SELECT statement is used to query the database and retrieve the fields that you specify. You can select as many fields (column names) as you want, or use the asterisk symbol "*" to select all fields.

The FROM statement specifies the table names that will be queried to retrieve the desired data.

The WHERE clause (optional) specifies which data values or rows will be returned or displayed, based on the criteria you specify.

The GROUP BY clause (optional) organizes data into groups.

The ORDER BY clause (optional) sorts the data by the field specified.

=Equal
>Greater than
<Less than
>=Greater than or equal to
<=Less than or equal to
<>Not equal to
LikeString comparison test. See Below

LIKE condition
The LIKE operator can be used in the conditional selection of the where clause. Like is a very powerful operator that allows you to select only rows that are "like" what you specify. The percent sign "%" can be used as a wild card to match any possible character that might appear before or after the characters specified. For example:

SELECT name
FROM members
WHERE name LIKE 'Mar%'


Will select all names starting with "Mar" such as "Mark, Mary and Margaret"

SELECT name
FROM members
WHERE name LIKE '%ll%'


Selects all names containing the double ll combination such as "Jill, Milly and William"

ALL and DISTINCT
ALL and DISTINCT are keywords used to select either ALL (default) or the "distinct" (unique) records in your data base. Using the DISTINCT keyword will not display any duplicate records in the field(s) specified. ALL will display "all" of the specified fields including all of the duplicates. The ALL keyword is the default if nothing is specified.

SELECT DISTINCT firstname
FROM members


This statement will return all of the unique firstnames in the name table.

GROUP BY clause
The GROUP BY clause will gather all of the rows together that contain data in the field(s) and will allow aggregate functions to be performed on the one or more columns.

SELECT max(age), city, name, address
FROM members
GROUP BY city


This query will select the maximum age for the members in each unique city. Basically, the age for the person who is oldest in each city will be displayed. Their name, address and city will be returned.

ORDER BY clause
ORDER BY is an optional clause which will allow you to display the results of your query in a sorted order -- either ascending (ASC - Default) or descending (DESC) based on the fields that you specify to order by. If you would like to order based on multiple columns, you must seperate the columns with commas.

SELECT name, city, age
FROM members
ORDER by city, age DESC