With reference to the AP database, you will answer the following questions. You

Question

With reference to the AP database, you will answer the following questions. You are to create a SQL script file with the SQL scripts for the following questions. Submit this script file to Moodle. 1. Write a SELECT statement that answers this question Which invoices have a Payment Total that's greater than the median Payment Total for all paid invoices? (The median marks the midpoint in a set of values, an equal number of values lie above and below it.) Return the InvoiceNumber and InvoiceTotal for each invoice. 2. Write a SELECT statement that returns the name, city, and state of each vendor that's located in a unique city and state. In other words, don't include sendors that have a city and state in common with another vendor. 3. Write a SELECT statement that returns tour columns VendorName, ImoviceID, InvoiceSequence, and Invoicel, ineItemAmount for each invoice that has more than one line item in the InvoiceI. ineItems table.

Explanation / Answer

1)
SELECT InvoiceNumber, InvoiceTotal
FROM INVOICES
WHERE PaymentTotaL > ALL
(SELECT TOP 50 PERCENT PaymentTotal
FROM INVOICES
ORDER BY PaymentTotal)

2)
Select VendorName, VendorCity, VendorState
FROM Vendors
WHERE VendorCity + VendorState
NOT IN
(SELECT VendorCity + VendorState
FROM Vendors
GROUP BY VendorCity, VendorState
HAVING COUNT(*) > 1 )
ORDER BY VendorState, VendorCity

3)
SELECT VendorName, InvoiceLineItems.InvoiceID, InvoiceSequence, InvoiceLineItemAmount
FROM InvoiceLineItems Join Invoices ON InvoiceLineItems.InvoiceID = Invoices.InvoiceID
JOIN Vendors ON Invoices.VendorID = Vendors.VendorID
WHERE InvoiceLineItems.InvoiceID IN
(SELECT InvoiceID
FROM InvoiceLineItems
WHERE InvoiceSequence > 1)

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.