SQL CREATE TABLE
SQL CREATE TABLE statement is used to create table in a database.
If you want to create a table, you should name the table and define its column and each column's data type.
Let's see the simple syntax to create the table.
("column1" "data type",
"column2" "data type",
"column3" "data type",
...
"columnN" "data type");
The data type of the columns may vary from one database to another. For example, NUMBER is supported in Oracle database for integer value whereas INT is supported in MySQL.
Let us take an example to create a STUDENTS 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.
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
PRIMARY KEY (ID)
);
You can verify it, if you have created the table successfully by looking at the message displayed by the SQL Server, else you can use DESC command as follows:
SQL> DESC STUDENTS;
| 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 STUDENTS table available in your database and you can use to store required information related to students.
SQL CREATE TABLE Example in MySQL
Let's see the command to create a table in MySQL database.
(
EmployeeID int,
FirstName varchar(255),
LastName varchar(255),
Email varchar(255),
AddressLine varchar(255),
City varchar(255)
);
SQL CREATE TABLE Example in Oracle
Let's see the command to create a table in Oracle database.
(
EmployeeID number(10),
FirstName varchar2(255),
LastName varchar2(255),
Email varchar2(255),
AddressLine varchar2(255),
City varchar2(255)
);
SQL CREATE TABLE Example in Microsoft SQLServer
Let's see the command to create a table in SQLServer database. It is same as MySQL and Oracle.
(
EmployeeID int,
FirstName varchar(255),
LastName varchar(255),
Email varchar(255),
AddressLine varchar(255),
City varchar(255)
);
Create a Table using another table
We can create a copy of an existing table using the create table command. The new table gets the same column signature as the old table. We can select all columns or some specific columns.
If we create a new table using an old table, the new table will be filled with the existing value from the old table.
The basic syntax for creating a table with the other table is:
SELECT column1, column2,...
FROM old_table_name WHERE ..... ;
The following SQL creates a copy of the employee table.
CREATE TABLE EmployeeCopy AS
SELECT EmployeeID, FirstName, Email
FROM Employee;
SQL Primary Key with CREATE TABLE Statement
The following query creates a PRIMARY KEY on the "D" column when the "Employee" table is created.
MySQL
EmployeeID NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
City varchar(255),
PRIMARY KEY (EmployeeID)
);
SQL Server / Oracle / MS Access
EmployeeID NOT NULL PRIMARY KEY,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
City varchar(255)
);
Use the following query to define a PRIMARY KEY constraints on multiple columns, and to allow naming of a PRIMARY KEY constraints.
For MySQL / SQL Server /Oracle / MS Access
EmployeeID NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
City varchar(255),
CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID, FirstName)
);
