What are SQL Commands?

 

SQL Command

DDL: Data Definition Language

All DDL commands are auto-committed. That means it saves all the changes permanently in the database.

Command	Description
Create	to create new table or database
Alter	for alteration
Truncate	delete data from a table
Drop	to drop a table
Rename	to rename a table

Table 1: DDL Commands

DML: Data Manipulation Language

DML commands are not auto-committed. It means changes are not permanent to database, they can be rolled back.

Command	Description
Insert	to insert a new row
Update	to update existing row
Delete	to delete a row
Merge	merging two rows or two tables

Table 2: DML Commands

TCL: Transaction Control Language

These commands are used to keep a check on other commands and their effect on the database. These commands can terminate changes made by other commands by rolling back to original state. It can also make changes permanent.

Command	Description
Commit	to permanently save
Rollback	to undo the change
save point	to save temporarily

Table 3: TCL Commands

DCL: Data Control Language

Data control language provides a command to grant and take back authority.

Command	Description
Grant	grant permission of the right
Revoke	take back permission

Table 4 : DCL Commands

DQL: Data Query Language

Command	Description
Select	retrieve records from one or more table

Table 5: DQL Commands

Create command

create is a DDL command used to create a table or a database.

Creating a Database

To create a database in RDBMS, create command is used.

Syntax: create database database-name;

Example: create database Test;

The above command will create a database named Test.

 

Creating a Table

create command is also used to create a table. We can specify names and datatypes of various columns along.

Syntax:

create table table-name

{

 column-name1 datatype1,

 column-name2 datatype2,

 column-name3 datatype3,

 column-name4 datatype4

};

create table command will tell the database system to create a new table with given table name and column information.

Example: create table Student (id int, name varchar, age int);

 

alter command: Used for alteration of table structures. There are various uses of alter command, such as,

• to add a column to the existing table
• to rename any existing column
• to change the datatype of any column or to modify its size
• to drop a column

Using alter command we can add a column to an existing table.

Syntax: alter table table-name add (column-name datatype);

Example: alter table Student add (address char);

 

To add a column with Default Value

alter command can add a new column to an existing table with default values.

Syntax: alter table table-name add (column-name1 datatype1 default data);

Example: alter table Student add (dob date default '1-Jan-99');

 

To Modify an existing Column: Used to modify data type of an existing column.

Syntax: alter table table-name modify (column-name datatype);

Example: alter table Student modify (address varchar (30));

 

To Rename a column: Using alter command user can rename an existing column.

Syntax: alter table table-name rename old-column-name to column-name;

Example: alter table Student rename address to Location;

 

To Drop a Column: Used to drop columns also.

Syntax: alter table table-name drop(column-name);

Example: alter table Student drop(address);

 

truncate command

The truncate command removes all records from a table. But this command will not destroy the table's structure. When we apply truncate command on a table its Primary key is initialized.

Syntax: truncate table table-name

Example: truncate table Student;

 

drop command

drop query completely removes a table from the database. This command will also destroy the table structure.

Syntax: drop table table-name

Example: drop table Student;

 

rename command

rename command is used to rename a table.

Syntax: rename table old-table-name to new-table-name

Example: rename table Student to Student-record;

 

DML Commands

1) INSERT command

Insert command is used to insert data into a table.

Syntax: INSERT into table-name values (data1, data2,)

Example:

Consider a table Student with following fields.

S_id     S_Name          age

 

INSERT into Student values(101,'Adam',15);

The above command will insert a record into Student table.

S_id     S_Name          age

101      Adam                15

 

2) UPDATE command

Update command is used to update a row of a table.

Syntax: UPDATE table-name set column-name = value where condition;

Example:

update Student set age=18 where s_id=102;

S_id     S_Name          age

101      Adam              15

102      Alex                 18

103      chris                14

Example:

UPDATE Student set s_name='Abhi’, age=17 where s_id=103;

The above command will update two columns of a record.

 

S_id     S_Name          age

101      Adam               15

102      Alex                 18

103      Abhi                17

 

3) Delete command

Delete command is used to delete data from a table. Delete command can also be used with the condition to delete a particular row.

Syntax: DELETE from table-name;

Example: DELETE from Student;

The above command will delete all the records from Student table.

 

TCL command

Transaction Control Language (TCL) commands are used to manage transactions in the database. These are used to manage the changes made by DML statements. It also allows statements to be grouped together into logical transactions.

Commit command

Commit command is used to permanently save any transaction into the database.

Syntax: commit;

 

Rollback command

This command restores the database to last committed state. It is also used with savepoint command to jump to a save point in a transaction.

Syntax: rollback to save point-name;

 

Savepoint command

savepoint command is used to temporarily save a transaction so that you can rollback to that point whenever necessary.

Syntax: savepoint savepoint-name;

 

DCL command

System: creating a session, table etc. are all types of system privilege.

Object: any command or query to work on tables comes under object privilege.

DCL defines two commands,

Grant: Gives user access privileges to the database.

Revoke: Take back permissions from the user.

Example: grant create session to username;

S_id	s_Name	Age	Address
101	Adam	15	Noida
102	Alex	18	Delhi
103	Abhi	17	Rohtak
104	Ankit	22	Panipat

Table 6: DCL Command Example