DB2 Example Sample Reference Code


1. SQL SELECT - Fetch specific column(s) from a table

SELECT column_name1, column_name2, ...
  FROM table_name;

2. SQL SELECT - Fetch all columns from a table

SELECT *
  FROM table_name;

3. SELECT DISTINCT - Selects only the DISTINCT column values from the table.

SELECT DISTINCT column_name1
  FROM table_name;

4. SELECT COUNT(DISTINCT) - lists the number of different (distinct) value in the table column.

SELECT COUNT(DISTINCT column_name)
  FROM table_name;

5. WHERE - Fetch records based on text field(s) in where clause

SELECT * FROM table_name
 WHERE column_name='text_value';

6. WHERE - Fetch records based on numeric field(s) in where clause

SELECT * FROM table_name
 WHERE column_name=numeric_value;

7. AND operator in WHERE clause

SELECT * FROM table_name
 WHERE column_name1=numeric_value
   AND column_name2=numeric_value;

8. OR operator in WHERE clause

SELECT * FROM table_name
 WHERE column_name1=numeric_value
    OR column_name2=numeric_value;

9. NOT operator in WHERE clause

SELECT * FROM table_name
 WHERE NOT column_name=numeric_value;

10. AND/OR operator in WHERE clause

SELECT * FROM table_name
 WHERE column_name1=numeric_value1
  AND (column_name2=numeric_value2
   OR column_name3=numeric_value3);

11. ORDER BY - Sort the record in ascending order

SELECT * FROM table_name
 ORDER BY column_name;

12. ORDER BY - Sort the record in descending order

SELECT * FROM table_name
 ORDER BY column_name DESC;

13. ORDER BY - Sort the record in ascending order by multiple columns

SELECT * FROM table_name
 ORDER BY column_name1, column_name2;

14. ORDER BY - Sort the record in descending order by multiple columns

SELECT * FROM table_name
 ORDER BY column_name1 DESC,
          column_name2 DESC;

15. INSERT INTO - Insert data into all columns in a row

INSERT INTO table_name
 VALUES ('text_value1','text_value2',numeric_value,'text_value3');

16. INSERT INTO - Insert data into specific column in a row

INSERT INTO table_name (column_name1, column_name2, column_name3)
  VALUES (numeric_value1, numeric_value2, 'text_value3');

17. IS NULL - Fetch null column rows in a table

SELECT *
  FROM table_name
  WHERE column_name IS NULL;

18. IS NOT NULL - Fetch not null column rows in a table

SELECT *
  FROM table_name
  WHERE column_name IS NOT NULL;

19. UPDATE - Update single or multiple row in a table using where clause

UPDATE table_name
   SET column_name1=value1, column_name2=value2
  WHERE column_name=value;

20. UPDATE - Update all row in a table

UPDATE table_name
  SET column_name=value;

21. DELETE - Delete single or multiple rows in a table

DELETE FROM table_name
 WHERE column_name='text_value';

22. DELETE - Delete all row in a table

DELETE FROM table_name;