This advanced SQL exercise is part of the book: Advanced SQL – Practical Techniques and Use-Cases, which includes 100 day-to-day SQL challenges (and solutions), need-to-know advanced features, and key concepts.
 

Given a table that specifies various customer billings (the first billing totals 543, the second billing totals 749):


CREATE TABLE Invoices
(InvID int,
InvAmount int)
GO

 

In addition, given a table that specifies the various payments the customer made for each of the billings. For example, one can see that on January 1st the customer paid 20 ILS out of billing no. 1 (which totals 543 ILS), and on January 3rd the customer paid 40 ILS out of billing no. 2, which totals 749 ILS:


CREATE TABLE payments
(PayID int identity(1,1),
InvID int,
PayAmount int,
PayDate date)
GO

 

Would you like to read more?