Other related articles:

Recently viewed articles:

CREATE TABLE (SQL)

CREATE TABLE (SQL) is used to define new tables in Database Management System (DBMS) and make them ready to accept data. When the user creates a table using 'CREATE TABLE' in SQL, he automatically becomes the owner of newly created table. The new table is created as empty table; means there is no data in it as such. But 'INSERT QUERY' can be used to insert rows in this table later on. 

A very simple CREATE TABLE (SQL) syntax may look like this:

CREATE TABLE tablename (columnname1 datatype1 (size), columnname2 datatype2 (size));


CREATETABLE (SQL) Example 1:

Scenario: Create a table to hold employee information such as employee id, employee name, department, salary, date of joining etc...

Solution: Following Create Table (sql) statement will create this table with specific datatype and thier sizes defined for each column:


CREATE TABLE Emp
(   Emp_id   NUMBER,
   Ename    VARCHAR (20),
   Dept     VARCHAR (20),
   Salary   NUMBER (10, 2),
   DOJ      DATE
);


where: Emp is tablename, Emp_id, Ename, Dept, Salary and DOJ are column names and number, varchar, date are datatypes and ( ) along with defines size for column... '


Image below shows the table created using above ‘CREATE TABLE’ statement. ‘Desc Emp’ is the command used to describe emp table:

create table sql

This 'CREATE TABLE' statement consists of many parts. 'CREATE' and 'TABLE' are keywords. tablename is the value for name of table specified by user. The part in between the parenthesis ( ) defines various column names and their data types in the table.

The order of columns in 'CREATE TABLE' (SQL) statement is the order of actual columns in table from left-to-right. It is recommended to use underscore in table name and column names to make them more readable. Also all SQL command should be followed by semicolon (;).

Each column name must have data type mentioned with it. There are many different data types used in DBMS. The user has to determine what data type is to be used with which column. Each data type should have a defined size. This size is the maximum number of characters that can be stored in that column.

For more information on data types, please visit following link:
SQL Datatypes

This create table query discussed above will create a very simple table definition without any constraints on any of the columns. For information on creating table using constraints please click here.

Discussed below are some of the advanced options used with ‘CREATE TABLE’ in SQL:


CREATE TABLE emp
(
   Emp_id   NUMBER,
   Ename    VARCHAR (20),
   Dept     VARCHAR (20),
   Salary   NUMBER (10, 2),
   DOJ      DATE
)
TABLESPACE XXD
PCTUSED    40
PCTFREE    20
INITRANS   10
MAXTRANS   255
STORAGE    (
            INITIAL          16K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
             )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

Here is a brief introduction to these options:

TABLESPACE: specify the tablespace  that is storage location where the actual data for database objects can be kept. If you omit tablespace, the table will be created in default tablespace for that schema.

PCTUSED & PCTFREE: These are used to define data limits for data block. For example, in above create table statement, PCTFREE is set to 20 which means we can insert data until data block is 80% full (100-20). After this we won’t be able to insert data into this table. To start inserting data again we have to free this table up to 60% (100-40), which is the size defined for PCTUSED.

INITRANS & MAXTRANS: These parameters are used to define number of transaction that can be performed on data block (INITRANS) and the maximum number of transaction that can be performed simultaneously.

STORAGE    (
            INITIAL       
            NEXT            
            MINEXTENTS     
            MAXEXTENTS      
            PCTINCREASE    
            BUFFER_POOL     
           ): Storage clause identifies the storage parameters for this object. INITIAL defined the size of first extent (a contiguous region of computer storage medium reserved for a file) allocated to this table. NEXT represents the size of next extent for this table. MINEXTENTS and MAXEXTENTS identify the minimum and maximum number of extents which can be allocated to this object respectively including INITIAL extent. PCTINCREASE defines the increase in size of extent to be allocated after second extent and so on. BUFFER_POOL let you specify the default buffer pool for this object.

LOGGING|NOLOGGING: specify whether the creation of table or any indexes on this table need to be logged in to the redo log file.

NOCOMPRESS|COMPRESS: to state whether to compress data segment or not to reduce disk space.

CACHE|NOCACHE|CACHE READS: NOCACHE is default value, identifies how Oracle buffers data for this object.

PARALLEL|NOPARALLEL|PARALLEL integer: to instruct if we want parallel operations on data object or not. We can also define degree of parallel operation by using PARALLEL integer.

MONITORING|NOMONITORING: to start or stop the collection of modification statistics on this object.


There are many other database objects which can be created using this 'CREATE' keyword such as:

  • Views
  • Synonyms
  • Indexes
  • Trigger