SQL CREATE TABLE

Table is a basic element of the database. Before storing data in the table you have to create it. SQL provides CREATE TABLE statement to allow you to do so. Here is the common syntax of SQL CREATE TABLE statement:

CREATE TABLE table_name(
Column_name1 data_type(data_length ),
Column_name2 data_type(data_length ),
..
)

There are four components you need to specify when creating a database table:

1. Table name. It is suggested that the name should be meaningful and in plural form of a noun. For example products is the name of a table which stores product data.
2. Name of each column in the table. It should be meaningful also and in a noun with singular form.
3. Data type for each column. Choose the correct data type for column is very important in the design phrase. The most common data types are text (varchar, nvarchar), numerical (smallint, int, bigint), date, time datetime, timespan,blob…you can refer it in the specific RDBMS.
4. Maximum length of each column. You have to specify the maximum length of data for each column. For example if you store product name, try to imagine the maximum length a product can have, such as 255 characters.

Beside those components above you can have another additions for each column such as the column is mandatory or not (NULL or not NULL), default value of the column and value of the column is unique or not. If a column is unique and not null it can be a primary key of the table.

Here is an example of creating products table.

CREATE TABLE products (                                 
            ProductID int(11) NOT NULL AUTO_INCREMENT,            
            ProductName varchar(40) NOT NULL,                     
            SupplierID int(11) DEFAULT NULL,                      
            CategoryID int(11) DEFAULT NULL,                      
            QuantityPerUnit varchar(20) DEFAULT NULL,             
            UnitPrice decimal(19,4) DEFAULT NULL,                 
            UnitsInStock smallint(6) DEFAULT NULL,                
            UnitsOnOrder smallint(6) DEFAULT NULL,                
            ReorderLevel smallint(6) DEFAULT NULL,                
            Discontinued tinyint(4) NOT NULL,                     
            availableOnline bit(1) DEFAULT NULL,                  
            PRIMARY KEY (ProductID)
          )

Once table created, you can drop it by using SQL DROP TABLE statement. Here the syntax of the SQL DROP TABLE statement:

DROP TABLE table_name

You need to specify the table name you want to remove from the database. Once removed, all the data is lost and there is no way to recover it again.