Computer Science Question

Assignment Instructions

Using the provided sample database, create queries to demonstrate a variety of multi-table select query functionality using appropriate joins.

1. Open the sample database.

For this assignment you will use this sample database:

IT163_ModelCarSampleDatabase.accdb

1. Use a query to determine which company had sales in Texas and Georgia and when were these sales.

Select the Company, State and Sales date from the Customers and Sales tables. Show results for customers from Texas or Georgia only. Use the appropriate join to garner the correct results.

Save as Query1. Run the query to see the subset of data produced. Verify that the results are correct.

Reference: Alexander, M., & Kusleika, D. (2019). Access 2019 bible. Wiley.

Chapter 8: “Selecting Data with Queries / Creating Multi-table Queries”

1. Use a query to determine which items had more than five items sold and when were these sales.

Select the Description, Sales Date and Quantity from the Products, Sales and SalesLineItems tables for quantities over 5. Use the appropriate join to garner the correct results.

Save as Query2. Run the query to see the subset of data produced. Verify that the results are correct.

Sometimes a direct relationship doesn’t exist between tables, but you still want to examine the data between them. Using the Products and Categories tables, explore the differences between inner, left and right joins. Create three queries for each type of join between Products and Categories showing the Description from the Products table and the Category from both the Categories and Products tables.

a. Select the description and category from the Products table and the category from the Categories table. Create an inner join using the category field that exists in each table. Save as Query3. Run the query to see the subset of data produced. Examine the results. Notice the total record count for the recordset in the results view.

b. Select the description and category from the Products table and the category from the Categories table. Create a left join using the category field that exists in each table. Save as Query4. Run the query to see the subset of data produced. Examine the results. Notice the total record count for the recordset in the results view.

c. Select the description and category from the Products table and the category from the Categories table. Create a right join using the category field that exists in each table. Save as Query5. Run the query to see the subset of data produced. Examine the results. Notice the total record count for the recordset in the results view.

Reference: Alexander, M., & Kusleika, D. (2019). Access 2019 bible. Wiley.

Chapter 8: “Selecting Data with Queries / Creating and Working with Query Joins”

1. Document your query results.

In a Word document, copy each of the questions from the five queries above in BOLD. After each question, place the SQL code. After the SQL code, place a screenshot of the successful execution from Access showing the correct results were garnered when you ran each query. Start a new page for each question.

Hint: you can copy your SQL code as plain text from the SQL view. Right-click your query and choose SQL View or use the View option from the ribbon.

1. At the end of this document, write a double-spaced paragraph discussing the results from the three queries created from step 4 using the various types of joins. What do the differing results tell you about how these three join types work when creating data from multiple tables using Microsoft Access?

Assignment Requirements

Submit a single Word document with all of the documentation as described in the assignment instructions.