An Introduction To SQL Commands: DDL, DML, DCL, and TCL

SQL Commands

SQL commands are instructions that are used to communicate with the database. It is used to perform specific tasks, work, and functions with data in the database.

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now

Data Definition Language 

Data Definition Language helps you to define the database structure or schemas. DDL commands are capable of creating, deleting, and modifying data.

Here are some commands that come under DDL:

• CREATE
• ALTER
• DROP
• TRUNCATE

CREATE

It is used to create a new table in the database.

Syntax:

CREATE TABLE TableName (

Column1 datatype,

Column2 datatype,

Column3 datatype,

ColumnN datatype

);

Example:

CREATE TABLE Employees {

Emp_Id int(3),

Emp_Name varchar(20)

};

This will create a table Employees with Emp_ID and Emp_Name.

ALTER

Alter is used to alter the structure of the database.

Syntax:

ALTER TABLE TableName

ADD ColumnName Datatype;

ALTER TABLE TableName

DROP COLUMN ColumnName;

Example:

ALTER TABLE Employees

ADD BloodGroup varchar(255);

This will add a column BloodGroup to the existing table Employees.

ALTER TABLE Employees

DROP BloodGroup varchar(255);

This will drop the column BloodGroup from the existing table.

DROP

It is used to delete both the structure and record in the table.

Syntax:

DROP TABLE TableName;

Example:

DROP TABLE Employees;

This SQL command will remove the table structure along with its data from the database.

TRUNCATE

This truncate command is used to delete all the rows from the table and free the space.

Syntax:

TRUNCATE TABLE TableName;

Example:

TRUNCATE TABLE Employees; 

Data Manipulation Language

Data Manipulation Language (DML) is the language that gives users the ability to access, or manipulate the condition that the appropriate data model has inherited.

Here are some SQL commands that come under DML:

• INSERT
• UPDATE
• DELETE

INSERT

INSERT command is used to insert new rows or records in a table.

Syntax:

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)  

VALUES (value1, value2, value3,...valueN);

Example:

INSERT INTO Employees(Emp_Id, Emp_Name) VALUES(04, “Sam Tully”);

UPDATE

This command is used to update or modify the value of a column in the table.

Syntax:

UPDATE table_name

SET column1 = value1, column2 = value2...., columnN = valueN

WHERE [condition];

Example:

UPDATE Employees 

SET Salary = 1000 

WHERE Emp_Id = 04;

The above code will modify the salary of the employee with Emp_ID= 04;

DELETE

DELETE is used for removing one or more rows from the table.

Syntax:

DELETE FROM TableName 

WHERE Condition;

Example:

DELETE FROM Employees 

WHERE Emp_Id = 04;

This will delete the record of the Employees whose Emp_ID is 4.

Data Control Language

Data Control Language is used to manage roles, permissions, and referential integrity on the database.

Here are some commands that come under DCL:

• GRANT
• REVOKE

GRANT

GRANT command is used to give access or permission to specific users.

Syntax:

GRANT object_privileges ON table_name TO user_name1;

Example:

GRANT SELECT, UPDATE ON Student TO John Doe;

REVOKE

REVOKE  is used for taking back permission, which is given to the user.

Syntax:

REVOKE object_privileges ON table_name FROM user1, user2,… userN;

Example:

REVOKE UPDATE ON Student FROM John Doe;

Transaction Control Language

TCL manages the issues and matters related to the transactions in any database. They are used to rollback or commit the changes in the database.

Here are some commands that come under TCL:

• COMMIT
• ROLLBACK

COMMIT

The COMMIT command is used to save all the transactions to the database.

Syntax:

COMMIT;

Example:

DELETE FROM Employees

WHERE AGE = 21;

COMMIT;

ROLLBACK

The rollback command is used to undo transactions that have not already been saved to the database.

Syntax:

ROLLBACK; 

Example:

DELETE FROM CUSTOMERS

WHERE AGE = 21;

ROLLBACK;

GROUP BY

The SQL GROUP BY command is used with the SELECT command to group the rows which have the same value.

Syntax:

SELECT column1, column2

FROM table_name

WHERE [ conditions ]

GROUP BY column1, column2

ORDER BY column1, column2;

Example:

Consider the following data:

If you want to know the total points of each customer, then the GROUP BY query would be used as follow:

This would produce the following results-

HAVING

The HAVING clause allows you to filter the results based on the groups. The SQL HAVING clause is used with the GROUP BY clause.

Consider the following dataset:

Following is an example, which would display a record for a similar city that would be equal to ‘Hampton’

This would produce the following result:

ORDER BY 

The ORDER BY keyword in SQL is used to sort the result in ascending or descending order.

Note: By default, the results are sorted in ascending order. 

Syntax: 

SELECT column1, column2, ...

FROM table_name

ORDER BY column1, column2, ... ASC|DESC

Example:

SELECT first_name, last_name
FROM customers
ORDER BY first_name;

Gain expertise in the latest Business analytics tools and techniques with the Post Graduate Program in Business Analysis. Enroll now!

Conclusion

It is crucial to understand the important commands in SQL to understand the basics of how to use and manipulate the dataset. The commands help us in various functions which include writing, updating, modifying, and deleting the data from the tables. Check out our tutorial on replace function in SQL.

Now that you know about the SQL commands, it’s time for you to start querying and manipulating this updated and existing data in different datasets. This is an essential step to moving forward in your journey to becoming an SQL expert. If you liked this article, and want to get certified, check out Simplilearn’s Business Analyst Master’s Program. This comprehensive program covers SQL in-depth, and earning your certification in this field can help to jumpstart your career. 

Do you have any questions for us? Please leave them in the comments section, and we’ll have our experts in the field answer them for you!