Capt. Horatio T.P. Webb
SQL SYNTAX SUMMARY
Parks -- FALL 2000
Last Updated 12PM 9/10/2006

 
  SQL Syntax -- Contents of this page:  

CREATE TABLEUsed to initially define the structure of a table. (this is normally done ONCE). The columns of the table are named and their data type specified.
 [ name_of_database. ]
 [ name_of_owner. ]
  name_of_table 
  ( begin the list of column definitions
  column_name_1 datatype 
datatypes:

bigint 		8 bytes
from -263 (-9,223,372,036,854,775,808)
to 263-1 (9,223,372,036,854,775,807)

int
    or
   integer		4 bytes
from -2,147,483,648
to 2,147,483,647

smallint 		2 bytes
from -32,768
to 32,767

tinyint 		1 byte
From 0 to 255

bit 		Integer with a value of 0 (zero) or 1 (one)

real 		4 bytes with seven digits of precision
From 1.18E-38
to 3.4E+38

float
   [ (n) ]		8 bytes is the default. 15 digits of precision
From 1.79E-308 to
2.23E+308
If n is specified as 1-7, the result is a real. If n is 8 thru 15, the result is the same as the default.

decimal [ ( p [ ,s ] ) ]
   or
 numeric [ ( p [, s ] ) ]		Values are between 10.E+38 to -10.E38.
p defines the number of digits to store to the left of the decimal (default is 18). s defines the number of digits to the right of the decimal (default is 0)

CHAR(n) 		Fixed width character string. n from 1 to 8,000.
If less than n are placed in the field, the remainder of the field is padded with blanks.

VARCHAR(n) 		Variable length character string. n can range from 0 to 8,000

VARCHAR(MAX) 		Variable length character string. n can range from 0 to 2,147,483,647. You cannot specific a value for MAX.

DATETIME 		Default format is:
MMM DD YYY HH:MM:AM|PM

SMALLDATETIME 		Uses 4 bytes.
Limited to 1/1/1900 to 6/6/2079. Uses two bytes for the "number of days since 1/1/1900. Uses 2 bytes for the number of minutes past midnight.

TIMESTAMP 		SQL generated timestamp.

BIT		Use a zero or one only. Must be preceeeded by the characters "0x" (i.e., a zero is 0x0, and a one is 0x1)

BINARY (n)		Fixed width binary. n is the number of bytes of binary data. May be from 1 to 8,000. To enter as hexadecimal preceeded by the characters "0x".

VARBINARY(n)		Variable width binary. May be from 1 to 8,000.

VARBINARY(MAX)		Variable width binary. You cannot specify a value for MAX. MAX is up to 2,147,483,647.

TEXT 		Up to 2,147,483,647 bytes.

IMAGE 		Up to 2,147,483,647 bytes.

MONEY		Uses 2 4-byte integers. Ranges from
-922,337,203,685,477.5808 to
+922,337,203,685,477.5807. Stores to the nearest ten-thousandth cent (i.e., 0.0001).
You must preceed the data with a $.

SMALLMONEY		Uses four bytes. Ranges from
-214,748.3648 to
+214,748.3647. Stores to the nearest ten-thousandth cent (i.e., 0.0001).
You must preceed the data with a $.
  [ NULL | NOT NULL] NULL allows for inserts which are null (i.e., empty). If NOT NULL you must enter a value during inserts
  [ IDENTITY [ (starting_value , increment ) ]  This is a property of a datatype. The data type must be either: int, smallint, tinyint,decimal(p,0), or numeric(p,0) and must not be NULL. The server generates an integer value (beginning at starting_value) for each row which is added to the table. The next row which is added gets an integer value equal to the previous identity value plus increment.
  , end of first column specification
  [column_name_2 datatype ... , next column
  [column_name_3 datatype ... , next column
  [column_name_4 datatype ... , etc.
  )  end of column specification
  PRIMARY KEY (first_column [,second_column, ... etc.] )Names the columns to be used as the primary key for the table
General Ledger Table Creation Example:

The following VBScript code snipet (an Active Server Page) creates a table named glmaster with columns named:

  1. major
  2. minor
  3. sub1
  4. sub2
  5. acctdesc
  6. balance

The primary key is identified as:

  1. major
  2. minor
  3. sub1
  4. sub2

The entire SQL command is stored in a string named create_string.

create_string="CREATE TABLE glmaster (major integer NOT NULL,"
create_string=create_string + "minor integer NOT NULL,"
create_string=create_string + "sub1 integer,"
create_string=create_string + "sub2 integer,"
create_string=create_string + "acctdesc char(50),"
create_string=create_string + "balance numeric NOT NULL, "
create_string=create_string + "primary key (major, minor, sub1, sub2) )"
connection_name.execute create_string

The string is then passed from the ActiveServerPage to the MS SQL Server database system for execution.

INSERT INTO name_of_table

(name_of_column1,
 name_of_column2,
 name_of_column3,
 name_of_column4,
 etc.,)

VALUES

(value_of_column1,
value_of_column2,
value_of_column3,
value_of_column4,
etc.
)

Used to add rows to the table. The table must exist (i.e., created as above) before you can perform an INSERT.

Values for the primary key MUST be specified. Values for any other NOT NULL columns must be specified.

The order of the column names must match the order of the values.
Characters must be enclosed with single quotation marks (ascii char 39).
Data for the money datatype must be preceeded with a $

INSERT Example

The following VBScript code snipet (from an Active Server Page) demonstrates the use of the INSERT.


Insert_String = "INSERT INTO glmaster (major, minor, sub1, sub2, acctdesc, balance) VALUES ("
Insert_String = Insert_String + Request.Form("major") + ","
Insert_String = Insert_String + Request.Form("minor") + ","
Insert_String = Insert_String + Request.Form("sub1") + ","
Insert_String = Insert_String + Request.Form("sub2") + ",'"
Insert_String = Insert_String + Request.Form("acctdesc") + "',0)"
cn.Execute Insert_String

A string named Insert_String is constructed that names six columns to be assigned values in the tabvle named glmaster. Then five values are retrieved from an user's querystring data (i.e., the form) and are in turn added to the VALUES part of the string. The last column is assigned a value of zero. The string is then passed from the Active Server Page to MS SQL 6.5 for execution.

DROP TABLE name_of_table

CAUTION:This command removes the table references AND the contents of the table. This action is not recoverable

 

DROP TABLE Example

The following VBscript code drops a table named je

 
Drop_String="DROP TABLE je"
 
cn.execute Drop_String

 

 

SELECTUsed to retrieve rows from a table that conform to specific selection criteria.
*
or
gets all columns

ALL

or
same as *

( column_name1,
 column_name2,
 column_name3,
 column_name4,
 column_name5,
 etc.,
)

Name the of the columns to be retrieved.
FROM name_of_table
[ WHERE [NOT] comparison1
comparison

column_name =
!=
<>
<
>
<=
>=		  
 
string
 
  or
 
number
 
 
LIKEUse % sign before and after the comparison value. For example:

WHERE acolumn LIKE '%somevalue%'

[OR | AND comparison2 ... ]
[ WHERE column_name BETWEEN first_value AND second_value ]May be used like a comparison
[ ORDER BY
column_name1 [ASC | DESC]
[, column_name2 [ASC | DESC]
[, column_name3 [ASC | DESC]
etc.

SELECT Example

The following string named Select_String is used to retreive all columns (i.e., *) from a table named glmaster. Only those rows WHERE sub1 is 1000 or 2000 AND sub2 is 1000 are retreived. The recordset should be returned in ascending sub1 order.

Select_String="SELECT * FROM glmaster WHERE (sub1=1000 OR sub1=2000) AND sub2=1000" order by sub1 ASC
rs.Open Select_String,"DSN=gl001;UID=gl001;PWD=11111;"

The table glmaster is in the database (DSN) named gl001.

UPDATE name_of_table SET

 name_of_column1 = value or expression,
 name_of_column2 = value or expression,
 name_of_column3 = value or expression,
 name_of_column4 = value or expression,
etc.

WHERE comparison

A comparison is required.
Expressions are any valid arithmetic expression using column_names and constants
(e.g., SET column_name1= column_name2/3.14159)
Update Example:

In the following VBScript snipet, an SQL UPDATE command is executed. In VBScript, a string named Update_String is created thta sets the value of the column named balance to a value from the user's querystring data (i.e., the value of balfield on a FORM). Only the record WHERE major, minor, sub1 and sub2 equal the values defined from the FORM is updated.


Update_String = "UPDATE glmaster SET balance = balance + "
Update_String = Update_String + Request.Form("balfield") + " WHERE "
Update_String = Update_String + "major=" + Request.Form("maj") + " AND "
Update_String = Update_String + "minor=" + Request.Form("min") + " AND "
Update_String = Update_String + "sub1=" + Request.Form("sub1") + " AND "
Update_String = Update_String + "sub2=" + Request.Form("sub2")
cn.Execute Update_String

DELETE [FROM] name_of_table

WHERE column_name= value

DELETE Example

The following VBscript code removes a row from the table where the major=1000, the minor=1000, sub1=1000 and sub2=1000.

Delete_String="DELETE FROM glmaster WHERE major=1000 AND minor=1000 AND sub1=1000 AND sub2=1000"
cn.execute Delete_String

 

ALTER TABLE name_of_table ADD column_name datatype

or for multiple new columns

ALTER TABLE name_of_table

 ADD (column_name1 datatype1, column_name2 datatype2 [...,column_namen datatypen])

 Adds one or more NEW columns to a table with the specified data type

ALTER TABLE name_of_table MODIFY column_name datatype

or for multiple columns changes

ALTER TABLE name_of_table

     MODIFY (column_name1 datatype1, column_name2 datatype2 [...,column_namen datatypen])

 Modifies existing column(s) data type

ALTER TABLE name_of_table DROP COLUMN column_name

 Removes an existing columns

ALTER TABLE name_of_table

     RENAME column_name old columnname TO new columnname

 Renames an existing column name
ALTER TABLE name_of_table RENAME TO new tablename

 Renames an existing table

ALTER should NOT be used without serious consideration of the impact that can result. Deleting primary key columns is prohibited; data type choices can create conflicts such a varchar to int, etc., etc.... Additionally, the specific database (e.g., MS SQL Server, Oracle, MYSQL, etc.) and version number may impact your decisions. Check your documentation for the rules, limitations and exlusions. Always RTFM.

ERROR TRAPS ON CONNECTIONS

The cn.execute syntax allows a second parameter to be specified. This field returns the "number of rows affected" by the connection execution. The syntax is:

cn.execute SQLstring, number-of-rows-affected

For updates, inserts and deletes the number-of-rows-affected is likely known ahead of time. Typically, a cn.execute for an insert, update or delete should yield a number-of-rows-affected value of 1 if it works and a value of 0 if it doesn't. We can test this result directly by evaluating number-of-rows-affected after the execution. Based on its value we can take appropriate action.

Connection Execution Error Trap Example

The following VBscript code examines the result of an insert.


Insert_String = "INSERT INTO glmaster (major, minor, sub1, sub2, acctdesc, balance) VALUES ("
Insert_String = Insert_String + Request.Form("major") + ","
Insert_String = Insert_String + Request.Form("minor") + ","
Insert_String = Insert_String + Request.Form("sub1") + ","
Insert_String = Insert_String + Request.Form("sub2") + ",'"
Insert_String = Insert_String + Request.Form("acctdesc") + "',0)"
cn.Execute Insert_String, numa

if numa=1 then
   code to execute if it happened correctly (i.e., inserted one row)
else
   if numa =0 then
      code to execute if failed
       (i.e., no insert -- so check for duplicate key)
   else
      code to execute in case of multiple inserts
      (i.e., more than one row added -- so check this numa count)
   end if
end if

 

TRANSACTION CONTROL ON CONNECTIONS

The connection object contains three critical methods that should be employed when modifying the contents of a table. The three methods are:

  1. cn.beginTrans

    This command should be executed once prior to any connection executions. This notifies SQL Server that all subsequent executions on this connection should be cached until a cn.rollbackTrans or a cn.commitTrans command is executed.

  2. cn.commitTrans

    This command should be executed after all other connection commands have successfully executed. This notifies SQL Server that all the cached transactions (i.e., those executed since the cn.beginTrans command) should be actually posted to the database. You do this if everything has worked ok.

  3. cn.rollbackTrans

    This command should be executed after all connection commands and an error(s) has been detected. This notifies SQL Server NOT to process the cached transactions that have accumulated since the original cn.beginTrans command. You do this to prevent the posting of erroneous transactions (specifically avoids partial posting of a sequence of events when an error is encountered)

To employ these three methods, there must be some variable that keeps track any errors encountered as processing occurs. The simply way is to set up an error counter prior to the cn.beginTrans and counts any errors that occur in the processing sequence. Then after all is done, commit of the error count is zero -- otherwise rollback.

This transaction control should be applied whether there is one or more executions. Generally, you should always run inserts, updates and dletes under transaction control (i.e., a cn.beginTrans, then executions, and then either cn.commitTrans or cn. rollbackTrans).

Note there are some things you cannot do under transaction control. You cannot:

  1. create a table
  2. drop a table
  3. creating a database

These actions are not able to be rolled back and therefore cannot be put under transaction control. Only those actions like INSERT, UPDATE and DELETE should use transaction control.

 

Transaction Control Example

The following VBscript code examines the result of a connection sequence with multiple executions inside a loop.

cn.open
.
.
.
err-count=0
cn.beginTrans
.
.
.
Loop control
.
.
.
   cn.execute SQL-string-for-update-insert-or-delete , numa

   if numa=1 then
      maybe do nothing
   else
      err-count=err-count + 1
   end if
.
.
.
end Loop
.
.
.

   if err-count = 0 then
      cn.commitTrans
   else
      cn.rollbackTrans
   end if
.
.
.
cn.close