-- 3 ----------------------------------------------
DROP VIEW Financials;

CREATE VIEW Financials AS
SELECT DISTINCT P.PID, P.pName, (SELECT SUM(A2.aBalance)
						FROM Accounts A2
						WHERE A2.PID = A.PID) AS money
FROM People P, Accounts A
WHERE P.PID = A.PID;

SELECT *
FROM Financials;

-- ----

INSERT INTO Bills VALUES( NULL,  1, '2010-10-17' , 300, 0);
INSERT INTO Bills VALUES( NULL,  1, '2010-10-17' , 50, 0);
INSERT INTO Bills VALUES( NULL,  2, '2010-10-17' , 1300, 0);

SELECT *
FROM Bills;

-- --
DROP VIEW Total_bills;

CREATE VIEW Total_bills AS
SELECT DISTINCT B.PID, (SELECT SUM(B2.bAmount)
						FROM Bills B2
						WHERE B2.PID = B.PID)AS Due
FROM Bills B
WHERE bIsPaid = 0;

SELECT *
FROM Total_bills;

-- --
DROP VIEW FinancialStatus;

CREATE VIEW FinancialStatus AS
SELECT DISTINCT F.PID, F.Pname, (F.money - T.Due) AS FinStatus
FROM Financials F, Total_bills T
WHERE F.PID = T.PID;

SELECT *
FROM FinancialStatus;