๐ผDML Commands
What are DML Commands?
DML or Data Manipulation Language commands are used to manipulate the data stored in a database.
SELECT
SELECT command or statement in SQL is used to fetch data records from the database table and present it in the form of a result set. It is usually considered a DQL command, but it can also be considered a DML.
The basic syntax for writing a SELECT query in SQL is as follows :
The parameters used in the above syntax are as follows :
column_name1, column_name2, โฆ : Specify the column_names which have to be fetched or selected for the final result set.
table_name: Specify the name of the database table from which these results have to be fetched.
condition_expression: Specify the condition expression for filtering records for the final result set.
Here are a few examples to illustrate the use of the SELECT command.
The query returns the following output.
In this example, we have fetched fields such as customer_id, sale_date, order_id, and store_state from customerโs table. Next, suppose if we want to fetch all the records from the customersโ table. This can be achieved with a simple query, as shown below.
The query returns the following output:
DISTINCT keyword
Overview
The DISTINCT keyword is used in a SELECT statement to remove duplicate rows from the result set. It filters the query to return unique values only.
Syntax
DISTINCT comes after SELECT and before the column names.
How it Works
DISTINCT evaluates the rows after all WHERE, GROUP BY and HAVING filters have been applied.
It compares all the selected columns in each row. If the values in each column match another row, it is removed as a duplicate.
Only one row with unique combinations of values across the selected columns is kept.
Example
This would return each unique combination of brand and model values. Brand 'A' with Model 'X' would be one row. Brand 'A' with Model 'Y' would be another unique row.
When to Use
DISTINCT is useful when:
You want to filter out duplicate rows from a query.
Removing duplicates generates a more manageable and meaningful result set.
You only care about the unique values for certain columns, like getting a list of brands or product categories.
Performance
DISTINCT can sometimes slow down queries as it needs to compare and filter rows after initial filtering and aggregation. But it is very useful for eliminating duplicates and clarifying results.
INSERT
INSERT commands in SQL are used to insert data records or rows in a database table. In an INSERT statement, we specify both the column_names for which the entry has to be made along with the data value that has to be inserted.
The basic syntax for writing INSERT statements in SQL is as follows :
By VALUES, we mean the value of the corresponding columns.
Here are a few examples to further illustrate the INSERT statement.
![](https://qatesting.gitbook.io/~gitbook/image?url=https%3A%2F%2Fcdn.educba.com%2Facademy%2Fwp-content%2Fuploads%2F2020%2F09%2FSQL-DML-Commands-1.3.png.webp&width=768&dpr=4&quality=100&sign=dc73b4f7&sv=1)
We have tried inserting a new row in the Customers table using the INSERT command. The query accepts two sets of arguments: field names or column names and their corresponding values.
Suppose we have to insert values into all the fields of the database table; then, we need not specify the column names, unlike the previous query. Follow the following query for further illustration.
![](https://qatesting.gitbook.io/~gitbook/image?url=https%3A%2F%2Fcdn.educba.com%2Facademy%2Fwp-content%2Fuploads%2F2020%2F09%2FSQL-DML-Commands-1.4.png.webp&width=768&dpr=4&quality=100&sign=8bd9bb6e&sv=1)
In this example, we have successfully inserted all the values without having to specify the fieldnames.
UPDATE
UPDATE command or statement is used to modify the value of an existing column in a database table.
The syntax for writing an UPDATE statement is as follows :
Having learned the syntax, let us now try an example based on the UPDATE statement in SQL.
![](https://qatesting.gitbook.io/~gitbook/image?url=https%3A%2F%2Fcdn.educba.com%2Facademy%2Fwp-content%2Fuploads%2F2020%2F09%2FSQL-DML-Commands-1.5.png.webp&width=768&dpr=4&quality=100&sign=8d2682a1&sv=1)
In this example, we have modified the value of store_state for a record where store_state was โNYโ and set it to a new value, โDLโ.
DELETE
DELETE statement in SQL is used to remove one or more rows from the database table. It does not delete the data records permanently. We can always perform a rollback operation to undo a DELETE command. With DELETE statements, we can use the WHERE clause for filtering specific rows.
The syntax for writing a DELETE statement is as follows :
Having learned the syntax, we are all set to try an example based on the DELETE command in SQL.
In this example, we have removed a row from the customerโs table where store_state was โMHโ and customer_id was โ1001โ.
Last updated