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

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

  • : 23/03/2017
  • : 30