SQL Languages

SQL Languages are those languages that allow the database users to read, modify, delete and store data in the database systems.

Following are the four different types of languages or commands which are widely used in SQL queries:

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

DDL (Data Definition Language)

Data Definition Languages allow users to create, modify, and destroy the schema of database objects.

We can enter the correct data in the database by applying the constraints in the DDL languages.

The DDL Languages or commands are categorized into five commands which are widely used in the SQL queries:

1. CREATE DDL Command
2. ALTER DDL Command
3. DROP DDL Command
4. TRUNCATE DDL Command
5. RENAME DDL Command

Let's discuss each DDL command with syntax and examples.

CREATE Command

This DDL command allows us to create the new table, function, stored procedure, and other database objects.

Syntax of Create DDL Command to create a new table in the database:

Example of Create Command:

The following SQL query creates the new Mobile_Details table using CREATE DDL command:

ALTER Command

This DDL command allows us to modify the structure of database objects.

Syntax of Alter Command to modify the existing table:

Example of Alter Command:

The following SQL query adds the new column in the Mobile_Details table using ALTER DDL command:

DROP Command

This DDL command allows us to remove the table definition and data from the SQL systems.

Syntax of Drop Command to remove the existing table:

Example of Drop Command:

The following SQL query deletes the Mobile_Details table using the DROP DDL command:

DROP TABLE Mobile_Details;

TRUNCATE Command

This DDL command allows the database users to remove all the existing records from the table.

Syntax of Truncate Command to delete all records:

Example of Truncate Command:

The following SQL query deletes all the inserted records from the Mobile_Details table using the TRUNCATE DDL command:

RENAME Command

This DDL command allows the users to change the name of the existing table.

Syntax of RENAME Command for changing the table name:

Example of Rename Command:

The following SQL query changes the name of Mobile_Details table to Mobile_Records table using the RENAME DDL command:

DML (Data Manipulation Language)

Data Manipulation languages allow database users to change the existing data of the tables.

We can use this type of language when we want to access the record, insert the new record, update the record, and delete the existing values from the tables.

Following are the four DML Languages or commands used in the SQL queries:

1. SELECT DML Command
2. INSERT DML Command
3. UPDATE DML Command
4. DELETE DML Command

Let's discuss each DML command with syntax and examples.

SELECT Command

This DML command allows us to access the stored records from the tables. We can also use the condition in the SELECT command for accessing the particular rows.

Syntax of SELECT Command:

Example of SELECT Command:

The following SQL query shows the records of the Mobile_Records table using the SELECT DML command:

INSERT Command

This DML command allows the database users to insert the new record or rows in the tables.

Syntax of INSERT Command for inserting the single record:

Example of INSERT Command:

The following SQL query inserts the single record of mobile into the Mobile_Records table using the INSERT DML command:

UPDATE Command

This DML command allows the database users to change the existing record or rows in the tables.

Syntax of UPDATE Command for modifying the records:

Example of UPDATE Command:

The following SQL query updates the values of the Mobile_Records table using the UPDATE DML command:

DELETE Command

This DML command allows the database users to delete a particular record or row from the tables.

Syntax of DELETE Command for removing the records:

Example of DML DELETE Command:

The following SQL query deletes the values from the Mobile_Records table using the DELETE DML command:

DCL (Data Control Language)

Data Control Languages allow DBA to manage the rights and permissions on the data in the database.

Following are the two DCL Languages or commands used in the SQL queries:

1. Grant DCL Command
2. Revoke DCL Command

Let's discuss the above two DCL commands one by one with syntax and examples.

GRANT Command

This DCL command allows the database administrator to give permissions to the user for retrieving the data.

Syntax of Grant DCL Command:

Example of Grant Command:

The following query grants the SELECT privilege on the Mobile_Records table:

REVOKE Command

This DCL command allows the database administrator to remove all the permissions applied by the GRANT DCL command.

Syntax of REVOKE DCL Command:

Example of REVOKE Command:

The following query removes the SELECT privilege from the Mobile_Records table:

TCL (Transaction Control Language)

Transaction Control languages maintain the SQL operations within the database. It also saves the changes made by the DML commands.

Following are the two TCL Languages or commands used in the SQL queries:

1. Commit TCL Command
2. Rollback TCL Command

Let's discuss the above TCL commands one by one with syntax and examples.

COMMIT Command

This command allows the database users to save the operations in the database.

Syntax of Commit command:

Example of Commit Command:

The following statements delete the record from the Mobile_Record table and commit the changes in the database:

Rollback Command

This command allows the database users to restore the transactions to that state which was last committed.

Syntax of Rollback command:

Example of Rollback Command:

The following statements delete the record from the Mobile_Record table and rollback the changes in the database: