-- DEMO HOW APPLICATION PROGRAMS CAN USE VIEWS ONLY
-- NOTE: NOTHING IS COMMITTED IN THIS DEMO.

   CREATE TABLE T1
     ( F1    CHAR(1)  NOT NULL,
       F2    CHAR(2)  NOT NULL )
     IN DATABASE HTDB01;

   INSERT INTO T1 VALUES ('A', 'A1');
   INSERT INTO T1 VALUES ('B', 'B1');
   INSERT INTO T1 VALUES ('D', 'D1');

   SELECT * FROM T1;

   CREATE TABLE T2
     ( F1    CHAR(1)  NOT NULL,
       F3    CHAR(2)  NOT NULL )
     IN DATABASE HTDB01;

   INSERT INTO T2 VALUES ('A', 'A2');
   INSERT INTO T2 VALUES ('C', 'C2');
   INSERT INTO T2 VALUES ('D', 'D2');

   SELECT * FROM T2;

-- NOTE A AND D ARE IN BOTH TABLES
-- BUT B IS IN T1 ONLY AND C IS IN T2 ONLY.

   CREATE VIEW V1 AS SELECT F1, F2 FROM T1;

   CREATE VIEW V2 AS SELECT F1, F3 FROM T2;

-- IT IS PROPOSED THAT APPLICATION PROGRAMS
-- WILL USE VIEWS ONLY.
-- SO THIS IS WHAT THE APPLICATION PROGRAMS SEE...

   SELECT * FROM V1;

   SELECT * FROM V2;

-- OBJECTIVE IS TO CHANGE T1, T2.
-- WE WILL, IN FACT, REPLACE TABLES T1 AND T2 WITH T3.
-- OF COURSE, WE WILL HAVE TO CHANGE THE DEFINITIONS
-- OF VIEWS V1 AND V2. BUT THE SELECTS ON V1 AND V2
-- MUST REMAIN UNCHANGED SO THAT WHAT THE APPLICATION
-- PROGRAMS SEE IS UNCHANGED.

   DROP TABLE T1;
   DROP TABLE T2;

   CREATE TABLE T3
     ( F1  CHAR(1)  NOT NULL ,
       F2  CHAR(2)           ,
       F3  CHAR(2)             )
     IN DATABASE HTDB01 ;

   INSERT INTO T3 VALUES ('A', 'A1', 'A2');
   INSERT INTO T3 VALUES ('B', 'B1', NULL);
   INSERT INTO T3 VALUES ('C', NULL, 'C2');
   INSERT INTO T3 VALUES ('D', 'D1', 'D2');

-- NO NEED TO DROP VIEWS V1 AND V2 BECAUSE
-- THE VIEWS WERE AUTOMATICALLY DROPPED WHEN
-- THEIR BASE TABLES WERE DROPPED.

   CREATE VIEW V1 AS
     SELECT F1, F2 FROM T3
       WHERE F2 IS NOT NULL;

   CREATE VIEW V2 AS
     SELECT F1, F3 FROM T3
       WHERE F3 IS NOT NULL;

-- SHOW THAT WHAT THE APPLICATION PROGRAMS SEE IS UNCHANGED

   SELECT * FROM V1;

   SELECT * FROM V2;

-- DELIBERATE ERROR SO NOTHING IS COMMITTED...

   OOPS!

-- END OF DEMO