What are different types of SQL commands?

Share

In this article we gonna learn about different type of SQL commands. If you don’t know what SQL is? Please read our SQL post and then witch back to this post. For those who already know what SQL is, let’s explore different types of SQL commands.

In general, SQL commands are grouped into following depending on their functionality:

  1. Data Definition Language ( DDL )
  2. Data Manipulation Language ( DML )
  3. Transaction Control Language ( TCL )
  4. Data Control Language ( DCL )

Let’s explore more about each of these SQL command types.

Data Definition Language ( DDL ) Commands

These are SQL command that are used to define database schemas ( Table, columns, objects ). Mainly, it deals with the description of database schema. Operations like creating, updating, deleting the structure of database objects. some DDL commands are CREATE, ALTER, DROP.

  • CREATE: It creates the database and its objects like table, functions, views, index, etc.
    For example:
CREATE TABLE Students { st_Id int(3), st_Name varchar(20) }; 
This will create a table Students with column st_Id which stores integer data of length 3 and st_Name which stores string data of length 20.
  • ALTER: It modify the existing database objects. It can add, drop and alter the columns in table.
    For example:
ALTER TABLE Students ADD st_Roll DOUBLE(8,2);
This query will add a column st_Roll to the existing table Students.

ALTER TABLE Students DROP COLUMN st_Roll; 
This query will delete the column st_Roll from the table Students.

ALTER TABLE Students MODIFY st_Name VARCHAR(40); 
This query will modify the existing column st_Name in table Students and change the size to 40 from 20.
  • DROP: we use it to delete the various existing database objects – an entire database, an entire table, a view of a table or other objects in the database also we can not get them back.
    For example:
DROP object object_name;
object: Keyword that represents the database object.

DROP DATABASE Class;
The database Class is deleted from the relational database management system.

DROP TABLE Students;
This SQL command will remove the tab(le structure along with its data from the database.

Data Manipulation Language ( DML ) Commands

These SQL commands are use to storing, retrieving, updating and deleting the data stored in database. Mainly, it deals with manipulation or say managing the data. some DML commands are INSERT, UPDATE, SELECT and DELETE.

SELECT:

We use to retrieve or fetch the data from a table in a database. Using SELECT command is so flexible that, you can fetch entire table or selected columns as well. furthermore, we can fetch data based on our specific condition as well. The data returned by this command is called result-set.
For example:

SELECT col1, col2,… colN FROM table_name WHERE condition;
  • col1,col2,… colN: represent the fields of the table that we want to retrieve. We can also use ‘*’ in place of column names to retrieve all the columns.
  • table_name: specifies the table from where we want to fetch the data.
  • condition: the condition is specified in the WHERE clause which is optional. It is used to filter out the data according to the specified condition.
SELECT * FROM Students;
Retrieves all the records with data from every field form the Students table.
SELECT Stu_Id, Stu_Name FROM Students;
Retrieves all the records with data of only field Stu_Id and Stu_Name form the Employees table.
SELECT * FROM Students WHERE Stu_Id = 11;
Retrieves all field information form the Students table of the Student whose ID is 11.

INSERT:

We use it to insert new data into row of a table.
For example:

INSERT INTO table_name(col1, col2,…) VALUES(value1, value2,…);
INSERT INTO Students(Stu_Id, Stu_Name) VALUES(7, “John Doe”);

UPDATE:

We use it to update or modify already existing data of a table in database. We can update single column or multiple column at once as well.
For example:

UPDATE table_name SET col1 = value1, col2 = value2,… colN = vlaueN WHERE condition;

UPDATE Employees SET marks = 100 WHERE Stu_Id = 11;
Modifies the marks(single column) of the student with Stu_Id 11 and sets Salary to 100.

UPDATE Employees SET marks=100, status=“pass” WHERE Stu_Id = 11;
Modifies the marks and status (multiple-column) of student with Stu_Id 1.

DELETE:

We use it to delete the existing data from table in a database. We can delete single or multiple records and also some specific data based on our condition.
For example:

DELETE FROM table_name WHERE condition;
// where clause is optional

DELETE FROM Students WHERE Stu_Id = 10;
Deletes the record (single row) of the Student whose Stu_Id is 10.

Transaction Control Language ( TCL ) commands

See the source image

These commands deals with transaction within database. A transaction is a unit of work that is performed against a database which results in a change in the state of data. COMMIT, ROLLBACK, and SAVEPOINT are some TCL commands.

  • COMMIT: We use it to save changes invoked by a transaction to the database. It saves all the transactions or changes to the database since the last COMMIT or ROLLBACK command. After COMMIT the changes cannot be undone.
  • ROLLBACK: We use it to undo transactions or changes that have not already been saved to the database. This command can only be used to undo transactions since the last COMMIT or ROLLBACK command was used. It restores the database to its original form since the last COMMIT.
  • SAVEPOINT: A SAVEPOINT is a name given to a point in a transaction. It serves as a checkpoint or a marker within a transaction. A save-point is a way of implementing sub-transactions within a RDBMS.

Data Control Language ( DCL ) commands

 These SQL commands deals with providing security to database objects( like table, views, procedures, etc). It allow us to control access to data within the database. GRANT and REVOKE are DCL commands.

  • GRANT: We use this command to give access or permission to specific users or roles on specific database objects like table, view, etc.
  • REVOKE: This command is used to revoke access or permission provided.

If you want to learn SQL and use it in real world projects. Make sure you keep exploring our SQL posts.
Reference PDF: sql_tutorial.pdf
Peace !! Happy coding !!

Share
Sudeep Mishra

Sudeep Mishra

Healing

Leave a Reply

Your email address will not be published.

%d bloggers like this: