Write SQL in Access – Not com

Introduction

Project 2 is to write SQL queries to operate and retrieve specific information from a simulated relational database stored in Access. This database has four tables storing particular attributes of a hypothetical company’s customers, inventory items, sales transactions, and sales and inventory linking table. For the purpose of this project, the data are fictitiously populated. The following image shows how these tables are related with the primary and foreign keys of each table connecting one table with another one.

Each of these tables has following attributes. A good insight into relationships among these tables and attributes (i.e., field) of each table is very important to understand the query questions asked in Project 2 and successfully write your queries to present the right answers for those questions. Please refer to Chapter 4 and the lecture note if you need to brush up your knowledge on relational database.

Customer [Customer_No, Customer_Name, Street, City, State, Credit_Limit]

• Customer_No records the customer code number for each customer. It is the primary key of the table uniquely identifying each customer.
• Customer_Name records the name of each customer. A customer may or may not have made any sales transaction.
• Street records the street address of each customer.
• City records the city name each customer lives in.
• State records the the state name each customer lives in.
• Credit limit records the total dollar amount of maximum amount of credit purchase each customer can make.

Inventory [Item_No, Description, Unit_Cost, Unit_Price, Quantity_on_Hand]

• Item_No records the inventory item code number for each inventory item. It is the primary key of the table uniquely identifying each inventory item.
• Description records the description about each inventory item.
• Unit_Cost records the dollar amount of unit purchase price of each inventory item.
• Unit_Price records the dollar amount of unit retail price of each inventory item.
• Quantity_on_Hand records the current quantity of each inventory item on hand.

Sales [Invocie_No, Date, Salesperson, Customer_Number_Amount]

• Invoice_No records an invoice number for each sales transaction made. It is the primary key of the table uniquely identifying each sales transaction.
• Date records the date of each sales transaction.
• Salesperson records the name of the salesperson involved in a sales transaction. Each sales transaction can have only one sales person, but a particular sales person may be involved in multiple sales transactions.
• Customer_Number is the foreign key of the table linking it with Customer table. Values recorded in Customer_Number in Sales table must correspond to those in Customer_No in Customer table (i.e., referential intergrity). Like each sales transaction can’t have more than one sales person, each sales transaction can be made with only one customer, but a particular customer may make multiple sales transactions.
• Amount records the total dollar amount of sales made in each sales transaction.

Sales_Inventory [Invoice_Number, Item_Number, Quantity, Extension] – This table is a linking table created to connect between Sales and Inventory table. Since one sales transactions can have many inventory items and one inventory item can have many sales transactions, a linking table must be created. Such a linking table takes the primary key of each of linked tables as the primary key as well as the foreign key for itself rather than putting a foreign key in one table to link with another table.

• Invoice_Number is the primary key of Sales_Inventory table. It is also the foreign key linking the table with Sales table. That is, values recorded in Invoice_Number must correspond those in Invoice_No in Sales table.
• Item_Number is another primary key attribute of Sales_Inventor table. It is also the foreign key linking the table with Inventory table. So, values recorded in Item_Number must correspond to those in Item_No in Inventory table.
  • Having two attributes (Invoice_Number and Item_Number) as the primary key means that a distinct combination of these primary key attributes are required to uniquely identify each record in Sales_Inventory table. In other wors, we must have particular Invoice_Number and Item_Number to determine what specific value should be recorded on non-key attributes, Quantity and Extension. Therefore;
• Quantity records the quantity of a particular inventory item sold on a particular invoice.
• Extension records the total dollar amount of a particular inventory item sold on a particular invoice.

Given these data tables and attributes as explained above, please write SQL queries to answer for each of the following questions and make sure your query returns the right answer for each question.

Required Query Questions:

Query 1. Write SQL queries to retrieve the customer number and name of each customer.

Query 2. Write SQL queries to retrieve the customer number and name of each customer who live in Phoenix, AZ.

Query 3. Write SQL queries to retrieve the purchase dates, customer number, and name of each customer who bought a clock(s).

Query 4. Write SQL queries to retrieve the name of each customer who made a purchase with Mahomet (a salesperson)

Query 5. Write SQL queries to count how many customers live in Illinois. Name the count field as Count.

Query 6. Write SQL queries to retrieve the item number and description of each inventory item.

Query 7. Write SQL queries to retrieve the item number and description, and quantity of each item sold on the invoice number 107.

Query 8. Write SQL queries to count how many sales transactions were made in October 2020? Name the count field as Total_Sales_Count.

Query 9. The company is considering marking up the current price of each inventory item by 12%. Write SQL queries to retrieve the description, purchase price, and 12% increased unit price of each inventory item. Name the 12% increased unit price field as New_Price.

Query 10. Assume that no invoice has been paid yet. Write SQL queries to retrieve the name and total invoice amounts grouped by each customer in descending order. Name the total invoice amounts field as Total_Balance.

Query 11. Quantity_on_Hand in Inventory table shows current stock on hand. Write SQL queries to retrieve the item number and description of each inventory item with at least 100 units on hand.

Query 12. Write SQL queries to retrieve the name and total invoice amounts grouped by each sales person in ascending order. Name the total invoice amounts field as Total_Invoice_Amounts.

Query 13. Write SQL queries to count how many different kinds of inventory items were sold in October. Name the count field as Count

Query 14. Write SQL queries to the description and total quantity of units sold grouped by each inventory item sold in October. Name the total quantity of units field as Unit_Count.

Query 15. Write SQL queries to retrieve the name and credit limit of each customer whose credit limit is greater than the largest credit limit in Illinois.

Data File:

The data are stored into four tables in Access file. This file is an Access file. You will need to write your queries and run them in that file to make sure your query fetches the right information. To open and start writing your SQL queries in this file, you can follow the same steps illustrated in Introductions for SQL Exercise Access File posted under Module 2. If you get a security warning message upon downloading the file, please go ahead and download it. The file is clean and safe to download.