What is DDL and What is DML in Oracle

What is DDL?

Data Definition Language helps you to define the database structure or schema. DDL commands help you to create the structure of the database and the other database objects. Its commands are auto-committed so, the changes are saved in the database permanently. The full form of DDL is Data Definition Language.

Commands for DDL

Five types of DDL commands are:

CREATE

CREATE statements is used to define the database structure schema:

Syntax:

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

For example:

Create database university;
Create table students;
Create view for_students;

DROP

Drops commands remove tables and databases from RDBMS.

Syntax:

DROP TABLE ;

For example:

Drop object_type object_name;
Drop database university;
Drop table student;

ALTER

Alters command allows you to alter the structure of the database.

Syntax:

To add a new column in the table

ALTER TABLE table_name ADD column_name COLUMN-definition;

To modify an existing column in the table:

ALTER TABLE MODIFY(COLUMN DEFINITION....);

For example:

Alter table guru99 add subject varchar;

TRUNCATE:

This command used to delete all the rows from the table and free the space containing the table.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE table students;

What is DML?

DML commands it to allow you to manage the data stored in the database, although DML commands are not auto-committed. Moreover, they are not permanent. So, It is possible to roll back the operation. The full form of DML is Data Manipulation Language.

Commands for DML

Here are some important DML commands:

  • INSERT
  • UPDATE
  • DELETE

INSERT:

This is a statement that is a SQL query. This command is used to insert data into the row of a table.

Syntax:

INSERT INTO TABLE_NAME  (col1, col2, col3,.... col N)  
VALUES (value1, value2, value3, .... valueN);  
Or 
INSERT INTO TABLE_NAME    
VALUES (value1, value2, value3, .... valueN);

For example:

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

UPDATE:

This command is used to update or modify the value of a column in the table.

Syntax:

UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]

For example:

UPDATE students    
SET FirstName = 'Jhon', LastName=' Wick' 
WHERE StudID = 3;

DELETE:

This command is used to remove one or more rows from a table.

Syntax:

DELETE FROM table_name [WHERE condition];

For example:

DELETE FROM students 
WHERE FirstName = 'Jhon';