Languages in SQL and its Examples

Introduction about the languages in SQL

There are three languages that control the SQL database. Like creating a table and modifying the data inside the table this type of operation will perform using the SQL languages, these languages are as follows.

DML

The DML stands for Data Manipulation Language

TCL

The TCL stands for Data Control Language.

DDL

The DDL stands for Data Definition Language. The standard command in SQL like create, select, Delete, Update  are divided into these languages.

DML 

Data manipulation language is used to retrieve information from the table and modify the data which is present in the table this language in SQL is widely used to retrieve data from multiple tables.

There are some commands of DML in SQL. 

• SELECT – retrieve data from the database
• INSERT – insert data into a table
• UPDATE – updates existing data within a table
• DELETE – deletes all records from a table, the space for the records remain.

With the help of these commands of DML, we can retrieve, modify, insert the data from the table. 

Select Command

Select command in SQL is used to select the columns from the table to retrieve the rows from it.

Without a select clause, we cannot retrieve the rows from the table. 

Syntax of Select Command:-

Select [Column_name]

From [table name]

Cndition_Clause [Condition column];

For example:-

Display first_name and salary from the employees’ table?

Query:-

Select first_name,salary 
From employees;

Output:-
First_name                         Salary
Abel				    24000
Rambo			    12000

Here we use Select statement to select the columns first_name and last_name to retrieve the information.

Insert command

After creating a table by using DDL language the main task is ti full the data into the created table the DML  insert command is used to insert a row in the table. 

Syntax of Insert Command:-

Insert Into [Table_name] values (column_value1,column_value2);

For example:-

This is the predefined table insert the row inside the table.

Query:-

Insert into students Values(101,'Abhishek','civil',123456)



Insert into students Values(102,'Sahil','mech',78912)

Output:-

Here we insert these tow rows inside the table.

Update Command

The update command is used to change values that already exist in a table.

After adding some information if you want to update the records or change that time you have to use update command.

Syntax of update command:-

UPDATE table

SET column_name = value [column = value …]

[WHERE condition];

For example:-

Data in a table :-

Update the branch_id=civil  to branch_id=ctech ?

Update students
set branch_id='ctech'
where branch_id='civil';
Output:-

STUDENT_ID STUDENT_NAME         BRANCH_ID  STUDENT_NO
       101 		Abhishek            		ctech        123456
       102 		Sahil               		 mech            78912

Here we update the existing value in the table.

DELETE Command

Delete command is used to delete some specific rows followed by the condition which we pass in the where clause from the table.

Syntax of Delete command:-

Delete From table _name

where condition;

For example:-

Data in a table:-

Delete the record who have a student_id 101. ?

Query:-

SQL> Delete from Students
  2  wherestudent_id =101;

Output:- 1 row deleted.
STUDENT_ID STUDENT_NAME         BRANCH_ID  STUDENT_NO
102 		Sahil               		 mech            78912

TCL 

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

There are some commands of TCL in SQL

• Commit
• Savepoint
• Rollback

With the help of these commands in SQL, we can easily control the database structure.

COMMIT: Commit command is used to permanently store or save any transaction into the database.

ROLLBACK: This command restores the database to last committed point It is also used with savepoint command to jump to a savepoint with its name in a transaction.

SAVEPOINT: Savepoint command is used to temporarily save a transaction with a savepoint name that you can rollback to that point whenever necessary.

Commit Command

COMMIT command is used to permanently store or save any transaction into the database.

When we use any Data manipulation language commands like INSERT, UPDATE, or DELETE, the modification made by these commands are not permanent until the session is closed, the changes made by these commands can be rolled back.

To avoid that, we use the COMMIT command to identify the changes as permanent.

Syntax of Commit Command:-

Commit;

For example:-

Create a table student and commit the session.

Query:-
Create table students(roll_no number(10),name varchar2(20));
commit;

Output:-
Commit complete;

Savepoint Command

SAVEPOINT command is used to tentatively save a transaction so that you can rollback to that point whenever needed.

Using this command we can name the various states of our data in any table and then rollback to that state using the ROLLBACK command whenever needed.

Syntax of savepoint Command:-

Savepoint  [name of savepint];

For example:-

Update the name of the student name to ‘Rishikesh’ whose student id is 101 and save it temporarily.

STUDENT_ID STUDENT_NAME         BRANCH_ID  STUDENT_NO

Query:-
Update students 
Set student_name =’Rishikesh’
Where stuednt_id=101;
Savepoint A;

Output:-
Savepoint completed;

Rollback Command

This command returns the database to the last committed state. It is also used with the SAVEPOINT command to jump to a savepoint with its name in a current transaction.

If we have used the UPDATE command to make some modifications into the database, and realize that those modifications were not required, then we can use the ROLLBACK command to rollback those modifications, if they were not committed using the COMMIT command.

Syntax of savepoint Command:-

Rollback;

Rollback  [name of savepint];

For example:-

Update the name of the student name to ‘Rishikesh’ whose student id is 101 and save it temporarily and then rollback the changes.

STUDENT_ID STUDENT_NAME         BRANCH_ID  STUDENT_NO

Query:-
Update students 
Set student_name =’Rishikesh’
Where stuednt_id=101;
Savepoint A;
Rollback A;


Output:-
rollback completed;

Here we rollback all the updated information which we update recently by using the name of savepoint and rollback command.

Conclusion

In this blog, you will get knowledge of all the languages in SQL.