DDL and DML Statements: The Complete Guide

Meta Description:

This blog post provides a comprehensive guide to DDL and DML statements in SQL. It covers all of the basic and advanced concepts, and provides examples of how to use these statements in real-world applications. If you're new to SQL, or you want to learn more about DDL and DML statements, this blog post is a great place to start.

Introduction:

Data Definition Language (DDL) and Data Manipulation Language (DML) are two of the most important types of SQL statements. DDL statements are used to create, modify, and drop database objects, such as tables, views, and stored procedures. DML statements are used to insert, update, and delete data from tables.

This blog post provides a comprehensive guide to DDL and DML statements. It covers all of the basic and advanced concepts, and provides examples of how to use these statements in real-world applications.

What is SQL?

Let's begin by defining SQL before delving into DDL and DML. Relational databases can be managed and manipulated using the programming language SQL. A database can be used to create, read, update, and delete data. Numerous applications, including e-commerce, healthcare, and finance, can manage data using SQL.

Use of SQL:

Users can access data kept in a relational database management system (RDBMS) thanks to it.
It assists in better characterizing the facts.
It helps you to define and manipulate data in a database.
Using SQL statements, DBMS databases and tables can be created and deleted.
A view, a stored procedure, and a function can all be created in a database using SQL.
Users' permissions can be set for tables, procedures, and views.

What are DDL and DML?

Data Definition Language, or DDL, is used to specify the database's structure. Database objects like tables, views, and indexes may all be created, modified, and deleted using DDL statements. The database's data can be changed using DML, which stands for Data Manipulation Language. Data in a table can be added, updated, and deleted using DML statements.

Understanding DDL Statements

Data Definition Language is referred to as DDL in SQL. The structure of database objects including tables, views, indexes, and constraints can be defined and modified using this set of SQL commands. In a database, objects can be created, modified, and deleted using DDL statements.

Use of DDL:

We can design and organize our database using DDL.
Data access is more securely efficient thanks to DDL.
The same database can be used by numerous users.

Following are the SQL, DDL statements include the following:

CREATE
DROP
TRUNCATE
ALTER
RENAME
COMMENT

1. CREATE statement:

The database and its objects, such as creating tables, functions, and views, are created with this statement.

Syntax:

CREATE OBJECT_TYPE OBJECT_NAME;

a) Creating Database:

Syntax:

CREATE DATABASE database_name;

database_name : name of the database(you can give any name)

Example:

CREATE DATABASE school;

The above example will create a database named school.

b) Creating Table:

Syntax:

CREATE TABLE table_name(

column1 data_type(size),

column2 data_type(size),

column3 data_type(size),

column4 data_type(size),

…..

);

CREATE TABLE is the keyword that tells the database system what you want to do. In this case, you want to create a new table.
table_name is the name of the new table. Table names must follow the rules for identifiers. Table names can be a maximum of 128 characters, except for local temporary table names (names prefixed with a single number sign ( # )) that can't exceed 116 characters.
column_name1 is the name of the first column in the table.
data_type is the data type of the column. The following are some of the most common data types:
INT - An integer value.VARCHAR - A variable-length string value.
DATE - A date value.
TIME - A time value.
DATETIME - A date and time value.
size is the optional size of the column. If the size is not specified, the column will be the default size for the data type.

Example:

CREATE TABLE students (

id INT NOT NULL AUTO_INCREMENT,

first_name VARCHAR(255),

last_name VARCHAR(255),

email VARCHAR(255)

);

The above example creates a table called students with the following columns:

id: An integer column that is the primary key of the table.
first_name: A varchar column that stores the student's first name.
last_name: A varchar column that stores the student's last name.
email: A varchar column that stores the student's email address.

2) DROP Statement:

The DROP statement can be used to delete a database or a table. If you delete a table, all of the data in the table will also be deleted.

Syntax:

DROP object object_name

i) DROP Database:

Syntax

DROP DATABASE database_name;

Example:

DROP DATABASE school;

The above example delete the database called school from your MySQL server. This will also delete all of the tables and data in the database.

ii) DROP Table

Syntax:

DROP TABLE Table_name;

Example:

DROP TABLE students;

The above example will delete the table called students from your MySQL server. This will also delete all of the data in the table.

3) TRUNCATE Statement:

Truncate command helps to remove all records from a table but it does not delete the table itself.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE students;

The above example will delete all of the rows from the table students. This will not delete the table itself, just the data in it.

4) ALTER Statement:

Alter command is helpful to change or modify the structure of the database or its object.

Following are the some examples of how to use the ALTER TABLE statement:

i) To add a new column to a table:

Syntax:

ALTER TABLE table_name ADD column_name data_type;

Example: To add a column called age to the students table, you would use the following statement:
ALTER TABLE students ADD age INT;

ii) To delete a column from a table:

Syntax:

ALTER TABLE table_name DROP COLUMN column_name;

Example: To delete the age column from the students table, you would use the following statement:

ALTER TABLE students DROP COLUMN age;

iii) To modify the data type of a column:

Syntax:

ALTER TABLE table_name CHANGE column_name new_column_name new_data_type;

Example: To change the data type of the age column from INT to VARCHAR(255), you would use the following statement:

ALTER TABLE students CHANGE age new_age VARCHAR(255);

iv) To add a constraint to a table:

Syntax:

ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition;

Example: To add a primary key constraint to the students table, you would use the following statement:

ALTER TABLE students ADD CONSTRAINT students_pk PRIMARY KEY (id);

v) To delete a constraint from a table:

Syntax:

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Example: to delete the primary key constraint from the students table, you would use the following statement:

ALTER TABLE students DROP CONSTRAINT students_pk;

5) RENAME Statement:

Rename is helpful to rename an object existing in the database. However, the RENAME statement is not standard SQL syntax and is not supported by all database management systems. Some database management systems use the ALTER TABLE statement instead of the RENAME statement.

i) RENAME Database:

Syntax:

ALTER DATABASE "Old_DatabaseName" RENAME TO "New_DatabaseName";

Example:

ALTER DATABASE “school” RENAME TO “college”;

The above example will change the database called school to college.

ii) RENAME Table:

Syntax:

ALTER TABLE old_table_name RENAME TO new_table_name;

Example: To change the name of the table students to students_new, you would use the following statement:

ALTER TABLE students RENAME TO students_new;

6) COMMENT Statement:

A comment in SQL is a statement or justification that the database skips over. SQL statements can either be prevented from being executed or have their purpose explained in comments.

There are two types of comments in SQL:

Single-line comments:

Single-line comments start with two hyphens (--) and end at the end of the line.

Example:

-- This is a single-line comment.

Multi-line comments:

Multi-line comments start with /* and end with */. The comment can span multiple lines.

This is a multi-line comment.

It can span multiple lines.

Understanding DML Statements:

DML stands for Data Manipulation Language. It is a set of SQL statements that are used to add, update, delete, and retrieve data from a database. The four main DML statements are:

INSERT
UPDATE
DELETE
SELECT

Uses of DML statements:

Following are the used of DML statements.

Adding new customers to a customer database.
Changing the price of products in an inventory database.
Deleting old records from a log database.
Merging two tables of customer data into a single table.

1) INSERT Statement:

The INSERT statement is used to add new rows of data to a table.

Syntax:

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

The table_name is the name of the table that you want to insert the data into.
The column1, column2, column3, ... are the names of the columns in the table that you want to insert the data into.
The value1, value2, value3, ... are the values that you want to insert into the columns.

a) INSERT VALUES method:

Example:

INSERT INTO students (name, age, gender) VALUES ('Mohan Chaudhary', 16, 'male');

The above examples adds a new row to the "students" table with the values "Mohan Chaudhary" for the name column, 16 for the age column, and “male” for the gender column.

Note: The INSERT statement can also be used to insert multiple rows of data at the same time. For example, the following statement would insert two new rows into the students table:

INSERT INTO students (name, age, gender)

VALUES ('Hari Sharma, 15, 'male'),

('Janaki Deol', 17, 'female');

b) INSERT SELECT method:

The INSERT SELECT method is used to insert data into a table from another table.

Example:

INSERT INTO new_students (first_name, last_name, email)

SELECT first_name, last_name, email

FROM old_students;

The above example inserts data from the "old_students" table into the "new_students" table, mapping the columns in the SELECT statement to the columns in the INSERT statement.

2) UPDATE Statement:

The UPDATE statement is used to modify the existing records in a table.

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition

Example:

UPDATE Student SET FirstName = "Navin" , LastName = "Kumar" WHERE StudentId=45678;

The code above is an UPDATE statement that will update the FirstName and LastName columns of the Student table to "Navin" and "Kumar" respectively, only for the row where the StudentId is equal to 45678.

3) DELETE Statement

It is possible to delete one or more rows from a database table using the DELETE statement in SQL. The data records are not permanently deleted. A rollback procedure can always be used to reverse a DELETE command. The WHERE clause can be used with DELETE statements to filter out particular records.

Syntax:

DELETE FROM table_name

WHERE condition;

The table_name is the name of the table that you want to delete rows from.
The condition is a Boolean expression that specifies which rows you want to delete.

Example 1: To delete single record.

DELETE FROM student WHERE student_Id = 'A202' ;

The above example will delete the row from the student table where the student_Id column is equal to the value A202.

Example 2: Delete the multiple records or rows from the database table.

DELETE FROM Student WHERE math > 40 ;

The above example will delete all rows from the Student table where the math column is greater than 40.

Example 3: Deleting all rows from a table.

Syntax:

DELETE FROM table_name;

Example:

DELETE FROM students;

The above example will delete all the records for the students table.

Example 4: Deleting multiple rows:

Syntax:

DELETE FROM table_name

WHERE condition1

OR condition2;

Example:

DELETE FROM students WHERE student_id = 1234567890 OR student_name = 'Jayendra Bam';

The above example will delete all rows from the students table where the student_id column is equal to 1234567890 or the student_name column is equal to Jayendra Bam.

Example:

DELETE FROM students WHERE s_name = 'Mohan' AND student_id = '1001';

The above example provided SQL statement uses the DELETE statement to remove a row from the "students" table, based on two conditions:

The value of the "s_name" column in the row must be equal to 'Mohan'
The value of the "student_id" column in the row must be equal to '1001'

4) SELECT Statement:

Data can be retrieved from one or more tables in a database using the SQL SELECT statement. The majority of database applications are built on one of the most often used SQL statements.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

In above syntax,

column1, column2, etc. are the names of the columns you want to retrieve data from.
table_name is the name of the table or tables you want to retrieve data from.
condition is an optional parameter that specifies which rows to retrieve. If you don't specify a condition, all rows in the table will be retrieved.

Example 1: Show all the columns from the table.

SELECT * FROM Student;

In the above example, the * wildcard character is used to retrieve all columns from the student table.

Example 2: Shows all the values of a specific column from the table.

SELECT first_name, last_name FROM student;

The above example retrieve the "first_name" and "last_name" columns from the "student" table.

Example 3: Use the WHERE clause with the SELECT DML command.

SELECT first_name, last_name

FROM student

WHERE grade >= 90;

In the above example, the WHERE clause filters the results to only include students whose grade is greater than or equal to 90.

Example 4: Select all the student ID and Name who are female using the student table

SELECT student_id, name

FROM student

WHERE gender = 'female';

Example 5: select all the student ID and Name, marks who scored marks between 80 to 90 using the student table.

SELECT student_id, name, marks

FROM student

WHERE marks BETWEEN 80 AND 90;

Example 6: Using wild cards in SQL

Known as a wildcard in SQL, this special character can be used to stand in for one or more characters in a search string. In SQL queries, wildcards are frequently combined with the LIKE operator to find data that matches a certain pattern or set of requirements.

Following are some examples of how to use wildcards in SQL:

The percent sign (%) can be used to represent any number of characters.

Example:

SELECT * FROM students WHERE name LIKE 'a%'

The above example will select all rows in the students table where the name column starts with the letter a.

SELECT LastName FROM student WHERE LastName LIKE ‘%sn%’

The above example retrieves all rows from the "student" table where the "LastName" column contains the characters 'sn' in any position.

The underscore (_) can be used to represent a single character.

SELECT * FROM students WHERE name LIKE '_a%';

The above query will select all rows in the students table where the name column starts with the letter a and is followed by any number of other characters.

SELECT * FROM students WHERE name LIKE '%_e';

The above query will select all rows in the students table where the name column ends with the letter e and is preceded by any number of other characters.

SELECT * FROM students WHERE name LIKE '___';

The above query will select all rows in the students table where the name column is exactly 3 characters long.

The question mark (?) can be used to represent a single character of any value.

SELECT * FROM employee WHERE emp_id LIKE '1__3';

The above example retrieves all rows from the "employee" table where the "emp_id" column contains a string of 5 characters and the first character is '1', the fourth character is '3', and the second and third characters can be any character.

SELECT * FROM students WHERE name LIKE '?a%';

The above example will select all rows in the students table where the name column starts with two characters, the first of which is any character and the second of which is the letter a.

SELECT * FROM students WHERE name LIKE '???';

The above example will select all rows in the students table where the name column is exactly 3 characters long and each character is any single character.

Differences Between DDL and DML Statements

The main difference between DDL and DML statements is that DDL statements are used to define the structure of the database, while DML statements are used to manipulate the data within the database. DDL statements are used to create, modify, and delete database objects such as tables, views, and indexes. DML statements are used to insert, update, and delete data in a table.

Best Practices for Using DDL and DML

When using DDL and DML statements, it is important to follow best practices to ensure the integrity of your data.

Here are a few tips to keep in mind:

Always backup your data before making any changes to your database.
Use descriptive names for your database objects to make it easier to understand the structure of your database.
Use constraints to enforce data integrity and prevent invalid data from being added to your tables.
Use transactions to ensure that a series of statements are completed successfully before committing the changes to the database.

Conclusion

DDL and DML statements are two of the most significant categories of SQL statements, to sum up. Database objects such as tables, views, and stored procedures are created, modified, and deleted using DDL statements. Data from tables can be inserted, updated, and deleted using DML statements.

You can manage your database and manipulate data efficiently by being familiar with DDL and DML statements. This will enable you to create robust applications and find solutions to challenging business issues.

I sincerely hope that this blog post was useful. Please feel free to ask any questions in the comments section below.

FAQs

1. What is the difference between DDL and DML statements?

Ans: DDL statements are used to define the structure of the database, while DML statements are used to manipulate the data within the database.

2. What is the CREATE statement used for?

Ans: The CREATE statement is used to create a new table in the database.

3. How do you add a new column to an existing table?

Ans: You can use the ALTER statement to add a new column to an existing table.

4. What is a constraint in SQL?

Ans: A constraint is a rule that is used to enforce data integrity and prevent invalid data from being added to a table.

5. Why is it important to backup your data before making changes to your database?

Ans: Backing up your data ensures that you can restore your database to its previous state if something goes wrong during the process of making changes to the database.

You may also read:

Number system conversion