Answered

Write a SELECT statement that returns four columns: VendorName From the Vendors table InvoiceNumber From the Invoices table InvoiceDate From the Invoices table Balance Derived from the Invoices table, i.e., InvoiceTotal minus the sum of PaymentTotal and CreditTotal The result set should have one row for each invoice with a non-zero balance. Sort the result set by VendorName in ascending order. Hint: correct query results 11 rows.

Answer :

mahamnasir

Answer:

SELECT VendorName, InvoiceNumber, InvoiceDate,

InvoiceTotal - (PaymentTotal + CreditTotal) AS Balance

FROM Vendors JOIN Invoices  ON Vendors.VendorID = Invoices.VendorID

WHERE InvoiceTotal - (PaymentTotal + CreditTotal) > 0

ORDER BY VendorName;

Explanation:

SELECT statement here selects four columns VendorName, InvoiceNumber, InvoiceDate and the last one is the subtration of InvoiceTotal with the sum of PaymentTotal and CreditTotal which is named as Balance  using the alias AS which is used to give a temporary name to one or more columns.

Next JOIN is used to combine the records from Vendors and Invoices tables. This is based on a related column between these tables. ON clause specifies which columns to join. INNER join can also be used here as it returns the records that matches with the condition specified so it can also be written as:

FROM Vendors INNER  JOIN Invoices ON Vendors.VendorID = Invoices.VendorID  

Then the WHERE clause is used to which will filter the rows further according to the condition that the records to select where the result set has one row for each invoice with a non-zero balance. As we know balance is InvoiceTotal - (PaymentTotal + CreditTotal.

In the last statement the result set is sorted in ascending order according to VendorName column. So ORDER BY is used to sort it and by default it sorts in ascending order. We can also write this statement as: ORDER BY VendorName ASC

Other Questions