Code Examples from SQL Anywhere Studio 9
Breck Carter [ bcarter@risingroad.com, www.risingroad.com ]
ISBN 1-55622-506-7 Copyright © 2004 Wordware Publishing, Plano, Texas [ www.wordware.com/computer ]

Here are all the code examples from SQL Anywhere Studio 9,
arranged by chapter and section and presented in a format that's easy
to copy and paste.
If you have any questions or comments, please write to me at bcarter@risingroad.com.
Also see the BNF Syntax file.

Chapter
--------------------------------------------------------------------------------
  Section
--------------------------------------------------------------------------------

1 - Creating  
2 - Inserting   
3 - Selecting     
4 - Updating   
5 - Deleting   
6 - Fetching     
7 - Synchronizing  
8 - Packaging  
9 - Protecting
10 - Tuning

CHAPTER 1 - CREATING
1.1 Introduction [Top]
1.2 Codd's 12 Rules For Relational Databases
1.3 Five Types of Tables  
1.4 Global Permanent Table
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1          INTEGER NOT NULL PRIMARY KEY,
   non_key_1      VARCHAR ( 100 ) NOT NULL,
   non_key_2      VARCHAR ( 100 ) NOT NULL );
-----------------------------------------------------------------------------------

1.5 Data Types [Top]
-----------------------------------------------------------------------------------

1.5.1 A String Is A String: BINARY, CHARACTER, LONG
-----------------------------------------------------------------------------------

1.5.2 Dates and Times
-----------------------------------------------------------------------------------

1.5.3 Exact Decimals
-----------------------------------------------------------------------------------
2 + TRUNCNUM ( ( p - s + 1 ) / 2, 0 ) + TRUNCNUM ( ( s + 1 ) / 2, 0 )
-----------------------------------------------------------------------------------

1.5.4 Floating Point Numbers
-----------------------------------------------------------------------------------

1.5.5 Binary Integers
-----------------------------------------------------------------------------------

1.5.6 Literals  
-----------------------------------------------------------------------------------

1.6 Column Properties
-----------------------------------------------------------------------------------

1.7 Computed Columns
-----------------------------------------------------------------------------------
CREATE TABLE street (
   pkey           INTEGER NOT NULL DEFAULT AUTOINCREMENT,
   street_name    VARCHAR ( 100 ) NOT NULL,
   range_part_1   INTEGER NOT NULL,
   range_part_2   VARCHAR ( 100 ) NOT NULL,
   postal_code    VARCHAR ( 6 ) NOT NULL,
   PRIMARY KEY ( pkey ) );
SELECT *
  FROM street
WHERE street_name LIKE 'GRAND%'
   AND postal_code LIKE 'L6H5%';
-----------------------------------------------------------------------------------
CREATE TABLE street (
   pkey           INTEGER NOT NULL DEFAULT AUTOINCREMENT,
   fast_search    VARCHAR ( 5 ) NOT NULL
                  COMPUTE ( STRING ( LEFT ( postal_code, 3 ),
                                     LEFT ( street_name, 2 ) ) ),
   street_name    VARCHAR ( 100 ) NOT NULL,
   range_part_1   INTEGER NOT NULL,
   range_part_2   VARCHAR ( 100 ) NOT NULL,
   postal_code    VARCHAR ( 6 ) NOT NULL,
   PRIMARY KEY ( pkey ) );
-----------------------------------------------------------------------------------
CREATE CLUSTERED INDEX xfast ON street ( fast_search );
SELECT *
  FROM street
WHERE fast_search = 'L6HGR'
   AND street_name LIKE 'GRAND%'
   AND postal_code LIKE 'L6H5%';
-----------------------------------------------------------------------------------
CREATE TABLE t (
   pkey INTEGER NOT NULL DEFAULT AUTOINCREMENT,
   x    INTEGER,
   y    INTEGER,
   z    INTEGER,
   PRIMARY KEY ( pkey ) );
SELECT * FROM t WHERE ( x + y + z ) BETWEEN 1501 AND 1510;
-----------------------------------------------------------------------------------
SELECT EXPRTYPE ( 'SELECT ( x + y + z ) FROM t', 1 );
-----------------------------------------------------------------------------------
CREATE TABLE t (
   pkey INTEGER NOT NULL DEFAULT AUTOINCREMENT,
   x    INTEGER,
   y    INTEGER,
   z    INTEGER,
   xyz  INTEGER COMPUTE ( x + y + z ),
   PRIMARY KEY ( pkey ) );
CREATE CLUSTERED INDEX xyz ON t ( xyz );
SELECT * FROM t WHERE ( x + y + z ) BETWEEN 1501 AND 1510;
-----------------------------------------------------------------------------------

1.8 DEFAULT Values
-----------------------------------------------------------------------------------

1.8.1 DEFAULT AUTOINCREMENT
-----------------------------------------------------------------------------------
BEGIN
   DECLARE @max_key_1 INTEGER;
   SELECT MAX ( key_1 ) INTO @max_key_1 FROM t1;
   CALL sa_reset_identity ( 't1', 'DBA', @max_key_1 );
END;
-----------------------------------------------------------------------------------

1.8.2 DEFAULT GLOBAL AUTOINCREMENT
-----------------------------------------------------------------------------------
SET OPTION PUBLIC.GLOBAL_DATABASE_ID = '5';
CREATE TABLE t (
   auto UNSIGNED BIGINT DEFAULT GLOBAL AUTOINCREMENT ( 1000 ) );
INSERT t VALUES ( DEFAULT );
INSERT t VALUES ( DEFAULT );
COMMIT;
SELECT * FROM t ORDER BY t.auto;
-----------------------------------------------------------------------------------
INSERT t VALUES ( 9999 );
INSERT t VALUES ( DEFAULT );
INSERT t VALUES ( 5100 );
INSERT t VALUES ( 5004 );
INSERT t VALUES ( DEFAULT );
COMMIT;
SELECT * FROM t ORDER BY t.auto;
-- Displays 5001, 5002, 5003, 5004, 5100, 5101, 9999
-----------------------------------------------------------------------------------
SET OPTION PUBLIC.GLOBAL_DATABASE_ID = '1000';
CREATE TABLE t (
   auto UNSIGNED BIGINT DEFAULT GLOBAL AUTOINCREMENT ( 1000000000000000 ) );
INSERT t VALUES ( DEFAULT );                 -- 1000000000000001
INSERT t VALUES ( DEFAULT );                 -- 1000000000000002
INSERT t VALUES ( 1001 * 1000000000000000 ); -- 1001000000000000
INSERT t VALUES ( DEFAULT );                 -- NULL
-----------------------------------------------------------------------------------

1.8.3 Literal Defaults
-----------------------------------------------------------------------------------
CREATE TABLE t (
   c1  INTEGER,
   c2  VARCHAR ( 1 )    DEFAULT 'Y',                -- Y
   c3  BINARY ( 20 )    DEFAULT 0x48656C6C6F,       -- Hello
   c4  VARCHAR ( 1 )    DEFAULT '\n',               -- new line
   c5  VARCHAR ( 100 )  DEFAULT 'c:\\new',          -- c:\new
   c6  LONG VARCHAR     DEFAULT '\x61\x62\x63',     -- abc
   c7  INTEGER          DEFAULT 0,                  -- 0
   c8  DECIMAL ( 9, 2 ) DEFAULT 27.95,              -- 27.95
   c9  DOUBLE           DEFAULT -123.456E-2,        -- -1.23456
   c10 DATE             DEFAULT '2003 07 06',       -- July 6, 2003
   c11 TIME             DEFAULT '00:01',            -- 1 minute past midnight
   c12 TIMESTAMP        DEFAULT '20030706 14:30' ); -- 2:30 PM, July 6, 2003
-----------------------------------------------------------------------------------

1.8.4 Special Update Defaults
-----------------------------------------------------------------------------------

1.8.5 Expressions As Defaults  
-----------------------------------------------------------------------------------
CREATE TABLE t (
   c1 INTEGER,
   server   VARCHAR ( 100 ) DEFAULT PROPERTY ( 'MachineName' ),
   today    VARCHAR ( 100 ) DEFAULT DAYNAME ( CURRENT DATE ),
   tomorrow DATE            DEFAULT ( CURRENT DATE + 1 ),
   guid     BINARY ( 16 )   DEFAULT NEWID() );
-----------------------------------------------------------------------------------

1.9 NULL Property [
-----------------------------------------------------------------------------------

1.10 Column Constraints  
-----------------------------------------------------------------------------------

1.10.1 NOT NULL Constraint  
-----------------------------------------------------------------------------------

1.10.2 Column CHECK Constraint  
-----------------------------------------------------------------------------------
CREATE TABLE schema_created (
   pkey    INTEGER NOT NULL DEFAULT 1 CHECK ( pkey = 1 ),
   created TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
   PRIMARY KEY ( pkey ) );
INSERT schema_created VALUES ( DEFAULT, DEFAULT );
-----------------------------------------------------------------------------------
CREATE TABLE t (
   c1 INTEGER CHECK ( c1 > ( SELECT MAX ( c1 ) FROM t ) ) );
INSERT t VALUES ( 3 ); -- OK
INSERT t VALUES ( 4 ); -- OK
INSERT t VALUES ( 1 ); -- Fails
-----------------------------------------------------------------------------------
CREATE TABLE t (
   c1 INTEGER CONSTRAINT "c1 must increase in value"
              CHECK ( c1 > ( SELECT MAX ( c1 ) FROM t ) ) );
-----------------------------------------------------------------------------------

1.10.3 PRIMARY KEY Column Constraint
-----------------------------------------------------------------------------------

1.10.4 Foreign Key Column Constraint  
-----------------------------------------------------------------------------------
CREATE TABLE country (
   country_code  VARCHAR ( 2 ) PRIMARY KEY,
   name          VARCHAR ( 100 ) NOT NULL );
CREATE TABLE office (
   office_code  VARCHAR ( 10 ) PRIMARY KEY,
   country_code VARCHAR ( 2 ) NULL
                CONSTRAINT "office.country_code must be valid or NULL"
                REFERENCES country ( country_code )
                ON UPDATE CASCADE ON DELETE SET NULL );
-----------------------------------------------------------------------------------
INSERT country VALUES ( 'CA', 'Canada' );
INSERT office VALUES ( '001', 'CA' ); -- OK
INSERT office VALUES ( '002', NULL ); -- OK
INSERT office VALUES ( '003', 'XX' ); -- fails

1.10.5 UNIQUE Column Constraint
-----------------------------------------------------------------------------------

1.11 User-Defined Data Types  
-----------------------------------------------------------------------------------
CREATE DOMAIN address AS VARCHAR ( 100 )
                         NOT NULL
                         DEFAULT ''
                         CHECK ( LENGTH ( TRIM ( @col ) ) > 0 );
CREATE TABLE office (
   office_code INTEGER PRIMARY KEY,
   address_1   address,
   address_2   address CHECK ( address_2 IS NOT NULL ),
   address_3   address NULL DEFAULT ( NULL ) );
INSERT office ( office_code, address_1 ) VALUES ( 1, '123 Main Street' );
-----------------------------------------------------------------------------------

1.12 Free Space
-----------------------------------------------------------------------------------

1.13 Table Constraints [
-----------------------------------------------------------------------------------

1.13.1 Table CHECK Constraint  
-----------------------------------------------------------------------------------
CREATE TABLE t (
   c1 INTEGER,
   c2 INTEGER,
   c3 INTEGER,
   CONSTRAINT "0 < c1 < c2 < c3"
   CHECK ( 0 < c1 AND c1 < c2 AND c2 < c3 ) );
INSERT t VALUES ( 1, 2, 3 ); -- OK
INSERT t VALUES ( 2, 2, 2 ); -- fails
-----------------------------------------------------------------------------------

1.13.2 PRIMARY KEY Table Constraint ]
-----------------------------------------------------------------------------------
CREATE TABLE audit_office (
   copied       TIMESTAMP DEFAULT TIMESTAMP,
   office_code  VARCHAR ( 10 ),
   country_code VARCHAR ( 2 ),
   PRIMARY KEY ( copied, office_code ) );
-----------------------------------------------------------------------------------

1.13.3 FOREIGN KEY Table Constraint
-----------------------------------------------------------------------------------
CREATE TABLE country (
   country_code  VARCHAR ( 2 ),
   name          VARCHAR ( 100 ),
   PRIMARY KEY ( country_code ) );
CREATE TABLE office (
   country_code VARCHAR ( 2 ),
   office_code  VARCHAR ( 10 ),
   address      VARCHAR ( 1000 ),
   PRIMARY KEY ( country_code, office_code ),
   FOREIGN KEY ( country_code ) REFERENCES country );
CREATE TABLE representative (
   country_code      VARCHAR ( 2 ),
   office_code       VARCHAR ( 10 ),
   representative_id INTEGER,
   name              VARCHAR ( 100 ),
   PRIMARY KEY ( country_code, office_code, representative_id ),
   FOREIGN KEY ( country_code, office_code ) REFERENCES office );
-----------------------------------------------------------------------------------

1.13.4 UNIQUE Table Constraint
-----------------------------------------------------------------------------------
CREATE TABLE t (
   c1 INTEGER PRIMARY KEY,
   c2 INTEGER,
   c3 INTEGER,
   UNIQUE ( c2, c3 ) );
INSERT t VALUES ( 1, 1, 1 ); -- OK
INSERT t VALUES ( 2, 1, 2 ); -- OK
INSERT t VALUES ( 3, 2, 1 ); -- OK
INSERT t VALUES ( 4, 1, 1 ); -- fails

1.14 Remote Data Access
-----------------------------------------------------------------------------------
SELECT *
  FROM Oracle_employee
  JOIN DB2_sales_projection
    ON Oracle_employee.employee_id
     = DB2_sales_projection.employee_id;
-----------------------------------------------------------------------------------
INSERT sales_projection
SELECT *
  FROM DB2_sales_projection;
-----------------------------------------------------------------------------------

1.14.1 CREATE SERVER
-----------------------------------------------------------------------------------

1.14.2 CREATE EXTERNLOGIN
-----------------------------------------------------------------------------------

1.14.3 CREATE Remote And Proxy Tables
-----------------------------------------------------------------------------------
CREATE SERVER ASAEXCEL CLASS 'ODBC' USING 'EXCEL SAMPLE';
CREATE TABLE etest
   ( pkey INTEGER NOT NULL,
     fld1 VARCHAR ( 20 ) NOT NULL,
     fld2 INTEGER NOT NULL )
   AT 'ASAEXCEL;c:\\temp\\asaexcel.xls;;test1';
INSERT INTO etest VALUES ( 1, 'Hello, World', 9 );
SELECT * FROM etest;
-----------------------------------------------------------------------------------

1.14.4 CREATE EXISTING TABLE [Top]
-----------------------------------------------------------------------------------
CREATE SERVER ORASAMPLE CLASS 'ORAODBC' USING 'ORA SAMPLE';
CREATE EXTERNLOGIN DBA TO ORASAMPLE
  REMOTE LOGIN system IDENTIFIED BY 'manager';
CREATE EXISTING TABLE ora_dept AT 'ORASAMPLE..SCOTT.DEPT';
SELECT * FROM ora_dept;
-----------------------------------------------------------------------------------
CREATE SERVER DB2DELL180 CLASS 'DB2ODBC' USING 'DB2 SAMPLE';
CREATE EXTERNLOGIN DBA TO DB2DELL180
  REMOTE LOGIN db2admin IDENTIFIED BY 'secret';
CREATE EXISTING TABLE db2_department
  AT 'DB2DELL180.SAMPLE.ADMINISTRATOR.DEPARTMENT';
SELECT * FROM db2_department;
-----------------------------------------------------------------------------------
CREATE EXISTING TABLE etest2
   ( pkey NUMERIC ( 10 ) NOT NULL,
     fld1 VARCHAR ( 100 ) NOT NULL )
   AT 'ASAEXCEL;c:\\temp\\asaexcel.xls;;test1';
INSERT INTO etest2 VALUES ( 2, 'A value that is longer than 20 characters.' );
SELECT * FROM etest2;
-----------------------------------------------------------------------------------
-- Part 1 of full end-to-end example using SQL Anywhere 9
CREATE TABLE t1 (
   pkey INTEGER NOT NULL,
   c1   VARCHAR ( 20 ) NOT NULL,
   PRIMARY KEY ( pkey ) );
INSERT INTO t1 VALUES ( 1, 'Hello, World' );
COMMIT;
-----------------------------------------------------------------------------------
-- Part 2 of full end-to-end example using SQL Anywhere 9
CREATE SERVER other CLASS 'ASAODBC' USING 'otherdsn';
CREATE EXTERNLOGIN DBA TO other
  REMOTE LOGIN DBA IDENTIFIED BY "SQL";
CREATE EXISTING TABLE t1proxy AT 'other;otherdb;dba;t1';
SELECT * FROM t1proxy; -- displays 'Hello, World'
CREATE TABLE t2proxy (
   pkey INTEGER NOT NULL,
   c1   VARCHAR ( 20 ) NOT NULL,
   PRIMARY KEY ( pkey ) )
   AT 'other;otherdb;dba;t2';
INSERT INTO t2proxy VALUES ( 1, 'Goodbye' );
SELECT * FROM t2proxy; -- displays 'Goodbye'
-----------------------------------------------------------------------------------

1.15 Temporary Tables
-----------------------------------------------------------------------------------

1.15.1 Global Temporary Tables
-----------------------------------------------------------------------------------
CREATE GLOBAL TEMPORARY TABLE t (
   c1 INTEGER )
   ON COMMIT PRESERVE ROWS;
INSERT t VALUES ( 1 ); -- gets rolled back
ROLLBACK;
INSERT t VALUES ( 2 ); -- gets committed
COMMIT;
INSERT t VALUES ( 3 ); -- gets rolled back
ROLLBACK;
SELECT * FROM t;       -- only shows row # 2
-----------------------------------------------------------------------------------
CREATE GLOBAL TEMPORARY TABLE t (
   c1 INTEGER )
   NOT TRANSACTIONAL;
INSERT t VALUES ( 1 );
ROLLBACK;              -- has no effect
INSERT t VALUES ( 2 );
COMMIT;                -- has no effect
SELECT * FROM t;       -- shows both rows
-----------------------------------------------------------------------------------

1.15.2 Local Temporary Tables
-----------------------------------------------------------------------------------

1.15.2.1 CREATE TABLE #table_name
-----------------------------------------------------------------------------------
CREATE TABLE #t ( c1 INTEGER );
INSERT #t VALUES ( 1 ); -- gets rolled back
ROLLBACK;
INSERT #t VALUES ( 2 ); -- gets committed
COMMIT;
INSERT #t VALUES ( 3 ); -- gets rolled back
ROLLBACK;
SELECT * FROM #t;       -- only shows row 2
-----------------------------------------------------------------------------------
CREATE TABLE #t ( c1 INTEGER );
INSERT #t VALUES ( 1 );
SELECT * FROM #t; -- displays 1
BEGIN
   SELECT * FROM #t; -- still displays 1
   CREATE TABLE #t ( c1 INTEGER );
   INSERT #t VALUES ( 2 );
   SELECT * FROM #t; -- now displays 2
END;
SELECT * FROM #t; -- displays 1 again
-----------------------------------------------------------------------------------

1.15.2.2 DECLARE LOCAL TEMPORARY TABLE
-----------------------------------------------------------------------------------
CREATE TABLE t ( c1 INTEGER ); -- permanent table
INSERT t VALUES ( 1 );
SELECT * FROM t;    -- displays 1
BEGIN
   DECLARE LOCAL TEMPORARY TABLE t ( c1 INTEGER );
   INSERT t VALUES ( 2 );
   SELECT * FROM t; -- displays 2
END;
SELECT * FROM t;    -- displays 1 again
-----------------------------------------------------------------------------------
CREATE GLOBAL TEMPORARY TABLE t ( c1 INTEGER );
INSERT t VALUES ( 1 );
SELECT * FROM t; -- displays 1
DECLARE LOCAL TEMPORARY TABLE t ( c1 INTEGER );
INSERT t VALUES ( 2 );
SELECT * FROM t; -- displays 2
DROP TABLE t;    -- drops the temporary table
SELECT * FROM t; -- displays 1 again

1.16 Normalized Design
-----------------------------------------------------------------------------------
CREATE TABLE order_form (
   order_number              INTEGER NOT NULL PRIMARY KEY,
   client_name               VARCHAR ( 100 ) NOT NULL,
   shipping_address          VARCHAR ( 1000 ) NOT NULL,
   salesperson_name          VARCHAR ( 100 ) NOT NULL,
   salesperson_phone         VARCHAR ( 100 ) NOT NULL,
   salesperson_commission    NUMERIC ( 6, 3 ) NOT NULL,
   product_number_1          INTEGER NOT NULL,
   product_description_1     VARCHAR ( 100 ) NOT NULL,
   requested_quantity_1      INTEGER NOT NULL,
   estimated_shipping_date_1 DATE NOT NULL,
   product_number_2          INTEGER NULL,
   product_description_2     VARCHAR ( 100 ) NULL,
   requested_quantity_2      INTEGER NULL,
   estimated_shipping_date_2 DATE NULL,
   product_number_3          INTEGER NULL,
   product_description_3     VARCHAR ( 100 ) NULL,
   requested_quantity_3      INTEGER NULL,
   estimated_shipping_date_3 DATE NULL );
-----------------------------------------------------------------------------------

1.16.1 First Normal Form
-----------------------------------------------------------------------------------
CREATE TABLE order_header (
   order_number              INTEGER NOT NULL PRIMARY KEY,
   client_name               VARCHAR ( 100 ) NOT NULL,
   shipping_address          VARCHAR ( 1000 ) NOT NULL,
   salesperson_name          VARCHAR ( 100 ) NOT NULL,
   salesperson_phone         VARCHAR ( 100 ) NOT NULL,
   salesperson_commission    NUMERIC ( 6, 3 ) NOT NULL );
CREATE TABLE order_detail (
   order_number              INTEGER NOT NULL REFERENCES order_header,
   product_number            INTEGER NOT NULL,
   product_description       VARCHAR ( 100 ) NOT NULL,
   requested_quantity        INTEGER NOT NULL,
   estimated_shipping_date   DATE NOT NULL,
   PRIMARY KEY ( order_number, product_number ) );
-----------------------------------------------------------------------------------

1.16.2 Second Normal Form
-----------------------------------------------------------------------------------
CREATE TABLE order_header (
   order_number              INTEGER NOT NULL PRIMARY KEY,
   client_name               VARCHAR ( 100 ) NOT NULL,
   shipping_address          VARCHAR ( 1000 ) NOT NULL,
   salesperson_name          VARCHAR ( 100 ) NOT NULL,
   salesperson_phone         VARCHAR ( 100 ) NOT NULL,
   salesperson_commission    NUMERIC ( 6, 3 ) NOT NULL );
CREATE TABLE product_catalog (
   product_number            INTEGER NOT NULL PRIMARY KEY,
   product_description       VARCHAR ( 100 ) NOT NULL );
CREATE TABLE product_order (
   order_number              INTEGER NOT NULL REFERENCES order_header,
   product_number            INTEGER NOT NULL REFERENCES product_catalog,
   requested_quantity        INTEGER NOT NULL,
   estimated_shipping_date   DATE NOT NULL,
   PRIMARY KEY ( order_number, product_number ) );
-----------------------------------------------------------------------------------

1.16.3 Third Normal Form
-----------------------------------------------------------------------------------
CREATE TABLE salesperson (
   salesperson_id            INTEGER NOT NULL PRIMARY KEY,
   name                      VARCHAR ( 100 ) NOT NULL,
   phone                     VARCHAR ( 100 ) NOT NULL );
CREATE TABLE sales_order (
   order_number              INTEGER NOT NULL PRIMARY KEY,
   client_name               VARCHAR ( 100 ) NOT NULL,
   shipping_address          VARCHAR ( 1000 ) NOT NULL,
   salesperson_id            INTEGER NOT NULL REFERENCES salesperson,
   salesperson_commission    NUMERIC ( 6, 3 ) NOT NULL );
CREATE TABLE product_catalog (
   product_number            INTEGER NOT NULL PRIMARY KEY,
   product_description       VARCHAR ( 100 ) NOT NULL );
CREATE TABLE product_order (
   order_number              INTEGER NOT NULL REFERENCES sales_order,
   product_number            INTEGER NOT NULL REFERENCES product_catalog,
   requested_quantity        INTEGER NOT NULL,
   estimated_shipping_date   DATE NOT NULL,
   PRIMARY KEY ( order_number, product_number ) );
-----------------------------------------------------------------------------------

1.16.4 Boyce-Codd Normal Form
-----------------------------------------------------------------------------------
CREATE TABLE sales_skill (
   sales_skill_id            INTEGER NOT NULL PRIMARY KEY,
   description               LONG VARCHAR );
CREATE TABLE salesperson_skill (
   salesperson_id            INTEGER NOT NULL,
   salesperson_name          VARCHAR ( 100 ) NOT NULL,
   sales_skill_id            INTEGER NULL REFERENCES sales_skill,
   PRIMARY KEY ( salesperson_id, sales_skill_id ),
   UNIQUE ( salesperson_name, sales_skill_id ) );
-----------------------------------------------------------------------------------
CREATE TABLE salesperson (
   salesperson_id            INTEGER NOT NULL PRIMARY KEY,
   salesperson_name          VARCHAR ( 100 ) NOT NULL UNIQUE );
CREATE TABLE sales_skill (
   sales_skill_id            INTEGER NOT NULL PRIMARY KEY,
   description               LONG VARCHAR );
CREATE TABLE salesperson_skill (
   salesperson_id            INTEGER NOT NULL REFERENCES salesperson,
   sales_skill_id            INTEGER NULL REFERENCES sales_skill,
   PRIMARY KEY ( salesperson_id, sales_skill_id ) );
-----------------------------------------------------------------------------------

1.16.5 Fourth Normal Form
-----------------------------------------------------------------------------------
CREATE TABLE salesperson (
   salesperson_id            INTEGER NOT NULL PRIMARY KEY,
   salesperson_name          VARCHAR ( 100 ) NOT NULL );
CREATE TABLE sales_skill (
   sales_skill_id            INTEGER NOT NULL PRIMARY KEY,
   description               LONG VARCHAR );
CREATE TABLE technical_skill (
   technical_skill_id        INTEGER NOT NULL PRIMARY KEY,
   description               LONG VARCHAR );
CREATE TABLE salesperson_skill (
   salesperson_id            INTEGER NOT NULL REFERENCES salesperson,
   sales_skill_id            INTEGER NOT NULL REFERENCES sales_skill,
   technical_skill_id        INTEGER NOT NULL REFERENCES technical_skill,
   PRIMARY KEY ( salesperson_id, sales_skill_id, technical_skill_id ) );
-----------------------------------------------------------------------------------
CREATE TABLE salesperson_sales_skill (
   salesperson_id            INTEGER NOT NULL REFERENCES salesperson,
   sales_skill_id            INTEGER NOT NULL REFERENCES sales_skill,
   PRIMARY KEY ( salesperson_id, sales_skill_id ) );
CREATE TABLE salesperson_technical_skill (
   salesperson_id            INTEGER NOT NULL REFERENCES salesperson,
   technical_skill_id        INTEGER NOT NULL REFERENCES technical_skill,
   PRIMARY KEY ( salesperson_id, technical_skill_id ) );
-----------------------------------------------------------------------------------

1.16.6 Fifth Normal Form
-----------------------------------------------------------------------------------
CREATE TABLE salesperson (
   salesperson_id            INTEGER NOT NULL PRIMARY KEY,
   salesperson_name          VARCHAR ( 100 ) NOT NULL );
CREATE TABLE company (
   company_id                VARCHAR ( 10 ) NOT NULL PRIMARY KEY,
   company_name              VARCHAR ( 100 ) NOT NULL );
CREATE TABLE product_line (
   product_line_id           VARCHAR ( 10 ) NOT NULL PRIMARY KEY,
   product_line_description  VARCHAR ( 100 ) NOT NULL );
CREATE TABLE salesperson_company_line (
   salesperson_id            INTEGER NOT NULL REFERENCES salesperson,
   company_id                VARCHAR ( 10 ) NOT NULL REFERENCES company,
   product_line_id           VARCHAR ( 10 ) NOT NULL REFERENCES product_line,
   PRIMARY KEY ( salesperson_id, company_id, product_line_id ) );
-----------------------------------------------------------------------------------
INSERT salesperson_company_line VALUES ( 1, 'Acme', 'cars' );
INSERT salesperson_company_line VALUES ( 2, 'Acme', 'trucks' );
INSERT salesperson_company_line VALUES ( 2, 'Best', 'cars' );
-----------------------------------------------------------------------------------
CREATE TABLE salesperson_company (
   salesperson_id            INTEGER NOT NULL REFERENCES salesperson,
   company_id                VARCHAR ( 10 ) NOT NULL REFERENCES company,
   PRIMARY KEY ( salesperson_id, company_id ) );
CREATE TABLE company_line (
   company_id                VARCHAR ( 10 ) NOT NULL REFERENCES company,
   product_line_id           VARCHAR ( 10 ) NOT NULL REFERENCES product_line,
   PRIMARY KEY ( company_id, product_line_id ) );
CREATE TABLE salesperson_line (
   salesperson_id            INTEGER NOT NULL REFERENCES salesperson,
   product_line_id           VARCHAR ( 10 ) NOT NULL REFERENCES product_line,
   PRIMARY KEY ( salesperson_id, product_line_id ) );
-----------------------------------------------------------------------------------
INSERT salesperson_company VALUES ( 1, 'Acme' );
INSERT salesperson_company VALUES ( 2, 'Acme' );
INSERT salesperson_company VALUES ( 2, 'Best' );
INSERT company_line VALUES ( 'Acme', 'cars' );
INSERT company_line VALUES ( 'Acme', 'trucks' );
INSERT company_line VALUES ( 'Best', 'cars' );
INSERT salesperson_line VALUES ( 1, 'cars' );
INSERT salesperson_line VALUES ( 2, 'cars' );
INSERT salesperson_line VALUES ( 2, 'trucks' );
SELECT DISTINCT
       salesperson_company.salesperson_id,
       company_line.company_id,
       salesperson_line.product_line_id
  FROM salesperson_company
  JOIN company_line
    ON salesperson_company.company_id = company_line.company_id
  JOIN salesperson_line
    ON salesperson_company.salesperson_id = salesperson_line.salesperson_id
   AND company_line.product_line_id = salesperson_line.product_line_id;

1.17 Chapter Summary
-----------------------------------------------------------------------------------

Chapter 2 - INSERTING
-----------------------------------------------------------------------------------

2.1 Introduction
-----------------------------------------------------------------------------------

2.2 Insert
-----------------------------------------------------------------------------------

2.2.1 INSERT All Values
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         INTEGER NOT NULL DEFAULT AUTOINCREMENT,
   non_key_1     VARCHAR ( 100 ) NOT NULL,
   last_updated  TIMESTAMP NOT NULL DEFAULT TIMESTAMP,
   PRIMARY KEY ( key_1 ) );
INSERT t1 VALUES ( 1, 'first row', '2003 09 29 13:21' );
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         INTEGER NOT NULL DEFAULT AUTOINCREMENT,
   non_key_1     VARCHAR ( 100 ) NOT NULL,
   last_updated  TIMESTAMP NOT NULL DEFAULT TIMESTAMP,
   PRIMARY KEY ( key_1 ) );
INSERT t1 VALUES ( 1, 'first row', DEFAULT );
INSERT t1 ON EXISTING UPDATE VALUES ( 1, 'replaced', DEFAULT );
INSERT t1 ON EXISTING SKIP  VALUES ( 1, 'ignored', DEFAULT );
-----------------------------------------------------------------------------------

2.2.2 INSERT Named Values
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         INTEGER NOT NULL DEFAULT AUTOINCREMENT,
   col_2         VARCHAR ( 100 ) NOT NULL DEFAULT 'X',
   col_3         VARCHAR ( 100 ) NOT NULL DEFAULT 'Y',
   updated_by    VARCHAR ( 128 ) NOT NULL DEFAULT LAST USER,
   last_updated  TIMESTAMP NOT NULL DEFAULT TIMESTAMP,
   PRIMARY KEY ( key_1 ) );
INSERT t1 ( col_3, col_2 ) VALUES ( 'B', 'A' );
-----------------------------------------------------------------------------------
INSERT t1 ( key_1 ) VALUES ( DEFAULT );
-----------------------------------------------------------------------------------

2.2.3 INSERT Select All Columns
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1         INTEGER NOT NULL DEFAULT AUTOINCREMENT,
   non_key_1     VARCHAR ( 100 ) NOT NULL DEFAULT 'xxx',
   last_updated  TIMESTAMP NOT NULL DEFAULT TIMESTAMP,
   PRIMARY KEY ( key_1 ) );
CREATE TABLE t2 (
   key_1         INTEGER NOT NULL DEFAULT AUTOINCREMENT,
   non_key_1     VARCHAR ( 100 ) NOT NULL DEFAULT 'xxx',
   last_updated  TIMESTAMP NOT NULL DEFAULT TIMESTAMP,
   PRIMARY KEY ( key_1 ) );
INSERT t2
SELECT key_1, non_key_1, last_updated
  FROM t1;
-----------------------------------------------------------------------------------
INSERT t2 SELECT * FROM t1;
-----------------------------------------------------------------------------------
INSERT t2
SELECT 0, 'first', '2001-01-01'
UNION
SELECT * FROM t1
WHERE key_1 BETWEEN 1 AND 9998
UNION
SELECT 9999, 'last', CURRENT TIMESTAMP;
-----------------------------------------------------------------------------------
CREATE TABLE t1 (
   key_1     INTEGER NOT NULL PRIMARY KEY );
INSERT t1 VALUES ( 1 );
CREATE TABLE t2 (
   key_1     VARCHAR ( 10 ) NOT NULL PRIMARY KEY,
   non_key_1 INTEGER NOT NULL );
INSERT t2 VALUES ( 'A', 1 );
INSERT t2 VALUES ( 'B', 2 );
INSERT t2 VALUES ( 'C', 2 );
INSERT t1
SELECT t2.non_key_1
  FROM t2
WHERE NOT EXISTS ( SELECT *
                      FROM t1
                     WHERE t1.key_1 = t2.non_key_1 );
-----------------------------------------------------------------------------------