【SQL开源代码栏目提醒】:文章导读:在新的一年中,各位网友都进入紧张的学习或是工作阶段。网学会员整理了SQL开源代码-create-table.sql的相关内容供大家参考,祝大家在新的一年里工作和学习顺利!
// Create tables for Duke's Bank, the tutorial's online banking application.
// Also seed the NEXT_ID table with initial values.
CREATE TABLE ACCOUNT (
account_id VARCHAR(8) 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 CUSTOMER (
customer_id VARCHAR(8) 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 TX (
tx_id VARCHAR(8) CONSTRAINT pk_tx PRIMARY KEY,
account_id VARCHAR(8),
time_stamp TIMESTAMP,
amount NUMERIC(10,2),
balance NUMERIC(10,2),
description VARCHAR(30),
CONSTRAINT fk_tx_account_id FOREIGN KEY (account_id) REFERENCES account(account_id));
CREATE TABLE CUSTOMER_ACCOUNT_XREF (
customer_id VARCHAR(8),
account_id VARCHAR(8),
CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
CONSTRAINT fk_account_id FOREIGN KEY (account_id) REFERENCES account(account_id));
CREATE TABLE NEXT_ID (
beanName VARCHAR(30) CONSTRAINT pk_next_id PRIMARY KEY,
id NUMERIC);
INSERT INTO NEXT_ID
VALUES ('customer', 202);
INSERT INTO NEXT_ID
VALUES ('account', 5050);
INSERT INTO NEXT_ID
VALUES ('tx', 100);
INSERT INTO ACCOUNT VALUES
('5005', 'Money Market', 'Hi Balance', 4000.00, 0.00, 3500.00, '2003-07-28 23:03:20');
INSERT INTO ACCOUNT VALUES
('5006', 'Checking', 'Checking', 85.00, 0.00, 66.54, '2003-07-21 03:12:00');
INSERT INTO ACCOUNT VALUES
('5007', 'Credit', 'Visa', 599.18, 5000.00, 166.08, '2003-07-23 10:13:54');
INSERT INTO ACCOUNT VALUES
('5008', 'Savings', 'Super Interest Account', 55601.35, 0.00, 5433.89, '2003-07-15 12:55:33');
INSERT INTO CUSTOMER VALUES
('200', 'Jones', 'Richard', 'K',
'88 Poplar Ave.', 'Cupertino', 'CA', '95014',
'408-123-4567', 'rhill@j2ee.com');
INSERT INTO CUSTOMER VALUES
('201', 'Jones', 'Mary', 'R',
'88 Poplar Ave.', 'Cupertino', 'CA', '95014',
'408-123-4567', 'mhill@
j2ee.com');
INSERT INTO CUSTOMER_ACCOUNT_XREF VALUES
('200', '5005');
INSERT INTO CUSTOMER_ACCOUNT_XREF VALUES
('201', '5005');
INSERT INTO CUSTOMER_ACCOUNT_XREF VALUES
('200', '5006');
INSERT INTO CUSTOMER_ACCOUNT_XREF VALUES
('200', '5007');
INSERT INTO CUSTOMER_ACCOUNT_XREF VALUES
('201', '5006');
INSERT INTO CUSTOMER_ACCOUNT_XREF VALUES
('201', '5007');
INSERT INTO CUSTOMER_ACCOUNT_XREF VALUES
('200', '5008');
INSERT INTO CUSTOMER_ACCOUNT_XREF VALUES
('201', '5008');
INSERT INTO TX VALUES
('1', '5005', '2003-9-01 12:55:33', 200.00, 4200.00, 'Refund');
UPDATE ACCOUNT SET balance = 4200.00 WHERE account_id = '5005';
INSERT INTO TX VALUES
('3', '5008', '2003-9-03 12:56:33', -1000.00, 54601.35, 'Transfer Out');
UPDATE ACCOUNT SET balance = 54604.35 WHERE account_id = '5008';
INSERT INTO TX VALUES
('4', '5006', '2003-9-03 12:57:33', 1000.00, 1085.00, 'Transfer In');
UPDATE ACCOUNT SET balance = 1085.00 WHERE account_id = '5006';
INSERT INTO TX VALUES
('5', '5007', '2003-9-05 12:58:33', 33.00, 199.08, 'Clothing');
UPDATE ACCOUNT SET balance = 199.08 WHERE account_id = '5007';
INSERT INTO TX VALUES
('6', '5006', '2003-9-06 12:59:33', 2000.00, 3085.00, 'Paycheck Deposit');
UPDATE ACCOUNT SET balance = 3085.00 WHERE account_id = '5006';
INSERT INTO TX VALUES
('7', '5005', '2003-9-07 13:00:33', -200.00, 4000.00, 'ATM Withdrawal');
UPDATE ACCOUNT SET balance = 3085.00 WHERE account_id = '5005';
INSERT INTO TX VALUES
('8', '5006', '2003-9-08 13:01:33', -200.00, 2885.00, 'Car Insurance');
UPDATE ACCOUNT SET balance = 2885.00 WHERE account_id = '5006';
INSERT INTO TX VALUES
('9', '5007', '2003-9-09 13:02:33', 186.00, 385.08, 'Car Repair');
UPDATE ACCOUNT SET balance = 385.08 WHERE account_id = '5007';
INSERT INTO TX VALUES
('10', '5008', '2003-9-10 12:55:33', 1000.00, 55601.35, 'Deposit');
UPDATE ACCOUNT SET balance = 55601.35 WHERE account_id = '5008';
INSERT INTO TX VALUES
('11', '5007', '2003-9-11 12:55:33', 585.00, 970.08, 'Airplane Tickets');
UPDATE ACCOUNT SET balance = 970.08 WHERE account_id = '5007';
INSERT INTO TX VALUES
('12', '5006', '2003-9-12 12:55:33', -675.00, 2210.00, 'Mortgag