SQL Tutorial

      Basics

    1. Introduction
    2. Database Structure

      Commands

    3. Create
    4. Select
    5. Insert
    6. Update
    7. Delete
    8. Drop

      Operations

    9. Joining Tables

      Appendices

    10. Terms
    11. Credits

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:

  • AVG - Get the average of a specified column.
  • COUNT - Get the quantity of values in the column.
  • MAX - Return the maximum value in a specified column.
  • MIN - Return the minimum value in a specified column.
  • SUM - Return the sum of all numeric values in the specified column.

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:

  • ASC - Ascending order.
  • DESC - Descending order.

Other Keywords

  • ALL - Used to select all records.
  • DISTINCT - Used to select unique records. Only unique values are returned.

Example

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