SQL – CREATE TABLE Statement

CREATE TABLE statement is used for creating tables in a database. Tables are organized in rows and columns. Where columns are the attributes and rows are known as records.

Syntax:

CREATE TABLE tableName
(
columnName_1 data_type,
columnName_2 data_type,
columnName_3 data_type,
columnName_4 data_type,
....
....
PRIMARY KEY (Column_Name(s))
);

CREATE TABLE Example

SQL> CREATE TABLE EMPLOYEES(
   SSN  CHAR(10)         NOT NULL,
   EMP_NAME VARCHAR(35)  NOT NULL,
   EMP_AGE  INT          NOT NULL,
   EMP_ADDR  VARCHAR(40) ,     
   PRIMARY KEY (SSN)
);

The above statement would create a table named “EMPLOYEES” in the database having primary key as “SSN”.
Note: Since SSN is a primary key it cannot has duplicate values as this column is used for identifying the unique record (row) from table.

SQL> DESC CUSTOMERS;
+---------+---------------+------+-----+---------+
| Field   | Type          | Null | Key | Default | 
+---------+---------------+------+-----+---------+
| SSN     | int(11)       | NO   | PRI |         |       
| EMP_NAME| varchar(20)   | NO   |     |         |       
| EMP_AGE | int(11)       | NO   |     |         |      
| EMP_ADDR| char(25)      | YES  |     | NULL    |      
+---------+---------------+------+-----+---------+
5 rows in set (0.00 sec)

Once the table is created you can perform the other operations like insert, delete, truncate etc.

CREATE TABLE from an existing table

Syntax:

CREATE TABLE TableName AS
   SELECT (columnName_1, columnName_2...)
   FROM OldTableName
   WHERE Clause

Example:

Lets say we have a table STUDENT having below records:

+---------+---------------+------+
| RollNo  | Name          | Age  | 
+---------+---------------+------+
| 1234    | Abey          | 23   |  
| 256     | Ajeet         | 21   |    
| 22      | Chaitanya     | 22   |    
| 890     | Anuj          | 23   |    
| 123     | Rahul         | 18   |   
+---------+---------------+------+

Now I want to create a table named “AGE” from the above table. This is how I should be doing it:

SQL> CREATE TABLE AGE AS
   SELECT RollNo, Age
   FROM STUDENT;

The new table “AGE” would be having the following records:

+---------+------+
| RollNo  | Age  | 
+---------+------+
| 1234    | 23   |  
| 256     | 21   |    
| 22      | 22   |    
| 890     | 23   |    
| 123     | 18   |   
+---------+------+