Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

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...

1 answer below »

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
. 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.
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
oth queries return the same results.

USE CIS111_AP
SELECT InvoiceNumber, InvoiceDate, VendorID
FROM Invoices
WHERE (VendorID = ANY
XXXXXXXXXXSELECT VendorID
XXXXXXXXXXFROM Vendors
XXXXXXXXXXWHERE 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 XXXXXXXXXXUse 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 ' XXXXXXXXXX' 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
Answered 1 days After Mar 05, 2022

Solution

Shweta answered on Mar 06 2022
103 Votes
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here