Create SQL Table

Create SQL Table :

SQL stands for Structured Query Language that allows us to access and manipulate databases. With SQL, we can run queries against databases, we can insert and update data in a database, can retrieve and delete from a database, can create new databases, tables, views, and procedures in a database, and can set permission on tables, views, and procedures. SQL is used to develop websites with a database application.

To create SQL table database, the SQL CREATE TABLE statement will be used. It is important to name the newly created table and define its column along with its data type. Below is the simple syntax for creating the table.

create table “tablename”

(“column1” “data type”,

“column2” “data type”,

“column3” “data type”,

….

“columnN” “datatype”);

Here is an example for creating a table name “EMPLOYEE” with EMPID as the primary key. NOT NULL denotes that the fields must not be null while creating records in the table.

SQL > CREATE TABLE EMPLOYEE (

EMPID INT NOT NULL,

EMPNAME VARCHAR(20) NOT NULL,

EMPAGE INT NOT NULL,

EMPADDRESS CHAR (25),

PRIMARY KEY (EMPID) );

Displaying the table in a table format

Once the table has been created, you can verify and view the table by using the DESC command as follows

SQL > DESC EMPLOYEE;

4 rows in set (0.00 sec)

Creating a Table in MySQL

Following is the example to create a table in the MySQL database

CREATE TABLE Employee (

EmployeeID int,

EmpName varchar (255),

Email varchar (255),

AddressLine varchar (255),

City varchar (255)

);

Creating a Table in Oracle

Below is the example for creating a table in Oracle

CREATE TABLE Employee

(

EmployeeID number(10),

EmpName varchar(255),

Email varchar(255),

AddressLine varchar (255),

City varchar (255)

);

Creating a Table in Microsoft SQL Server

Following is the example for creating a table in MS SQL Server

CREATE TABLE Employee

(

EmployeeID int;

EmpName varchar (255),

Email varchar (255),

Address varchar (255),

City varchar (255)

);

Creating a new table using the existing table

It is possible to create a copy of the existing table using create table command and the new table can get all the fields and columns from the old table or we can select the required columns for the new table.

The syntax for creating a table with an existing table

CREATE TABLE table_name AS

SELECT column1, column2, ……

FROM old_table_name WHERE ……;

SQL creates the following copy of the employee table

CREATE TABLE EmployeeCopy AS

SELECT EmployeeID, EmpName, Email

FROM Employee;

Assigning PRIMARY KEY in a TABLE

Use the following query for the respective database applications for creating a primary key to the table

MySQL

CREATE TABLE Employee (

EmpID NOT NULL,

EmpName varchar (255) NOT NULL,

City varchar (255),

PRIMARY KEY (EmpID)

);

SQL Server/Oracle/MS Access

CREATE TABLE Employee

EmpID NOT NULL PRIMARY KEY,

EmpName varchar (255) NOT NULL,

City varchar (255),

);

Use the following query for creating a PRIMARY KEY for multiple columns of the table

For MySQL / SQL Server / Oracle / MS Access

CREATE TABLE Employee (

EmpID NOT NULL,

EmpName varchar (255),

City varchar (255),

CONSTRAINT PK_Employee PRIMARY KEY (EmpID, EmpName));

Conclustion

Learn more about creating a table and querying against tables in various database applications by enrolling in our SQL Training in Chennai at SLA Institute.