Access software: We need to use Microsoft Access 2007 or higher version to complete this project. Access is installed on most of computers at CI computer labs and you can also borrow one from the CI Library. It can also be accessed via Virtual Lab (Please refer to a separate posted page “Using Microsoft Access via Virtual Lab” in this Module regarding how to use Virtual Lab to use Microsoft Access for both Mac and Windows users.)
How to get started. Check out the video posted on Canvas, it will help tremendously.
Download the Northwind database (listed as an item under the Database and SQL Module) and rename it with the following naming convention:
Rename your database Northwind_LastName_FirstName.mdb
By repalcing LastName and FirstName with your own last name and first name such as Northwind_Chen_Minder.mdb (don’t change the file extension .mdb If you save the database in new Access format .accdb after working on it, it is acceptable.)
Open the database, under SECURITY WARNING (see figure below). Click Enable Content to get started.
Start working on the predefined dummy queries by modifying them with real queries. Save these queries after you are done under the same query name. Q14 is not a query, you need to perform 4 tasks: (1) create a department table, (2) a simple data entry form, (3) a relationship, and (4) a master-detail form. Submit the completed database by first close the database (no need to Zip it) via Canvas system's: Database Assignment under Assignments area.
You should be seeing Q01... to Q13, and Your Final Score queries under Queries. Don't forget there is Q14 which is not a query but needs to be done.
All the queries have been created for you with a dummy SELECT statement.
These queries have the following naming convention:
QNN_MeaningfulNameOfYourQuery where NN is the question number,
01, 02, 03, 04, etc. Such as
Q01_TotalNumberOf_USA_Customers
Q02_MostExpensiveProducts
Double click on an existing query that you are working on. You will see the default “dummy” result of the query showing up.
Fist make sure that you select the Home Tab (The first tab). Click on the View Icon at the top-right corner to select either SQL View and Design View to modify the query such that it will generate the correct query result. Remember to Save the query under the same name (use Save, instead of Save As) when you are done.
When you close the Query window, if you have not saved your changes, the following dialog box show up. Click Yes to save your asnwer.
Submit the completed northwind database via Canvas under Assignments area under Assignment 2: Database Assignment,
You will receive 0.8 for each question from Q1~Q13 if you have the correct answer. Question 14 counts for 2.6 points. Q12 is an UPDATE statement, not a typical query.
Grading: You will receive the graded database back to your assignment and then you can run the “Your Final Score” query in the database to find out your final score. The grading result and instructor’s comment can be found in the Grading table.
NorthWind Scenerio:
The database contains the sales data for a fictitious company called Northwind Traders, which imports
and exports specialty foods from all around the world.
Suppliers: Suppliers' names, addresses, phone numbers and hyperlinks to home pages.
Products: Product names, suppliers, prices and units in stock.
Categories: Categories of Northwind products.
Orders: Customer name, order date and freight charge for each order.
Order Details: Details on products, quantities and prices for each order in the Orders table.
Employees: Employees' names, titles and personal information.
Customers: Customers' names, addresses and phone numbers.
Shippers: Shippers' names and phone numbers.
=== Beginning of the Assignment Questions ====
Q1 to Q13: 0.8 point per Question 0.8*13=10.4 points
=== Read the requirements and Hint for each query/question carefully.
Q1: List all the customers from United Kingdom with Company Name and Country columns. Sorted by Company Name in ascending order.
Hint: You need to find out the country code for United Kingdom by studying the country
coulumn of the Customers table.
Q2. How many customers are located in France? Your query should return just a number representing the number of customers who are located in France. Define a meaningful alias for the resulting column so that the heading says “The total number of customers in “France”.
Q3: How many unique titles do we have in the employee table?
Hint: You need to create TWO queries,
1. The first query returned all the unique titles, i.e., Q03_UniqueTitles (use the Distinct keyword and use the SQL View to type in the SQL statement directly (Design View will not work). Please refer to SQL_MIS310 slide#105~107 for guidance. You cannot use Distinct and Count together in Access and this is why you have to do it in two related two queries.). The second query is a query against the first query (i.e., Q03_UniqueTitles) and count the number of unique titles (Use the aggregate function Count) and the query should return a number.
2. The second query is named Q03_UniqueTitlles_Count and it has been created for you
already. You should use Q03_UniqueTitles (not a table) to create this query. This query should return a result that is a simple number.
Hint: See SQL slide#105~107
Q4: List all current products (the products that are not discontinued – Discontinued is False to exclude discontinued products) from the most expensive products to the least expensive ones.
Requirements: Your query should return all products that are not discontinued and sorted by price from highest to the lowest. The query result should contain Product id, product name, UnitPrice, and discontinued.
To select only active products, you need to set the criteria under Discontinued column to False (no quotation) – Discontinued is a Boolean data type. This will allow you to exclude all discontinued products).
Q5: List all the products by its product ID, Product Name, and UnitPrice (has to be in this sequence). Sort the query result first by UnitPrice in DESCENDING order, and then by Product Name in ASCENDING order.
Hint: See SQL slide#83 & 84
Q6: List all products with the price range between 30 to 50 (>= 30 and <= 50), and Price (sorted by price in Ascending order). List productID, ProductName, UnitPrice, and CategoryName of the product.
Hint: You need to JOIN the Product table with the Category table using the cetagroyID in
both tables in order to display categoryName (Not CatgoryID) in the query result.
Q7: List only those products with their inventory below the reordering level. Please list the product ID, Product Name, UnitsInStock, ReorderLevel, and the shortage amount (sorted by Shortage amount in Descending order). ShoratgeAmount is a calculated column and it is defined as
Hint: Only list products that that have ShortageAmount > 0
Q8: Please find out all the products with product name start with “Ch” List the query result with Product Name, UnitPrice, and UnitsInStock and Sorted by ProductName.
Hint: See SQL slide#76 use Ch*
Q9. Total Sales for each customer in October 1996 (based on OrderDate). Show the result in CustomerID, CompanyName, and [total sales], sorted in [total sales] in Decending order.
Hint: [total sales] is a calculated field. Limit the query results to order date that is during October 1996. You need to JOIN customers, order, and [order details] tables. Use the following critera to limit OrderDate between #10/01/1996# and #10/31/1996#
Please [Choose WHERE at the TOTAL Row under OrderDate] and please note "between" is a keywword and is part of the criteria.
for October 1996 sales data. [chooset WHERE at the TOTAL Row]
[Total Sales] is a calculated field defined as the following (copy and paste it to prevent typing error): Total Sales: CCur(Sum(([UnitPrice]*[Quantity])*(1-[discount])))
[choose EXPRESSION at the TOTAL Row] for this calculated column
If you use the following alternative formula
Total Sales: CCur(([UnitPrice]*[Quantity])*(1-[discount]))
[choose SUM at the TOTAL Row]
Q10: List all the products total sales amount from 1997?
List the query result by ProductID, ProductName, and Annual Sales Amount for 1997 and
sorted it by Annual Sales in Descending order (the annual sales is a calculated column).
Hint:
1. You need to join several tables to answer this question. (Products, Orders, and [Order Details])
2. Use the critera for OrderDate
[Annual Sales] is a calculated column. You need to use the aggregate function Sum( ) to calculate the annual sales for each product, i.e., the sum of the subtotal (extended price) of an order item for a product ordered in an order. The calculated column is defined as:
CCur(Sum([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])))
*** You have to use [Order Details].[UnitPrice] instead of [UnitPrice] because there are two UnitPrice colum in Products table and [Order Details] table. Please note that there is a space character in the [Order Details]
The result will be displayed in currency format and to 2 decimal places. You need to use Group By at the TOTAL Row for ProductID and ProductName
Q11: Write a SQL Update Statement to change the Compnay Name of the shipper #2 (ShipperID = 2) from 'United Package' to 'DHL' You need to save the query as Q11_UpdateShipperName. The best way to do this is to use SQL View of the query to write the SQL Update statement directly. When you run it, you are going to be prompted
with messageBox telling you a record will be changed. Say YES, and no query result will
be retruned because this is not a read only query.
Hint: See SQL slide#92
Q12: Show the two most expensive products that are less than 90 dollars with ProductID, Product Name, and Unit Price and remerber to sort the result by Unit Price in Decending order.
Hint: Use Select Top 2 ...... to get the top 2 records. (Do this under SQL View) See SQL slide#82 You need to type Top 2 in the Select statement
Q13: What is the average product price for products in each product category? Display category ID, Category name, and average price in each category. Sort by the average price in Decending order.
Hint: Join the Products and Categories tables and GROUP BY Category ID and Categories Name and use the aggregate function AVG for average
== The last Question Q14 is not a query, but has to be done ==
Q14: (2.6 points) There are 4 questions that are not queries. You need to create a table, enter some data for the table, create a relationship, and create two forms.
Hint: Watch Create Tables and Forms http://www.youtube.com/watch?
v=AH3ilFm_C88
a. (Add a new table called Department based on the following information (0.7 point):
Table name: Department
Columns:
DepartmentID: Text data type with 5 characters in length
Name: Text data type with 50 characters in length