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:

TCL (Transaction Control Language)
DML (Data Manipulation Language)
DCL (Data Control Language)
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:

CREATE DDL Command
ALTER DDL Command
DROP DDL Command
TRUNCATE DDL Command
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:

CREATE TABLE Name_of_Table ( Column1 datatype (Length), Column2 datatype (Length) …….);

Example of Create Command:

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

CREATE TABLE Mobile_Details
(
Mobile_Number INT NOT NULL,
Mobile_Name Varchar(50),
Manufacturing_Year INT NOT NULL,
Mobile_Cost INT
);

ALTER Command

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

Syntax of Alter Command to modify the existing table:

ALTER TABLE Name_of_Table ADD Column_Name Datatype (Length of Column);

Example of Alter Command:

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

ALTER TABLE Mobile_Details ADD Mobile_Color Varchar (50);

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:

DROP TABLE Name_of_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:

TRUNCATE TABLE Name_of_Table;

Example of Truncate Command:

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

TRUNCATE TABLE Mobile_Details;

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:

RENAME Old_Table_Name TO New_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:

RENAME Mobile_Details TO Mobile_Records;

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:

SELECT DML Command
INSERT DML Command
UPDATE DML Command
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:

SELECT * FROM Name_of_Table;

Example of SELECT Command:

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

SELECT * FROM Mobile_Records;

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:

INSERT INTO Name_of_Table ( Column_1, Column_2, Column_3, …..) VALUES (Value1_of_Column_1, Value2_of_Column_2, Value3_of_Column_3,…….);

Example of INSERT Command:

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

INSERT INTO Mobile_Records (Mobile_number, Mobile_Name, Manufacturing_Year, Mobile_Cost, Mobile_Color) VALUES (95872, Apple, 2020, 95000, Black);

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:

UPDATE Name_of_Table SET Column_Name = Value WHERE [ Condition ];

Example of UPDATE Command:

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

UPDATE Mobile_Records SET Manufacturing_Year = 2022 WHERE Mobile_Color = 'White';

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:

DELETE FROM Name_of_Table WHERE [ condition ];

Example of DML DELETE Command:

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

DELETE FROM Mobile_Records WHERE Manufacturing_Year = 2019;

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:

Grant DCL Command
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:

GRANT Name_of_Privilege ON Object TO User;

Example of Grant Command:

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

GRANT SELECT ON Mobile_Records TO 'OnePlus'@'localhost';

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:

REVOKE Name_of_Privilege ON Object FROM User;

Example of REVOKE Command:

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

REVOKE SELECT ON Mobile_Records FROM 'OnePlus'@'localhost';

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:

Commit TCL Command
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:

COMMIT;

Example of Commit Command:

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

DELETE FROM Mobile_Records WHERE Mobile_cost = 20000;
COMMIT;

Rollback Command

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

Syntax of Rollback command:

ROLLBACK;

Example of Rollback Command:

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

DELETE FROM Mobile_Records WHERE Mobile_cost = 20000;
ROLLBACK;

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Miscl

SQL MCQ

PL/SQL Tutorial

Sql Interview Question

SQl Quiz