Lecture/데이터베이스/2009/실습시간에 사용할 실습데이터

-- 2009년 10월 15일 데이터베이스 실습 데이터 
-- 학번 : 20091234
-- 이름 : 홍 길 동
 
 
-- borrower table 생성
CREATE TABLE borrower (
	customer_name CHAR(20),
	loan_number CHAR(10),
	PRIMARY KEY (customer_name,loan_number))
 
-- loan table 생성 
CREATE TABLE loan(
	loan_number CHAR(10),
	branch_name CHAR(15),
	amount NUMERIC(10,0),
	PRIMARY KEY(loan_number))
 
-- depositor table 생성
CREATE TABLE depositor(
	customer_name CHAR(20),
	account_number CHAR(10),
	PRIMARY KEY(customer_name, account_number))
 
-- branch table 생성 
CREATE TABLE branch (
	branch_name CHAR(15),
	branch_city CHAR(30),
	assets NUMERIC(16,2),
	PRIMARY KEY(branch_name))
 
-- borrower table 에 values 입력 
 
INSERT INTO borrower VALUES('Adans','L-16')
INSERT INTO borrower VALUES('Curry','L-93')
INSERT INTO borrower VALUES('Hayes','L-15')
INSERT INTO borrower VALUES('Jackson','L-14')
INSERT INTO borrower VALUES('Jones','L-17')
INSERT INTO borrower VALUES('Smith','L-11')
INSERT INTO borrower VALUES('Smith','L-23')
INSERT INTO borrower VALUES('Willians','L-17')
 
-- loan table 에 values 입력 
INSERT INTO loan VALUES('L-11','Round Hill','900')
INSERT INTO loan VALUES('L-14','Downtown','1500')
INSERT INTO loan VALUES('L-15','Perryridge','1500')
INSERT INTO loan VALUES('L-16','Perryridge','1300')
INSERT INTO loan VALUES('L-17','Downtown','1000')
INSERT INTO loan VALUES('L-23','Redwood','2000')
INSERT INTO loan VALUES('L-93','Mianus','500')
 
-- depositor table values 입력
INSERT INTO depositor VALUES('Hayes','A-102')
INSERT INTO depositor VALUES('Johnson','A-101')
INSERT INTO depositor VALUES('Johnson','A-201')
INSERT INTO depositor VALUES('Jones','A-217')
INSERT INTO depositor VALUES('Lindsay','A-222')
INSERT INTO depositor VALUES('Smith','A-215')
INSERT INTO depositor VALUES('Turner','A-305')
 
-- 실습1 Table 생성과 값입력 
 
-- account table 생성
-- customer table 생성
-- account table values 입력
-- branch table values 입력
-- customer table values 입력
 
-- 실습2 select 문 
 
-- 실습3 집합연산 
 
-- 실습4 집계함수 
 
-- 실습5 Join relation
 
-- 실습6 Database update