For SQL Database Development

Open the AP database using the SQL Server 2012 Management Studio. Please make sure to review the SQL syntax for each query that is created. Each of the queries is worth 2 points, unless otherwise noted.

Please turn in the following items:

1) The sql code. a. Use comments to show your name, assignment, and the query number

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')))