SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL OPERATORS SQL COMMANDS SQL Queries

SQL Table

SQL TABLE SQL CREATE TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL UPDATE TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT INTO Statement

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

DML Commands in SQL

DML is an abbreviation of Data Manipulation Language.

Data Manipulation Language commands in Structured Query Language manipulate the data in the database. DML commands are used to retrieve records, add records, update the records, as well as remove the data from the existing table.

The SQL commands fall under the DML commands is as follows:

  1. SELECT Command.
  2. INSERT Command.
  3. UPDATE Command.
  4. DELETE Command.

1. INSERT DML Command

INSERT is a DML command in the SQL. The INSERT command is used to add a new record to the table.

The syntax of the INSERT command is as follows:

INSERT INTO TABLE_NAME VALUES (Data_1, Data_2, Data_3, Data_4);

The above syntax adds records in all the table fields without mentioning the field name, but the column sequence must be correct.

INSERT INTO TABLE_NAME (COLUMN_NAME1, COLUMN_NAME2) VALUES (Data_1, Data_2);

The above Syntax is used to add records for the selected columns of the table.

Examples of INSERT Command

Example 1: The below example adds a record to the table

Consider the newly created table name stud, which has no data.

We will execute the INSERT command to add data to the stud table

INSERT INTO STUD VALUES (1, ‘Shivani Agrawal’, 15, ‘Mumbai’, 85, 9);

The above query adds a single record to the table. We can insert multiple values into the tables, taking multiple value insertion.

Example 2: These examples describe how to INSERT multiple values into the table.

Now we will store multiple new records on the stud table. To store multiple records in a table, we will execute the following Data Manipulation Language INSERT command:

INSERT INTO STUD VALUES (2, 'Pranoti Shende', 16, 'Kohlapur', 95, 9), (3, 'Vaibhav Sharma', 15, 'Noida', 90, 16);

2. SELECT DML Command

The Most important DML command in the SQL is the SELECT Command. We execute the SELECT query to display data from the table.

The Syntax of the SELECT command is as follows:

SELECT * FROM TABLE_NAME;

The above syntax retrieves all the records from the mentioned table name

SELECT COLUMN_NAME_1, COLUMN_NAME_2, COLUMN_NAME_N FROM TABLE_NAME;

The above syntax is used to retrieve records from the mentioned column name

SELECT * FROM TABLE-NAME WHERE COLUMN_NAME;

The above syntax is used to display the specific records from the table

Examples of SELECT Command

Example 1:  This example fetches all the data of each column from the table:

SELECT * FROM STUD;

The output of the above query is as follows:

STUD_IDSTUD_NAMESTUD_AGESTUD_CITYSTUD_MARKS
1Shivani Agrawal15Mumbai85
2Pranoti Shende16Kolhapur95
3Vaibhav Sharma15Noida90
DML Commands in SQL

Example 2:  This example fetches all the data of the selected column from the table:

SELECT STUD_ID, STUD_NAME, STUD_AGE FROM STUD;

The above query retrieves records from the selected columns

The output of the above query is as follows:

STUD_IDSTUD_NAMESTUD_AGE
1Shivani Agrawal15
2Pranoti Shende16
3Vaibhav Sharma15
DML Commands in SQL

Example 3:  These examples fetch a single row of all data using the WHERE clause specified by column name from the table:

SELECT * FROM STUD WHERE STUD_ID = 1;

The above query retrieves records of student information whose student id is 1.

The output of the above query is as follows:

STUD_IDSTUD_NAMESTUD_AGESTUD_CITYSTUD_MARKS
1Shivani Agrawal15Mumbai85
DML Commands in SQL

3. UPDATE Data Manipulation Language Command

UPDATE Command is a DML Command in the SQL. The UPDATE commands are     used to update the existing records in the table.

The syntax of the UPDATE command is as follows:

UPDATE TABLE_NAME SET COLUMN_NAME1 = VALUES, COLUMN_NAME2 = VALUES WHERE CONDITION;

The SET keyword is used to update the column in the above syntax

Examples of UPDATE Command

Example 1: The below example helps you with how the UPDATE query works:

Consider the already existing table with the following data

Table Name: Stud

STUD_IDSTUD_NAMESTUD_AGESTUD_CITYSTUD_MARKS
1Shivani Agrawal15Mumbai85
2Pranoti Shende16Kolhapur95
3Vaibhav Sharma15Noida90

I want to modify the student city whose student id is 3. To update records, we will execute the following Data Manipulation Language UPDATE command:

UPDATE STUD SET STUD_CITY = ‘PUNE’ WHERE STUD_ID = 3;

We will execute the SELECT query to verify whether the student information is successfully updated or not

UPDATE STUD SET STUD_CITY = ‘PUNE’ WHERE STUD_ID = 3;

The output of the above query is as follows:

STUD_IDSTUD_NAMESTUD_AGESTUD_CITYSTUD_MARKS
3Vaibhav Sharma15Pune90
DML Commands in SQL

Example 2: The below example describes how to modify the multiple values.

We need to modify the student marks and student age whose student city is ‘Pune’. To update records, we will execute the following Data Manipulation Language UPDATE command:

UPDATE STUD SET STUD_MARKS = 93, STUD_AGE = 16 WHERE STUD_CITY = 'PUNE';

We will execute the SELECT query to verify whether the student information is successfully updated or not

SELECT * FROM STUD WHERE STUD_CITY = ‘PUNE’; 

The output of the above query is as follows:

STUD_IDSTUD_NAMESTUD_AGESTUD_CITYSTUD_MARKS
3Vaibhav Sharma16Pune93
DML Commands in SQL

4. DELETE DML Command

The DELETE command is a DML command that removes the data from the existing table. The WHERE clause conditions are used in the queries to remove data from the table.

The syntax of the DELETE command is as follows:

DELETE FROM TABLE_NAME WHERE CONDITION;

Examples of DELETE Command

Example 1: The below example describes how to remove the records from the existing table.

Consider the already existing table with the following data

Table Name: Stud

STUD_IDSTUD_NAMESTUD_AGESTUD_CITYSTUD_MARKS
1Shivani Agrawal15Mumbai85
2Pranoti Shende16Kolhapur95
3Vaibhav Sharma16Pune93

For example, we want to delete the records from the stud table whose city name is 'Mumbai'. To remove records, we will execute the following Data Manipulation Language DELETE command:

DELETE FROM STUD WHERE STUD_CITY = ‘MUMBAI’;

We will execute the SELECT query to verify whether the student information is successfully deleted or not

SELECT * FROM STUD;

The output of the above query is as follows:

STUD_IDSTUD_NAMESTUD_AGESTUD_CITYSTUD_MARKS
2Pranoti Shende16Kolhapur95
3Vaibhav Sharma16Pune93
DML Commands in SQL



ADVERTISEMENT
ADVERTISEMENT