🔼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 :

SELECT column_name1, column_name2, …
FROM table_name
WHERE condition_ expression;

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.

SELECT customer_id,
sale_date,
order_id,
store_state
FROM customers;

The query returns the following output.

SQL DML Commands-1.1

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.

SELECT * FROM customers;

The query returns the following output:

SQL DML Commands-1.2

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

SELECT DISTINCT column_1, column_2, ... 
FROM table_name;

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

SELECT DISTINCT brand, model 
FROM products;

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 :

INSERT INTO table_name (column_name_1, column_name_2, column_name_3, ...)
VALUES (value1, value2, value3, ...)

By VALUES, we mean the value of the corresponding columns.

Here are a few examples to further illustrate the INSERT statement.

INSERT INTO public.customers(
customer_id, sale_date, sale_amount, salesperson, store_state, order_id)
VALUES (1005,'2019-12-12',4200,'R K Rakesh','MH','1007');

SQL DML Commands-1.3

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.

INSERT INTO customers
VALUES ('1006','2020-03-04',3200,'DL', '1008');

Output-1.4

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 :

UPDATE table_name
SET column_name_1 = value1, column_name_2 = value2, ...
WHERE condition;

Having learned the syntax, let us now try an example based on the UPDATE statement in SQL.

UPDATE customers
SET store_state = 'DL'
WHERE store_state = 'NY';

Output-1.5

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 :

DELETE FROM table_name WHERE condition;

Having learned the syntax, we are all set to try an example based on the DELETE command in SQL.

DELETE FROM customers
WHERE store_state = 'MH'
AND customer_id = '1001';

Output-1.6

In this example, we have removed a row from the customer’s table where store_state was ‘MH’ and customer_id was ‘1001’.