Question

Write about DDL and DML commands with syntax.

CLASS X
INFORMATION TECHNOLOGY
DATABASE DEVELOPMENT ​

Answer 1

DDL is Data Definition Language

DML is Data Manipulation Language

DCL is Data Control Language

TCL is Transaction Control Language

As you see from its name it allows to define, manipulate and control data and transactions in SQL language.

It’s four types of SQL sub-languages, that’s why it’s no sense to search for a difference between DDL vs DML or DCL vs TCL.

SQL commands list:

LanguageCommand List DDL

CREATE

DROP

ALTER

RENAME

TRUNCATE

 DML

SELECT

INSERT

UPDATE

DELETE

 DCL

GRANT

REVOKE

 TCL

START TRANSACTION

COMMIT

ROLLBACK

Keep reading and I’ll explain in details what are DDL, DML, DCL, and TCL with examples.

Table of Contents [hide]

What is DDL in SQL?

CREATE

DROP

ALTER

RENAME

TRUNCATE

What is DML in SQL?

SELECT

INSERT

UPDATE

DELETE

What is DCL in SQL?

GRANT

REVOKE

What is TCL in SQL?

START TRANSACTION (BEGIN, BEGIN WORK)

COMMIT

ROLLBACK

What is DDL in SQL?

DDL allows you to create SQL statements to make operations with database data structures (schemas, tables etc.).

These are SQL DDL commands list and examples:

CREATE

CREATE statement is used to create a new database, table, index or stored procedure.

Create database example:

1

CREATE DATABASE explainjava;

Create table example:

1

2

3

4

CREATE TABLE user (

  id INT(16) PRIMARY KEY AUTO_INCREMENT,

  name VARCHAR(255) NOT NULL

);

DROP

DROP statement allows you to remove database, table, index or stored procedure.

Drop database example:

1

DROP DATABASE explainjava;

Drop table example:

1

DROP TABLE user;

ALTER

ALTER is used to modify existing database data structures (database, table).

Alter table example:

1

ALTER TABLE user ADD COLUMN lastname VARCHAR(255) NOT NULL;

RENAME

RENAME command is used to rename SQL table.

Rename table example:

1

RENAME TABLE user TO student;

TRUNCATE

TRUNCATE operation is used to delete all table records.

Logically it’s the same as DELETE command.

Differences between DELETE and TRUNCATE commands are:

TRUNCATE is really faster

TRUNCATE cannot be rolled back

TRUNCATE command does not invoke ON DELETE triggers

Example:

1

TRUNCATE student;

What is DML in SQL?

DML is a Data Manipulation Language, it’s used to build SQL queries to manipulate (select, insert, update, delete etc.) data in the database.

This is DML commands list with examples:

SELECT

SELECT query is used to retrieve a data from SQL tables.

Example:

1

SELECT * FROM student;

INSERT

INSERT command is used to add new rows into the database table.

Example:

1

INSERT INTO student (name, lastname) VALUES ('Dmytro', 'Shvechikov');

UPDATE

UPDATE statement modifies records into the table.

Example:

1

UPDATE student SET name = 'Dima' WHERE lastname = 'Shvechikov';

DELETE

DELETE query removes entries from the table.

Example:

1

DELETE FROM student WHERE name = 'Dima';

What is DCL in SQL?

DCL a Data Control Language.

Its commands are responsible for access restrictions inside of the database.

Let’s take a look at DCL statements definitions.

GRANT

GRANT command gives permissions to SQL user account.

For example, I want to grant all privileges to ‘explainjava’ database for user ‘dmytro@localhost’.

Let’s create a user first:

1

CREATE USER 'dmytro'@'localhost' IDENTIFIED BY '123';

Then I can grant all privileges using GRANT statement:

1

GRANT ALL PRIVILEGES ON explainjava.* TO 'dmytro'@'localhost';

and we have to save changes usingFLUSH command:

1

FLUSH PRIVILEGES;

REVOKE

REVOKE statement is used to remove privileges from user accounts.

Example:

1

REVOKE ALL PRIVILEGES ON explainjava.* FROM 'dmytro'@'localhost';

and save changes:

1

FLUSH PRIVILEGES;

What is TCL in SQL?

TCL is a Transaction Control Language.

Its commands are used to manage transactions in SQL databases.

This is TCL commands list:

START TRANSACTION (BEGIN, BEGIN WORK)

START TRANSACTION is used to start a new SQL transaction.

BEGIN and BEGIN WORK are aliases for START TRANSACTION.

Example:

1

START TRANSACTION;

after that, you’re doing manipulations with a data (insert, update, delete) and at the end, you need to commit a transaction.

COMMIT

As a mentioned above COMMIT command finishes transaction and stores all changes made inside of a transaction.

Example:

1

2

3

START TRANSACTION;

INSERT INTO student (name, lastname) VALUES ('Dmytro', 'Shvechikov');

COMMIT;

ROLLBACK

ROLLBACK statement reverts all changes made in the scope of transaction.

Example:

Answer 2

DDL Commands:

(i) It is an acronym for 'Data Definition Language'.

(ii) These are used to define the database structure or schema.

(iii) They create, modify and remove database objects such as tables, indexes and users.

(iv) Common DDL statements are: Create, Alter, Drop, Truncate, Comment, Rename.

Example of DDL:

Create table emp(eno number(5), ename varchar2(10));

alter table emp add(salary number(10));

Rename emp to employee;

Truncate table employee;

Drop table employee;

DML Commands:

(i) It is an acronym for 'Data Manipulation Language'.

(ii) These are used to retrieve, insert and modify database information.

(iii) These commands will be used by all database users during the routine operation of the database.

(iv) Common DML commands are: Insert, update, Delete, Merge.

Example of DML:

insert into emp values(1, 'A');

update emp set ename = 'B' where ename='A';

delete emp;

<!Hope this information helps..>