DB2 Concepts

Fetch Second Highest salary from table:

SELECT MAX(A.EMP_SAL)

     FROM EMP_TAB A

     WHERE A.EMP_SAL < 

                (SELECT MAX(B.EMP_SAL)

                      FROM EMP_TAB B)

WITH UR;

Fetch 10th(Nth) Highest salary from table:

SELECT A.EMP_SAL

   FROM EMP_TAB A

   WHERE 10 IN  

              (SELECT COUNT(*) 

                    FROM EMP_TAB B

                         WHERE A.EMP_SAL <= B.EMP_SAL)

WITH UR;

Create Table Definitions with referential integrity:

CREATE TABLE  

   DEPT_TAB

   (DEPT_ID CHAR(05) NOT NULL PRIMARY KEY,

    DEPT_NAME CHAR(20) NOT NULL)

IN D13A10B1.T13A10B1;

CREATE UNIQUE INDEX INX ON DEPT_TAB(DEPT_ID;

CREATE TABLE

    EMP_TAB

      (EMP_ID CHAR(05) NOT NULL PRIMARY KEY,

       EMP_NAME CHAR(20) NOT NULL,

       EMP_SAL DECIMAL(9,2) NOT NULL,

       DEPT_ID CHAR(05) NOT NULL

       FOREIGN KEY(DEPT_ID)  

       REFEENCES DEPT_TAB 

      ON DELETE CASCADE)

IN D13A10B1.T13A10B1;