Insert and Select Records in DB2 Table

Prev
Next

What is DML?

It stands for Data Manipulation Language, it used to access the records or modify the object, in DB2 DML operations are used to Insert and Select Records in Table

Important DML operation is

Insert records in DB2 table

Insert records in DB2 table – it is used to put the record in the table/insert the records into the table.

Syntax:
INSERT INTO TABLE-NAME VALUES(val1,val2,val3,….)

If we want to insert values to a particular column.

Syntax:
INSERT INTO TABLE-NAME(col1,col2,col3…….) VALUES(val1,val2,val3,….)

Suppose, you have already created a table and want to directly dump the record to the new table.

Syntax:
INSERT INTO TABLE-NAME SELECT * FROM ANOTHER-TABLE-NAME;

If you want to insert a specific record from old table to new table, then

Syntax:
INSERT INTO TABLE-NAME(COL-4, COL-8) SELECT COL-44, COL-89.
FROM ANOTHER TABLE-NAME;

Syntax:
CREATE TABLE TABLE NAME LIKE OLD TABLE NAME;

SELECT RECORDS FROM TABLE- Part 1

Use select to retrieve the records from the table, there are multiple forms of SELECT.
To select all the columns from the table.

Syntax:
SELECT * FROM TABLE;

To retrieve all the records or specific columns from a table.

Syntax:
SELECT (*/C1, C2….) FROM TABLE-NAME [WHERE cond] [GROUP BY colm] [HAVING group-cond] [ORDER BY colm-name];(These are optional, we will understand about these later).

Whatever columns we give in SELECT query, it will retrieve data for that column and record will be displayed in the output.
You can also use WHERE clause with SELECT.

Syntax: SELECT COLUMN1, COLUMN2 FROM TABLE NAME WHERE CONDITION;
Example: SELECT STORE_ID, STORE_NAME FROM T2 WHERE STORE_ID=9999.

Here, Only store-id with 9999 will be displayed in the output. This is the simplest code of WHERE clause we can use different operational operator here (like: =, <>,NOT =, < ,>, >=, =<)

IN OPERATOR – It is used to check with a list of values.

For example, WHERE C1 IN(20,10,50,100),this means C1 is either 20 or 10 or 50 or 100.

SELECT RECORDS FROM TABLE- Part 2

SELECT BETWEEN – this operator is used for a numeric check for a range from starting value to end value.

For example, WHERE C1 BETWEEN NUMERIC1 AND NUMERIC2 means if C1 is the range of 20 TO 100( information will be displayed including 20 and 100).

Note: it is used only for numeric values.

Opposite of BETWEEN is ‘NOT BETWEEN’.
LIKE operator – It is used for a non-numeric check for a particular pattern expression.

Syntax:
SELECT * FROM TABLE;

For Example

->WHERE C1 LIKE ‘%ABC’ – To match a value in column C1 which ends with ABC.
->WHERE C5 LIKE ‘AAXY1%’ – To match a value in column C5 which starts with AAXY1.
-> WHERE C3 LIKE ‘%xyzpqr%’ – To match a value in column C3 which contains XYZPQR in data records.
-> WHERE C3 LIKE ‘_xyz_1%’ – To match a value in column C2 which contains 1 letter at the start followed by XYZ, again followed by 1 letter, followed by number 1, and followed by 1 or more characters.

NOTE: In place of LIKE we can also give NOT LIKE.

Example: SELECT STORE_ID, STORE_NAME FROM T2 WHERE STORE_NAME NOT LIKE ‘_BC LT_%’;
This will select all these STORE_ID, STORE_NAME from table where STORE_NAME is not like this pattern ‘_BC LT_%’.

SELECT RECORDS FROM TABLE- Part 3

What is EXISTS Operator?

It is used to check if at least one row of record is present in the table.

Syntax:
WHERE EXISTS (Any other sub-query).
WHERE NOT EXISTS (Any other sub-query)

For example

   a) SELECT * FROM TABLE;
      To retrieve all the records from the table.
	  
   b) SELECT C3 FROM TABLE;
      To retrieve records from Column C3 from the table.
  
	  
   c) SELECT * FROM TABLE WHERE C1='value-1';
      To retrieve records from all the columns where the condition 
	  C1='value-1' is true.
  
   d) SELECT C7 FROM TABLE WHERE C1 LIKE '%KJH_'
      GROUP BY C7 HAVING C7 NOT IN (5,10) ORDER BY C7 DESC   

   
   e) SELECT C2,C10 FROM TABLE WHERE C1='value' GROUP BY C10;
      To retrieve records from Columns C2 and C10 where the condition
	  C1='value' is true and the result should be grouped on the 
	  similar records for Column C10.
	  
      SELECT C7 FROM TABLE
      GROUP BY C7 HAVING C7 NOT IN (5,10) ORDER BY C7 DESC  	  
	  
   f)  
       SELECT C7 FROM TABLE
       GROUP BY C7 HAVING C7 NOT IN (5,10) 		  
		  
   g)	   
      SELECT C7 FROM TABLE ORDER BY C7 DESC 

   h) Combination of all
      SELECT C7 FROM TABLE WHERE C5 = 10
      GROUP BY C7 HAVING C7 NOT IN (5,10) ORDER BY C7 DESC

EXISTS will just test for the existence of records. It will be true if at least one row is returned in the resultant. It will be false if no records will be turned in the result set. Opposite of this is WHERE NOT EXIST.

GROUP BY- It is used to group the records based on one or more columns provided that the column or columns which is provided in the ‘GROUP BY’ should be present in ‘SELECT’ query.

Example:
SELECT STORE_ID, SUM(STORE_BAL) SUM_BAL, FROM T2 WHERE STORE_ID=9999
GROUP BY STORE_ID;
THIS CODE WILL HOW MUCH BALANCE DOES store_ID HAVE.
To check how many times STORE_ID is present we can give,
SELECT STORE_ID, SUM(STORE_BAL) SUM_BAL, COUNT(*) STORE_CNT FROM T2
GROUP BY STORE_ID;
It will count a total number of records.

SELECT RECORDS FROM TABLE- Part 4

HAVING clause in DB2

When we want to check for a condition on a column or set of columns which talks part in the ‘GROUP BY’ clause, we can use HAVING clause. This means if we use GROUP BY and we want to check a condition on the column or columns which follow the GROUP BY, we can use ‘HAVING’ clause. Having is used in the aggregate function where we cannot use ‘WHERE’ clause.

What is Aggregate Function?

1. AVG – To calculate the average.
2. SUM – To calculate the sum.
3. MIN – To calculate the minimum.
4. MAX – To calculate the maximum
5. COUNT – To calculate the count or the number of occurrences.
We have other aggregate functions also like MEDIAN and BIG but these is not that much in use in mainframe DB2, these 5 are the more important aggregate functions in DB2. Aggregate functions cannot be used with WHERE clause

Syntax:
SELECT * FROM TABLE;

For Example

SELECT STORE_ID, SUM(STORE_BAL) SUM_BAL, COUNT(*) STORE_CNT FROM T2
GROUP BY STORE_ID HAVING SUM(STORE_BAL) > 2000;

This code will give STORE_id, a sum of balance and the total number of the store.

SELECT RECORDS FROM TABLE- Part 5

ORDER BY – It is used for sorting the records in ascending order. The default order is Ascending

For Example

SELECT STORE_ID FROM T2 ORDER BY STORE_ID;(here it is by default Ascending order)

From this code, all the stores will be sorted in ascending order. For ascending order we can also use ASC.

For Descending order, we have to give DESC

Example: STORE_ID FROM T2 ORDER BY STORE_ID DESC;
From this code, all the stores will be sorted in Descending order.

For Example

COMBINATION OF ALL – SELECT C7 FROM TABLE C5 = 10
GROUP BY C7 NOT IN (5,10) ORDER BY C7 DESC.

How to Update TABLE in DB2

The update is used to either set a particular value or it is used to change the value of an existing record in the table.

Syntax:
UPDATE TABLE NAME
SET C5=VALUE-5,C6=VALUE-6,C8=VALUE-8,……

Example: UPDATE T2 set ITEM_ID=1234.

This code will update ITEM_ID in table T2 with 1234 WHERE STORE_ID=98765

Delete records from table in DB2

2. DELETE FROM TABLE-NAME WHERE CONDITION.

Example: DELETE FROM TABLE-NAME WHERE C6=’SAM’;
This code will delete a record from column 6 where there is ‘SAM’

Syntax:
1. DELETE FROM TABLE_NAME;

Prev
Next
Facebook Twitter Google-plus

Tutorials for all brains!