Question

Write the DDL statement to create the VIEW named UNPAID_TOTAL: fname, lname, full address "bill" NULL if not present, total amount for this person, of any students that have unpaid bills. So this will involve a SUM/GROUP BY.

Answer 1

CREATE VIEW UNPAID_TOTAL AS

SELECT fname,lname,adtype, streetaddr,city,state,country,zip,

CASE WHEN c.paid IN ('false') THEN sum(Amount)

else "NULL"

end as Bill

FROM PERSON a

JOIN ADDR b

ON a.idnum=b.idnum

JOIN BILLING c

on c.idnum=a.idnum

group by fname,lname,adtype, streetaddr,city,state,country,zip;

Step-by-step explanation: