SQL DDL vs DML Commands Quiz

SQL DDL and DML

In SQL, two fundamental broad categories of commands exist: Data Definition Language (DDL) and Data Manipulation Language (DML). Both sets of commands serve distinct purposes. However, understanding their differences begins with recognizing the similarities between them.

SQL DDL

The main objective of Data Definition Language (DDL) is to shape the database's structure. It provides a means to describe, create, and manipulate the metadata, including tables, indices, and other database objects. Some common DDL operations are:

• Create: Used to establish new database objects, such as tables, indexes, procedures, or functions.
• Alter: Essentially used for modifying the structure of an already present table by adding or removing columns or modifying their parameters.
• Drop: Execution of this command results in the removal of specified database objects, like tables.
• Truncate: Removal of all records from a particular table while keeping the table structure intact.
• Comment: Adds explanatory text to the database definitions.

For instance, let's say you have a database with a table named employees. Using the CREATE command, you could create a table for employees with their respective columns:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    emp_salary FLOAT
)

After successfully executing this command, a new table called employees would be created. Now, assuming you wish to add a new column called emp_address to this table, you would use the ALTER command:

ALTER TABLE employees ADD COLUMN emp_address VARCHAR(100)

If you decide to delete the table entirely, you can utilize the DROP command:

DROP TABLE employees

Finally, if you need to remove all records from the employees table, you can employ the TRUNCATE command:

TRUNCATE TABLE employees

SQL DML

On the other hand, Data Manipulation Language (DML) enables the interaction with data stored within the database. It focuses on performing operations involving CRUD (Create, Read, Update, Delete) actions. Specifically, DML includes the following commands:

• Select: This command retrieves data from the database, returning information from tables or views.
• Insert: Utilized to introduce new rows of data into the table.
• Update: Facilitates modification of existing rows in a table.
• Delete: Erases selected rows from the table.

As an example, suppose we have an employees table containing details about each employee. With the INSERT command, you can add a new entry for a new hire:

INSERT INTO employees VALUES (101, 'John', 'Software Engineer', 80000)

To update an existing entry, say you want to increase the emp_salary of employee 101, you can apply the UPDATE command:

UPDATE employees SET emp_salary = 90000 WHERE emp_id = 101

Lastly, if you wish to remove an employee with emp_id 101, you can utilize the DELETE command:

DELETE FROM employees WHERE emp_id = 101

In conclusion, understanding the difference between DDL and DML is crucial for effectively utilizing SQL commands. While DDL deals with database structure definition, DML facilitates data manipulation within the established structural framework.