Views on the sample tables

Db2 creates a number of views on the sample tables for use in the sample applications.

The following table indicates the tables on which each view is defined and the sample applications that use the view. All view names have the qualifier DSN8C10.

Table 1. Views on sample tables
View name On tables or views Used in application
VDEPT DEPT
Organization
Project
VHDEPT DEPT Distributed organization
VEMP EMP
Distributed organization
Organization
Project
VPROJ PROJ Project
VACT ACT Project
VPROJACT PROJACT Project
VEMPPROJACT EMPPROJACT Project
VDEPMG1
DEPT
EMP
Organization
VEMPDPT1
DEPT
EMP
Organization
VASTRDE1 DEPT  
VASTRDE2
VDEPMG1
EMP
Organization
VPROJRE1
PROJ
EMP
Project
VPSTRDE1
VPROJRE1
VPROJRE2
Project
VPSTRDE2 VPROJRE1 Project
VFORPLA
VPROJRE1
EMPPROJACT
Project
VSTAFAC1
PROJACT
ACT
Project
VSTAFAC2
EMPPROJACT
ACT
EMP
Project
VPHONE
EMP
DEPT
Phone
VEMPLP EMP Phone
Begin general-use programming interface information.

The following SQL statement creates the view named VDEPT.

CREATE VIEW DSN8C10.VDEPT
   AS SELECT ALL      DEPTNO  ,
                      DEPTNAME,
                      MGRNO   ,
                      ADMRDEPT
   FROM DSN8C10.DEPT;

The following SQL statement creates the view named VHDEPT.

 CREATE VIEW DSN8C10.VHDEPT
    AS SELECT ALL      DEPTNO  ,
                       DEPTNAME,
                       MGRNO   ,
                       ADMRDEPT,
                       LOCATION
    FROM DSN8C10.DEPT;

The following SQL statement creates the view named VEMP.

CREATE VIEW DSN8C10.VEMP
   AS SELECT ALL      EMPNO   ,
                      FIRSTNME,
                      MIDINIT ,
                      LASTNAME,
                      WORKDEPT
   FROM DSN8C10.EMP;

The following SQL statement creates the view named VPROJ.

CREATE VIEW DSN8C10.VPROJ
   AS SELECT ALL
             PROJNO, PROJNAME, DEPTNO, RESPEMP, PRSTAFF,
             PRSTDATE, PRENDATE, MAJPROJ
   FROM DSN8C10.PROJ ;

The following SQL statement creates the view named VACT.

CREATE VIEW DSN8C10.VACT
      AS SELECT ALL   ACTNO   ,
                      ACTKWD  ,
                      ACTDESC
           FROM DSN8C10.ACT ;

The following SQL statement creates the view named VPROJACT.

CREATE VIEW DSN8C10.VPROJACT
      AS SELECT ALL
            PROJNO,ACTNO, ACSTAFF, ACSTDATE, ACENDATE
            FROM DSN8C10.PROJACT ;

The following SQL statement creates the view named VEMPPROJACT.

CREATE VIEW DSN8C10.VEMPPROJACT
      AS SELECT ALL
          EMPNO, PROJNO, ACTNO, EMPTIME, EMSTDATE, EMENDATE
          FROM DSN8C10.EMPPROJACT ;

The following SQL statement creates the view named VDEPMG1.

CREATE VIEW DSN8C10.VDEPMG1
      (DEPTNO, DEPTNAME, MGRNO, FIRSTNME, MIDINIT,
       LASTNAME, ADMRDEPT)
    AS SELECT ALL
       DEPTNO, DEPTNAME, EMPNO, FIRSTNME, MIDINIT,
        LASTNAME, ADMRDEPT
        FROM DSN8C10.DEPT LEFT OUTER JOIN DSN8C10.EMP
        ON MGRNO = EMPNO ;

The following SQL statement creates the view named VEMPDPT1.

CREATE VIEW DSN8C10.VEMPDPT1
      (DEPTNO, DEPTNAME, EMPNO, FRSTINIT, MIDINIT,
       LASTNAME, WORKDEPT)
    AS SELECT ALL
       DEPTNO, DEPTNAME, EMPNO, SUBSTR(FIRSTNME, 1, 1), MIDINIT,
       LASTNAME, WORKDEPT
        FROM DSN8C10.DEPT RIGHT OUTER JOIN DSN8C10.EMP
        ON WORKDEPT = DEPTNO ;

The following SQL statement creates the view named VASTRDE1.

CREATE VIEW DSN8C10.VASTRDE1
    (DEPT1NO,DEPT1NAM,EMP1NO,EMP1FN,EMP1MI,EMP1LN,TYPE2,
     DEPT2NO,DEPT2NAM,EMP2NO,EMP2FN,EMP2MI,EMP2LN)
    AS SELECT ALL
        D1.DEPTNO,D1.DEPTNAME,D1.MGRNO,D1.FIRSTNME,D1.MIDINIT,
        D1.LASTNAME, '1',
        D2.DEPTNO,D2.DEPTNAME,D2.MGRNO,D2.FIRSTNME,D2.MIDINIT,
        D2.LASTNAME
        FROM DSN8C10.VDEPMG1 D1, DSN8C10.VDEPMG1 D2
        WHERE D1.DEPTNO = D2.ADMRDEPT ;

The following SQL statement creates the view named VASTRDE2.

CREATE VIEW DSN8C10.VASTRDE2
    (DEPT1NO,DEPT1NAM,EMP1NO,EMP1FN,EMP1MI,EMP1LN,TYPE2,
     DEPT2NO,DEPT2NAM,EMP2NO,EMP2FN,EMP2MI,EMP2LN)
    AS SELECT ALL
         D1.DEPTNO,D1.DEPTNAME,D1.MGRNO,D1.FIRSTNME,D1.MIDINIT,
         D1.LASTNAME,'2',
         D1.DEPTNO,D1.DEPTNAME,E2.EMPNO,E2.FIRSTNME,E2.MIDINIT,
         E2.LASTNAME
         FROM DSN8C10.VDEPMG1 D1, DSN8C10.EMP E2
         WHERE D1.DEPTNO = E2.WORKDEPT;

The following figure shows the SQL statement that creates the view named VPROJRE1.

Figure 1. VPROJRE1
CREATE VIEW DSN8C10.VPROJRE1
  (PROJNO,PROJNAME,PROJDEP,RESPEMP,FIRSTNME,MIDINIT,
   LASTNAME,MAJPROJ)
   AS SELECT ALL
      PROJNO,PROJNAME,DEPTNO,EMPNO,FIRSTNME,MIDINIT,
      LASTNAME,MAJPROJ
     FROM DSN8C10.PROJ, DSN8C10.EMP
     WHERE RESPEMP = EMPNO ;

The following SQL statement creates the view named VPSTRDE1.

CREATE VIEW DSN8C10.VPSTRDE1
  (PROJ1NO,PROJ1NAME,RESP1NO,RESP1FN,RESP1MI,RESP1LN,
   PROJ2NO,PROJ2NAME,RESP2NO,RESP2FN,RESP2MI,RESP2LN)
   AS SELECT ALL
        P1.PROJNO,P1.PROJNAME,P1.RESPEMP,P1.FIRSTNME,P1.MIDINIT,
        P1.LASTNAME,
        P2.PROJNO,P2.PROJNAME,P2.RESPEMP,P2.FIRSTNME,P2.MIDINIT,
        P2.LASTNAME
     FROM DSN8C10.VPROJRE1 P1,
       DSN8C10.VPROJRE1 P2
     WHERE P1.PROJNO = P2.MAJPROJ ;

The following SQL statement creates the view named VPSTRDE2.

CREATE VIEW DSN8C10.VPSTRDE2
  (PROJ1NO,PROJ1NAME,RESP1NO,RESP1FN,RESP1MI,RESP1LN,
   PROJ2NO,PROJ2NAME,RESP2NO,RESP2FN,RESP2MI,RESP2LN)
   AS SELECT ALL
        P1.PROJNO,P1.PROJNAME,P1.RESPEMP,P1.FIRSTNME,P1.MIDINIT,
        P1.LASTNAME,
        P1.PROJNO,P1.PROJNAME,P1.RESPEMP,P1.FIRSTNME,P1.MIDINIT,
        P1.LASTNAME
     FROM DSN8C10.VPROJRE1 P1
       WHERE NOT EXISTS
         (SELECT * FROM DSN8C10.VPROJRE1 P2
           WHERE P1.PROJNO = P2.MAJPROJ) ;

The following SQL statement creates the view named VFORPLA.

CREATE VIEW DSN8C10.VFORPLA
  (PROJNO,PROJNAME,RESPEMP,PROJDEP,FRSTINIT,MIDINIT,LASTNAME)
   AS SELECT ALL
      F1.PROJNO,PROJNAME,RESPEMP,PROJDEP, SUBSTR(FIRSTNME, 1, 1),
      MIDINIT, LASTNAME
     FROM DSN8C10.VPROJRE1 F1 LEFT OUTER JOIN DSN8C10.EMPPROJACT F2
     ON F1.PROJNO = F2.PROJNO;

The following SQL statement creates the view named VSTAFAC1.

CREATE VIEW DSN8C10.VSTAFAC1
  (PROJNO, ACTNO, ACTDESC, EMPNO, FIRSTNME, MIDINIT, LASTNAME,
   EMPTIME,STDATE,ENDATE, TYPE)
   AS SELECT ALL
         PA.PROJNO, PA.ACTNO, AC.ACTDESC,' ', ' ', ' ', ' ',
         PA.ACSTAFF, PA.ACSTDATE,
         PA.ACENDATE,'1'
     FROM DSN8C10.PROJACT PA, DSN8C10.ACT AC
     WHERE PA.ACTNO = AC.ACTNO ;

The following SQL statement creates the view named VSTAFAC2.

CREATE VIEW DSN8C10.VSTAFAC2
  (PROJNO, ACTNO, ACTDESC, EMPNO, FIRSTNME, MIDINIT, LASTNAME,
   EMPTIME,STDATE, ENDATE, TYPE)
   AS SELECT ALL
         EP.PROJNO, EP.ACTNO, AC.ACTDESC, EP.EMPNO,EM.FIRSTNME,
         EM.MIDINIT, EM.LASTNAME, EP.EMPTIME, EP.EMSTDATE,
         EP.EMENDATE,'2'
     FROM DSN8C10.EMPPROJACT EP, DSN8C10.ACT AC, DSN8C10.EMP EM
     WHERE EP.ACTNO = AC.ACTNO  AND EP.EMPNO = EM.EMPNO ;

The following SQL statement creates the view named VPHONE.

CREATE VIEW DSN8C10.VPHONE
              (LASTNAME,
               FIRSTNAME,
               MIDDLEINITIAL,
               PHONENUMBER,
               EMPLOYEENUMBER,
               DEPTNUMBER,
               DEPTNAME)
   AS SELECT ALL      LASTNAME,
                      FIRSTNME,
                      MIDINIT ,
                      VALUE(PHONENO,'    '),
                      EMPNO,
                      DEPTNO,
                      DEPTNAME
   FROM DSN8C10.EMP, DSN8C10.DEPT
   WHERE WORKDEPT = DEPTNO;

The following SQL statement creates the view named VEMPLP.

CREATE VIEW DSN8C10.VEMPLP
              (EMPLOYEENUMBER,
               PHONENUMBER)
   AS SELECT ALL      EMPNO   ,
                      PHONENO
   FROM DSN8C10.EMP ;
End general-use programming interface information.