ejbbank.sql

噶额外噶外骨骼感广泛高热感 就 啊啊

echo --- load the EJBBANK database ---

echo --- connect to EJBBANK database ---
CONNECT TO EJBBANK

DELETE FROM ITSO.TRANSRECORD
DELETE FROM ITSO.SAVINGS
DELETE FROM ITSO.CHECKING
DELETE FROM ITSO.CUSTACCT
DELETE FROM ITSO.ACCOUNT
DELETE FROM ITSO.CUSTADDRESS
DELETE FROM ITSO.CUSTOMER
DELETE FROM ITSO.CUSTOMERINFO

echo --- insert into CUSTOMER tables ---
INSERT INTO ITSO.CUSTOMER                                             \
   (customerid, title, firstname, lastname, userid, password) VALUES  \
   (101, 'Mr', 'Maik',    'Schumacher', 'cust101', 'MS'),             \
   (102, 'Mr', 'Fabio',   'Ferraz',     'cust102', 'FF'),             \
   (103, 'Mr', 'Henrik',  'Sjostrand',  'cust103', 'HS'),             \
   (104, 'Mr', 'Ian',     'Brown',      'cust104', 'IB'),             \
   (105, 'Ms', 'Unknown', 'Lady',        null,      null),            \
   (106, 'Mr', 'Ueli',    'Wahli',      'cust106', 'UW') 

INSERT INTO ITSO.CUSTADDRESS                           \
   (customerid, street, city, state, zipcode) VALUES   \
   (101, 'A St', 'A village',  'Germany',    '11111'), \
   (102, 'B Bl', 'B big City', 'Brazil',     '22222'), \
   (103, 'C Rd', 'C City',     'Sweden',     '33333'), \
   (104, 'D Ln', 'D Metro',    'Canada',     '44444'), \
   (105, 'E Ct', 'E Farm',     'Hawaii',     '55555'), \
   (106, 'F Av', 'F Town',     'California', '66666')

echo --- insert into ACCOUNT tables ---
INSERT INTO ITSO.ACCOUNT                                     \
   (accid, balance, interest, acctype, discriminator) VALUES \
   ('101-1001',   80.00, 4, 'CHECKING', 'C'),                \
   ('101-1002',  375.26, 5, 'SAVINGS',  'S'),                \
   ('102-2001', 9375.26, 5, 'SAVINGS',  'S'),                \
   ('102-2002',   75.50, 3, 'CHECKING', 'C'),                \
   ('103-3001',  100.00, 6, 'SAVINGS',  'S'),                \
   ('104-4001',  888.88, 4, 'SAVINGS',  'S'),                \
   ('104-4002',   88.88, 4, 'CHECKING', 'C'),                \
   ('105-5001',    0.00, 2, 'CHECKING', 'C'),                \
   ('106-6001', 1000.00, 3, 'CHECKING', 'C'),                \
   ('106-6002', 2000.00, 4, 'SAVINGS',  'S'),                \
   ('106-6003', 3000.00, 6, 'SAVINGS',  'S')

INSERT INTO ITSO.CHECKING    \
   (accid, overdraft) VALUES \
   ('101-1001',200.00),      \
   ('102-2002',200.00),      \
   ('104-4002',250.00),      \
   ('105-5001',200.00),      \
   ('106-6001',300.00)

INSERT INTO ITSO.SAVINGS     \
   (accid, minamount) VALUES \
   ('101-1002',100.00),      \
   ('102-2001',100.00),      \
   ('103-3001',150.00),      \
   ('104-4001',200.00),      \
   ('106-6002',100.00),      \
   ('106-6003',250.00)

echo --- insert into CUSTACCT table ---
INSERT INTO ITSO.CUSTACCT                               \
   (customerid, accid) VALUES                           \
   (101,'101-1001'), (101,'101-1002'),                  \
   (102,'102-2001'), (102,'102-2002'),                  \
   (103,'103-3001'),                                    \
   (104,'104-4001'), (104,'104-4002'),                  \
   (105,'105-5001'),                                    \
   (106,'106-6001'), (106,'106-6002'), (106,'106-6003'), (106,'105-5001')

echo --- insert into TRANSRECORD table ---
INSERT INTO ITSO.TRANSRECORD                                    \
   (transid,           accid,      transtype, transamt) VALUES  \
   (CURRENT TIMESTAMP, '101-1001', 'C',         80.00 )
INSERT INTO ITSO.TRANSRECORD                            VALUES  \
   (CURRENT TIMESTAMP, '101-1002', 'D',        200.00 )
INSERT INTO ITSO.TRANSRECORD                            VALUES  \
   (CURRENT TIMESTAMP, '102-2001', 'C',       1000.00 )
INSERT INTO ITSO.TRANSRECORD                            VALUES  \
   (CURRENT TIMESTAMP, '102-2002', 'D',         70.00 )
INSERT INTO ITSO.TRANSRECORD                            VALUES  \
   (CURRENT TIMESTAMP, '103-3001', 'C',        100.00 )
INSERT INTO ITSO.TRANSRECORD                            VALUES  \
   (CURRENT TIMESTAMP, '104-4001', 'C',         88.00 )
INSERT INTO ITSO.TRANSRECORD                            VALUES  \
   (CURRENT TIMESTAMP, '104-4002', 'C',         88.88 )
INSERT INTO ITSO.TRANSRECORD                            VALUES  \
   (CURRENT TIMESTAMP, '106-6001', 'D',         66.66 )
INSERT INTO ITSO.TRANSRECORD                            VALUES  \
   (CURRENT TIMESTAMP, '106-6001', 'C',         10.00 )
INSERT INTO ITSO.TRANSRECORD                            VALUES  \
   (CURRENT TIMESTAMP, '106-6002', 'C',         66.66 )
INSERT INTO ITSO.TRANSRECORD                            VALUES  \
   (CURRENT TIMESTAMP, '106-6003', 'C',       3000.00 )

echo --- insert into CUSTOMERINFO table ---
INSERT INTO ITSO.CUSTOMERINFO                     \
   (customerid, infoid, description,data) VALUES  \
   (101, 1, 'Picture', blob('xxxxxxxxxx')),       \
   (101, 2, 'Scanned image', blob('yyyyyy'))    

echo --- connect reset ---
CONNECT RESET