3.23 CREATE VIEW [Top]
-----------------------------------------------------------------------------------
CREATE VIEW v_parent_child AS
SELECT USER_NAME ( parent_table.creator ) AS parent_owner,
       parent_table.table_name            AS parent_table,
       USER_NAME ( child_table.creator )  AS child_owner,
       child_table.table_name             AS child_table
  FROM SYS.SYSFOREIGNKEY AS foreign_key
       INNER JOIN
          ( SELECT table_id,
                   creator,
                   table_name
              FROM SYS.SYSTABLE
             WHERE table_type = 'BASE' ) -- no VIEWs, etc.
       AS parent_table
       ON parent_table.table_id = foreign_key.primary_table_id
       INNER JOIN
          ( SELECT table_id,
                   creator,
                   table_name
              FROM SYS.SYSTABLE
             WHERE table_type = 'BASE' ) -- no VIEWs, etc.
       AS child_table
       ON  child_table.table_id =  foreign_key.foreign_table_id;
-----------------------------------------------------------------------------------
SELECT parent_owner,
       parent_table,
       child_owner,
       child_table
  FROM v_parent_child
WHERE parent_owner = 'DBA'
   AND child_owner = 'DBA'
ORDER BY 1, 2, 3, 4;
-----------------------------------------------------------------------------------
CREATE TABLE parent (
   key_1         INTEGER NOT NULL PRIMARY KEY,
   non_key_1     INTEGER NOT NULL );
CREATE VIEW v_parent AS
SELECT *
  FROM parent;
CREATE TABLE child (
   key_1         INTEGER NOT NULL REFERENCES parent ( key_1 ),
   key_2         INTEGER NOT NULL,
   non_key_1     INTEGER NOT NULL,
   PRIMARY KEY ( key_1, key_2 ) );
CREATE VIEW v_child AS
SELECT *
  FROM child;
CREATE VIEW v_family (
   parent_key_1,
   parent_non_key_1,
   child_key_1,
   child_key_2,
   child_non_key_1 ) AS
SELECT parent.key_1,
       parent.non_key_1,
       child.key_1,
       child.key_2,
       child.non_key_1
  FROM parent
       INNER JOIN child
               ON child.key_1 = parent.key_1;
INSERT v_parent VALUES ( 1, 444 );
INSERT v_parent VALUES ( 2, 555 );
INSERT v_parent VALUES ( 3, 666 );
INSERT v_child VALUES ( 1, 77, 777 );
INSERT v_child VALUES ( 1, 88, 888 );
INSERT v_child VALUES ( 2, 99, 999 );
INSERT v_child VALUES ( 3, 11, 111 );
UPDATE v_family
   SET parent_non_key_1 = 1111,
       child_non_key_1  = 2222
WHERE parent_key_1 = 1
   AND child_key_2  = 88;
DELETE v_child
WHERE key_1 = 3
   AND key_2 = 11;
SELECT * FROM v_family
ORDER BY parent_key_1,
       child_key_2;
-----------------------------------------------------------------------------------

3.24 WITH Clause [Top]
-----------------------------------------------------------------------------------
CREATE VIEW v_parent_child AS
WITH base_table AS
        ( SELECT table_id,
                 creator,
                 table_name
            FROM SYS.SYSTABLE
           WHERE table_type = 'BASE' )
SELECT USER_NAME ( parent_table.creator ) AS parent_owner,
       parent_table.table_name            AS parent_table,
       USER_NAME ( child_table.creator )  AS child_owner,
       child_table.table_name             AS child_table
  FROM SYS.SYSFOREIGNKEY AS foreign_key
       INNER JOIN base_table
               AS parent_table
               ON parent_table.table_id = foreign_key.primary_table_id
       INNER JOIN base_table
               AS child_table
               ON child_table.table_id =  foreign_key.foreign_table_id;
-----------------------------------------------------------------------------------

3.24.1 Recursive UNION [Top]
-----------------------------------------------------------------------------------
CREATE TABLE employee (
   employee_id  INTEGER NOT NULL,
   manager_id   INTEGER NOT NULL REFERENCES employee ( employee_id ),
   name         VARCHAR ( 20 ) NOT NULL,
   salary       NUMERIC ( 20, 2 ) NOT NULL,
   PRIMARY KEY ( employee_id ) );
INSERT INTO employee VALUES ( 1,  1,  'Ainslie',  1000000.00 );
INSERT INTO employee VALUES ( 2,  1,  'Briana',    900000.00 );
INSERT INTO employee VALUES ( 3,  1,  'Calista',   900000.00 );
INSERT INTO employee VALUES ( 4,  1,  'Delmar',    900000.00 );
INSERT INTO employee VALUES ( 5,  2,  'Electra',   750000.00 );
INSERT INTO employee VALUES ( 6,  3,  'Fabriane',  800000.00 );
INSERT INTO employee VALUES ( 7,  3,  'Genevieve', 750000.00 );
INSERT INTO employee VALUES ( 8,  4,  'Hunter',    800000.00 );
INSERT INTO employee VALUES ( 9,  6,  'Inari',     500000.00 );
INSERT INTO employee VALUES ( 10, 6,  'Jordan',    100000.00 );
INSERT INTO employee VALUES ( 11, 8,  'Khalil',    100000.00 );
INSERT INTO employee VALUES ( 12, 8,  'Lisette',   100000.00 );
INSERT INTO employee VALUES ( 13, 10, 'Marlon',    100000.00 );
INSERT INTO employee VALUES ( 14, 10, 'Nissa',     100000.00 );
-----------------------------------------------------------------------------------
WITH RECURSIVE superior_list
   ( level,
     chosen_employee_id,
     manager_id,
     employee_id,
     name )
AS ( SELECT CAST ( 1 AS INTEGER ) AS level,
            employee.employee_id    AS chosen_employee_id,
            employee.manager_id     AS manager_id,
            employee.employee_id    AS employee_id,
            employee.name           AS name
       FROM employee
     UNION ALL
     SELECT superior_list.level + 1,
            superior_list.chosen_employee_id,
            employee.manager_id,
            employee.employee_id,
            employee.name
       FROM superior_list
            INNER JOIN employee
                    ON employee.employee_id = superior_list.manager_id
      WHERE superior_list.level <= 99
        AND superior_list.manager_id <> superior_list.employee_id )
SELECT superior_list.level,
       superior_list.name
  FROM superior_list
WHERE superior_list.chosen_employee_id = 13
ORDER BY superior_list.level DESC;
-----------------------------------------------------------------------------------
CREATE VIEW v_superior_list AS
WITH RECURSIVE superior_list
   ( level,
     chosen_employee_id,
     manager_id,
     employee_id,
     name )
AS ( SELECT CAST ( 1 AS INTEGER ) AS level,
            employee.employee_id    AS chosen_employee_id,
            employee.manager_id     AS manager_id,
            employee.employee_id    AS employee_id,
            employee.name           AS name
       FROM employee
     UNION ALL
     SELECT superior_list.level + 1,
            superior_list.chosen_employee_id,
            employee.manager_id,
            employee.employee_id,
            employee.name
       FROM superior_list
            INNER JOIN employee
                    ON employee.employee_id = superior_list.manager_id
      WHERE superior_list.level <= 99
        AND superior_list.manager_id <> superior_list.employee_id )
SELECT *
  FROM superior_list;
-----------------------------------------------------------------------------------
SELECT v_superior_list.level,
       v_superior_list.name
  FROM v_superior_list
WHERE v_superior_list.chosen_employee_id = 13
ORDER BY v_superior_list.level DESC;
-----------------------------------------------------------------------------------
SELECT LIST ( v_superior_list.name,
              ', then '
              ORDER BY v_superior_list.level ASC ) AS "Khalil's Superiors"
  FROM v_superior_list
WHERE v_superior_list.chosen_employee_id = 11
   AND v_superior_list.level > 1;
-----------------------------------------------------------------------------------
CREATE VIEW v_salary_list AS
WITH RECURSIVE salary_list
   ( level,
     chosen_employee_id,
     manager_id,
     employee_id,
     name,
     salary )
AS ( SELECT CAST ( 1 AS INTEGER ) AS level,
            employee.employee_id  AS chosen_employee_id,
            employee.manager_id   AS manager_id,
            employee.employee_id  AS employee_id,
            employee.name         AS name,
            employee.salary       AS salary
       FROM employee
     UNION ALL
     SELECT salary_list.level + 1,
            salary_list.chosen_employee_id,
            employee.manager_id,
            employee.employee_id,
            employee.name,
            employee.salary
       FROM salary_list
            INNER JOIN employee
                    ON employee.manager_id = salary_list.employee_id
      WHERE salary_list.level <= 99
        AND employee.manager_id <> employee.employee_id )
SELECT *
  FROM salary_list;
-----------------------------------------------------------------------------------
SELECT employee.name,
       ( SELECT SUM ( v_salary_list.salary )
           FROM v_salary_list
          WHERE v_salary_list.chosen_employee_id
              = employee.employee_id )           AS payroll
  FROM employee
ORDER BY 1;
-----------------------------------------------------------------------------------

3.25 UNLOAD TABLE and UNLOAD SELECT [Top]
-----------------------------------------------------------------------------------
UNLOAD TABLE t1 TO 't1_a1.txt';
UNLOAD SELECT * FROM t1 TO 't1_a2.txt';
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         INTEGER NOT NULL,
   col_2         VARCHAR ( 100 ) NULL,
   col_3         BINARY ( 100 ) NULL,
   col_4         DECIMAL ( 11, 2 ) NULL,
   col_5         DATE NULL,
   col_6         INTEGER NOT NULL,
   PRIMARY KEY ( key_1 ) );
INSERT t1 VALUES (
   1, 'Fred''s Here',         'Fred''s Here',     12.34, '2003-09-30', 888 );
INSERT t1 VALUES (
   2, 0x74776f0d0a6c696e6573, 'two\x0d\x0alines', 67.89, '2003-09-30', 999 );
COMMIT;
UNLOAD SELECT * FROM t1 ORDER BY key_1
   TO 't1_b1.txt';
UNLOAD SELECT * FROM t1 ORDER BY key_1
   TO 't1_b2.txt' ESCAPES OFF;
UNLOAD SELECT * FROM t1 ORDER BY key_1
   TO 't1_b3.txt' ESCAPES OFF QUOTES OFF;
UNLOAD SELECT * FROM t1 ORDER BY key_1
   TO 't1_b4.txt' HEXADECIMAL OFF ESCAPES OFF QUOTES OFF;
UNLOAD SELECT * FROM t1 ORDER BY key_1
   TO 't1_b5.txt' DELIMITED BY '' HEXADECIMAL OFF ESCAPES OFF QUOTES OFF;
-----------------------------------------------------------------------------------
SELECT STRING (
          key_1,
          col_2,
          col_3,
          col_4,
          col_5,
          col_6 )
  FROM t1
ORDER BY key_1;
-----------------------------------------------------------------------------------
UNLOAD
SELECT STRING (
          '<HTML><BODY><OL>\x0d\x0a',
          '   <LI>',
          LIST ( DISTINCT state,
                 '</LI>\x0d\x0a   <LI>'
                 ORDER BY state ),
          '</LI>\x0d\x0a',
          '</OL></BODY></HTML>' ) AS states_page
  FROM employee
WHERE dept_id = 100
TO 'c:\\temp\\states_page.html' ESCAPES OFF QUOTES OFF;
-----------------------------------------------------------------------------------

3.26 ISQL OUTPUT [Top]
-----------------------------------------------------------------------------------
SELECT *
  FROM product
WHERE name = 'Sweatshirt'
ORDER BY id;
OUTPUT TO 'product.txt';
-----------------------------------------------------------------------------------
SELECT id,
       name,
       quantity,
       unit_price
  FROM product
WHERE name = 'Sweatshirt'
ORDER BY id;
OUTPUT TO 'c:\\temp\\product.dta' COLUMN WIDTHS ( 5, 15, 5, 7 ) FORMAT FIXED;
OUTPUT TO 'c:\\temp\\product.html' FORMAT HTML;
OUTPUT TO 'c:\\temp\\product.xml' FORMAT XML;
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         INTEGER NOT NULL,
   col_2         VARCHAR ( 100 ) NULL,
   col_3         BINARY ( 100 ) NULL,
   col_4         DECIMAL ( 11, 2 ) NULL,
   col_5         DATE NULL,
   col_6         INTEGER NOT NULL,
   PRIMARY KEY ( key_1 ) );
INSERT t1 VALUES (
   1, 'Fred''s Here',         'Fred''s Here',     12.34, '2003-09-30', 888 );
INSERT t1 VALUES (
   2, 0x74776f0d0a6c696e6573, 'two\x0d\x0alines', 67.89, '2003-09-30', 999 );
COMMIT;
SELECT * FROM t1 ORDER BY key_1;
OUTPUT TO 't1_c1.txt';
OUTPUT TO 't1_c2.txt' DELIMITED BY '' HEXADECIMAL ASIS QUOTE '';
-----------------------------------------------------------------------------------

3.27 Chapter Summary [Top]
-----------------------------------------------------------------------------------

Chapter 4 - UPDATING [Top]
-----------------------------------------------------------------------------------

4.1 Introduction [Top]
-----------------------------------------------------------------------------------

4.2 Single-Row UPDATE [Top]
-----------------------------------------------------------------------------------
UPDATE customer
   SET address = '114 PIONEER AV'
WHERE id = 101;
-----------------------------------------------------------------------------------
UPDATE customer
   SET address = '114 PIONEER AV'
WHERE id = 101
   AND address = '114 Pioneer Avenue';
-----------------------------------------------------------------------------------
UPDATE customer
   SET fname = 'Fred',
       lname = 'Jones'
WHERE company_name = 'The Power Group';
-----------------------------------------------------------------------------------

4.3 Multi-Row UPDATE [Top]
-----------------------------------------------------------------------------------
UPDATE employee
   SET salary = salary * 1.05;
-----------------------------------------------------------------------------------
UPDATE employee
   SET termination_date = CURRENT DATE
WHERE dept_id = 100;
-----------------------------------------------------------------------------------

4.4 Logical Execution of a Set UPDATE [Top]
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         UNSIGNED INTEGER NOT NULL PRIMARY KEY,
   non_key_1     INTEGER NOT NULL );
CREATE TABLE t2 (
   key_1         UNSIGNED INTEGER NOT NULL PRIMARY KEY,
   non_key_1     INTEGER NOT NULL );
INSERT t1 VALUES ( 1,   0 );
INSERT t1 VALUES ( 2,   0 );
INSERT t2 VALUES ( 1, 100 );
INSERT t2 VALUES ( 2, 100 );
-----------------------------------------------------------------------------------
UPDATE t1
       CROSS JOIN t1 AS x
       CROSS JOIN t2
   SET t1.non_key_1 = NUMBER(*),
       t2.non_key_1 = NUMBER(*)
WHERE t1.non_key_1 = 0
ORDER BY t1.key_1 DESC,
       t2.key_1 DESC;
-----------------------------------------------------------------------------------
-- Step 1:
SELECT ...
  FROM t1
       CROSS JOIN t1 AS x
       CROSS JOIN t2
WHERE t1.non_key_1 = 0
ORDER BY t1.key_1 DESC,
       t2.key_1 DESC;
-----------------------------------------------------------------------------------
-- Step 3
SELECT t1.*,
       t2.*,
       NUMBER(*)
  FROM t1
       CROSS JOIN t1 AS x
       CROSS JOIN t2
WHERE t1.non_key_1 = 0
ORDER BY t1.key_1 DESC,
       t2.key_1 DESC;
-----------------------------------------------------------------------------------

4.4.1 Set UPDATE [Top]
-----------------------------------------------------------------------------------
CREATE VIEW v1 AS
SELECT * FROM t1;
CREATE PROCEDURE p1()
BEGIN
   SELECT * FROM t1;
END;
UPDATE v1
       CROSS JOIN p1() AS x
       CROSS JOIN ( SELECT * FROM t2 ) AS d2
   SET v1.non_key_1 = NUMBER(*),
       d2.non_key_1 = NUMBER(*)
WHERE v1.non_key_1 = 0
ORDER BY v1.key_1 DESC,
       d2.key_1 DESC;
-----------------------------------------------------------------------------------
UPDATE employee
       INNER JOIN ( SELECT FIRST sales_order.sales_rep AS top_rep_id
                      FROM sales_order
                           INNER JOIN sales_order_items
                                   ON sales_order_items.id = sales_order.id
                           INNER JOIN product
                                   ON product.id = sales_order_items.prod_id
                     GROUP BY sales_order.sales_rep
                     ORDER BY SUM ( sales_order_items.quantity
                                      * product.unit_price ) DESC )
                  AS top_rep
               ON top_rep.top_rep_id = employee.emp_id
   SET employee.salary = employee.salary * 1.1;
-----------------------------------------------------------------------------------
UPDATE employee SET salary = 43230.00 WHERE emp_id = 299; -- salary was 39300.00
-----------------------------------------------------------------------------------
CREATE PROCEDURE p_top_salespeople
   ( IN @top_count INTEGER )
   RESULT ( top_rep_id INTEGER )
BEGIN
   DECLARE @select LONG VARCHAR;
   SET @select = STRING (
      'SELECT TOP ', @top_count, ' sales_order.sales_rep
         FROM sales_order
              INNER JOIN sales_order_items
                      ON sales_order_items.id = sales_order.id
              INNER JOIN product
                      ON product.id = sales_order_items.prod_id
        GROUP BY sales_order.sales_rep
         ORDER BY SUM ( sales_order_items.quantity
                           * product.unit_price ) DESC' );
   EXECUTE IMMEDIATE @select;
END;
UPDATE employee
       INNER JOIN p_top_salespeople ( 3 )
               ON p_top_salespeople.top_rep_id = employee.emp_id
   SET employee.salary = employee.salary * 1.1;
-----------------------------------------------------------------------------------
UPDATE TOP 2
       employee
       INNER JOIN department
               ON department.dept_id = employee.dept_id
   SET employee.salary = employee.salary * 0.95
WHERE department.dept_name = 'Finance'
ORDER BY employee.start_date DESC;
-----------------------------------------------------------------------------------

4.5 UPDATE WHERE CURRENT OF Cursor [Top]
-----------------------------------------------------------------------------------
BEGIN
DECLARE @t1_key_1          INTEGER;
DECLARE @t1_non_key_1      INTEGER;
DECLARE @t2_key_1          INTEGER;
DECLARE @t2_non_key_1      INTEGER;
DECLARE @number            INTEGER;
DECLARE @SQLSTATE          VARCHAR ( 5 );
DECLARE cloop1 CURSOR FOR
SELECT t1.key_1,
       t1.non_key_1,
       t2.key_1,
       t2.non_key_1
  FROM t1
       CROSS JOIN t1 AS x
       CROSS JOIN t2
WHERE t1.non_key_1 = 0
ORDER BY t1.key_1 DESC,
       t2.key_1 DESC;
OPEN cloop1;
FETCH cloop1 INTO
   @t1_key_1,
   @t1_non_key_1,
   @t2_key_1,
   @t2_non_key_1;
SET @SQLSTATE = SQLSTATE;
SET @number = 0;
WHILE ( @SQLSTATE IN ( '00000', '01W04' ) ) LOOP
   SET @number = @number + 1;
   UPDATE t1, t2
      SET t1.non_key_1 = @number,
          t2.non_key_1 = @number
    WHERE CURRENT OF cloop1;
   FETCH cloop1 INTO
      @t1_key_1,
      @t1_non_key_1,
      @t2_key_1,
      @t2_non_key_1;
   SET @SQLSTATE = SQLSTATE;
END LOOP;
CLOSE cloop1;
END;
-----------------------------------------------------------------------------------

4.6 Chapter Summary [Top]
-----------------------------------------------------------------------------------

Chapter 5 - DELETING [Top]
-----------------------------------------------------------------------------------

5.1 Introduction [Top]
-----------------------------------------------------------------------------------

5.2 Single-Row DELETE [Top]
-----------------------------------------------------------------------------------
DELETE sales_order_items
WHERE id = 2015
   AND line_id = 4;
-----------------------------------------------------------------------------------

5.3 Multi-Row DELETE [Top]
-----------------------------------------------------------------------------------
DELETE sales_order_items;
-----------------------------------------------------------------------------------
DELETE sales_order_items
WHERE id = 2015;
-----------------------------------------------------------------------------------

5.4 Logical Execution of a Set DELETE [Top]
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         UNSIGNED INTEGER NOT NULL PRIMARY KEY,
   non_key_1     INTEGER NOT NULL );
INSERT t1 VALUES ( 1, 1 );
INSERT t1 VALUES ( 2, 2 );
INSERT t1 VALUES ( 3, 3 );
INSERT t1 VALUES ( 4, 4 );
INSERT t1 VALUES ( 5, 5 );
-----------------------------------------------------------------------------------
DELETE t1
  FROM t1
       CROSS JOIN t1 AS x
WHERE t1.key_1 = 2;
-----------------------------------------------------------------------------------
-- Step 1
SELECT DISTINCT ...
  FROM t1
       CROSS JOIN t1 AS x
WHERE t1.key_1 = 2;
-----------------------------------------------------------------------------------
-- Step 3
SELECT DISTINCT t1.*
  FROM t1
       CROSS JOIN t1 AS x
WHERE t1.key_1 = 2;
-----------------------------------------------------------------------------------
-- Step 5
DELETE t1 WHERE key_1 = 2;
-----------------------------------------------------------------------------------