Module - 2 Creating and Managing Databases in Postgres
Lesson - 4 DDL and DML 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. 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.
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
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.
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
INSERTINTO Employees (EmployeeID, FirstName, LastName, Department)
VALUES (1, 'John', 'Smith', 'IT');
SET Salary = 50000WHERE EmployeeID = 1;
SELECT * 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
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.
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.
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