bytes

tutorials

sql

dml ddl commands in sql

What are DML and DDL Commands in SQL?

Module - 2 Creating and Managing Databases in Postgres
What are DML and DDL Commands in SQL?

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.

What is 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 is 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.

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

Types of DML Statements

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

DDL vs DML Commands

DDLDML
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

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

DDL and DML 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 DDL and DML

  1. 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.
  2. 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.
  3. Avoid using DDL statements within stored procedures: DDL statements should not be used within stored procedures as they can cause unintentional side effects.
  4. 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.
  5. 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

  1. 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.
  2. 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.
  3. DDL and DML are two vital components of SQL, the Structured Query Language utilized to associated with databases.
  4. 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.
  5. 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 ? 

  1. Data Definition Language and Data Manipulation Language 
  2. Data Definition Language and Data Mapping Language 
  3. Data Design Language and Data Manipulation Language 
  4. Design Language and Data Mapping Language

Answer: a. Data Definition Language and Data Manipulation Language

2. What is the purpose of DDL ? 

  1. To define data types and structures 
  2. To manipulate stored data 
  3. To store data 
  4. To create a database

Answer: a. To define data types and structures

3. What is the purpose of DML ? 

  1. To define data types and structures 
  2. To manipulate stored data 
  3. To store data 
  4. To create a database

Answer: b. To manipulate stored data

4. What are the components of DDL ? 

  1. CREATE, ALTER, DROP
  2. SELECT, INSERT, UPDATE 
  3. CREATE, ALTER, INSERT 
  4. SELECT, ALTER, DRO

Answer:a. CREATE, ALTER, DROP

Related Programs
Full Stack Data Science with Placement Guarantee of 5+ LPA
Course
20,000 people are doing this course
Become a job-ready Data Science professional in 30 weeks. Join the largest tech community in India. Pay only after you get a job above 5 LPA.
Related Tutorials

AlmaBetter’s curriculum is the best curriculum available online. AlmaBetter’s program is engaging, comprehensive, and student-centered. If you are honestly interested in Data Science, you cannot ask for a better platform than AlmaBetter.

avatar
Kamya Malhotra
Statistical Analyst
Fast forward your career in tech with AlmaBetter
Vikash SrivastavaCo-founder & CPTO AlmaBetter
Vikas CTO
Related Tutorials to watch
Top Articles toRead
AlmaBetter
Made with heartin Bengaluru, India
  • Location
  • 4th floor, 133/2, Janardhan Towers, Residency Road, Bengaluru, Karnataka, 560025
  • Follow Us
  • facebookinstagramlinkedintwitteryoutubetelegram

© 2022 AlmaBetter