Lesson 2 – SQL Commands

As discussed in lesson 1, there are five types of SQL commands based on functionality. They are;

• DDL (Data Definition Language)

• DML (Data Manipulation Language)

• DQL (Data Query Language).

• DCL (Data Control Language).

• TCL (Transaction Control Language).

Data Definition language (DDL) are a set of SQL commands that are used to create and manipulate databases. DDL statements are used to CREATE, ALTER, DROP, TRUNCATE, COMMENT and RENAME database and database objects.

• CREATE – Just as the name implies, it is used to create database and its objects like tables, index, views, store procedure, function, and triggers.

• ALTER – This is used to alter the structure of an existing database. It can be used to either add or drop columns, primary and foreign key constraints, change data types and may others.

• DROP – This statement is used to delete an entire database or objects from the database. A drop statement removes the entire structure of the affected database or object..

• TRUNCATE – A Truncate statement is an operation that is used to remove all records from a table. The result of this operation quickly removes all data from a table, typically overriding any set of integrity constraints.

• COMMENT – This adds comments to the data dictionary

• RENAME – Used to rename an object in an existing database.

Data Manipulation Language (DML) is used to manipulate data in existing databases. It is one of the common set of commands in SQL. DML statements are used to store, update and delete data.

The commands are:

• UPDATE – This statement is used to update existing data in a table.

• INSERT – This statement is used to insert data into a table

• DELETE – As the name implies, it is used to delete data from a table.

Data Query Language (DQL) is mostly bumped into DML statements but the main purpose of DQL statements is to get some schema relation based on the query passed to it.

• SELECT – This is used to fetch records from the database. It includes functionalities such as a WHERE clause that allows you to specify criteria to search the data in the database.

Data Control Language (DCL) are a set of commands used to give and/or deny access to database. It serves as a security measure as to who is authorized to view or access specific set of data within an organization’s database. This set of commands are;

• GRANT – allows authorized personnel privilege to access a database.

• REVOKE - withdraw privileges given earlier to authorized personnel.

Transaction Control Language (TCL) is used to control transactions in the database. TCL statements are used to manage changes made to the data by a DML statement. These commands are;

• COMMIT – This is used to permanently save any changes in the database. Once any change to the database has been committed, it cannot be rolled back.

• ROLLBACK - This restores the database to last committed state. When we make changes to a database without committing and needs to revert to the old state. The Rollback statement allows us to restore to the previously committed state of the database.

• SAVEPOINT – This command is used to temporarily save a transaction in order to be able to rollback to that point when needed.

I hope this article helps you understand the concept and functionality of the various types of SQL commands.