sqlst_final.sql

Project 01, Chapter 08 Bank Operation

create database Banking
print 'Banking Database created'

use banking
print 'Current DB is Banking'
--drop table Account_Detail
--print 'Dropping table Account_Detail'
--drop table BankLogin
--print 'Dropping table BankLogin'
--drop table Calulate_Interest
--print 'Dropping table Calulate_Interest'
--drop table Check_Book
 --print 'Dropping table Check_Book'
 --drop table Fixed_Deposit
 --print 'Dropping table Fixed_Deposit'
--drop table Mail_Detail
--print 'Dropping table Mail_Detail'
--drop table Transaction_Detail
--print 'Dropping table Transaction_Detail'

print 'Creating table Account_Detail'
create table Account_Detail
(
   Account_Number int IDENTITY (10000,1) not null primary key,
   First_Name varchar(30) not null,
   Last_Name varchar(30) not null,
   date_birth varchar(10) not null,
   Permanent_Addr varchar(50) not null,
   Mailing_Addr varchar(50) not null,
   Phone_Number  varchar(15) not null,
   eMailID varchar(30) null,
   Type_account char(1) not null,
   Balance float not null,
   Valid_Acc char(2) not null,
   Ref_Acc_No int not null
)
go
insert Account_Detail
values('Masine','Philip','09/09/72','Street V Opp. Great Circle','Bldg-11, Avenue 2','91-20392012','[e-mail]','S',800,'VA',0)
go

print 'Creating table BankLogin'
create table BankLogin
(
  UserNm varchar(5) not null,
  Pwd varchar(15) not null
)
go

insert BankLogin
values('S103','password')
go


print 'Creating table Check_Book'
create table Check_Book
(
   Tran_No int Identity(100,1) primary key,
   Account_Number int not null constraint fk_AcctNumb foreign key references Account_Detail(Account_Number),
   Number_of_Checks int not null,
   Date_of_Issue char(10) not null,
   Start_Check_Number int not null,
   End_Check_Number int not null,
   Hand_Over char(1) not null
)
go
insert Check_Book
values(1,20,'30/09/01',1,20,'C')
go


print 'Creating table Transaction_Detail'
create table Transaction_Detail
(
   TranId int identity(100,1) primary key,
   Account_Number int not null constraint fk_AcctNumb1 foreign key references Account_Detail(Account_Number),
   Transaction_Mode char(3) not null,
   Transaction_Type char(3) not null,
   Transaction_Date char(10) not null,
   Transaction_Amount float not null,
   Check_Draft_Number int null,
   To_Account int null,
   Creditor_Name varchar(30) null,
   Creditor_BankName varchar(30) null,
   Creditor_BankAddr varchar(50) null,
   Transaction_Status char(1) not null
)
go