Open the SQL AP database using the SQL Server 2012 Management Studio. Please make sure to review the SQL syntax for each query that is created.

One query per page. Please create the following queries:

1. Building a CTE (6 points) a. First derived table in the CTE - display account numbers, account descriptions for account numbers within the 500 range. b. The second derived table in the CTE - should list account numbers, invoice totals and invoice ids that have an Invoice total greater than 1000. c. The final output should show the account number, account description and invoice total on Invoices that are larger than the average invoice Total. Order the output by Account number

2. Write a statement, with a subquery, that returns the same result set as the statement below. Substitute a subquery in the WHERE clause to replace the inner join.

USE AP

SELECT DISTINCT VendorName FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID ORDER BY VendorName

3. List the Vendor ID, name and state for those Vendors who have NOT submitted an invoice. Use a subquery.

4. Convert the following SQL code that has a sub query, into an SQL query with a join. Make sure both queries return the same results.

USE AP

SELECT InvoiceNumber, InvoiceDate, VendorID FROM Invoices WHERE (VendorID = ANY (SELECT VendorID FROM Vendors WHERE VendorState IN ('MI', 'OH')))