DB2 SQL – Select, Insert, Delete and Update

DML – Data manipulation language commands. S-I-D-U (The way we can remember it as Cricketer name)

SELECT

All queries begin with the SELECT SQL statement, which is an extremely powerful statement that is used to construct a wide variety of queries containing an infinite number of variations (using a finite set of rules).

And because the SELECT statement is recursive, a single SELECT statement can derive its output from a successive number of nested SELECT statements (which are known as subqueries).

(Earlier, we saw how SELECT statements can be used to provide input to INSERT and UPDATE statements; SELECT statements can be used to provide input to other SELECT statements in a similar manner.)

SELECT * FROM SAMPLES.TEST_1

The same command we can use either for Tables/Views.

INSERT

By using a different form of the INSERT SQL statement, the results of a query can also be used to provide values for one or more columns in a base table.

With this form of the INSERT statement, a SELECTstatement (known as a subselect) is provided in place of the VALUES clause (we’ll look at the SELECTstatement shortly), and the results of the subselect are assigned to the appropriate columns.

This form of the INSERT statement creates a type of “cut and paste” action in which values are retrieved from one base table or view and copied into another.

As you might imagine, the number of values returned by the subselect must match the number of columns provided in the column name list (or the number of columns found in the table if no column name list is provided), and the order of assignment is the same as that used when literal values are provided in a VALUES clause.

INSERT INTO SAMPLES.TEST_1 VALUES(1000,'RAMU','HR',25000);

DELETE

The DELETE statement can either work directly with the table that rows are to be removed from or work with an updatable view that references the table that rows are to be removed from.

DELETE  FROM SAMPLES.TEST_1;
or

DELETE FROM SAMPLES.TEST_1
WHERE ID =1000;

UPDATE

Data residing in a database is rarely static. Over time, the need to change, or even remove, one or more values stored in a table can, and will, arise. In such situations, specific data values can be altered by executing the UPDATE SQL statement.