Understanding SQL Commands

SQL (Structured Query Language) serves as the query language for interacting with relational databases. It enables the storage, manipulation, and retrieval of data from a database. Commands written in SQL to perform specific tasks are referred to as SQL commands. These commands function as written instructions to communicate with the database.

SQL commands facilitate a wide range of tasks, including table creation, data insertion into tables, table modification, the establishment of relationships between tables, and even setting user permissions within the database.

These commands are grouped into 5 categories

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Transaction Control Language (TCL)
• Data Query Language (DQL)

Note: For this article, MySQL will be the preferred database used in examples and demonstrations.

DDL: Data Definition Language

Data Definition Language commands define the database structure or schema. It is a set of SQL commands used to create, modify, and delete database structures.

Commands include:
CREATE, ALTER, DROP, TRUNCATE, RENAME

CREATE:
This command is used in creating the database and its associated objects. Some objects of the database include Table, View, Stored Procedure, Trigger, etc

Examples

CREATE DATABASE Students_DB

CREATE TABLE table_name (
  column_name1 data_type(size),
  column_name2 data_type(size)
);

There are also some advanced use cases of the create statement, where it can be used to create views, stored procedures, etc.

Note: These are advanced SQL concepts, so if you aren’t feeling adventurous at the moment, you can safely ignore these concepts.

CREATE VIEW view_name
AS
SELECT column1 [,column2 ] from table_name

CREATE PROCEDURE
 procedure_name()
BEGIN
 SELECT * FROM Table_name
END

ALTER
This is used to alter the structure of the database or its objects
The alter command changes the structure of the database objects. This command can be used to add a new attribute.

ALTER TABLE table_name ADD (
 column_name1 data_type (size),
 column_name2 data_type (size)
);

Also, to modify existing attributes/columns added to a table, the alter command comes in handy

Syntax

ALTER TABLE table_name MODIFY (
 column_name new_data_type(new_size)
);

ALTER TABLE Students MODIFY (
level char(30)
);

To drop a column, the alter command can also perform such an operation

ALTER TABLE table_name DROP COLUMN column_name;

To rename an existing attribute/column name, we can also make use of the alter command

ALTER TABLE Table_name
RENAME COLUMN Old_column_name TO New_column_name;

DROP
This command removes/deletes a database or table

DROP DATABASE Students_DB
DROP TABLE Students

TRUNCATE
This command will delete all the records/rows present in a table. The database table isn’t removed, only the records stored

TRUNCATE TABLE Students

RENAME
Renames an object existing in a database.

RENAME old_table_name TO new_table_name

RENAME Employees_Info TO Employees

DML: Data Manipulation Language

These commands are responsible for performing all types of data manipulation. It allows the user to modify a database table by inserting, modifying/updating, and deleting its data.

Rollbacks(reverse of query operations) to DML statements are possible if the operations we carried out were made out of error.

DML Commands include
INSERT, UPDATE, DELETE

INSERT
This command is used to insert new records into a database table

INSERT INTO Employees (emp_id, name, department)
VALUES (“rec64”, “John”, “Finance);

UPDATE
This command modifies or updates records stored in a table

UPDATE Employees SET name = “Mark”
WHERE emp_id = “bbc54”;

DELETE
Removes one, more, or all rows/records in a table

DELETE FROM Employees;

The above command removes all records of a company’s employees from the Employees table.

To remove a record based on a condition

DELETE FROM Employees WHERE name = “John”;

DQL: Data Query Language

Data Query Language (DQL) is used to fetch/retrieve data from the database. It uses only one command: the SELECT clause

SELECT column_1 [,column_2] FROM table_name

SELECT * FROM Employees
SELECT name, department FROM Employees

DCL: Data Control Language

Protects information in a table from unauthorized access. With a DCL command, a user can either be enabled or disabled from accessing information from a database or its objects. This command is used in managing roles and permissions.

Syntax

GRANT object_privileges ON table_name
TO user_name1[, user_name2]

Example

GRANT SELECT, UPDATE ON Students TO ‘Aviator’@localhost

Here, the database administrator is granting only select and update privileges to a database user with the name Aviator. This user is only allowed to perform select and update queries on the Students table but other queries such as alter and delete are restricted.

GRANT ALL ON Students TO ‘Aviator’@localhost

Here, the database user named Aviator is granted all privileges on the database table named Students. The user can create, update, alter, and delete records from a database table named Students.

REVOKE

REVOKE object_privileges ON table_name
FROM user_name1[, user_name2]

Used for taking back permission granted to a user.

REVOKE UPDATE ON Students FROM ‘Aviator’@localhost

If a database administrator wants to take back privileges and permission granted to a user, the revoke command is used.
With the above example, the user named Aviator is being revoked of the update privileges granted to them. In this case, they won’t be able to perform update queries on the database table named Students again.

TCL: Transaction Control Language

TCL manages all operations related to transactions in a database. They make it possible to roll back or commit changes to the database.
TCL commands can only be used with DML commands like INSERT, DELETE, and UPDATE.

Commands under TCL include
COMMIT, ROLLBACK, SAVEPOINT

COMMIT
Saves all transactions in a database. With this command, all changes made during a transaction are made permanent.

DELETE FROM Students WHERE registration_number = 20;
COMMIT;

ROLLBACK
This command is used to undo transactions that have not already been committed/saved into the database. All changes made during a transaction are undone. This could be because of an error or mistake made during the commit process of a transaction.

DELETE FROM Students WHERE registration_number = 20;
ROLLBACK;

SAVEPOINT
It is used to roll a transaction back to a certain point without having the entire transaction rolled back.

During the process of making a commit into the database, several savepoints can be created at each stage, such as savepoint_1, and savepoint_2.
When we are ready to move to a particular savepoint to inspect what we have done, we could reach that point using the savepoint command.

SAVEPOINT savepoint_name

SAVEPOINT savepoint_1

Conclusion

In this tutorial, you learned the different types of SQL commands and the categories in which they are grouped. I do hope this article gave you some understanding of SQL commands and would serve as a helpful guide as you continue to learn about SQL.

Connect with me on Linkedin Twitter