cft

Understanding SQL Commands

Learn about the different types of SQL commands and the categories in which they are grouped


user

Aviator Ifeanyichukwu

2 years ago | 4 min read

SQL is the Query Language for communicating with relational databases. It is used to store, manipulate, and retrieve data from a database.Queries that are written in order to perform specific tasks are called commands. SQL commands are written instructions used to communicate with the database.

SQL commands makes it possible to achieve tasks that ranges from table creation, adding data to a table, modifying a table, establishing relationships and even setting user permissions.

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)

DDL: Data Definition Language

Data Definition Language commands defines 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)

);

ALTER TABLE Students ADD (

level varchar(10),

department varchar(100)

);

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 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 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;

This 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 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 is able to 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 rollback or commit changes into 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 savepoint can be created at each stage, such as savepoint_1, savepoint_2. When we then want to move to a particular savepoint to inspect what we have done, we could reach to that point using the savepoint command.

SAVEPOINT savepoint_name

SAVEPOINT savepoint_1

Conclusion

In this tutorial, we discussed 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

Upvote


user
Created by

Aviator Ifeanyichukwu

Learning, Writing, Helping others understand Data Analytics


people
Post

Upvote

Downvote

Comment

Bookmark

Share


Related Articles