Question

Write the SQL queries that accomplish the following tasks using the AP Database

9. Write a select statement to show the invoicelineitemdescriptions that have the total invoicelineitemamount >1000 and the number of accountno is >2.

10. Write a select statement that returns the vendorid, paymentsum of each vendor, and the number of invoices of each vendor, where paymentsum is the sum of the paymentotal column. Return only the top ten vendors who have been paid the most and the number of invoices is >5.

Answer 1

1)Write a select statement to show the invoicelineitemdescriptions that have the total invoicelineitemamount >1000 and the number of accountno is >2.

SELECT InvoiceLineItemDescription

FROM InvoiceLineItems

WHERE InvoiceLineItemAmount>1000 AND AccountNo > 2;

2) Write a select statement that returns the vendorid, paymentsum of each vendor, and the number of invoices of each vendor, where paymentsum is the sum of the paymentotal column. Return only the top ten vendors who have been paid the most and the number of invoices is >5.

// In 2 ways second question can be done:

SELECT TOP 10 VendorName, SUM(PaymentTotal) AS Payments

FROM Vendors V, Invoices I

WHERE V.VendorID = I.VendorID

GROUP BY V.VendorID, VendorName

HAVING COUNT(*)>5;

OR

SELECT VendorName, SUM(PaymentTotal) AS Payments

FROM Vendors V, Invoices I

WHERE V.VendorID = I.VendorID

GROUP BY V.VendorID, VendorName

HAVING COUNT(*)>5

LIMIT 10;