CREATE TABLE Branch ( 
	branch_num		NUMBER NOT NULL, 
	/*bank_name		VARCHAR(10) NOT NULL,*/ 
      /*mgm_id 		NUMBER NOT NULL,*/ 
	routing_num		NUMBER NOT NULL, 
	phone_num		NUMBER NOT NULL, 
	fax_num		NUMBER NOT NULL, 
	address		VARCHAR(30), 
	PRIMARY KEY 	(branch_num) 
      /*FOREIGN KEY 	(bank_name)  
      REFERENCES 		Bank(name)*/ 
      /*FOREIGN KEY 	(mgm_id)  
      REFERENCES 		Employee(employee_id)*/ 
)

CREATE TABLE Employee ( 
	employee_id		NUMBER NOT NULL, 
	branch_num		NUMBER NOT NULL, 
	frist_name		VARCHAR(10), 
	last_name		VARCHAR(10), 
	address		VARCHAR(45), 
	title			VARCHAR(30), 
	start_date		DATE, 
	email			VARCHAR(20), 
	phone_num		NUMBER, 
	dob			DATE, 
	PRIMARY KEY 	(employee_id), 
	FOREIGN KEY 	(branch_num)  
      REFERENCES 		Branch(branch_num) 
)

CREATE TABLE Account ( 
	account_num		NUMBER NOT NULL, 
	branch_num		NUMBER NOT NULL, 
	balance		NUMBER NOT NULL, 
	type			VARCHAR(8) NOT NULL, 
	card_num		NUMBER NOT NULL, 
	PRIMARY KEY 	(Account_num), 
	FOREIGN KEY 	(branch_num)  
      REFERENCES 		Branch(branch_num) 
)

CREATE TABLE Client ( 
	ssn			NUMBER NOT NULL, 
	/*empy_id		NUMBER NOT NULL,*/ 
	first_name 		VARCHAR(10) NOT NULL, 
	last_name   	VARCHAR(10) NOT NULL, 
	address		VARCHAR(40) NOT NULL, 
	phone_num		NUMBER NOT NULL, 
	email			VARCHAR(25) NOT NULL, 
	dob			DATE, 
	PRIMARY KEY 	(ssn) 
	/*FOREIGN KEY 	(empy_id)  
      REFERENCES 		Employee(Employee_id)*/ 
)

CREATE TABLE Loan ( 
  	loan_num		NUMBER NOT NULL, 
	branch_num		NUMBER NOT NULL, 
	balance		NUMBER NOT NULL, 
	interest_rate 	Number NOT NULL, 
	amount_borrowed	NUMBER NOT NULL, 
	PRIMARY KEY 	(loan_num), 
	FOREIGN KEY 	(branch_num)  
      REFERENCES 		Branch(branch_num) 
)

CREATE TABLE Account_owner ( 
	ssn 	    	    	NUMBER NOT NULL, 
	account_num 	NUMBER NOT NULL, 
	PRIMARY KEY 	(ssn, account_num), 
	FOREIGN KEY 	(ssn)  
      REFERENCES 		Client(ssn), 
  	FOREIGN KEY 	(account_num)  
      REFERENCES 		Account(account_num) 
)

CREATE TABLE Loan_owner ( 
  	ssn 	    	NUMBER NOT NULL, 
 	loan_num 		NUMBER NOT NULL, 
 	PRIMARY KEY 	(ssn, loan_num), 
  	FOREIGN KEY 	(ssn)  
    REFERENCES 		Client(ssn), 
 	FOREIGN KEY 	(loan_num)  
    REFERENCES 		Loan(loan_num) 
)

INSERT INTO Branch VALUES(1, 12345, 5550555, 5550550, '1 Bank Drive, Norfolk, WV')

INSERT INTO Branch VALUES(2, 23456, 5551555, 5551551, '2 Bank Lane, Charlotte, TN')

INSERT INTO Branch VALUES(3, 34567, 5552555, 5552552, '3 Bank Street, Denver, CA')

INSERT INTO Branch VALUES(4, 45678, 5553555, 5553553, '4 Bank Avenue, Detroit, NC')

INSERT INTO Branch VALUES(5, 56789, 5554555, 5554554, '5 Bank Circle, San Diego, TX')

INSERT INTO Branch VALUES(6, 67890, 5555555, 5555550, '6 Bank Way, San Antonio, OH')

INSERT INTO Branch VALUES(7, 78901, 5556555, 5556556, '7 Bank Road, Nashville, AR')

INSERT INTO Branch VALUES(8, 89012, 5557555, 5557557, '8 Bank Corner, Sitka, KY')

INSERT INTO Branch VALUES(9, 90123, 5558555, 5558558, '9 Money Drive, Columbia, ME')

INSERT INTO Branch VALUES(10, 01234, 5551055, 5555510, '10 Money Lane, Bakersfield, LA')

INSERT INTO Employee VALUES(1, 1, 'John', 'Smith', '1 JS Lane, Norfolk, WV', 'Teller',DATE'2016-01-01', 'js@example.com', 5550001, DATE'2000-01-01')

INSERT INTO Employee VALUES(2, 2, 'Joan', 'Smith', ' 2 J Street, Charlotte, TN', 'Manager', DATE'2012-02-02', 'joans@example.com', 5550002, DATE'1985-02-02')

INSERT INTO Employee VALUES(3, 3, 'Kim', 'Evans', '3 KE Drive, Denver, CA', 'Loan Processor', DATE'2014-03-03','km@example.com', 5550003, DATE'1986-03-03')

INSERT INTO Employee VALUES(4, 4, 'David', 'Hung', '4 DH Avenue, Detroit, NC', 'Mortgage Consultant', DATE'2013-04-04', 'dh@example.com', 5550004, DATE'1987-04-04')

INSERT INTO Employee VALUES(5, 5, 'Sylvio','Grand', '5 SG Circle, San Diego, TX', 'Banker', DATE'2015-05-05', 'sg@example.com', 5550005, DATE'1988-05-05')

INSERT INTO Employee VALUES(6, 6, 'Sylvia', 'Herr', '6 SH Way, San Antonio, OH', 'Credit Analyst', DATE'2008-06-06', 'sh@example.com', 5550006, DATE'1978-06-06')

INSERT INTO Employee VALUES(7, 7, 'Nancy', 'Blake', '7 NB Road, Nashville, AR', 'Investment Representative', DATE'2011-07-07', 'nb@example.com', 5550007, DATE'1972-07-07')

INSERT INTO Employee VALUES(8, 8, 'Liz', 'Drew', '8 LD Corner, Sitka, KY', 'Customer Relations', DATE'2014-08-08', 'ld@example.com', 5550008, DATE'1987-08-08')

INSERT INTO Employee VALUES(9, 9, 'Andy', 'Bobandy', '9 AB Drive, Columbia, ME', 'Money Man', DATE'2021-09-09', 'ab@example.com', 5550009, DATE'1991-09-09')

INSERT INTO Employee VALUES(10, 10, 'Randy', 'Marsh', '10 Tegridy Lane, Bakersfield, LA', 'Assets Manager', DATE'2004-10-10', 'rm@example.com', 5550010, DATE'1968-10-10')

INSERT INTO Account VALUES(0124578258963, 1, 5000.41, 'Checking', 4578523658962321)

INSERT INTO Account VALUES(4782158963256, 2, 9856.36, 'Savings', 4578523658962321)

INSERT INTO Account VALUES(9875214589632, 3, 2584542.85, 'Checking', 4578523658962321)

INSERT INTO Account VALUES(9874254215645, 4, 323.78, 'Savings', 4578523658962321)

INSERT INTO Account VALUES(3215678524545, 5, 98756.45, 'Checking', 4578523658962321)

INSERT INTO Account VALUES(9825623256556, 6, 100020232.36, 'Checking', 4578523658962321)

INSERT INTO Account VALUES(2514365878525, 7, 1000000000000.15, 'Savings', 4578523658962321)

INSERT INTO Account VALUES(9636258552452, 8, 365875.78, 'Checking', 4578523658962321)

INSERT INTO Account VALUES(2141254215521, 9, 12458962.15, 'Savings', 4578523658962321)

INSERT INTO Account VALUES(3256525652325, 10, 1.00, 'Savings', 4578523658962321)

INSERT INTO Client VALUES(123456789, 'Bret', 'Smith', '58 somewhere ct, pineplace, SC 27893', 9106874589, 'Bret@gmail.com', TO_DATE('2003/06/09', 'yyyy/mm/dd'))

INSERT INTO Client VALUES(487858952, 'Tim', 'Trails', '29 place dr, pineplace, NC 58991', 9108795248, 'Timthetoolman@gmail.com', TO_DATE('1989/07/06', 'yyyy/mm/dd'))

INSERT INTO Client VALUES(458726589, 'Bob', 'Popes', '32 outthere ln, pineplace, VT 54687', 9106879852, 'bobthbuilder@gmail.com', TO_DATE('1987/05/04', 'yyyy/mm/dd'))

INSERT INTO Client VALUES(369852489, 'John', 'Stone', '45 laketown dr, pineplace, VT 75648', 9108789658, 'stonehill@gmail.com', TO_DATE('1999/02/08', 'yyyy/mm/dd'))

INSERT INTO Client VALUES(125648932, 'Kim', 'Town', '102 newplace dr, pineplace, TN 45892', 9105878965, 'kimcat@gmail.com', TO_DATE('1989/03/03', 'yyyy/mm/dd'))

INSERT INTO Client VALUES(125432598, 'Jack', 'Stack', '302 upcreek dr, pineplace, TN 75645', 9103985236, 'jackboo@gmail.com', TO_DATE('1978/06/04', 'yyyy/mm/dd'))

INSERT INTO Client VALUES(547851236, 'Meg', 'Ryan', '14 downstream dr, pineplace, NC 12354', 9108741258, 'megry@gmail.com', TO_DATE('1969/05/03', 'yyyy/mm/dd'))

INSERT INTO Client VALUES(471254862, 'Beth', 'Prim', '66 lightscape ln, pineplace, SC 78954', 9107412369, 'bethbrid@gmail.com', TO_DATE('2010/09/08', 'yyyy/mm/dd'))

INSERT INTO Client VALUES(547821549, 'Anna', 'House', '486 nightowl ct, pineplace, NC 45621', 9102584563, 'annabanna@gmail.com', TO_DATE('2007/06/07', 'yyyy/mm/dd'))

INSERT INTO Client VALUES(321456897, 'jenny', 'Jones', '21 wheretobe ln, pineplace, SC 74125', 9108758888, 'jennyjones@gmail.com', TO_DATE('2005/01/04', 'yyyy/mm/dd'))

INSERT INTO Loan VALUES(3337845785785, 1, 5000.45, 4.5, 10000)

INSERT INTO Loan VALUES(7734745888745, 2, 15000.36, 3.1, 20000)

INSERT INTO Loan VALUES(1235454859458, 3, 32565.00, 3.7, 45000)

INSERT INTO Loan VALUES(5458544584658, 4, 15032.00, 3.4, 50000)

INSERT INTO Loan VALUES(4557532554558, 5, 99999.87, 4.0, 100000)

INSERT INTO Loan VALUES(3698524565896, 6, 2568.91, 3.9, 10000)

INSERT INTO Loan VALUES(1465465465152, 7, 11025.25, 4.5, 15000)

INSERT INTO Loan VALUES(5124548662358, 8, 102264, 6.0, 150000)

INSERT INTO Loan VALUES(2542156256356, 9, 300320.84, 2.3, 302500)

INSERT INTO Loan VALUES(3698521785525, 10, 3002.02, 4.9, 5000)

INSERT INTO Account_owner VALUES(123456789, 0124578258963)

INSERT INTO Account_owner VALUES(487858952, 4782158963256)

INSERT INTO Account_owner VALUES(458726589, 9875214589632)

INSERT INTO Account_owner VALUES(369852489, 9874254215645)

INSERT INTO Account_owner VALUES(125648932, 3215678524545)

INSERT INTO Account_owner VALUES(125432598, 9825623256556)

INSERT INTO Account_owner VALUES(547851236, 2514365878525)

INSERT INTO Account_owner VALUES(471254862, 9636258552452)

INSERT INTO Account_owner VALUES(547821549, 2141254215521)

INSERT INTO Account_owner VALUES(321456897, 3256525652325)

INSERT INTO Loan_owner VALUES(123456789, 3337845785785)

INSERT INTO Loan_owner VALUES(487858952, 7734745888745)

INSERT INTO Loan_owner VALUES(458726589, 1235454859458)

INSERT INTO Loan_owner VALUES(369852489, 5458544584658)

INSERT INTO Loan_owner VALUES(125648932, 4557532554558)

INSERT INTO Loan_owner VALUES(125432598, 3698524565896)

INSERT INTO Loan_owner VALUES(547851236, 1465465465152)

INSERT INTO Loan_owner VALUES(471254862, 5124548662358)

INSERT INTO Loan_owner VALUES(547821549, 2542156256356)

INSERT INTO Loan_owner VALUES(321456897, 3698521785525)