On Microsft SQL Server Management Studio Correct Table Joins and Join Fields: CIS336 Week 2 Lab

On Microsft SQL Server Management Studio CIS336 Week 2 Lab DeVry University Correct Table Joins and Join Fields:

Course: CIS336 Introduction to Database with Lab
School: DeVry University

Vendors to Invoices (on VendorID)
Invoices to InvoiceLineItems (on InvoiceID)
InvoiceLineItems to GLAccounts (on AccountNo)

Correct table joins and join fields:

Vendors to Invoices (on VendorID)
Invoices to InvoiceLineItems (on InvoiceID)
InvoiceLineItems to GLAccounts (on AccountNo)

Two Table Joins:

1. Write a select that returns VendorName, InvoiceNumber, InvoiceDate and InvoiceTotal from the appropriate tables. Filter the results to only return rows where the InvoiceDate is prior to 6/1/2008. Sort the results by VendorName.

Correlation Names:

2. Write a select statement that returns InvoiceNumber, VendorName and InvoiceDate from the Vendors and Invoices table. Use the following correlations for each table:

a. Vendors v

b. Invoices i

Filter the results to return only rows where a balance is due.

Multiple Table Joins:

3. Write a select statement that returns VendorName, a column named Contact (created by concatenating VendorContactFName, a space between, and VendorContactLName), InvoiceNumber, InvoiceDate and InvoiceLineItemAmount from the appropriate tables. Sort the results by VendorName.

4. Write a select statement that returns InvoiceDate, VendorName, InvoiceNumber and InvoiceLineItemAmount from the appropriate tables. Filter the results to return only rows where VendorID = 115, 122 and 123. Sort the results by VendorName.

5. Write a select statement that returns VendorName, VendorState, InvoiceNumber, InvoiceTotal, AccountNo, InvoiceLineItemDescription and AccountDescription from the appropriate tables. Sort the results by VendorState.

6. Modify the solution for #5 using implicit syntax (code the joins in the WHERE clause). Filter the results to only return rows where the balance due is 0 (zero).

Outer Joins & Combined Skills:

7. Write a select statement that returns VendorName, VendorCity, VendorState and InvoiceDate. Create a left join that returns all records from the Vendors table and only those that match in the Invoices table. Sort the results by VendorName.

8. Write a select statement that returns 4 columns from 3 tables, all using column aliases:

a. Vendor VendorName column

b. Date InvoiceDate column

c. Number InvoiceNumber column

d. Description AccountDescription column

Assign the following correlation names to the tables:

v Vendors table

i Invoices table

l InvoiceLineItems table

g GLAccounts table

Sort the results by Vendor, Description

Table creation with data inserts:

9. Write a SELECT statement that copies all of the data from the Vendors table into a new table named VendorTwo. (no result set for this query- message should appear that says: 122 rows affected)

10. Write a SELECT statement that copies all of the data from the Invoices table into a new table named InvoiceTwo. (no result set for this query- message should appear that says: 114 rows affected)

11. Write a SELECT statement that copies filtered data from the Invoices table into a new table named Snapshot. Filter the data that’s copied into the new table as such:

• Records that have no balance due

• InvoiceTotal greater than $500

• InvoiceDates between 6/20/2008 and 7/20/2008

Inserting data into the newly created tables:

12. Write an INSERT statement that adds the following values into the VendorTwo table without a column list:*

(Star Printing, 9843 E. University, NULL, Mesa, AZ, 85207, (480) 873-3994, Johnson, Robert, 1, 540)

*Use single quotations where applicable

13. Write an INSERT statement that adds the following values into the InvoiceTwo table using a column list:*

-VendorID (124) -PaymentTotal (0)

-InvoiceNumber (7-113-752) -CreditTotal (0)

-InvoiceDate (8/3/2008) -TermsID (1)

-InvoiceTotal (157.33) -InvoiceDueDate (8/10/2008)

*Use single quotations where applicable

Updating data in the newly created tables:

14. Write an UPDATE statement that modifies the InvoiceTwo table. Change the following values where the VendorID = 95:

• TermsID = 3

• InvoiceDueDate = (the function that adds one month to InvoiceDate)*

*Refer to chapter 8 for Date functions

15. Write an UPDATE statement that uses a subquery to modify the data in the InvoiceTwotable. Modify the following column:

CreditTotal = 10.00

Subquery portion: (search condition subquery) Use the VendorID in the InvoiceTwo table and search for equivalent VendorID’s in the VendorTwo table where the VendorName starts with “Fed” (refer to Chapter 3 for pattern matching).

Further filter the update so it only affects records where the InvoiceDate is between 7/18/2008 and 7/22/2008 (no result set for this query- message should appear that says: 4 rows affected).

Deleting data in the newly created tables:

16. Write a DELETE statement that removes all rows from the InvoiceTwo table where there’s no balance due and where the TermsID = 3.

You are now finished using the “fake” tables for this assignment. Run the following query to remove them from the AP database (SAVE YOUR QUERIES TO SUBMIT!):

DROP TABLE VendorTwo

DROP TABLE InvoiceTwo

DROP TABLE Snapshot