If you are a newbie or haven’t heard of SQL at all, it stands for Structured Query Language. And, the first milestone to achieve is getting an introduction to SQL.
Now, moving on to DML.
DML, which stands for Data Manipulation Language, covers all SQL operations that modify or manipulate existing data in a database. This includes inserting new records, updating existing ones, deleting records, and more. Common SQL commands such as INSERT, UPDATE, DELETE, and SELECT are examples of DML commands that are used to manage data records in database tables, without altering the database structure.
So now, let’s learn in brief about them along with their execution.
DML operations are not auto-saved, we need to use the COMMIT command to mark the changes as permanent.
The following statements are used for data manipulation:
The INSERT statement represents an SQL instruction for adding records to a table. To insert data into particular columns, it's essential to specify those columns within the SQL command. This command holds significance in SQL as it enables the addition of fresh data to an already established table in a database.
Syntax:
Example:
Here, we inserted a new record to the "Department" table, providing information about a position with the job title 'Manager', a salary of 60000, and a commission of 2588.
The UPDATE statement is used to modify one or more existing records in a table. It is a powerful tool that allows us to make changes to specific fields or entire rows of data. By using the UPDATE statement, we can ensure that our data is always up-to-date and accurate, which is essential for any database-driven application.
Syntax:
Example:
Here, we updating the salary of an employee in the "Department" table, specifically setting their salary to 65000 where the employee ID is 101.
This statement is used to delete one or more records from the table based on the WHERE condition specified in the command. Without the WHERE clause, all rows are deleted from the table.
Syntax:
Example:
Here, this SQL statement is essentially removing a record from the "Department" table where the employee_id is 101. It deletes information about an employee from the department records.
If the DELETE condition is not specified in the SQL command, the entire table will be deleted.
Syntax:
Example:
Here, this SQL statement is effectively erasing all records from the "Department" table. It deletes all information stored in that table.
It is used to save the changes made in a table.
In simple terms, "COMMIT" in databases is like hitting the "save" button.
When we’re making changes to data, like adding or updating information, those changes are not immediately saved to the database.
The "COMMIT" command is what we use to officially save those changes. It's like saying, "I'm done making my changes; now save them permanently."
This ensures that the changes are securely stored and can be seen by others using the database.
Example-1:
This SQL code inserts a new record into the "BONUS" table, assigning values to specific columns, and then commits the changes to make them permanent.
Example-2:
This SQL code updates the job title of an employee in the "Employees" table, specifically setting their job to 'Developer' where the employee ID is 1876, and then commits the changes to make them permanent.
Example-3:
This SQL code deletes a record from the "Department" table where the employee_id is 654 and then commits the changes to make them permanent.
It is used to fetch selected fields or columns from a database table. When fetching data, you may not always need to retrieve the entire set of data in a table. In such cases, you can use a query to fetch only specific fields or columns from the table. This makes the process more efficient and helps reduce the amount of data transferred, especially when dealing with large datasets.
Syntax:
The syntax of a SELECT statement includes multiple clauses, such as SELECT, FROM, WHERE, GROUP BY, HAVING, and more.
To select all the columns present in the table, * is used.
ALIAS is an Alternative name given to a column or an expression. In the result table.
An Alias can be used with or without using the 'AS' keyword.
Alias name should be a single word or a String enclosed in double quotes.
Alias is not mandatory but is recommended to be provided.
To remove repeated or duplicated values in a result table, we can use the DISTINCT clause.
For the DISTINCT clause, we have to pass column_name or an expression as an argument.
When using the DISTINCT clause, it should always be the first argument in the SELECT clause.
WHERE clause is used to filter the records.
For the WHERE clause, we can pass the filter condition as an argument.
WHERE clause executes row by row.
We use the GROUP BY clause to group the records.
For the GROUP BY clause, we can pass a column name or an expression as an argument.
GROUP BY clause executes row by row.
To filter the groups HAVING clause is used.
We can pass multi-row function conditions in the HAVING clause.
The HAVING clause executes group by group.
Example:
Q. Write a query to display the number of employees earning a salary of more than 1200 in each job and the total salary needed to pay employees of each job must exceed 3800.
This SQL code retrieves the count of employees, job titles, and the total salary for each job from the "EMP" table, considering only rows where the salary is greater than 1200. It then groups the results by job title and filters out groups where the total sum of salaries exceeds 3800. The aliases (NumEmployees and TotalSal) are given for clarity in the result set.
The SELECT statement, a powerful tool in SQL, allows us to retrieve specific data from a database. For a more in-depth understanding of the SELECT statement and its various capabilities, you can explore this fundamental SQL command by mastering SELECT statements in SQL.
Now that you may have an idea about DML commands and learned how to use them, is there any good that they bring to the table? Let’s find out.
DML commands let us retrieve data as per specific requirements by using the WHERE clause to apply conditions.
It is easier to modify the data stored in a database quickly and efficiently.
DML offers a straightforward way to update data, ensuring seamless and efficient updates as needed.
Additionally, DML commands help to keep data consistent by allowing control over changes to records, which helps to minimize errors and variances.
As we wrap up, it's essential to understand how important mastering Data Manipulation Language (DML) commands in SQL is for effective database management. DML enables users to accurately retrieve and manipulate data, resulting in tailored outcomes using SELECT, INSERT, UPDATE, and DELETE statements. The advantages of DML include the provision of consistent data, and minimal errors, highlighting its significance in maintaining a robust database system.
With the insights shared in this article, aspiring SQL practitioners can optimize their skills to navigate the complexities of data manipulation and boost their proficiency in SQL's DML for accurate database operations.
