Queries are questions asked of a database to help you find database information quickly. In this project, you will create a database with grocery store data and use the standard filter, query wizard, and operators to find information.

Question

Objectives
Create a database using database software.
Use filters, queries, and operators to find database information.
Directions
Part 1: Use the following information to create a database.

Product Name Unit Price Average Price Lowest Price Highest Price
Hulls ketchup 1.56 1.45 1.40 1.56
Windy Wheat bread 1.78 1.73 1.70 1.78
Grapes 2.65 2.61 2.60 2.68
Smith jelly 4.21 4.20 4.18 4.21
Baldelli spaghetti 1.75 1.70 1.68 1.80
Jersey cream cheese 3.90 3.81 3.75 3.90
Cloud 2% milk 5.20 5.00 4.80 5.25
Ballotti Olive Oil 9.50 9.22 9.10 9.50
Granny's apple sauce 2.20 2.17 2.15 2.20
Cloud cottage cheese 4.56 4.53 4.48 4.56
Mendunster cheese 6.12 6.11 6.10 6.15
Hulls mustard 1.53 1.45 1.40 1.56
Windy rye bread 1.72 1.73 1.70 1.80
Bananas 2.63 2.65 2.60 2.68
Smith peanut butter 4.19 4.20 4.18 4.21
Baldelli noodles 1.74 1.75 1.68 1.80
Paris Parmesan Cheese 3.80 3.81 3.80 3.95
Cloud 1% milk 4.99 5.00 4.80 5.25
Mit Apple Juice 4.15 4.22 4.10 4.50
Graham's Gravy 2.16 2.17 2.15 2.20
Silver Crackers 2.51 2.53 2.48 2.56
Silver Chips 2.10 2.11 2.10 2.15

Answer 1

To complete your project by creating a database of grocery store data, follow the steps below. For this task, we'll assume you are using a common database software such as Microsoft Access or MySQL. Here’s how to set up your database and conduct queries.

Step 1: Create the Database

1. Open your chosen database software.

2. Create a new database and give it a name (e.g., GroceryStoreDB).

3. Create a new table within your database:

   • Name the table Products.
   • Define the fields/columns for the table:
     • ProductName (Text)
     • UnitPrice (Currency or Decimal)
     • AveragePrice (Currency or Decimal)
     • LowestPrice (Currency or Decimal)
     • HighestPrice (Currency or Decimal)

4. Enter the data as specified in the table below:

| Product Name | Unit Price | Average Price | Lowest Price | Highest Price | |-----------------------------|------------|---------------|--------------|---------------| | Hulls ketchup | 1.56 | 1.45 | 1.40 | 1.56 | | Windy Wheat bread | 1.78 | 1.73 | 1.70 | 1.78 | | Grapes | 2.65 | 2.61 | 2.60 | 2.68 | | Smith jelly | 4.21 | 4.20 | 4.18 | 4.21 | | Baldelli spaghetti | 1.75 | 1.70 | 1.68 | 1.80 | | Jersey cream cheese | 3.90 | 3.81 | 3.75 | 3.90 | | Cloud 2% milk | 5.20 | 5.00 | 4.80 | 5.25 | | Ballotti Olive Oil | 9.50 | 9.22 | 9.10 | 9.50 | | Granny's apple sauce | 2.20 | 2.17 | 2.15 | 2.20 | | Cloud cottage cheese | 4.56 | 4.53 | 4.48 | 4.56 | | Mendunster cheese | 6.12 | 6.11 | 6.10 | 6.15 | | Hulls mustard | 1.53 | 1.45 | 1.40 | 1.56 | | Windy rye bread | 1.72 | 1.73 | 1.70 | 1.80 | | Bananas | 2.63 | 2.65 | 2.60 | 2.68 | | Smith peanut butter | 4.19 | 4.20 | 4.18 | 4.21 | | Baldelli noodles | 1.74 | 1.75 | 1.68 | 1.80 | | Paris Parmesan Cheese | 3.80 | 3.81 | 3.80 | 3.95 | | Cloud 1% milk | 4.99 | 5.00 | 4.80 | 5.25 | | Mit Apple Juice | 4.15 | 4.22 | 4.10 | 4.50 | | Graham's Gravy | 2.16 | 2.17 | 2.15 | 2.20 | | Silver Crackers | 2.51 | 2.53 | 2.48 | 2.56 | | Silver Chips | 2.10 | 2.11 | 2.10 | 2.15 |

5. Save the table.

Step 2: Use Filters, Queries, and Operators

1. Basic Filtering:

   • You can use the filter options in your database software to search for specific price ranges. For example, to find all products with a Unit Price less than $3.00, apply a filter on the UnitPrice field.

2. Create Queries:

   • Query to find all products with a Unit Price greater than $2.00:

     • In the query design view, add the Products table, select the desired fields, and set the criteria for UnitPrice as >2.00.

   • Query to find products that have the highest price greater than $5.00:

     • Similar to the previous query, set the criteria for HighestPrice as >5.00.

3. Use of Operators:

   • Use operators in your queries to filter results. For example:
     • =, >, <, >=, <=, <> for comparisons.
     • AND, OR for combining conditions. Example: To find products with a Unit Price between $1.50 and $3.00: UnitPrice >= 1.50 AND UnitPrice <= 3.00.

Example Queries

1. Find all products whose Average Price is less than $4.00:

   SELECT ProductName, AveragePrice
   FROM Products
   WHERE AveragePrice < 4.00;
   

2. Find products that are above the average price of $3.00:

   SELECT ProductName, UnitPrice
   FROM Products
   WHERE UnitPrice > 3.00;
   

3. Get products based on specific category conditions, e.g., 'cheese':

   SELECT ProductName, UnitPrice
   FROM Products
   WHERE ProductName LIKE '%cheese%';
   

Step 3: Running the Queries

• Execute the queries you’ve created in the query tool of your database software. Review the results and note any observations.

Conclusion

You have now created a simple grocery store database and have performed queries and applied filters to retrieve information efficiently. As you become more familiar with your database software, you can explore more advanced features for querying and reporting.