DB2 Tutorial For Software Developers (3 of 5)

DB2 tutorial is good for both DB2 and mainframe developers is it is quick to read and given in the form of questions and answers.

Read my part-2 post on DB2 tutorial.

Data Definition Language(DDL)

CREATE: –This statement is used to create objects

Syntax : For Creating a Table

CREATE TABLE <tabname> (Col Definitions)

PRIMARY KEY(Columns) / FOREIGN KEY

UNIQUE (Colname) (referential constraint)

[LIKE Table name / View name]

[IN Database Tablespace Name ]

  • Foreign Key references dbname.table on ‘relation condition for delete’
  • Table1 references table2(target) – Table2’s Primary key is the foreign key defined in Table1
  • The Conditions that can be used are CASCADE, RESTRICT & SET NULL (referential constraint for the foreign key definition)
  • Inserting (or updating ) rows in the target is allowed only if there are no rows in the referencing table

ALTER:- This statement is used for altering all DB2 objects

Syntax : For altering a Table

ALTER TABLE <Tablename>

ADD Column Data-type [ not null with default]

  • Alter allows primary & Foreign key specifications to be changed
  • It does not support changes to width or data type of a column or dropping a column

DROP :- This statement is used for dropping all DB2 objects

Syntax : For dropping a table

DROP TABLE <Tablename>

Some general rules for RI & Table Parameters

  • Avoid nulls in columns participating in Arithmetic logic or comparisons
  • Primary key cols cannot be nulls
  • Limit referential structures to no more than three levels in a direction
  • Use DB2’s inherent features rather than program coded RI’s.

SQL – Selection & Projection

  • Select retrieves a specific number of rows from a table
  • Projection operation retrieves a specified subset of columns(but all rows) from the table

E.g.. : SELECT CUST_NO, CUST_NAME FROM CUSTOMER;

  • The WHERE clause defines the Predicates for the SQL operation.
  • The above WHERE clause can have multiple conditions using AND & OR .

Other Clauses

Many other clauses can be used in conjunction with the WHERE clause to code the required predicate, some are :-

  • Between / Not Between
  • In / Not In
  • Like / Not Like
  • IS NULL / IS NOT NULL

SELECT using a range :

Between Clause

E.g.

SELECT CUST_NO, CUST_NAME, CUST_ADDR FROM CUSTOMER
WHERE CUST_NO BETWEEN 1000 AND 2000;

In Clause

E.g. SELECT CUST_NO, CUST_NAME, CUST_ADDR FROM CUSTOMER

WHERE CUST_NO IN(1000, 1001,1002);

Like Clause

E.g. SELECT CUST_NO, CUST_NAME, CUST_ADDR

FROM CUSTOMER

WHERE CUST_ID like/not like ‘425%’

Note :- ‘_’ for a single char ; ‘%’ for a string of chars

Escape ‘\’ – escape char; if precedes ‘_’ or ‘%’ overrides their meaning

NULL Clause: To check null the syntax is ‘IS NULL’

E.g.

SELECT CUST_NO, CUST_NAME, ORDER_NO
WHERE ORDER_NO IS NULL;

However if there are null values for ORDER_NO, then these are always evaluated as a ‘Not True’ condition in a Query.

Order by and Group by clauses :

  • Order by sorts retrieved data in the specified order; uses the WHERE clause
  • Group by operator causes the table represented by the FROM clause to be rearranged into groups, such that within one group all rows have the same value for the Group by column (not physically in the database). The Select clause is applied to the grouped data and not to the original table.

Here ‘HAVING’ is used to eliminate groups, just like WHERE is used for rows.

E.g.

SELECT ORDER_NO, SUM(NO_PRODUCTS)
FROM ORDER
GROUP BY ORDER_NO
HAVING AVG(NO_PRODUCTS) < 10
ORDER BY ORDER_NO ;

Also read

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.