// Create tables for all Java EE Tutorial examples

// Create tables for Web bookstore applications

CREATE TABLE WEB_BOOKSTORE_BOOKS
(bookId VARCHAR(8),
surname VARCHAR(24),
firstName VARCHAR(24),
title VARCHAR(96),
price FLOAT,
onSale SMALLINT,
calendar_year INT,
description VARCHAR(30),
inventory INT);

INSERT INTO WEB_BOOKSTORE_BOOKS VALUES('201', 'Duke', '',
 'My Early Years: Growing up on *7',
 30.75, 0, 1995, 'What a cool book.', 20);

INSERT INTO WEB_BOOKSTORE_BOOKS VALUES('202', 'Jeeves', '',
 'Web Servers for Fun and Profit', 40.75, 1,
 2000, 'What a cool book.', 20);

INSERT INTO WEB_BOOKSTORE_BOOKS VALUES('203', 'Masterson', 'Webster',
 'Web Components for Web Developers',
 27.75, 0, 2000, 'What a cool book.', 20);

INSERT INTO WEB_BOOKSTORE_BOOKS VALUES('205', 'Novation', 'Kevin',
 'From Oak to Java: The Revolution of a Language',
 10.75, 1, 1998, 'What a cool book.', 20);

INSERT INTO WEB_BOOKSTORE_BOOKS VALUES('206', 'Gosling', 'James',
 'Java Intermediate Bytecodes', 30.95, 1,
 2000, 'What a cool book.', 20);

INSERT INTO WEB_BOOKSTORE_BOOKS VALUES('207', 'Thrilled', 'Ben',
 'The Green Project: Programming for Consumer Devices',
 30.00, 1, 1998, 'What a cool book', 20);

INSERT INTO WEB_BOOKSTORE_BOOKS VALUES('208', 'Tru', 'Itzal',
 'Duke: A Biography of the Java Evangelist',
 45.00, 0, 2001, 'What a cool book.', 20);

// Create tables for Order application (examples/ejb/order)
 
// Self-reference to identify Bill of Material (BOM)
// Compound PK 
 
CREATE TABLE EJB_ORDER_PART (
	PARTNUMBER		VARCHAR(15)	NOT NULL,
	REVISION		NUMERIC(2)      NOT NULL,
	DESCRIPTION		VARCHAR(255),
	REVISIONDATE		TIMESTAMP       NOT NULL,
	BOMPARTNUMBER           VARCHAR(15),
	BOMREVISION		NUMERIC(2) ,
	PRIMARY KEY (PARTNUMBER, REVISION)
);

ALTER TABLE EJB_ORDER_PART
	add CONSTRAINT FK_1 FOREIGN KEY (BOMPARTNUMBER, BOMREVISION) 
                REFERENCES EJB_ORDER_PART (PARTNUMBER, REVISION);

// Bean will be mapped to 2 tables (PART and PART_DETAIL)
// BLOB column type
// CLOB column type
 
CREATE TABLE EJB_ORDER_PART_DETAIL (
	PARTNUMBER	VARCHAR(15)	NOT NULL,
	REVISION	NUMERIC(2)      NOT NULL,
	SPECIFICATION	CLOB(10K),
	DRAWING		BLOB(10K),
	PRIMARY KEY (PARTNUMBER, REVISION)
);

// PK can be mapped to a primitive PK field type

CREATE TABLE EJB_ORDER_VENDOR (
	VENDORID        INTEGER        	PRIMARY KEY,
	VENDORNAME	VARCHAR(30)	NOT NULL,
	ADDRESS		VARCHAR(255)	NOT NULL,
	CONTACT		VARCHAR(255)	NOT NULL,
	PHONE		VARCHAR(30)	NOT NULL
);

// Can be used for unknown PK 
// 1-1 map to PART
// Compound FK
 
CREATE TABLE EJB_ORDER_VENDOR_PART (
	VENDORPARTNUMBER	BIGINT                  PRIMARY KEY,
	DESCRIPTION		VARCHAR(255),
	PRICE			DOUBLE PRECISION        NOT NULL,
	VENDORID		INTEGER                 NOT NULL,
	PARTNUMBER		VARCHAR(15)		NOT NULL,
	PARTREVISION		NUMERIC(2)              NOT NULL,
	CONSTRAINT FK_2 FOREIGN KEY (VENDORID) REFERENCES EJB_ORDER_VENDOR (VENDORID),
	CONSTRAINT FK_3 FOREIGN KEY (PARTNUMBER, PARTREVISION) REFERENCES EJB_ORDER_PART (PARTNUMBER, REVISION),
	UNIQUE (PARTNUMBER, PARTREVISION)
);

CREATE TABLE EJB_ORDER_SEQUENCE_GENERATOR (
        GEN_KEY         VARCHAR(255),
        GEN_VALUE       NUMERIC(20)
);

CREATE TABLE EJB_ORDER_ORDER (
	ORDERID         INTEGER         PRIMARY KEY,
	STATUS		CHAR(1)		NOT NULL,
	LASTUPDATE	TIMESTAMP       NOT NULL,
	DISCOUNT	NUMERIC(2)      NOT NULL,
	SHIPMENTINFO	VARCHAR(255)	
);

// Overlapping PK-FK 
// Uni-directional to EJB_ORDER_VENDOR_PART
 
CREATE TABLE EJB_ORDER_LINEITEM (
	ORDERID                 INTEGER         NOT NULL,
	ITEMID			NUMERIC(3)      NOT NULL,
	QUANTITY		NUMERIC(3)      NOT NULL,
	VENDORPARTNUMBER	BIGINT          NOT NULL,
	CONSTRAINT FK_4 FOREIGN KEY (ORDERID) REFERENCES EJB_ORDER_ORDER (ORDERID),
	CONSTRAINT FK_5 FOREIGN KEY (VENDORPARTNUMBER) REFERENCES EJB_ORDER_VENDOR_PART (VENDORPARTNUMBER),
	PRIMARY KEY (ORDERID, ITEMID)
);

// Create tables for online banking application (Duke's Bank)

CREATE TABLE BANK_SEQUENCE_GENERATOR (
        GEN_KEY         VARCHAR(255),
        GEN_VALUE       NUMERIC(20)
);

CREATE TABLE BANK_ACCOUNT (
    ACCOUNT_ID NUMERIC(20) CONSTRAINT PK_ACCOUNT PRIMARY KEY,
    TYPE VARCHAR(24),
    DESCRIPTION VARCHAR(30),
    BALANCE NUMERIC(10,2),
    CREDIT_LINE NUMERIC(10,2),
    BEGIN_BALANCE NUMERIC(10,2),
    BEGIN_BALANCE_TIME_STAMP TIMESTAMP);

CREATE TABLE BANK_CUSTOMER (
    CUSTOMER_ID NUMERIC(20) CONSTRAINT PK_CUSTOMER PRIMARY KEY,
    LAST_NAME VARCHAR(30),
    FIRST_NAME VARCHAR(30),
    MIDDLE_INITIAL VARCHAR(1),
    STREET VARCHAR(40),
    CITY VARCHAR(40),
    STATE VARCHAR(2),
    ZIP VARCHAR(5),
    PHONE VARCHAR(16),
    EMAIL VARCHAR(30));

CREATE TABLE BANK_TX (
    TX_ID NUMERIC(20) CONSTRAINT PK_TX PRIMARY KEY,
    ACCOUNT_ID NUMERIC(20),
    TIME_STAMP TIMESTAMP,
    AMOUNT NUMERIC(10,2),
    BALANCE NUMERIC(10,2),
    DESCRIPTION VARCHAR(30),
    CONSTRAINT FK_TX_ACCOUNT_ID FOREIGN KEY (ACCOUNT_ID) REFERENCES BANK_ACCOUNT(ACCOUNT_ID));

CREATE TABLE BANK_CUSTOMER_ACCOUNT_XREF (
    CUSTOMER_ID NUMERIC(20), 
    ACCOUNT_ID NUMERIC(20),
    CONSTRAINT FK_BANK_CUSTOMER_ID FOREIGN KEY (CUSTOMER_ID) REFERENCES BANK_CUSTOMER(CUSTOMER_ID),
    CONSTRAINT FK_BANK_ACCOUNT_ID FOREIGN KEY (ACCOUNT_ID) REFERENCES BANK_ACCOUNT(ACCOUNT_ID));

// FOR PERSISTENCE ORDER EXAMPLE
INSERT INTO EJB_ORDER_SEQUENCE_GENERATOR
    VALUES ('VENDOR_PART_ID',1);

// Duke's bank

INSERT INTO BANK_SEQUENCE_GENERATOR
    VALUES ('CUSTOMER_ID',203);

INSERT INTO BANK_SEQUENCE_GENERATOR
    VALUES ('ACCOUNT_ID',5050);

INSERT INTO BANK_SEQUENCE_GENERATOR
    VALUES ('TX_ID',100);

INSERT INTO BANK_ACCOUNT VALUES
(5005, 'Money Market', 'Hi Balance', 4000.00, 0.00, 3500.00, '2005-07-28 23:03:20');
 
INSERT INTO BANK_ACCOUNT VALUES
(5006, 'Checking', 'Checking', 85.00, 0.00, 66.54, '2005-07-21 03:12:00');

INSERT INTO BANK_ACCOUNT VALUES
(5007, 'Credit', 'Visa', 599.18, 5000.00, 166.08, '2005-07-23 10:13:54');

INSERT INTO BANK_ACCOUNT VALUES
(5008, 'Savings', 'Super Interest Account', 55601.35, 0.00, 5433.89, '2005-07-15 12:55:33');

INSERT INTO BANK_CUSTOMER VALUES
(200, 'Jones', 'Richard', 'K',
 '88 Poplar Ave.', 'Cupertino', 'CA', '95014',
 '408-123-4567', 'rhill@j2ee.com');

INSERT INTO BANK_CUSTOMER VALUES
(201, 'Jones', 'Mary', 'R',
 '88 Poplar Ave.', 'Cupertino', 'CA', '95014',
 '408-123-4567', 'mhill@j2ee.com');

INSERT INTO BANK_CUSTOMER_ACCOUNT_XREF VALUES
(200, 5005);

INSERT INTO BANK_CUSTOMER_ACCOUNT_XREF VALUES
(201, 5005);

INSERT INTO BANK_CUSTOMER_ACCOUNT_XREF VALUES
(200, 5006);

INSERT INTO BANK_CUSTOMER_ACCOUNT_XREF VALUES
(200, 5007);

INSERT INTO BANK_CUSTOMER_ACCOUNT_XREF VALUES
(201, 5006);

INSERT INTO BANK_CUSTOMER_ACCOUNT_XREF VALUES
(201, 5007);

INSERT INTO BANK_CUSTOMER_ACCOUNT_XREF VALUES
(200, 5008);

INSERT INTO BANK_CUSTOMER_ACCOUNT_XREF VALUES
(201, 5008);

INSERT INTO BANK_TX VALUES
(1, 5005, '2005-9-01 12:55:33', 200.00, 4200.00, 'Refund');
UPDATE BANK_ACCOUNT SET BALANCE = 4200.00 WHERE ACCOUNT_ID = 5005;
INSERT INTO BANK_TX VALUES
(3, 5008, '2005-9-03 12:56:33', -1000.00, 54601.35, 'Transfer Out');
UPDATE BANK_ACCOUNT SET BALANCE = 54604.35 WHERE ACCOUNT_ID = 5008; 
INSERT INTO BANK_TX VALUES
(4, 5006, '2005-9-03 12:57:33', 1000.00, 1085.00, 'Transfer In');
UPDATE BANK_ACCOUNT SET BALANCE = 1085.00 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(5, 5007, '2005-9-05 12:58:33', 33.00, 199.08, 'Clothing');
UPDATE BANK_ACCOUNT SET BALANCE = 199.08 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(6, 5006, '2005-9-06 12:59:33', 2000.00, 3085.00, 'Paycheck Deposit');
UPDATE BANK_ACCOUNT SET BALANCE = 3085.00 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(7, 5005, '2005-9-07 13:00:33', -200.00, 4000.00, 'ATM Withdrawal');
UPDATE BANK_ACCOUNT SET BALANCE = 3085.00 WHERE ACCOUNT_ID = 5005; 
INSERT INTO BANK_TX VALUES
(8, 5006, '2005-9-08 13:01:33', -200.00, 2885.00, 'Car Insurance');
UPDATE BANK_ACCOUNT SET BALANCE = 2885.00 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(9, 5007, '2005-9-09 13:02:33', 186.00, 385.08, 'Car Repair');
UPDATE BANK_ACCOUNT SET BALANCE = 385.08 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(10, 5008, '2005-9-10 12:55:33', 1000.00, 55601.35, 'Deposit');
UPDATE BANK_ACCOUNT SET BALANCE = 55601.35 WHERE ACCOUNT_ID = 5008; 
INSERT INTO BANK_TX VALUES
(11, 5007, '2005-9-11 12:55:33', 585.00, 970.08, 'Airplane Tickets');
UPDATE BANK_ACCOUNT SET BALANCE = 970.08 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(12, 5006, '2005-9-12 12:55:33', -675.00, 2210.00, 'Mortgage Payment');
UPDATE BANK_ACCOUNT SET BALANCE = 2210.00 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(13, 5005, '2005-9-13 12:55:33', -100.00, 3900.00, 'ATM Withdrawal');
UPDATE BANK_ACCOUNT SET BALANCE = 3900.00 WHERE ACCOUNT_ID = 5005; 
INSERT INTO BANK_TX VALUES
(14, 5006, '2005-9-14 12:55:33', -385.08, 1824.92, 'Visa Payment');
UPDATE BANK_ACCOUNT SET BALANCE = 1824.92 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(15, 5007, '2005-9-15 12:55:33', -385.08, 585.00, 'Payment');
UPDATE BANK_ACCOUNT SET BALANCE = 585.00 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(17, 5007, '2005-9-17 12:55:33', 26.95, 611.95, 'Movies');
UPDATE BANK_ACCOUNT SET BALANCE = 611.95 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(18, 5006, '2005-9-18 12:55:33', -31.00, 1793.92, 'Groceries');
UPDATE BANK_ACCOUNT SET BALANCE = 1793.92 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(19, 5005, '2005-9-19 12:55:33', -150.00, 3750.00, 'ATM Withdrawal');
UPDATE BANK_ACCOUNT SET BALANCE = 3750.00 WHERE ACCOUNT_ID = 5005; 
INSERT INTO BANK_TX VALUES
(20, 5006, '2005-9-20 12:55:33', 2000.00, 3173.92, 'Paycheck Deposit');
UPDATE BANK_ACCOUNT SET BALANCE = 3173.92 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(21, 5007, '2005-9-21 12:55:33', 124.00, 735.95, 'Furnishings');
UPDATE BANK_ACCOUNT SET BALANCE = 735.95 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(23, 5007, '2005-9-23 12:55:33', 33.12, 769.07, 'Hardware');
UPDATE BANK_ACCOUNT SET BALANCE = 769.07 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(24, 5006, '2005-9-24 12:55:33', -175.33, 2998.59, 'Utility Bill');
UPDATE BANK_ACCOUNT SET BALANCE = 2998.59 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(25, 5006, '2005-9-25 12:55:33', -123.00, 2875.59, 'Groceries');
UPDATE BANK_ACCOUNT SET BALANCE = 2875.59 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(26, 5006, '2005-9-26 12:55:33', -675.00, 2200.59, 'Mortgage Payment');
UPDATE BANK_ACCOUNT SET BALANCE = 2200.59 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(27, 5007, '2005-9-27 12:55:33', 24.72, 793.79, 'Cafe');
UPDATE BANK_ACCOUNT SET BALANCE = 793.79 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(28, 5008, '2005-9-28 12:55:33', 1000.00, 56601.35, 'Deposit');
UPDATE BANK_ACCOUNT SET BALANCE = 56601.35 WHERE ACCOUNT_ID = 5008; 
INSERT INTO BANK_TX VALUES
(29, 5007, '2005-9-29 12:55:33', 35.00, 828.79, 'Hair Salon');
UPDATE BANK_ACCOUNT SET BALANCE = 828.79 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(30, 5006, '2005-9-30 12:55:33', -20.00, 2180.59, 'Gasoline');
UPDATE BANK_ACCOUNT SET BALANCE = 2180.59 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(31, 5005, '2005-10-01 12:55:33', -100.00, 3650.00, 'ATM Withdrawal');
UPDATE BANK_ACCOUNT SET BALANCE = 3650.00 WHERE ACCOUNT_ID = 5005; 
INSERT INTO BANK_TX VALUES
(32, 5006, '2005-10-02 12:55:33', -56.87, 2123.72, 'Phone Bill');
UPDATE BANK_ACCOUNT SET BALANCE = 2123.72 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(33, 5007, '2005-10-03 12:55:33', 67.99, 896.78, 'Acme Shoes');
UPDATE BANK_ACCOUNT SET BALANCE = 896.78 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(35, 5007, '2005-10-05 12:55:33', 24.00, 920.78, 'Movies');
UPDATE BANK_ACCOUNT SET BALANCE = 920.78 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(36, 5006, '2005-10-06 12:55:33', 2000.00, 4123.72, 'Paycheck Deposit');
UPDATE BANK_ACCOUNT SET BALANCE = 4123.72 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(38, 5006, '2005-10-08 12:55:33', -100.00, 4023.72, 'Groceries');
UPDATE BANK_ACCOUNT SET BALANCE = 4023.72 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(39, 5007, '2005-10-09 12:55:33', 26.95, 947.73, 'Pizza');
UPDATE BANK_ACCOUNT SET BALANCE = 947.73 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(41, 5007, '2005-10-11 12:55:33', 125.00, 1072.73, 'Dentist');
UPDATE BANK_ACCOUNT SET BALANCE = 1072.73 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(42, 5006, '2005-10-12 12:55:33', -675.00, 3348.72, 'Mortgage Payment');
UPDATE BANK_ACCOUNT SET BALANCE = 3348.72 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(43, 5005, '2005-10-13 12:55:33', -150.00, 3500.00, 'ATM Withdrawal');
UPDATE BANK_ACCOUNT SET BALANCE = 3500.00 WHERE ACCOUNT_ID = 5005; 
INSERT INTO BANK_TX VALUES
(44, 5006, '2005-10-14 12:55:33', -947.73, 2400.99, 'Visa Payment');
UPDATE BANK_ACCOUNT SET BALANCE = 2400.99 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(45, 5007, '2005-10-15 12:55:33', -947.73, 125.00, 'Payment');
UPDATE BANK_ACCOUNT SET BALANCE = 125.00 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(47, 5007, '2005-10-17 12:55:33', 49.90, 100.85, 'Bookstore');
UPDATE BANK_ACCOUNT SET BALANCE = 100.85 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(48, 5006, '2005-10-18 12:55:33', -100.00, 2300.99, 'Groceries');
UPDATE BANK_ACCOUNT SET BALANCE = 2300.99 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(50, 5006, '2005-10-20 12:55:33', 2000.00, 4300.99, 'Paycheck Deposit');
UPDATE BANK_ACCOUNT SET BALANCE = 4300.99 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(51, 5007, '2005-10-21 12:55:33', 80.32, 181.17, 'Restaurant');
UPDATE BANK_ACCOUNT SET BALANCE = 181.17 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(53, 5007, '2005-10-23 12:55:33', 11.78, 192.95, 'Electronics');
UPDATE BANK_ACCOUNT SET BALANCE = 192.95 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(54, 5006, '2005-10-24 12:55:33', -150.45, 4150.54, 'Utility Bill');
UPDATE BANK_ACCOUNT SET BALANCE = 4150.54 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(55, 5005, '2005-10-25 12:55:33', -100.00, 3400.00, 'ATM Withdrawal');
UPDATE BANK_ACCOUNT SET BALANCE = 3400.00 WHERE ACCOUNT_ID = 5005; 
INSERT INTO BANK_TX VALUES
(56, 5006, '2005-10-26 12:55:33', -675.00, 3475.54, 'Mortgage Payment');
UPDATE BANK_ACCOUNT SET BALANCE = 3475.54 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(57, 5007, '2005-10-27 12:55:33', 24.00, 216.95, 'Ice Skating');
UPDATE BANK_ACCOUNT SET BALANCE = 216.95 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(58, 5006, '2005-10-28 12:55:33', -1000.00, 2475.54, 'Transfer Out');
UPDATE BANK_ACCOUNT SET BALANCE = 2475.54 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(59, 5008, '2005-10-28 12:55:33', 1000.00, 57601.35, 'Transfer In');
UPDATE BANK_ACCOUNT SET BALANCE = 57601.35 WHERE ACCOUNT_ID = 5008; 
INSERT INTO BANK_TX VALUES
(60, 5006, '2005-11-02 12:55:33', -99.22, 3376.32, 'Phone Bill');
UPDATE BANK_ACCOUNT SET BALANCE = 3376.32 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(61, 5007, '2005-11-03 12:55:33', 29.97, 246.92, 'Toy Store');
UPDATE BANK_ACCOUNT SET BALANCE = 246.92 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(62, 5006, '2005-11-04 12:55:33', -2000.00, 376.32, 'Transfer Out');
UPDATE BANK_ACCOUNT SET BALANCE = 376.32 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(63, 5008, '2005-11-05 12:55:33', 2000.00, 59601.35, 'Transfer In');
UPDATE BANK_ACCOUNT SET BALANCE = 59601.35 WHERE ACCOUNT_ID = 5008; 
INSERT INTO BANK_TX VALUES
(64, 5006, '2005-11-06 12:55:33', 2000.00, 2376.32, 'Paycheck Deposit');
UPDATE BANK_ACCOUNT SET BALANCE = 59601.35 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(65, 5007, '2005-11-07 12:55:33', 14.69, 261.61, 'Cafe');
UPDATE BANK_ACCOUNT SET BALANCE = 261.61 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(66, 5006, '2005-11-08 12:55:33', -108.99, 2267.33, 'Groceries');
UPDATE BANK_ACCOUNT SET BALANCE = 2267.33 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(67, 5006, '2005-11-09 12:55:33', -30.12, 2237.21, 'Gasoline');
UPDATE BANK_ACCOUNT SET BALANCE = 2237.21 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(69, 5007, '2005-11-11 12:55:33', 125.00, 386.61, 'Dentist');
UPDATE BANK_ACCOUNT SET BALANCE = 2237.21 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(70, 5006, '2005-11-12 12:55:33', -675.00, 1562.21, 'Mortgage Payment');
UPDATE BANK_ACCOUNT SET BALANCE = 1562.21 WHERE ACCOUNT_ID = 5006;  
INSERT INTO BANK_TX VALUES
(72, 5006, '2005-11-13 12:55:33', -261.61, 1300.60, 'Visa Payment');
UPDATE BANK_ACCOUNT SET BALANCE = 1300.60 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(73, 5007, '2005-11-14 12:55:33', -261.61, 125.00, 'Payment');
UPDATE BANK_ACCOUNT SET BALANCE = 125.00 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(75, 5007, '2005-11-15 12:55:33', 24.00, 149.00, 'Drug Store');
UPDATE BANK_ACCOUNT SET BALANCE = 149.00 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(76, 5006, '2006-03-16 12:55:33', -67.98, 1232.62, 'Groceries');
UPDATE BANK_ACCOUNT SET BALANCE = 1232.62 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(78, 5006, '2006-03-17 12:55:33', 2000.00, 3232.62, 'Paycheck Deposit');
UPDATE BANK_ACCOUNT SET BALANCE = 3232.62 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(79, 5007, '2006-03-18 12:55:33', 32.95, 181.95, 'CDs');
UPDATE BANK_ACCOUNT SET BALANCE = 181.95 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(81, 5007, '2006-04-01 12:55:33', 14.10, 196.05, 'Sports Store');
UPDATE BANK_ACCOUNT SET BALANCE = 196.05 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(82, 5006, '2006-04-01 12:55:33', -99.30, 3133.32, 'Utility Bill');
UPDATE BANK_ACCOUNT SET BALANCE = 3133.32 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(84, 5006, '2006-04-04 12:55:33', -675.00, 2458.32, 'Mortgage Payment');
UPDATE BANK_ACCOUNT SET BALANCE = 2458.32 WHERE ACCOUNT_ID = 5006; 
INSERT INTO BANK_TX VALUES
(85, 5007, '2006-04-10 12:55:33', 23.98, 220.03, 'Garden Supply');
UPDATE BANK_ACCOUNT SET BALANCE = 220.03 WHERE ACCOUNT_ID = 5007; 
INSERT INTO BANK_TX VALUES
(86, 5005, '2006-04-19 12:55:33', -100.00, 3300.00, 'ATM Withdrawal');
UPDATE BANK_ACCOUNT SET BALANCE = 3300.00 WHERE ACCOUNT_ID = 5005; 
