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
. 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
y 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.
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
4. Convert the following SQL code that has a sub query, into an SQL query with a join. Make sure
oth queries return the same results.
SELECT InvoiceNumber, InvoiceDate, VendorID
WHERE (VendorID = ANY
XXXXXXXXXXWHERE VendorState IN ('IN', 'MI')))
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.
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 XXXXXXXXXXUse the Employees
Query example Solution
You can use block comment, or line comment
-- This is a line comment
SELECT Firstname, LastName, LastName + ', ' + Firstname AS [Full Name]
WHERE Title LIKE '%Sales Rep%'
BirthDate BETWEEN ' XXXXXXXXXX' AND '12/31/1969'
(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