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

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.

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

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

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.

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.