Creating Tables
All tables within a database must be created at some point in time...let's
see the format of a simple create table statement:
CREATE TABLE table_name
(column_name1 data_type(constraint),
column_name2 data_type(constraint),
..);
Let's have a quick look at the above query.
"CREATE TABLE" is the keyword to create a new table.
"table_name" and "column_name1" are the name of the table and column respectively. These names must start with a letter and can be followed by letters, numbers or underscores and should not exceed a total of 30 characters in length. Do not use any SQL reserved keywords as names for table or column names.
The data type specifies what type of data that particular column can store. Most common data types are shown below:
| char(x) | A column of fixed-length character string, where x is a number designating the number of characters allowed. Max 255 bytes. |
| varchar(x) | Variable-length character string, where x denotes the maximum size. |
| integer | A column of whole numbers, positive or negative. |
| date | A date column in a DBMS-specific format.
|
| logical | A column that can hold only two values: TRUE or FALSE. |
The constraint is optional. A constraint is basically a rule associated with a column that the data entered into that column must follow. E.g. a most popular constraint is "not null" which specifies that a column can't be left blank.
Example:
CREATE TABLE Emp_Add
(ssn INTEGER NOT NULL,
fname VARCHAR (200),
lname VARCHAR (200),
address VARCHAR (500),
city VARCHAR (100),
state VARCHAR (100));
This statement creates a table named "Emp_Add", containing six columns named ssn, fname, lname, address, city and state. It also tells the DBMS about the type of data that can be stored in each column of the table. The table when viewed at this point will look as follows:
| ssn |
fname |
lname |
address |
city |
state |
| |
|
|
|
|
|
