Some Minor SQL errors

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic


CREATE TABLE branch
	(branch_no VARCHAR(10) NOT NULL,
	branch_name VARCHAR(60) NOT NULL,
	branch_address VARCHAR(120) NOT NULL,
	branch_town VARCHAR(50) NOT NULL,
	branch_postcode VARCHAR(10) NOT NULL,
	branch_phone VARCHAR(18) NOT NULL,
CONSTRAINT PK_branch PRIMARY KEY (branch_no))

CREATE TABLE customer
	(customer_no VARCHAR(6) NOT NULL,
	customer_name VARCHAR(30) NOT NULL,
	customer_address VARCHAR(100) NOT NULL,
	customer_town VARCHAR(30) NOT NULL,
	customer_postcode VARCHAR(8) NOT NULL,
	customer_phone VARCHAR(14),
	branch_no VARCHAR(5) NOT NULL,
	date_recorded DATETIME,
CONSTRAINT PK_customer PRIMARY KEY (customer_no))


CREATE Table finance_company
	(lender_no VARCHAR(5) NOT NULL,
	lender_name VARCHAR(30) NOT NULL,
	lender_address VARCHAR(100) NOT NULL,
	lender_town VARCHAR(30) NOT NULL,
	lender_postcode VARCHAR(8) NOT NULL,
	lender_phone VARCHAR(14) NOT NULL,
CONSTRAINT PK_finance_company PRIMARY KEY (lender_no))

CREATE TABLE mortgage
	(mortgage_no VARCHAR(5) NOT NULL,
	customer_no VARCHAR(6) NOT NULL,
	mortgage_amount INT NOT NULL,
	monthly_repayment INT NOT NULL,
	lender_no VARCHAR(5) NOT NULL,
CONSTRAINT PK_mortgage PRIMARY KEY (mortgage_no),
CONSTRAINT FK_mortgage FOREiGN KEY (lender_no) References finance_company(lender_no),
CONSTRAINT FK_mortgage2 FOREIGN KEY (customer_no) References customer(customer_no))

CREATE TABLE loan
	(loan_no VARCHAR(5) NOT NULL,
	customer_no VARCHAR(6) NOT NULL,
	loan_amount INT NOT NULL,
	monthly_repayment INT NOT NULL,
	lender_no VARCHAR(5) NOT NULL,
CONSTRAINT PK_loan PRIMARY KEY (loan_no),
CONSTRAINT FK_loan FOREIGN KEY (lender_no)References finance_company(lender_no),
CONSTRAINT FK_loan2 FOREIGN KEY (customer_no)References customer(customer_no))


	CREATE UNIQUE NONCLUSTERED INDEX NC_branch_name ON branch(branch_name)
	CREATE UNIQUE NONCLUSTERED INDEX NC_customer_name ON customer(customer_name)
	CREATE UNIQUE NONCLUSTERED INDEX NC_lender_name ON finance_company(lender_name)
	CREATE UNIQUE NONCLUSTERED INDEX NC_lender_no ON loan(loan_no)
	CREATE UNIQUE NONCLUSTERED INDEX NC_lender_no ON mortgage(mortgage_no)
	CREATE UNIQUE NONCLUSTERED INDEX NC_branch_no ON customer(branch_no)

INSERT INTO customer
VALUES
('C1','Owen Hedges','26 Chatsworth Place','Cleethorpes','DN35 8NG','01234567890','B1',05/09/2003)
INSERT INTO customer
VALUES
('C2','Jono Popham','18 Something Street','Lincoln','LN7 6TY','01234098765','B1',04/09/2003)
INSERT INTO customer
VALUES
('C3','Nigel Gissing','22 Somewhere Lane','Market Rasen','MK34 9UK','09876543210','B2',07/08/2003)
INSERT INTO customer
VALUES
('C4','Michael Davies','45 Satellite Rd','Gainsborough','GB6 9UJ','01267873987','B3',03/03/2003)
INSERT INTO customer
VALUES
('C5','Stephen Keeling','35 Sideburn Street','Lincoln','LN78 09GH','09876786987','B3',12/07/2003)
INSERT INTO customer
VALUES
('C6','Hayley Keeling','37 Sideburn Street','Lincoln','LN78 09GH','09876786999','B2',11/07/2003)

INSERT INTO branch
VALUES
('B1','Lincoln High St','High St','LINCOLN','LN7 3NY','01234 567 891')
INSERT INTO branch
VALUES
('B2','Lloyds TSB','37 High St','LINCOLN','LN7 3RT','01234 536 970')
INSERT INTO branch
VALUES
('B3','Royal Bank Of Scotland','Saltergate','LINCOLN','LN31 3TY','01234 111 891')


INSERT INTO finance_company
VALUES
('L1','Start Finance','10 Ripemov Lane','Lincoln','LN89 9NL',' 01234 567 765')
INSERT INTO finance_company
VALUES
('L2','Cheap Loans','27 Flower Street,','Market Rasen','MK7 0DN','98769 765 987')
INSERT INTO finance_company
VALUES
('L3','Lloyds TSB Personal Credit','High Street,','Lincoln','LN6 9UK','01234 506 978')
INSERT INTO finance_company
VALUES
('L4',' Nationwide Loans','34 Clasketgate Lincoln','LN45 9HY','01234 726 876')

INSERT INTO loan
VALUES
('LN1','C1',25000,200,'L1')
INSERT INTO loan VALUES('LN1','C3',1000,146,'L1')
INSERT INTO loan VALUES('LN3','C4',5000,87,'L3')

INSERT INTO mortgage
VALUES
('M1','C1',60000,200,'L2')
INSERT INTO mortgage VALUES
('M2','C4',40000,80,'L1')


SELECT	branch.branch_no, branch.branch_name, branch.branch_address
FROM branch INNER JOIN
customer ON branch.branch_no = customer.branch_no INNER JOIN
loan ON customer.customer_no = loan.customer_no INNER JOIN
mortgage ON customer.customer_no = mortgage.customer_no WHERE
(loan.loan_no = 'LN3')

SELECT branch.branch_no, branch.branch_name, customer.customer_no,
customer.customer_name, loan.loan_no,
	     finance_company.lender_no, finance_company.lender_name,
customer.customer_address, customer.customer_town,
	     customer.customer_postcode
FROM	branch INNER JOIN
	 customer ON branch.branch_no = customer.branch_no INNER JOIN
	 loan ON customer.customer_no = loan.customer_no INNER JOIN
	 finance_company ON loan.lender_no = finance_company.lender_no
WHERE	(finance_company.lender_no = 'L1')


SELECT	customer.customer_no, loan.loan_no, branch.branch_no, branch.branch_name,
finance_company.lender_name,
	 loan.loan_amount, loan.monthly_repayment, customer.customer_name,
customer.customer_address
FROM	finance_company LEFT OUTER JOIN
	 loan ON finance_company.lender_no = loan.lender_no FULL OUTER JOIN
	 customer INNER JOIN
	 branch ON customer.branch_no = branch.branch_no ON loan.customer_no = 
customer.customer_no
WHERE	(finance_company.lender_name = 'Stuart Finance') AND (branch.branch_name = 
'Lincoln High Street')


SELECT	customer.customer_no, finance_company.lender_name AS 'Lender Name',
mortgage.mortgage_no, branch.branch_no,
	 branch.branch_name, finance_company.lender_name, mortgage.mortgage_amount,
mortgage.monthly_repayment,
	 customer.customer_name, customer.customer_address
FROM	finance_company INNER JOIN
	 mortgage ON finance_company.lender_no = mortgage.lender_no INNER JOIN
	 customer INNER JOIN
	 branch ON customer.branch_no = branch.branch_no ON mortgage.customer_no = 
customer.customer_no
WHERE	(branch.branch_name = 'Lincoln High Street') AND (finance_company.lender_name = 
'Cheap Loans')


SELECT	customer.customer_no, mortgage.mortgage_amount, mortgage.monthly_repayment,
	customer.customer_name, customer.customer_address, branch.branch_no, branch.branch_name
FROM	mortgage INNER JOIN
	 customer ON mortgage.customer_no = customer.customer_no INNER JOIN
	 branch ON customer.branch_no = branch.branch_no
WHERE	(mortgage.mortgage_amount > 40000)


SELECT	customer.customer_no, mortgage.mortgage_amount, mortgage.monthly_repayment, 
	customer.customer_name, customer.customer_address, branch.branch_no, branch.branch_name
FROM	mortgage INNER JOIN
	 customer ON mortgage.customer_no = customer.customer_no INNER JOIN
	 branch ON customer.branch_no = branch.branch_no
WHERE	(mortgage.mortgage_amount > 40000) AND branch_name = 'Lincoln High Street'