DML and DDL Commands in SQL

home bytes tutorials sql dml ddl commands in sql

Course Outline

What is PostGreSQL?

Create and Delete Database in Postgres

Data Types in SQL

DML and DDL Commands in SQL

Last Updated: 31st May, 2024

DDL stands for Data Definition Language and refers to SQL commands used to create, modify, and delete database structures such as tables, indexes, and views. DML stands for Data Manipulation Language and refers to SQL commands used to insert, update, and delete data within a database. Now, let’s learn about the DDL and DML commands in depth.

What are DDL Commands in SQL?

DDL (Data Definition Language) is a type of SQL command used to define data structures and modify data. It creates, alters, and deletes database objects such as tables, views, indexes, and users. Examples of DDL statements include CREATE, ALTER, DROP and TRUNCATE.

What are DML Commands in SQL?

DML (Data Manipulation Language) is a type of SQL command used to manipulate data in a database. It inserts, updates, and deletes data from a database table. Examples of DML statements include INSERT, UPDATE, and DELETE.

Types of DDL Statements

Snega is a talented programmer who has been eager to learn more about the various aspects of Python programming. One day, she comes across a tutorial about databases and SQL queries. She is particularly interested in learning about the basics of databases, such as Data Definition Language (DDL) and Data Manipulation Language (DML). She eagerly starts reading the tutorial. Lets help her in the journey.

CREATE: It is used to create objects in the database, such as tables, views, stored procedures, and more.
ALTER: It is used to modify the structure of an existing database object.
DROP: It is used to delete an entire object or part of an object from the database.
TRUNCATE: Used to delete all records from a table but does not delete the table structure.
RENAME: Used to rename an existing database object.

Types of DML Statements

INSERT: Used to add new records to a database table.
UPDATE: Used to modify existing records in a database table.
DELETE: Used to delete existing records from a database table.
MERGE: Used to combine data from two or more tables into one.
SELECT: Used to retrieve data from one or more tables in a database.
CALL: Used to call a stored procedure or function.

DDL vs DML Commands

Explore the difference between DDL and DML commands in the below table. Understand how DDL commands shape database structures, while DML commands manipulate data within the database.

DDL	DML
Used to define database objects like tables, indexes, views, etc.	Used to manipulate data within the database.
Examples of DDL statements include CREATE, ALTER, and DROP.	Examples of DML statements include SELECT, INSERT, UPDATE, and DELETE.
Changes made using DDL affect the structure of the database.	Changes made using DML affect the data stored in the database.
DDL statements are not transactional, meaning they cannot be rolled back.	DML statements are transactional, meaning they can be rolled back if necessary.
DDL statements are usually executed by a database administrator.	DML statements are executed by application developers or end-users.
DDL statements are typically used during the design and setup phase of a database.	DML statements are used during normal operation of a database.
Examples of DDL statements: CREATE TABLE, DROP TABLE, ALTER TABLE, CREATE INDEX, etc.	Examples of DML statements: SELECT, INSERT, UPDATE, DELETE, etc.

Benefits of DDL and DML Commands in SQL

DDL (Data Definition Language) provides the ability to define, create and modify database objects such as tables, views, indexes, and users.
DML (Data Manipulation Language) allows for manipulating data in a database, such as inserting, updating, and deleting records.
DDL and DML commands can be used to ensure data integrity in the database.
DDL and DML commands provide a way to control access to the database by granting and revoking privileges.
DDL and DML commands allow for the efficient retrieval of data from the database.
DDL and DML commands allow for the efficient execution of queries.
DDL and DML commands provide a way to maintain database performance.

DML and DDL Commands in SQL with Examples

DDL (Data Definition Language) Example

CREATE TABLE Employees (
EmployeeID INT,
FirstName VARCHAR(255),
LastName VARCHAR(255),
Department VARCHAR(255)
);
ALTER TABLE Employees
ADD Salary INT;
DROP TABLE Employees;

This code creates a new employee table containing four columns: EmployeeID, FirstName, LastName, and Department. It then adds an additional column, Salary, to the table. Finally, it drops the Employees table from the database.

DML (Data Manipulation Language) Example

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department)
VALUES (1, 'John', 'Smith', 'IT');
UPDATE Employees
SET Salary = 50000
WHERE EmployeeID = 1;
SELECT * FROM Employees;
DELETE FROM Employees
WHERE EmployeeID = 1;

This code inserts a new row into the Employees table with the values of 1, 'John', 'Smith', and 'IT' for the respective columns. It then updates the Salary column for the row with an EmployeeID of 1 to 50000. It then selects all of the rows from the Employees table and finally deletes the row with an EmployeeID of 1.

Best Practices for Using DML and DDL Commands

Always use the most specific DDL statement possible: It is important to use the most specific DDL statement possible to avoid any unintended side effects. For example, using DROP TABLE instead of DROP DATABASE to delete a single table.
Use transactions when making multiple changes: When making multiple changes to a database, it is important to use transactions to ensure that all changes are applied together and that none of the changes are lost.
Avoid using DDL statements within stored procedures: DDL statements should not be used within stored procedures as they can cause unintentional side effects.
Use DML operations cautiously: When using DML operations, such as inserting, updating, or deleting data, it is important to ensure that the data being changed is valid and that the changes are valid for the current state of the database.
Make sure to back up your database regularly: It is important to back up your database regularly in case something goes wrong. This will ensure that you can restore the database to its previous state and avoid data loss.

Key Takeaways

DDL (Data Definition Language) could be a language utilized to characterize and alter information structures in a database, such as making and erasing tables, altering columns, and adding and dropping constraints.
DML (Data Manipulation Language) could be a dialect utilized to control and query information in a relational database, such as embeddings, upgrading, and erasing information.
DDL and DML are two vital components of SQL, the Structured Query Language utilized to associated with databases.
DDL is utilized for making, modifying, and erasing information structures in a database, whereas DML is utilized for controlling and questioning data in a database.
Knowing how to utilize DDL and DML can assist you to work more proficiently with databases and make data structure changes faster.

Quiz

1. What is DDL and DML?

Data Definition Language and Data Manipulation Language
Data Definition Language and Data Mapping Language
Data Design Language and Data Manipulation Language
Design Language and Data Mapping Language

Answer: a. Data Definition Language and Data Manipulation Language

2. What is the purpose of DDL?

To define data types and structures
To manipulate stored data
To store data
To create a database

Answer: a. To define data types and structures

3. What is the purpose of DML?

To define data types and structures
To manipulate stored data
To store data
To create a database

Answer: b. To manipulate stored data

4. What are the components of DDL?

CREATE, ALTER, DROP
SELECT, INSERT, UPDATE
CREATE, ALTER, INSERT
SELECT, ALTER, DRO

Answer:a. CREATE, ALTER, DROP

Module 2: Creating and Managing Databases in Postgres