1
CIS111 Chapter 7 Homework
Using the AP database. Please make sure you review the SQL syntax for each query that is created.
Always create a new table with your initials included in the table name. For example, my table name for the
Vendors copy information would be VendorcopyRRJ
Each question is worth 1 point if the following is included in your submission.
1) The sql code.
a. The USE Statement for the database used
. Use comments to show your name and the query number
2) The first 5 (or less) lines of the results tab, with headings (if applicable). If asked, show the data before
and after the change.
3) The output in the messages tab
Please create solutions to the following queries:
1. Create a query that will use a SELECT INTO statement to create one table named Invoicecopy that is a
complete copy of the Invoices tables. Submit the query that creates the new table , and the query that will test
the creation of that table which also show the first 5 lines of data.
2. Write an INSERT statement that adds a row to the Invoicecopy table with the following values:
VendorID: 52 InvoiceTotal: $458.34 TermsID: 2
InvoiceNumber: AX XXXXXXXXXXPaymentTotal: $0.00 InvoiceDueDate: today’s date + 30
InvoiceDate: today’s date CreditTotal: $0.00 PaymentDate: null
3. Write a statement that will verify that the data was inserted (not just a select * from your table). Target the
specific data just inserted). Submit the query and the output of the single row.
4. Write a statement that will create a new Vendorcopy table, based on the Vendors table BUT only add the rows
for vendors located in Michigan.
5. Write a statement that verifies that the rows were added from question 4.
6. Modify the Vendorcopy table. Change the default account number to 101 for each vendor that has a default
account number of 400. Make sure you show the data before and then after the change in your answer
submission. Submit the query and the first 5 rows.
7. Write an UPDATE statement that modifies the Invoicecopy table. Change the PaymentDate to today’s date
and the PaymentTotal to 500 for all accounts with a balance due. Set today’s date using the GETDATE()
function. Submit the query and the first 5 rows.
8. Write a statement that deletes all invoices that have a TermsID of 1 or 2, from the Invoicecopy table.
9. Write a delete statement that will remove all of the data from both the Vendorcopy and invoicecopy tables
(don’t delete the table though).
10. Write a query that will verify that the data was removed per question 9.
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