Page 1 of 2
IDS 410 – Business Database Technology
Spring 2023
Individual Assignment 4
Structured Query Language – SQL
Due Date: Submit your competed Access database file (with extension .accdb) to the
Blackboard by 11:59 pm on Tuesday, April 11, 2023
Instructions:
• Download the attached Microsoft Access Database file (IDS 410 Assignment 4 – Start
File.accdb).
• Rename IDS 410 Assignment 4 – Start File.accdb to Assignment4_yourNetid.accdb, where
yourNetid is your Netid.
• Create (compose) 16 queries according to their requirements (see below). Be sure to save all
queries (you can name them as follows: Query_01, Query_02, …, Query_16) inside the
Assignment4_yourNetid.accdb.
A. Single Table Queries
1. List everything from the raw material table for raw materials made of birch, mahogany, or oak.
Order the list by firstly thickness, then by material, and lastly by standard price.
2. Display the product finish, and the maximum product standard price (name this column as
MaxPrice) for all products in each product finish.
3. List the IDs of the sales persons and the total number of customers that each sales person has
worked with in 2017.
4. For every product line that has more than two product finishes, display the product line ID, and
the number of product finishes associated with the product line ID.
5. For each group of product finishes, find the most expensive one (name this column as MaxPrice).
In the result table, show product finish and the maximum price. Show the results in descending
order.
6. List the average supply unit price of each vendor except for vendor with ID 1.
7. For each product ID (except for product IDs 1, 2, and 3), list all materials (use material ID), and the
required quantity of each material used to create the product, and whose required quantity is
greater than 2.
8. List order line ID, orde ID, product ID, and ordered quantity for order IDs 1 to 10 which have the
number of quantities ordered greater than 5, or for oder IDs 11 to 35 which have the number of
quantities ordered lower than 10.
