Tables are the basic structure
where data is stored in the
database. Given that in most
cases, there is no way for the
database vendor to know ahead of
time what your data storage needs
are, chances are that you will
need to create tables in the
database yourself. Many database
tools allow you to create tables
without writing SQL, but given
that tables are the container of
all the data, it is important to
include the CREATE
TABLE syntax
in this tutorial.
Before we dive into the SQL
syntax for CREATE
TABLE, it is a good idea to
understand what goes into a table.
Tables are divided into rows and
columns. Each row represents one
piece of data, and each column can
be thought of as representing a
component of that piece of data.
So, for example, if we have a
table for recording customer
information, then the columns may
include information such as First
Name, Last Name, Address, City,
Country, Birth Date, and so on. As
a result, when we specify a table,
we include the column headers and
the data types for that particular
column.
So what are data types?
Typically, data comes in a variety
of forms. It could be an integer
(such as 1), a real number (such
as 0.55), a string (such as 'sql'),
a date/time expression (such as
'2000-JAN-25 03:22:22'), or even
in binary format. When we specify
a table, we need to specify the
data type associated with each
column (i.e., we will specify that
'First Name' is of type char(50) -
meaning it is a string with 50
characters). One thing to note is
that different relational
databases allow for different data
types, so it is wise to consult
with a database-specific reference
first.
The SQL syntax for CREATE
TABLE is
CREATE
TABLE "table_name"
("column 1" "data_type_for_column_1",
"column 2" "data_type_for_column_2",
... )
So, if we are to create the
customer table specified as above,
we would type in
CREATE
TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)
Sometimes, we want to provide a
default value for each column. A
default value is used when you do
not specify a column's value when
inserting data into the table. To
specify a default value, add
"Default [value]" after the data
type declaration. In the above
example, if we want to default
column "Address" to "Unknown" and
City to "Mumbai", we would type in
CREATE
TABLE customer
(First_Name char(50),
Last_Name char(50),
Address char(50) default 'Unknown',
City char(50) default 'Mumbai',
Country char(25),
Birth_Date date)
