SQL - CREATE Table

Creating a basic table involves naming the table and defining its columns and each column's data type.

The SQL CREATE TABLE statement is used to create a new table.

Let's see the simple syntax to create the table.


create table "tablename" 

("column1" "data type", 

"column2" "data type", 

"column3" "data type", 

... 

"columnN" "data type"); 			

The data type of the columns from one database to another. For example, NUMBER is supported in Oracle database for integer value where as INT is supported in MySQL.

Now see example to create a EMP table with ID as primary key and NOT NULL are the constraint showing that these fields cannot be NULL while creating records in the table.


SQL> CREATE TABLE EMP ( 

ID INT NOT NULL, 

NAME VARCHAR (19) NOT NULL, 

AGE INT NOT NULL, 

ADDRESS CHAR (26), 

PRIMARY KEY (ID) 

); 			

if you have created the table successfully by looking at the message displayed by the SQL Server. Use DESC command.

SQL>DESC EMP;
Field Type Null Key Default Extra
ID Int(11) No PRI
NAME varchar(20) NO
AGE Int(11) NO
ADDRESS Varchar(25) YES NULL

4 rows in set (0.00 sec)

Now you have the EMP table available in your database and you can use to store required information related to EMP

SQL CREATE TABLE Example in MySQL


CREATE TABLE Student  

( 

StudentID int, 

FirstName varchar(250), 

LastName varchar(250), 

Email varchar(250), 

AddressLine varchar(250), 

City varchar(250) 

); 

SQL CREATE TABLE Example in Oracle

Now see the command to create a table in Oracle database.


CREATE TABLE Student  

( 

StudentID number(10), 

FirstName varchar2(250), 

LastName varchar2(250), 

Email varchar2(250), 

AddressLine varchar2(250), 

City varchar2(250) 

); 

SQL CREATE TABLE Example in Microsoft SQLServer

Now see the command to create a table in SQLServer database. It is same as MySQL and Oracle.


CREATE TABLE Student  

( 

StudentID int, 

FirstName varchar(250), 

LastName varchar(250), 

Email varchar(250), 

AddressLine varchar(250), 

City varchar(250) 

);