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.
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 |
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.
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 ;