DML Queries in SQL - How to Use SELECT, INSERT, UPDATE, and DELETE Statements
DML Queries in SQL
Data Manipulation Language (DML) Queries in SQL are used to manipulate the data present in SQL tables. As we explain how to construct these queries, we’ll use the following sample table.
NAME: ID FIRST LAST
1 PAT S
2 MOO X
3 RAM YO
4 JIM CHOO
SELECT Command
The SELECT query can be used to select tuples of data in the table and display them.
The basic syntax of the SELECT query is:
SELECT COLUMNNAME FROM TABLENAME;
This will display all the values of that column present in the table.
For example, the command
SELECT FIRST FROM NAME
would give the following output.
FIRST
PAT
MOO
RAM
JIM
To select all columns and display them, use:
SELECT * FROM TABLENAME;
For example, the command
SELECT * FROM NAME;
would give the output:
ID FIRST LAST
1 PAT S
2 MOO YO
3 RAM YO
4 JIM CHOO
To select only distinct values in a column, use:
SELECT DISTINCT COLUMNNAME FROM TABLENAME;
For example,
SELECT DISTINCT LAST FROM NAME;
gives the output:
LAST
S
YO
CHOO
Note: that the YO surname will be displayed only once.
WHERE Clauses and AND/OR Statements
If you don’t want to select all values, but only those which fulfill a certain condition, use the WHERE clause.
SELECT COLUMNNAME FROM TABLENAME WHERE [CONDITION];
For example,
SELECT FIRST FROM NAME WHERE ID=1;
gives the output:
FIRST
PAT
SELECT FIRST FROM NAME WHERE ID>2;
gives the output:
FIRST
RAM
JIM
SELECT FIRST FROM NAME WHERE ID IN (1,3);
yields:
FIRST
PAT
RAM
You can also use AND / OR logical connectives to combine result sets.
SELECT COLUMNNAME FROM TABLENAME WHERE CONDITION1 AND CONDITION2;
SELECT COLUMNNAME FROM TABLENAME WHERE CONDITION1 OR CONDITION2;
For example,
SELECT FIRST FROM NAME WHERE ID=2 AND LAST=“YO”;
gives:
FIRST
MOO
Note: In an AND connective, only those results which satisfy both conditions are returned.
SELECT FIRST FROM NAME WHERE ID=2 OR LAST=“YO”;
yields the following result:
FIRST
MOO
RAM
Note: In an OR connective, all those results which satisfy either of the two, or both conditions are returned.
You can also combine the two for a complex query:
SELECT FIRST FROM NAME WHERE (ID=2 OR ID=4) AND LAST=“CHOO”;
returns the following information:
FIRST
JIM
INSERT Command
The INSERT query can be used to insert data into the table.
The basic syntax of the INSERT command is:
INSERT INTO TABLENAME VALUES (value1, value2, value3);
or
INSERT INTO TABLENAME (COLUMN1, COLUMN2, COLUMN3) VALUES (value1, value2, value3);
If you use the first syntax, you don’t have to specify the column order, but you have to insert values for all the columns in the order in which the columns are defined in the table.
For example,
INSERT INTO NAME VALUES (5, ‘AAA’, ‘B’);
This will insert a new tuple in the table:
ID FIRST LAST
5 AAA B
Using the second syntax, you can change the order of the column variables.
For example,
INSERT INTO NAME(FIRST, LAST, ID) VALUES (‘AAA’, ‘B’, 5);
This will insert the same tuple in the table:
ID FIRST LAST
5 AAA B
If you use the second syntax, you can also skip columns which you don’t need.
For example,
INSERT INTO NAME(FIRST, ID) VALUES (‘AAA’, 5);
This will insert the same tuple in the table:
ID FIRST LAST
5 AAA
UPDATE Command
Using the update command, you can update the values in the table to whichever values you want.
The basic syntax is:
UPDATE TABLENAME
SET COLUMNNAME=“newvalue” WHERE [CONDITION]
For example,
UPDATE NAME SET FIRST=“XYZ” WHERE ID=3;
This command will change the value of RAM to XYZ.
DELETE Command
The DELETE command will tuples in the table based on the condition specified.
DELETE FROM TABLENAME WHERE [CONDITION]
For example,
DELETE FROM NAME WHERE ID=1;
This command will delete the first tuple in the table.
You can also delete all the tuples by not specifying the condition.
DELETE FROM NAME;
OR
DELETE * FROM NAME;
This will delete all tuples.
We are done with the basic DML commands. In the next article, we will study basic aggregate and scalar functions in SQL.
This post is part of the series: Introduction to SQL
This series is an introduction to SQL. It includes details on tables, DDL and DML queries, joins in SQL, and more.
