Data Definition Language
SQL Tutorials

How to Create table in SQL | Basic SQL Commands

Amit S March 7, 2017

How to Create table in SQL using multiple ways 

SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in relational database. In this article i wanted to give the basic idea of Basic SQL Commands which are used in day to day life with its syntax and real life examples. Basic SQL commands are used to select the data, insert the data in to table, giving the grants and access to tables etc. In this article i will try to give idea about How to Create  table in SQL with multiple ways. The basic idea behind this is user will get the information about How to Create table in SQL but there are multiple ways of creating tables. Before that we need to check about the basic SQL commands with its explanation.

How to Create a table in SQL
Basic SQL Commands

1.Data Definition Language(DDL) Commands:

1.1.Create:  Create Command is used to create new table,new view or any database objects from the table.

1.2.Alter: Alter Statements are used to modify the existing database object such as add new column in the table,remove column from the table,enable disable constraints from the table.

1.3.Drop:Drop statement deletes the entire table,view and other database objects.

Click here to get 20 Most important Tech mahindra interview Questions..

2.Data Manipulation Language(DML) Commands:

2.1 Update:Update statement of SQL are used to update the records from the table

2.2 Insert: Insert statement of SQL are used to insert the records in the table.

2.3 Delete : Delete statement in SQL are used to delete the records from the table.

3.Data Control Language(DCL) commands:

3.1 Grant:Grant command gives the privilege to the user.

3.2 Revoke: Revoke command takes back the privileges from the user

4.Data Query Language Commands:

4.1 Select:

Select Command is used to retrieve the records from the table.

Data Definition language:

We will start with data definition language.The data definition language as the name contains ‘data definition’ are used to define the structure of the different objects in the databases whether it is table,view e.t.c.Data definition Language statements used to create,alter or drop the different database objects such as tables,views,indexes.

1.1.How to Create table in SQL Using multiple ways :

User should have ‘Create’ privilege to create table,views,indexes.Means if user wants to create view he/she should have create view privilege.Te CREATE TABLE statement is used to create tables and store data.Here you will get proper idea about How to Create a table in SQL using multiple ways.

Type 1: Create Table without adding constraints

1) Syntax:

CREATE TABLE <Table_Name>

(Column_Name1 Data_Type (Size),

Column_Name2 Data_Type (Size),

…Column_NameN Data_Type (Size)

);

Example:

CREATE TABLE Student

(RollNo Number (10),

FName Varchar2 (15),

LName Varchar2 (15),

Class Number (10),

DOB Date,

Gender Varchar2 (15));

In Above Example We have missed to add the constraints.You should add the Constraints using following Syntax:

ALTER TABLE <Table_Name>

MODIFY Column_Name Data_Type (Size) PRIMARY KEY;

Example:

ALTER TABLE Student

MODIFY RollNo Number (10) PRIMARY KEY;

Click Here to Check 20 Most Important Complex SQL Queries..

Type 2 :  Create table in SQL using other table

User can create a table using other table.

Syntax:

CREATE TABLE <Table_Name>

AS SELECT * FROM <Table_Name>;

Example:

CREATE TABLE Student_Temp

AS SELECT * FROM Student;

In the above statement, Student_Temp Table is created with the same number of Columns and Data_Type as Student Table but no CONSTRAINTS are carried over.

Type 3 :Create table with Adding Constraints

Integrity Constraints like Primary Key, Unique Key, and Foreign Key can be defined for the Columns while creating the Table. The Integrity Constraints can be defined at Column level or Table level.

Syntax:

CREATE TABLE <Table_Name>

(Column_Name1 Data_Type (Size),

Column_Name2 Data_Type (Size),

Constraint Constrant_name   primary key(Column_name1……)

);

Example:

CREATE TABLE Student

(RollNo Number (10),

FName Varchar2 (15),

LName Varchar2 (15),

Class Number (10),

DOB Date,

Gender Varchar2 (15),

Constraint PK_Roll_No primary key (RollNo));

In Above example we have defined the primary key constraint for RollNo Column.

Type 4 : Only Selecting columns from the table

Syntax:

CREATE TABLE <Table_Name>

AS SELECT <Column_Name1>,<Column_Name2>…FROM <Table_Name>;

Example:

CREATE TABLE Student_TEMP1

AS SELECT RollNo, FName, LName FROM Student;

1.In the above statement, Student_TEMP1 Table is created with the Specified number of Columns and Data_Type as Student Table but no CONSTRAINTS are carried over.

2.Also, while writing Column names you should not include Column names in parenthesis.

3.Data_Type is same as  Student Table but no CONSTRAINTS are carried over.

These are some ways of How to Create a table in SQL.

Click Here To Get 20 Most important interview Questions for IBM..

2. ALTER STATEMENT

 The ALTER TABLE command is used to modify the Definition/Structure of a Table by modifying the definition of its columns. The ALTER command is used to perform the following functions:

ALTER Statement Usage:

1) Add, Modify, and Drop Columns

2) Add and Drop Constraints

3) Enable and Disable Constraints

1.ADD Column to Table using Alter:

Syntax:

ALTER TABLE <Table_Name>

ADD <Column_Name> Data_Type (Size);

 Example :

ALTER TABLE Student

ADD Percent Number(5,2);

2.Modify column Using Alter Statement:

Syntax:

ALTER TABLE <Table_Name>

MODIFY <Column_Name> Data_Type (Size);

Example:

ALTER TABLE Student

MODIFY Percent Varchar2 (10);

In above example we modify percent column  and change its datatype from number to varchar2

What is meaning of Modify?

  1. a) In the above Statement MODIFY means, you can change the Data_Type & Size of the Column
  2. b) To change the Data_Type, Column Data must be Empty but you can Increase/Decrease size.

3.Change The Column Name:

Syntax:

ALTER TABLE <Table_Name>

RENAME COLUMN <Old_Column_Name> TO <New_Column_Name>;

Example:

ALTER TABLE STUDENT

RENAME COLUMN Percent TO Percentage;

4.Drop The column:

Syntax:

ALTER TABLE <Table_Name>

DROP COLUMN <Column_Name>;

Example:

ALTER TABLE Student

DROP COLUMN Percentage;

5. How to  ADD a Constraint: [Existing column from a table]

Syntax:

ALTER TABLE <Table_Name>

ADD PRIMARY KEY <Column_Name>;

ALTER TABLE <Table_Name>

MODIFY <Column_Name> PRIMARY KEY;

ALTER TABLE <Table_Name>

MODIFY <Column_Name> Data_Type (Size) PRIMARY KEY;

In the above queries, PRIMARY KEY can be added & at the same time Data_Type Size can be Increased or Decreased. Also, a Table can have only ONE PRIMARY KEY.

Example:

 ALTER TABLE Student

ADD PRIMARY KEY (RollNo);

ALTER TABLE Student

MODIFY RollNo PRIMARY KEY;

ALTER TABLE Student

MODIFY RollNo Number (10) PRIMARY KEY;

6.How to DROP a Constraint:

ALTER TABLE <Table_Name>

DROP <Constraint_Name>;

Example:

ALTER TABLE Student

DROP PRIMARY KEY;

3.DROP TABLE:

The DROP TABLE Statement is used to DELETE the Rows in the Table and the Table Structure is removed from the Database.

Syntax:

DROP TABLE <Table_Name>;

Example:

DROP TABLE Student;

CLICK HERE TO GET INFORMATION ON BASICS OF PERFORMANCE TUNING

Once a Table is dropped we cannot get it back through ROLLBACK because DROP is a DDL Command.

Hope you like the article on How to Create a table in SQL using multiple ways.I have tried to give the multiple ways to create a table. Dont forget to comment this article on How to Create a table in SQL using multiple ways.

HOME