Create SQL Table
July 12, 2022 2022-12-22 17:55Create 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;
FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
EMPID | Int(11) | NO | PRI | ||
EMPNAME | Varchar(20) | NO | |||
EMPAGE | Int(11) | NO | |||
EMPADDRESS | Varchar(25) | YES | NULL |
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.