CHum 240

SQL CREATE TABLE Statement

To create a new table you must specify the name of the table
and the name, type and size of each column. Most DBMSs 
enforce rules about the type of data that can go into a 
column. This is one way that the integrity of the data is 
protected.

Commonly recognized types of data are:

* INTEGER -- the DBMSs native idea of a whole number which
  can be positive or negative. Since this type is native to
  the DBMS, no size information is required or can be
  given. 

* INT -- a variable size whole number. If you know your
  numbers will never have more than three digits, you can
  save space by making the size equal to three. Often there
  is also a default size that is used if you do not specify
  it.

* FLOAT -- a floating point number, or number with a 
  decimal point and
  
* REAL -- Like a float, but does not require decimal point.

* CHAR or CHARACTER -- a fixed size string of characters. If
  you store fewer characters then spaces are appended until
  it is the size specified. This type is generally only used
  when all data to be stored in the column is the same size.
  For example, two letter state abbreviations or three-digit
  area codes.
  
* VARCHAR -- a variable size string of characters, but with
  a maximum length. No spaces are appended, so when you
  select this column you only get the characters stored into
  it. If the string is too long, some systems will truncate
  it or give an error. 
  
* TEXT -- a string of characters of any size. Often there is
  a limit, but it is usually very large, on the order of
  gigabytes. When used in web-based applications, TEXT 
  columns may store entire HTML documents.

* BLOB -- untyped data of any size. This kind of column can
  be used to store graphical images or binary data of any
  kind. It is up to the program retrieving the data to do
  the right thing with it. SELECT statements in the client
  program usually just give the size of the stored data and
  do not try to determine what it is.

* DATE -- a date, which must be in a form the DBMS can
  recognize. International standards recommend yyyy-mm-dd.

* TIME -- a time of day in the form recognized by the DBMS.
  Usually in 24 hour format, such as the standard
  hh:mm:ss.tt

* DATETIME -- both a date and time.

The data types supported by the DBMS is one place that 
vendors can try to distinguish themselves in the marketplace,
so, depending on the DBMS, there may be many, many other 
types.

Note that SQLite uses the TEXT type for all columns of a 
table despite what you specify when creating a table. It 
does remember what was specified so that when the table is
moved to another system the types will go with it.

To create a table:

  create table movie ( 
    title varchar(32), length int(4), score int(1) 
  );
  

- - - -

Copyright ©2007 Brigham Young University