jh
1 CIS111 Chapter 6 Homework Using the AP database. Please make sure to review the SQL syntax for each query that is created. The first query, CTE, is worth 6 points, and the remaining three worth 2 points each. Please turn in the following items: 1) The sql code. a. Use comments to show your name, assignment, and the query number 2) The first 5 lines of the results tab, with headings. 3) The output in the messages tab 4) 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 accounts that have the account descriptions end with the letters S or T b. The second derived table in the CTE - should list account numbers, Invoice totals and invoice id’s that have an Invoice total greater than 7000. c. The final output should show the account number, Invoice ID. account description and invoice total on Invoices that are larger than the average invoice Total. Order the output by Invoice ID, in ascending order. D. Put your initials within your derived table names. 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, and using only the Vendors table in the FROM line. USE CIS111_AP SELECT DISTINCT AccountNo FROM GLAccounts JOIN Vendors ON Vendors.DefaultAccountNo = GLAccounts.AccountNo ORDER by AccountNo 3. List the Vendor names 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 CIS111_AP SELECT InvoiceNumber, InvoiceDate, VendorID FROM Invoices WHERE (VendorID = ANY (SELECT VendorID FROM Vendors WHERE VendorState IN ('IN', 'MI'))) Query Example Query Example This example will show you how to assemble a solution for printing. If you are submitting the query directly into Moodle, you simply need to upload your readable sql query directly. 1. Each solution must follow this prescribed format. Points will be deducted if not followed. 2. Each query and solution will be on a separate sheet. 3. Your name, the assignment number, and problem number, will be included in the query as a comment. You can use either a block or line comment. 4. Your name must be included in each query. 5. The messages output will be included for printed submissions. 6. The first 5 lines of the results tab will be included in printed solutions. 7. If you print all pages of the results output that produces more than one page to be printed, 1 point will be deducted for each additional page turned in. Query question: Display the employee first name, last name and full name in the following format: last name comma first name such as King, Robert. This list should ONLY include employees with the job title of Sales Representative that were born in the 1960’s (1960 - 1969). Use the Employees table. Query Solution: USE CIS11102_Northwind /* Assignment 1 Query example Solution Bob James You can use block comment, or line comment */ -- This is a line comment SELECT Firstname, LastName, LastName + ', ' + Firstname AS [Full Name] FROM Employees WHERE Title LIKE '%Sales Rep%' AND BirthDate BETWEEN '01-01-1960' AND '12/31/1969' Messages (4 row(s) affected) Results (don’t need to be lined up) Firstname LastName Full Name Janet Leverling Leverling, Janet Michael Suyama Suyama, Michael Robert King King, Robert Anne Dodsworth Dodsworth, Anne