Question

Write SQL statements to answer the following questions using Assignment 3’s schema (tables from part 1). 1- Find how many branches had have loans over $2000.00. 2- For each branch, find the most expensive loan. Your output should include Branch Id, loan amount for the highest loan for that Branch. 3- Find how many accounts there are for each customer. The output should include customer id and number of accounts for that customer. 4- Find the total balance amount of all accounts by each Branch. The output should be a list of Branch Id and for each Branch Id, the total balance of accounts in that Branch. 5- Find Customer ID, Customer name and the number of loans for each Customer. 6- Find Customer ID, Customer name for all accounts, sorted by Customer name. 7- Find Loan number and Branch Id of the loan with the lowest amount. 8- Create a view called MPL_Branch_V that contains Branch Id, Branch name, and number of loans for each Branch that is in the city of Minneapolis. 9- For each Customer in Hopkins, find the total amount of all their loans. 10- Find how many different accounts each customer has at each Branch. The output should be a list of Customer ID and for each Customer ID, the number of accounts for this customer by Branch ID.

TABLES

1. Branch(

branch_id:integer

, branch_name:varchar(50), branch_city:varchar(50),

assets:numeric(11,2)

2. Loan(

loan_number:integer

, branch_id:integer, amount:numeric(8,2))

foreign key branch_id references Branch(branch_id)

3. Customer(

customer_id:integer

, customer_name:varchar(30),

customer_street:varchar(30), customer_city:varchar(50))

4. Borrower(

customer_id:integer

,

loan_number:integer)

foreign key (customer_id) references Customer(customer_id)

foreign key (loan_number) references Loan(loan_number)

5. Depositor(

customer_id:integer

,

account_number:integer)

foreign key (customer_id) references Customer(customer_id)

foreign key (account_number) references Account(account_number)

6. Account(

account_number:integer

, branch_id:integer, balance:numeric(8,2))

foreign key branch_id references Branch(branch_id

Answer 1

-- 1- Find how many branches had have loans over $2000.00. - result ok
select COUNT(*) as TotalBranches from
Branch where Branch_ID In (
select
--count(branch_id) as TotalLoans,
--SUM(amount) as TotalLoanAmount,
branch_id
from Loan
group by
branch_id
having SUM(amount)>2000
)

-- 2. For each branch, find the most expensive loan.
-- Your output should include Branch Id, loan amount for
-- the highest loan for that Branch. -- result OK

select branch_id , max(amount) as loan_amount
from
Loan
group by branch_id

-- 3- Find how many accounts there are for each customer.
--The output should include customer id and
--number of accounts for that customer. - result ok

Select customer_id , COUNT(*) as TotalAccounts
from Depositor
group by
customer_id

-- 4- Find the total balance amount of all accounts by each Branch.
-- The output should be a list of Branch Id and for each
-- Branch Id, the total balance of accounts in that Branch. -- result OK

Select branch_id, SUM(balance) as Total_Balance_Of_Accounts
from Account group by branch_id

-- 5- Find Customer ID, Customer name and the number of loans
-- for each Customer. -- result OK

Select cust.customer_id, count(borr.loan_number) as Total_Loans
from Customer as cust , Loan as L, Borrower as borr
where borr.customer_id = cust.customer_id
and L.loan_number = borr.loan_number
group by cust.customer_id

-- 6- Find Customer ID, Customer name for all accounts,
-- sorted by Customer name. -- result ok

Select customer_id, customer_name from Customer
where customer_id in
(Select DISTINCT(borrower.customer_id) from Customer , Borrower
where
customer.customer_id = borrower.customer_id )
order by customer_name

-- 7-Find Loan number and Branch Id of the loan with
--the lowest amount. result ok

select branch_id , min(amount) as loan_amount
from
Loan
group by branch_id

-- 8 Create a view called MPL_Branch_V
-- that contains Branch Id, Branch name, and
-- number of loans for each Branch
--that is in the city of Minneapolis. result ok

CREATE VIEW MPL_Branch_V
AS
select branch_city, count(loan_number) as Total_Loans from Branch, Loan
where branch.branch_id = loan.branch_id
and branch.branch_city = 'Minneapolis'
group by branch_city

--SELECT * FROM MPL_Branch_V

-- 9 For each Customer in Hopkins,
-- find the total amount of all their loans.
--Select Customer.*, Borrower.* , Loan.* from Customer, Borrower , Loan

Select customer.customer_ID , SUM(Loan.amount) as TotalLoanAmountTaken from Customer, Borrower , Loan
where customer_city = 'Hopkins'
and Customer.customer_id = Borrower.customer_id
and Loan.loan_number = Borrower.loan_number
group by customer.customer_id

-- 10-Find how many different accounts
-- each customer has at each Branch.
-- The output should be a list of Customer ID
-- and for each Customer ID,
-- the number of accounts for this customer by
-- Branch ID.

Select DEP.customer_id , ACC.branch_id, count(ACC.account_number) as TotalAccountsAtBranch from Depositor as DEP, Account as ACC
where
DEP.account_number = ACC.account_number
group by DEP.customer_id , ACC.branch_id

DATABASE DIAGRAM:

If need be, YOU CAN USE THE BELOW SCRIPT TO CREATE DATABASE ALONG WITH TEST ROWS. I created them using Visual Studio - SQL Express

/****** Object: ForeignKey [FK_Borrower_Customer]    Script Date: 10/17/2018 08:54:46 ******/
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Borrower_Customer]') AND parent_object_id = OBJECT_ID(N'[dbo].[Borrower]'))
ALTER TABLE [dbo].[Borrower] DROP CONSTRAINT [FK_Borrower_Customer]
GO
/****** Object: ForeignKey [FK_Borrower_Loan]    Script Date: 10/17/2018 08:54:46 ******/
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Borrower_Loan]') AND parent_object_id = OBJECT_ID(N'[dbo].[Borrower]'))
ALTER TABLE [dbo].[Borrower] DROP CONSTRAINT [FK_Borrower_Loan]
GO
/****** Object: ForeignKey [FK_Depositor_Account]    Script Date: 10/17/2018 08:54:46 ******/
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Depositor_Account]') AND parent_object_id = OBJECT_ID(N'[dbo].[Depositor]'))
ALTER TABLE [dbo].[Depositor] DROP CONSTRAINT [FK_Depositor_Account]
GO
/****** Object: ForeignKey [FK_Depositor_Customer]    Script Date: 10/17/2018 08:54:46 ******/
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Depositor_Customer]') AND parent_object_id = OBJECT_ID(N'[dbo].[Depositor]'))
ALTER TABLE [dbo].[Depositor] DROP CONSTRAINT [FK_Depositor_Customer]
GO
/****** Object: ForeignKey [FK_Loan_Branch]    Script Date: 10/17/2018 08:54:46 ******/
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Loan_Branch]') AND parent_object_id = OBJECT_ID(N'[dbo].[Loan]'))
ALTER TABLE [dbo].[Loan] DROP CONSTRAINT [FK_Loan_Branch]
GO
/****** Object: Table [dbo].[Borrower]    Script Date: 10/17/2018 08:54:46 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Borrower]') AND type in (N'U'))
DROP TABLE [dbo].[Borrower]
GO
/****** Object: View [dbo].[MPL_Branch_V]    Script Date: 10/17/2018 08:54:47 ******/
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[MPL_Branch_V]'))
DROP VIEW [dbo].[MPL_Branch_V]
GO
/****** Object: Table [dbo].[Depositor]    Script Date: 10/17/2018 08:54:46 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Depositor]') AND type in (N'U'))
DROP TABLE [dbo].[Depositor]
GO
/****** Object: Table [dbo].[Loan]    Script Date: 10/17/2018 08:54:46 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Loan]') AND type in (N'U'))
DROP TABLE [dbo].[Loan]
GO
/****** Object: Table [dbo].[Branch]    Script Date: 10/17/2018 08:54:46 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Branch]') AND type in (N'U'))
DROP TABLE [dbo].[Branch]
GO
/****** Object: Table [dbo].[Customer]    Script Date: 10/17/2018 08:54:46 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customer]') AND type in (N'U'))
DROP TABLE [dbo].[Customer]
GO
/****** Object: Table [dbo].[Account]    Script Date: 10/17/2018 08:54:46 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Account]') AND type in (N'U'))
DROP TABLE [dbo].[Account]
GO
/****** Object: Table [dbo].[Account]    Script Date: 10/17/2018 08:54:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Account]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Account](
   [account_number] [int] NOT NULL,
   [branch_id] [int] NOT NULL,
   [balance] [numeric](8, 2) NOT NULL,
CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED
(
   [account_number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
INSERT [dbo].[Account] ([account_number], [branch_id], [balance]) VALUES (1, 1, CAST(232.00 AS Numeric(8, 2)))
INSERT [dbo].[Account] ([account_number], [branch_id], [balance]) VALUES (2, 2, CAST(343.00 AS Numeric(8, 2)))
INSERT [dbo].[Account] ([account_number], [branch_id], [balance]) VALUES (3, 3, CAST(343.00 AS Numeric(8, 2)))
INSERT [dbo].[Account] ([account_number], [branch_id], [balance]) VALUES (4, 3, CAST(454.00 AS Numeric(8, 2)))
INSERT [dbo].[Account] ([account_number], [branch_id], [balance]) VALUES (5, 2, CAST(3433.00 AS Numeric(8, 2)))
/****** Object: Table [dbo].[Customer]    Script Date: 10/17/2018 08:54:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customer]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Customer](
   [customer_id] [int] NOT NULL,
   [customer_name] [varchar](30) COLLATE Latin1_General_CI_AI NOT NULL,
   [customer_street] [varchar](30) COLLATE Latin1_General_CI_AI NOT NULL,
   [customer_city] [varchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
   [customer_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
INSERT [dbo].[Customer] ([customer_id], [customer_name], [customer_street], [customer_city]) VALUES (1, N'c1', N'cs1', N'Hopkins')
INSERT [dbo].[Customer] ([customer_id], [customer_name], [customer_street], [customer_city]) VALUES (2, N'c2', N'cs2', N'cc2')
INSERT [dbo].[Customer] ([customer_id], [customer_name], [customer_street], [customer_city]) VALUES (3, N'c3', N'cs3', N'cc1')
INSERT [dbo].[Customer] ([customer_id], [customer_name], [customer_street], [customer_city]) VALUES (4, N'c4', N'cs4', N'Hopkins')
INSERT [dbo].[Customer] ([customer_id], [customer_name], [customer_street], [customer_city]) VALUES (5, N'c5', N'cs5', N'cc1')
INSERT [dbo].[Customer] ([customer_id], [customer_name], [customer_street], [customer_city]) VALUES (6, N'c6', N'cs6', N'Hopkins')
/****** Object: Table [dbo].[Branch]    Script Date: 10/17/2018 08:54:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Branch]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Branch](
   [branch_id] [int] NOT NULL,
   [branch_name] [varchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
   [branch_city] [varchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
   [assets] [numeric](11, 2) NULL,
CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED
(
   [branch_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
INSERT [dbo].[Branch] ([branch_id], [branch_name], [branch_city], [assets]) VALUES (1, N'b1', N'Minneapolis', CAST(398.00 AS Numeric(11, 2)))
INSERT [dbo].[Branch] ([branch_id], [branch_name], [branch_city], [assets]) VALUES (2, N'b2', N'bc2', CAST(343.00 AS Numeric(11, 2)))
INSERT [dbo].[Branch] ([branch_id], [branch_name], [branch_city], [assets]) VALUES (3, N'b3', N'Minneapolis', CAST(7554.00 AS Numeric(11, 2)))
/****** Object: Table [dbo].[Loan]    Script Date: 10/17/2018 08:54:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Loan]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Loan](
   [loan_number] [int] NOT NULL,
   [branch_id] [int] NOT NULL,
   [amount] [numeric](8, 2) NOT NULL,
CONSTRAINT [PK_Loan] PRIMARY KEY CLUSTERED
(
   [loan_number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
INSERT [dbo].[Loan] ([loan_number], [branch_id], [amount]) VALUES (1, 3, CAST(100.00 AS Numeric(8, 2)))
INSERT [dbo].[Loan] ([loan_number], [branch_id], [amount]) VALUES (2, 1, CAST(100.00 AS Numeric(8, 2)))
INSERT [dbo].[Loan] ([loan_number], [branch_id], [amount]) VALUES (3, 2, CAST(3000.00 AS Numeric(8, 2)))
INSERT [dbo].[Loan] ([loan_number], [branch_id], [amount]) VALUES (4, 2, CAST(1200.00 AS Numeric(8, 2)))
INSERT [dbo].[Loan] ([loan_number], [branch_id], [amount]) VALUES (5, 3, CAST(2200.00 AS Numeric(8, 2)))
INSERT [dbo].[Loan] ([loan_number], [branch_id], [amount]) VALUES (6, 3, CAST(3300.00 AS Numeric(8, 2)))
INSERT [dbo].[Loan] ([loan_number], [branch_id], [amount]) VALUES (7, 2, CAST(1300.00 AS Numeric(8, 2)))
INSERT [dbo].[Loan] ([loan_number], [branch_id], [amount]) VALUES (8, 3, CAST(2500.00 AS Numeric(8, 2)))
INSERT [dbo].[Loan] ([loan_number], [branch_id], [amount]) VALUES (9, 3, CAST(1222.00 AS Numeric(8, 2)))
/****** Object: Table [dbo].[Depositor]    Script Date: 10/17/2018 08:54:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Depositor]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Depositor](
   [customer_id] [int] NOT NULL,
   [account_number] [int] NOT NULL,
CONSTRAINT [PK_Depositor] PRIMARY KEY CLUSTERED
(
   [customer_id] ASC,
   [account_number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
INSERT [dbo].[Depositor] ([customer_id], [account_number]) VALUES (1, 3)
INSERT [dbo].[Depositor] ([customer_id], [account_number]) VALUES (1, 4)
INSERT [dbo].[Depositor] ([customer_id], [account_number]) VALUES (2, 1)
INSERT [dbo].[Depositor] ([customer_id], [account_number]) VALUES (3, 2)
INSERT [dbo].[Depositor] ([customer_id], [account_number]) VALUES (4, 2)
INSERT [dbo].[Depositor] ([customer_id], [account_number]) VALUES (5, 5)
/****** Object: View [dbo].[MPL_Branch_V]    Script Date: 10/17/2018 08:54:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[MPL_Branch_V]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[MPL_Branch_V]
AS
select branch_city, count(loan_number) as Total_Loans from Branch, Loan
where branch.branch_id = loan.branch_id
and branch.branch_city = ''Minneapolis''
group by branch_city
'
GO
/****** Object: Table [dbo].[Borrower]    Script Date: 10/17/2018 08:54:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Borrower]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Borrower](
   [customer_id] [int] NOT NULL,
   [loan_number] [int] NOT NULL,
CONSTRAINT [PK_Borrower] PRIMARY KEY CLUSTERED
(
   [customer_id] ASC,
   [loan_number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO
INSERT [dbo].[Borrower] ([customer_id], [loan_number]) VALUES (1, 1)
INSERT [dbo].[Borrower] ([customer_id], [loan_number]) VALUES (1, 8)
INSERT [dbo].[Borrower] ([customer_id], [loan_number]) VALUES (2, 2)
INSERT [dbo].[Borrower] ([customer_id], [loan_number]) VALUES (3, 3)
INSERT [dbo].[Borrower] ([customer_id], [loan_number]) VALUES (3, 4)
INSERT [dbo].[Borrower] ([customer_id], [loan_number]) VALUES (4, 5)
INSERT [dbo].[Borrower] ([customer_id], [loan_number]) VALUES (4, 6)
INSERT [dbo].[Borrower] ([customer_id], [loan_number]) VALUES (4, 7)
/****** Object: ForeignKey [FK_Borrower_Customer]    Script Date: 10/17/2018 08:54:46 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Borrower_Customer]') AND parent_object_id = OBJECT_ID(N'[dbo].[Borrower]'))
ALTER TABLE [dbo].[Borrower] WITH CHECK ADD CONSTRAINT [FK_Borrower_Customer] FOREIGN KEY([customer_id])
REFERENCES [dbo].[Customer] ([customer_id])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Borrower_Customer]') AND parent_object_id = OBJECT_ID(N'[dbo].[Borrower]'))
ALTER TABLE [dbo].[Borrower] CHECK CONSTRAINT [FK_Borrower_Customer]
GO
/****** Object: ForeignKey [FK_Borrower_Loan]    Script Date: 10/17/2018 08:54:46 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Borrower_Loan]') AND parent_object_id = OBJECT_ID(N'[dbo].[Borrower]'))
ALTER TABLE [dbo].[Borrower] WITH CHECK ADD CONSTRAINT [FK_Borrower_Loan] FOREIGN KEY([loan_number])
REFERENCES [dbo].[Loan] ([loan_number])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Borrower_Loan]') AND parent_object_id = OBJECT_ID(N'[dbo].[Borrower]'))
ALTER TABLE [dbo].[Borrower] CHECK CONSTRAINT [FK_Borrower_Loan]
GO
/****** Object: ForeignKey [FK_Depositor_Account]    Script Date: 10/17/2018 08:54:46 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Depositor_Account]') AND parent_object_id = OBJECT_ID(N'[dbo].[Depositor]'))
ALTER TABLE [dbo].[Depositor] WITH CHECK ADD CONSTRAINT [FK_Depositor_Account] FOREIGN KEY([account_number])
REFERENCES [dbo].[Account] ([account_number])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Depositor_Account]') AND parent_object_id = OBJECT_ID(N'[dbo].[Depositor]'))
ALTER TABLE [dbo].[Depositor] CHECK CONSTRAINT [FK_Depositor_Account]
GO
/****** Object: ForeignKey [FK_Depositor_Customer]    Script Date: 10/17/2018 08:54:46 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Depositor_Customer]') AND parent_object_id = OBJECT_ID(N'[dbo].[Depositor]'))
ALTER TABLE [dbo].[Depositor] WITH CHECK ADD CONSTRAINT [FK_Depositor_Customer] FOREIGN KEY([customer_id])
REFERENCES [dbo].[Customer] ([customer_id])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Depositor_Customer]') AND parent_object_id = OBJECT_ID(N'[dbo].[Depositor]'))
ALTER TABLE [dbo].[Depositor] CHECK CONSTRAINT [FK_Depositor_Customer]
GO
/****** Object: ForeignKey [FK_Loan_Branch]    Script Date: 10/17/2018 08:54:46 ******/
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Loan_Branch]') AND parent_object_id = OBJECT_ID(N'[dbo].[Loan]'))
ALTER TABLE [dbo].[Loan] WITH CHECK ADD CONSTRAINT [FK_Loan_Branch] FOREIGN KEY([branch_id])
REFERENCES [dbo].[Branch] ([branch_id])
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Loan_Branch]') AND parent_object_id = OBJECT_ID(N'[dbo].[Loan]'))
ALTER TABLE [dbo].[Loan] CHECK CONSTRAINT [FK_Loan_Branch]
GO