1. Objective: In this assignment you will apply your SQL Query knowledge/skills.

Question

1. Objective: In this assignment you will apply your SQL Query knowledge/skills. You will be asked to develop SQL statements in Access 2. An Overview of Assignment. In this assignment, you will need to create SQL Queries on the MS Access database that is given to vou. Tables and table relationships in the Access database is provided below. Customer ZIP Table PK |Custo |VARCHAR(9) BillStatement PK Z?PCode VARCHAR(5) PK StatNo VARCHAR(5) Name Address DoB HomePhone VARCHAR(10) VARCHAR(30) VARCHAR(40) FHReceives DATETIME as city StateVARCHAR(2) VARCHAR(20) StatDate DATETIME DueDate DATETIME FK1 ZIPcode VARCHAR(5) FK1 CustNo VARCHAR(9) has has / as StatementLine Loan Underwriter LoanNo CHAR(9) PK,FK1StatNo VARCHAR(5) PK LineNo VARCHAR(1) PK UWNo VARCHAR(9) ProcDate DATETIME Auth Date DATETIME DisbDate DATETIME AmtDue SINGLE Prin Amt SINGLE Int Amt SINGLE Min Amt SINGLE LoanNo VARCHAR(9) as Name VARCHAR(40) has Phone VARCHAR(10) TaxIDVARCHAR(9) BalanceSINGLE FK1 UWNo VARCHAR(9) FK2 CustNo VARCHAR(9) FK3 EmpIDPVARCHAR(9) FK4 EmpIDM VARCHAR(9) FK1 ZIPcode VARCHAR(5) FK2 has / has /is of Approves Payment LoanProcessor LoanManager PK,FK1StatNo VARCHAR(5) PK,FK1 LineNo VARCHAR(1) VARCHAR(9) PK,FK1 EmplD SMALLINT SMALLINT PK,FK1 EmplD VARCHAR(9) DatePaid DATETIME AmtPaid SINGLE CheckNo VARCHAR(5) Rank Title AnnualSalary SINGLE VARCHAR(15) Hourly Rate SINGLE Hours This is a duplicate of ZIP Table for easy illustration of the data model. No need to create it ZIPTable1 PK ZIPcode VARCHAR(5) Employee has -H city State VARCHAR(2) PK EmplD VARCHAR(9) VARCHAR(20) Name VARCHAR(30) DoB DATETIME FK1 ZIPcode VARCHAR(5) EmpType CHAR(1) 1

Explanation / Answer

If you have any doubts, please give me comment...

CREATE VIEW eLoanManager AS
SELECT E.EmpID, Name, DoB, Salary, City, State, E.Zipcode
FROM Employee E, LoanManager LM, ZIPTable Z
WHERE E.EmpID = LM.EmpID AND E.Zipcode = Z.Zipcode;

CREATE VIEW eLoanProcessor AS
SELECT E.EmpID, Name, DoB, HourlyRate, Hours, City, State, Z.Zipcode
FROM Employee E, LoanProcessor LP, ZIPtable Z
WHERE E.EmpID = LP.EmpID AND E.Zipcode = Z.Zipcode;

CREATE VIEW eCustomer AS
SELECT CustNo, Name, Address, DoB, HomePhone, City, State, ZIPcode
FROM Customer C, Zipcode Z
WHERE C.ZIPcode = Z.ZIPcode;

--4)
--1) List Name & Address of Customers living in SanDiego or Seattle.
SELECT Name, City, State, ZIPcode
FROM eCustomer
WHERE City = 'SanDiego' OR City = 'Seattle';

--2)
SELECT C.CustNo, C.Name, LoanNo, Balance, EmpID, LP.Name
FROM eCustomer C, Loan L, eLoanProcessor LP
WHERE C.CustNo = L.CustNo AND L.EmpIDP = LP.EmpID AND LP.City = 'Portage';

--3)
SELECT LP.EmpID, LP.Name, LP.City, C.CustID, C.Name
FROM eLoanProcessor LP, Customer C
WHERE LP.ZIPCode = C.ZIPcode;

--4)
SELECT C.CustNo, C.Name, (L.Balance - PL.PaidAmt) AS `RemainAmt`
FROM Customer C, Loan L, (
    SELECT LoanNo, SUM(AmtPaid) AS PaidAmt
    FROM StatementLine S, Payment P
    WHERE S.StatNo = P.StatNo AND S.LineNo = P.LineNo
    GROUP BY LoanNo
) AS PaidLoan PL
WHERE C.CustNo = L.LoanNo AND L.LoanNo = PL.LoanNo;

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.