SQL Data Manipulation Language (DML) Operations: Insert, Update, Delete

Imagine yourself as the master conductor of a symphony orchestra, and the database is your musical score. With SQL's DML operations, you hold the baton that allows you to add, modify, and remove the harmonious notes that compose your database. Yes, you read that right! This may sound like a dream, but SQL (Structured Query Language) turns this dream into reality.

SQL, as we all know, is not just any ordinary tool; it’s a game-changing tool that has the ability to transform how we manage and manipulate relational databases. With the power of SQL, you open up a world of endless possibilities for handling and controlling your valuable data. SQL includes Data Manipulation Language (DML), a powerful set of operations like Insert, Update, and Delete,  that controls access to data and to the database.  It allows you to mold and shape your data in various ways. With just a few lines of code, you can easily and quickly add new data, modify or update existing data, and remove unnecessary data from database tables. Whether you're an experienced professional or a novice, understanding these SQL operations will give you the ability to turn your data to meet your specific requirements. 

In this article, we will explore various DML operations and understand how they help achieve effective data management. If you looking for more query questions in SQL, check out SQL Query Interview Questions and Answers that help you prepare for Interviews. By the end, you'll have a better understanding of how these SQL operations effectively covert or alter your data in a meaningful way. So, let's dive in together and discover what data manipulation language holds in SQL!

SQL Data Manipulation Language (DML) Operations

Let’s embark on an adventure through the captivating landscape of SQL Data Manipulation Language (DML) operations: Insert, Update, and Delete.

NOTE: DML commands, like DELETE, are not automatically saved in the database, which means changes can be rolled back. To prevent loss of information, we use the COMMIT command to make the changes permanent in the database tables. To make all changes permanent, you can use the COMMIT statement at the end of your DML command.

Now, let’s try to understand each of the DML commands stated above in more detail:

1. Insert Operation

It allows users to add new data into rows of a database table. Insert operation is important as it allows us to fill tables with essential data. 

Syntax:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

We can also specify the value of data to be inserted without including the column names.

INSERT INTO table_name    
VALUES (value1, value2, .... valuen);  

Here:

• Table_name represents the name of the table into which data will be inserted. 
• "column1, column2, column3, …” represent the specific columns to which values will be inserted. 
• "value1, value2, value3, ..." represent actual values that will be inserted into the specified columns.

SQL Query Example:

Let’s consider a table called "Employees" with columns "EmployeeID," "FirstName," "LastName", “Email”, “PhoneNo”, and “Salary”. To insert a new employee record, we can use the following SQL statement:

INSERT INTO Employees (EmployeeID, FirstName, LastName, Email, Country, Salary)
VALUES (1, 'Navya', ‘John', 'navyajohn@email.com', 'USA', 50000.00),
       (2, 'Kunal', 'Swami', 'kunalswami@email.com', 'UK', 44000.00),
       (3, 'Jai', 'Doneriya', 'jaidoneriya@email.com', 'India', 35000.00);

Output:

2. Update Operation

It allows users to modify or update existing data within a table. It generally offers the flexibility to update or modify the values of one or more records in the table. 

Syntax:

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

Here:

• "table_name" represents the name of the table to be updated. 
• "column1 = value1, column2 = value2, ..." specifies the columns to be updated along with their new values. 
• "WHERE" clause is optional. The update will be applied to all records in the table unless the user specifies a "WHERE" clause.

SQL Query Example:

Suppose want to update the FirstName of the employee with EmployeeID 1 to 'Smith' and Email with ‘smithjohn@gmail.com’. The UPDATE statement would look like this:

UPDATE Employees
SET LastName = 'Smith', Email= ‘smithjohn@gmail.com’
WHERE EmployeeID = 1;

Output:

3. Delete Operation

It allows users to remove/delete records or sets of records from the database tables depending upon the condition specified in the WHERE clause.

Syntax:

DELETE FROM table_name
WHERE condition;

Here:

• "table_name" represents the name of the table from which records should be deleted. 
• "WHERE" clause is used to determine which records should be deleted. Without a "WHERE" clause, all records from the specified table will be removed. 

SQL Query Example:

Suppose we want to delete all employees whose EmployeeID is greater than and equal to 2. The DELETE Command would look like:

DELETE FROM Employees
WHERE EmployeeID >= 2;

Output:

Other Operations

Discover powerful commands and techniques that will enhance your SQL skills. Let's dive in!

1. Select Statement 

The Select statement is the most widely used DML command to retrieve data from a table. It allows you to select specific columns you want to retrieve, apply filters, and even sort the results in the desired way. The data returned is stored in a new table called the result set.

Syntax:

SELECT column1, column2 FROM table_name WHERE condition ORDER BY column ASC/DESC;

2. Join Operation

Join operations allow you to combine data from multiple tables based on a common column. It enables you to fetch related data and create meaningful connections between tables.

Syntax:

SELECT column1, column2 FROM table1 JOIN table2 ON table1.column = table2.column;

3. Aggregate Functions

Aggregate functions perform calculations on sets of values and return a single value. They are useful for obtaining summarized information from a table, such as finding the sum, average, maximum, or minimum value.

Syntax:

SELECT aggregate_function(column) FROM table_name;

4. Subqueries

Subqueries are queries embedded within another query. They allow you to use the result of one query as a condition in another query, providing advanced filtering and data manipulation capabilities.

Syntax:

SELECT column1 FROM table_name WHERE column2 IN (SELECT column3 FROM table2 WHERE condition);

5. Group By 

The Group By clause is used to group rows based on one or more columns. It is often combined with aggregate functions to perform calculations on each group separately.

Syntax:

SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1;

6. Having Clause

The Having clause works in conjunction with the Group By clause. It allows you to apply filters to the grouped data based on specific conditions.

Syntax:

SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1 HAVING condition;

7. Distinct

The Distinct keyword retrieves only distinct (unique) values from a column, as it effortlessly eliminates duplicates from the result set returned by the SQL Query. It ensures that the result set contains only one instance of each value.

Syntax:

SELECT DISTINCT column FROM table_name;

8. Drop-Table

The Drop Table statement is used to delete an entire table from a database. This operation permanently removes the table and all its associated data.

Syntax:

DROP TABLE table_name;

Conclusion

All DML operations mentioned above, like Insert, Update, Delete, and more, are essential for managing and manipulating data in SQL databases. Users can add new data, update/modify existing data, and remove unnecessary data from the tables using DML Operations. Therefore, having a deep understanding of the syntax and functionality of DML operations is essential for users to effectively control and maintain data integrity. Remember, becoming proficient in DML operations offers you powerful capabilities for data management, simplifying the handling of data-driven applications and ensuring the reliability of information in relational databases.