Back
Next
SQL Tutorial will guide us that 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 employees information, then the columns may include
information such as First Name, Last Name, Address, City, Country, Date of Birth,
Gender
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 'John Austin'), a date/time expression (such as '2007-JAN-01 10:00:00'),
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",
... )
Real Example of Table:
CREATE TABLE employees
(First_Name char(50),
Last_Name char(50),
Gender char(1),
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 "Not Available" and City to
"New York", we would type in
CREATE TABLE employees
(First_Name char(50),
Last_Name char(50),
Gender char(1) ,
Address char(50) default 'Not Available',
City char(50) default 'New York',
Country char(25),
Birth_Date date)
You can also limit the type of information of a table or
a column can hold. This is done through the Constraints keyword, we will discus
constraints in SQL Tutorial section of SQL
Constraints.
Back
Next
|
Home 