SQL CREATE DATABASE
SQL DROP DATABASE
SQL CREATE TABLE
SQL ALTER TABLE
SQL DROP TABLE
SQL CREATE INDEX
SQL DROP INDEX
SQL ADD FOREIGN KEY
SQL DROP FOREIGN KEY
SQL CREATE VIEW
SQL DROP VIEW

SQL ADVANCED

SQL CONCAT
SQL SUBSTRING
SQL TRIM
SQL AND & OR
SQL IN
SQL BETWEEN
SQL LIKE
SQL DISTINCT
SQL GROUP BY
SQL AGGREGATE
SQL HAVING
SQL ORDER BY
SQL JOIN
SQL OUTER JOIN

SQL Syntax

Previous | Next

This SQL Syntax Reference page shows the syntax for each feature described in the SQL Tutorial. It provides a Quick Reference to SQL.

For a further description of SQL functionality, please click the links in the navigation area of this page.

SQL Basics	SQL Administration	SQL Advanced
SQL SELECT SQL WHERE SQL INSERT SQL UPDATE SQL DELETE	SQL CREATE DATABASE SQL DROP DATABASE SQL CREATE TABLE SQL ALTER TABLE SQL DROP TABLE SQL CREATE INDEX SQL DROP INDEX SQL ADD FOREIGN KEY SQL DROP FOREIGN KEY SQL CREATE VIEW SQL DROP VIEW	SQL CONCAT SQL SUBSTRING SQL TRIM SQL AND & OR SQL IN SQL BETWEEN SQL LIKE SQL DISTINCT SQL GROUP BY SQL AGGREGATE SQL HAVING SQL ORDER BY SQL JOIN SQL OUTER JOIN

SQL Syntax and Conventions

SQL statements are represented as text.

SQL statements have keywords that must be spelled following rules. The keywords can be upper or lower case - SQL is not case sensitive. By convention and to improve readability, this tutorial spells SQL keywords in upper case.

SQL statements are independent of text lines. A single SQL statement can be placed on one text line or on multiple. In addition, multiple SQL statements can be combined on a single text line. By convention and to improve readability, this tutorial does not put more than one SQL statement on a single text line. Further, SQL statements are often broken into multiple lines.

A SQL statements may be terminated by a semi-colon or the word 'GO'. This tutorial leaves these terminators out. Please supply as needed.

SQL BASICS

SQL SELECT Syntax

SELECT <columnlist>
FROM <tablename>

SQL WHERE Clause Syntax

<main-statement>
WHERE <conditions>

Each condition tests column(s) using comparison operator(s). The following basic comparison operators are supported:

Operator	Description
=	Equal
<>	Not Equal
>	Greater Than
<	Less Than
>=	Greater Than Or Equal
<=	Less Than Or Equal

The comparison may involve literal value(s) that are constants like:

10
'Minnesota'
-5006.3

Alphanumeric literals are enclosed in single quotes ('XXX').

SQL INSERT Syntax

INSERT INTO <table_name> ( <column_list> )
VALUES (<value_list>)

SQL UPDATE Syntax

UPDATE <table_name>
SET <column_name> = <column_value>
WHERE <where_conditions>

SQL DELETE Syntax

DELETE FROM <table_name>
WHERE <where_conditions>

SQL ADMINISTRATION

SQL CREATE DATABASE Syntax

CREATE DATABASE <database_name>

SQL DROP DATABASE Syntax

DROP DATABASE <database_name>

SQL CREATE TABLE Syntax

CREATE TABLE <table_name> (
<column_name1> <datatype1> <constraint1>
<column_name2> <datatype2> <constraint2>
<constraint-list>
)

The number of characters that can make up SQL table names and column names varies by DBMS. In many cases the limit is 30 characters. The leading character of the name must be alphabetic - not a number or special character. The name of a new table can not duplicate the name of an existing table and should not be the same as a SQL reserved word. The underscore character can be used to improve readability. The same column name can not be repeated within a table. List elements are seperated by commas.

Here are some example datatypes:

SQL Datatype	Description
integer(size) int(size) smallint(size) tinyint(size)	Integers
decimal(size,decimals) numeric(size,decimals)	Numbers with decimals
char(size)	Fixed length character string
varchar(size)	Variable length character string
date	A date in yyyymmdd format

SQL ALTER TABLE Syntax

ALTER TABLE <table_name>
ADD <column_name1> <datatype1> <constraint1>

ALTER TABLE <table_name>
ALTER COLUMN <column_name1> <datatype1> <constraint1>

ALTER TABLE <table_name>
DROP COLUMN <column_name1> <datatype1>

SQL DROP TABLE Syntax

DROP <table_name> ( <column_list>

SQL CREATE INDEX Syntax

CREATE INDEX <index_type> <index_name> ON <table_name> (
<column_name1> <index_order>,
<column_name2> <index_order>,
)

CREATE UNIQUE INDEX <index_type> <index_name> ON <table_name> (
<column_name1> <index_order>,
<column_name2> <index_order>,
)

The number of characters that can make up SQL names for tables, columns and indexes varies by DBMS. In many cases the limit is 30 characters. The leading character of the name must be alphabetic - not a number or special character. The name of a new index can not duplicate the name of an existing index for the same table and should not be the same as a SQL reserved word. The underscore character can be used to improve readability. List elements are seperated by commas.

SQL DROP INDEX Syntax

DROP INDEX <table_name>.<index_name>

SQL ADD FOREIGN KEY Syntax

ALTER TABLE <table_name>
ADD <constraint_name> FOREIGN KEY
(<column_name1> ,
<column_name2> )
REFERENCES <table_name>
(<column_name1> ,
<column_name2>)

The number of characters that can make up SQL names for tables, columns and foreign keys varies by DBMS. In many cases the limit is 30 characters. The leading character of the name must be alphabetic - not a number or special character. The name of a new foreign key can not duplicate the name of an existing foreign key for the database and should not be the same as a SQL reserved word. To make the foreign key unique it is common practice to include the table and column name as part of the foreign key name. The underscore character can be used to improve readability. List elements are seperated by commas.

SQL DROP FOREIGN KEY Syntax

ALTER TABLE <table_name>
DROP FOREIGN KEY <foreignkey_name>

SQL CREATE VIEW Syntax

CREATE VIEW <view_name> (
<column_name1>,
<column_name2>
) AS
<sql_select_statement>

The number of characters that can make up SQL names for tables, columns and views varies by DBMS. In many cases the limit is 30 characters. The leading character of the name must be alphabetic - not a number or special character. The name of a new view can not duplicate the name of an existing view or table and should not be the same as a SQL reserved word. The underscore character can be used to improve readability. List elements are seperated by commas.

SQL DROP VIEW Syntax

DROP VIEW <view_name>.<

SQL ADVANCED

SQL CONCAT Syntax

<value_1> + <value_2>

The means of achieving concatenation differs by database type. for example:

MySQL uses the CONCAT() function
Oracle uses the CONCAT() function and the || operator
SQL Server uses the + operator

SQL SUBSTRING Syntax

SELECT SUBSTRING(<column_name>, position, length)
FROM <table_name>

SQL TRIM Syntax

SELECT LTRIM(<value_1>)

SELECT RTRIM(<value_1>)

SQL AND & OR Syntax

SELECT <column_list>
FROM <table_name>
WHERE <condition_1>
AND|OR <condition_2>

SQL IN Syntax

SELECT <column_list>
FROM <table_name>
WHERE <column_name IN (value_list)>

There must be one or more members of the value_list. Numeric and non-numeric values are supported.

SQL BETWEEN Syntax

SELECT <column_list>
FROM <table_name>
WHERE <column_name> BETWEEN
<lower_value> AND <higher_value>

SQL LIKE Syntax

SELECT <column_list>
FROM <table_name>
WHERE <column_name> LIKE <like_condition>

The <like_condition> supports the following patterns:

'ABC%' - where a string begins with the letters 'ABC'
'%XYZ' - where a string ends with the 'XYZ'
'%TUV%' - where the string contais 'TUV' anywhere

SQL DISTINCT Syntax

SELECT DISTINCT <column_name>
FROM <table_name>

The word DISTINCT can be placed in front of a single column name or a number of column names. When in front of multiple column names, a distinct combination is returned.

SQL GROUP BY Syntax

SELECT <column_name1>, <column_name2> <aggregate_function>
FROM <table_name>
GROUP BY <column_name1>, <column_name2>

The GROUP BY clause must follow the FROM and WHERE clauses. The columns in a SELECT clause must be either group by columns or aggregate function columns.

SQL Aggregate Functions Syntax

SELECT <column_name1>, <column_name2> <aggregate_function(s)>
FROM <table_name>
GROUP BY <column_name1>, <column_name2>

The SQL Aggregate Functions are functions that provide mathematical operations. The functions include:

count() - counts a number of rows
sum() - compute sum
avg() - compute average
min() - compute minimum
max() - compute maximum

SQL HAVING Syntax

SELECT <column_name1>, <column_name2> <aggregate_function>
FROM <table_name>
GROUP BY <column_name1>, <column_name2>
HAVING <having_condition>

The <having_condition> may include Aggregate Function(s).

SQL ORDER BY Syntax

SELECT <column_name1>, <column_name2>
FROM <table_name>
ORDER BY <column_name1>[ASC|DESC], <column_name2>[ASC|DESC]

Multiple columns can be included in the ORDER BY clause. The direction of the sort is controlled by:

ASC - ascending sequence
DESC - descending sequence

SQL JOIN Syntax

SELECT <column_name1>, <column_name2> <aggregate_function>
FROM <table_name>
JOIN <table_name> ON <join_conditions>

SQL OUTER JOIN Syntax

SELECT <column_name1>, <column_name2> <aggregate_function>
FROM <table_name>
LEFT OUTER JOIN <table_name> ON <join_conditions>

Previous | Next

SQL TUTORIAL HOME

SQL BASICS

SQL ADMINISTRATION

SQL ADVANCED

SQL Syntax

SQL Syntax and Conventions

SQL BASICS

SQL ADMINISTRATION

SQL ADVANCED