Introduction to SQL statements: DDL, DML, DCL, and TCL

 

SQL is a structured query language used to store, manipulate and retrieve data present on a database server.

MySQL is a relational database management system. It supports large databases and is customizable. This article will cover basic statements in SQL.

 

 

• SQL statements are instructions. Accustomed to communicating with the database. It is also used to execute specific tasks, functions, and data queries.

• SQL can perform various tasks such as creating a table, adding data to tables, deleting a table, modifying a table, and setting permissions for a user.

 

 

• DDL – Data Definition Language

• DQl – Data Query Language

• DML – Data Manipulation Language

• DCL – Data Control Language

 

 

Data Definition Language helps you define a database structure or schema. Let's learn about DDL statements with syntax.

The five types of DDL statements in SQL are:

CREATE

CREATE statements are used to define the database structure schema:

Syntax:

CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,….]);

For example:

Create a database university;

Create a table of students;

Create view for_students;

DECREASE

Drops commands remove tables and databases from an RDBMS.

Syntax

DOP TABLE ;

For example:

Drop object_type object_name;

Drop database university;

Folding table student;

ALTER

The Alters command enables you to change the database structure.

Syntax:

Adding a new column to a table

ALTER TABLE table_name ADD column_name column definition;

To edit an existing column in a table:

ALTER TABLE MODIFY(COLUMN DEFINITION….);

For example:

Alter table theiotacademy add subject varchar;

SHORTEN:

This command removes all rows from a table and frees up the space containing the table.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE table students;

 

 

Data Manipulation Language (DML) allows you to modify a database instance by inserting, changing, and deleting its data. It is responsible for performing all types of data modifications in the database.

The three basic constructs that allow database programs and users to enter data and information are:

Here are some basic DML statements in SQL:

INSERT

UPDATE

DELETE

 

INSERT:

This command is an SQL query. This command is utilized to insert data into a table row.

Syntax:

INSERT INTO TABLE_NAME (column1, column2, column3,… column N)

VALUES (value1, value2, value3, …. value);

Or

INSERT INTO TABLE_NAME

VALUES (value1, value2, value3, …. value);

For example:

INSERT INTO students (RollNo, FirstName, LastName) VALUES ('60', 'Tom', Erichsen');

UPDATE:

This command is utilized to update or modify a column value in a table.

Syntax:

UPDATE table_name SET [column_name1 = value1,…column_nameN = valueN] [NOTE WHERE]

For example:

Student UPDATE

SET First Name = 'Jhon', Last Name = 'Wick'

WHERE StudID = 3;

DELETE:

This command is utilized to delete one or more rows from a table.

 

Syntax:

DELETE FROM table_name [WHERE condition];

For example:

DELETE FROM students

WHERE Name = 'Jhon';

 

 

DCL (Data Control Language) contains commands like GRANT and REVOKE that help grant "rights and permissions." Additional permissions control database system parameters.

Examples of DCL commands:

Commands that fall under DCL:

Grant

Cancel

Grant:

This command is used to grant a user access rights to the database.

Syntax:

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

For example:

GRANT SELECT ON Users TO'Tom'@'localhost;

Cancel:

It is helpful to back up permissions from the user.

Syntax:

DELETE privilege_name ON object_name FROM {user_name |PUBLIC |role_name}

For example:

DELETE SELECT, UPDATE STUDENT FROM BCA, MCA;

 

 

Transaction Control Language (TCL) is utilized in the database to handle transactions. This command is used to process edits to DML statements. TCL allows you to connect your statements into logical transactions.

Below are some TCL commands:

COMMIT Command: Commit is used to save all transactions in the database.

Syntax: COMMIT;

Example: UPDATE Employee SET DOB=’1995-02-17’ WHERE Emp_Name=’Joe’;

COMMIT;

This example inserts dob into a table named =Joe and then COMMITs the changes to the database.

ROLLBACK Statement: If any grouped SQL statement produces an error, all modifications must be rolled back. The term "rollback" directs to the process of rolling back changes. This command can only roll back transactions since the last COMMIT or ROLLBACK command.

Syntax: ROLLBACK;

Example: UPDATE Employee SET DOB=’1995-02-17’ WHERE Emp_Name=’Joe’;

ROLLBACK;

This example would insert dob into a table named =Joe and then ROLLBACK the changes in the database. So this operation does not affect the table.

SAVEPOINT statement: Used to roll back a transaction to a specific point, not the entire transaction.

Syntax: SAVEPOINT SavepointName;

Among all transactions, this command is used exclusively to create a SAVEPOINT.

ROLLBACK is a statement that is used to undo a set of transactions.

Syntax of the rollback to savepoint command:

ROLLBACK TO SavepointName;

Example: SAVEPOINT S1; //save point created

DELETE FROM Employee WHERE Emp_Name = 'Joe'; //deleted

SAVEPOINT S2; //The saved point has been created.

 

 

DQL statements are used to query data contained in schema objects. The goal of DQL statements is to return a schema relation depending on the query. DQL can be defined as follows: It is the part of an SQL statement that allows you to retrieve data from a database and put it in order. The SELECT statement is included. This command will enable you to extract data from a database and perform its actions. This is the exact as the relational algebra projection process. When a SELECT statement is achieved on a table or set of tables, the result is assembled into a new temporary table, which is then displayed or received by the program, i.e., the front-end.

The database server looks at the select clause, which is the first and one of the last clauses of the select statement. This is because we need to know all the alternative columns that could be included in the resulting result set before deciding what will be in it.

            Syntax: SELECT expressions FROM Tables WHERE conditions;

            Example: SELECT Emp_Name FROM Employee WHERE Mobile=9935467903;

The above command selects a record from the 'Employee' table where the mobile number is '9935467903'.

 

 

In this blog, we have discussed all the five categories of SQL statements, i.e., DCL, DML, DDL, TCL, and DQL, and their subtypes. We have gone through each command in detail with its syntax and an example to help you write queries. SQL statements allow you to create and manipulate different database objects using various statements. After going through this blog, you can easily play SQL commands and work with their database.