1. Write a SELECT statement that returns the same result set as this SELECT statement. Substitute a subquery in a WHERE clause for the inner join.
SELECT DISTINCT vendorName
PROM vendors J01N Invoices
ON Vendors. VendorID Invoices .Vend0rID
ORDER BY vendorName;
2. Write a SELECT statement that answers this question: Which invoices have a PaymentTotal that's greater than the average PaymentTotal for all paid invoices? Return the InvoiceNumber and InvoiceTotal for each invoice.
4. Write a SELECT statement that returns two columns from the GLAccounts table: AccountNo and AccountDescription. The result set should have one row for each account number that has never been used. Use a correlated subquery introduced with the NOT EXISTS operator. Sort the final result set by AccountNo.
6. Write a SELECT statement that returns a single value that represents the sum of the largest unpaid invoices submitted by each vendor. Use a derived table that returns MAX(lnvoiceTotal) grouped by VendorID, filtering for invoices with a balance due.
9. Rewrite exercise 6 so it uses a common table expression (CTE) instead of a derived table.