Create a query that will select records for customers

Assignment Help Database Management System
Reference no: EM131322955

Unlimited Pickers is a group of workers who have joined together to provide harvesting services to farmers who need to have their crops brought in. The organization has many teams of workers who travel from location to location picking or harvesting different crops, such as strawberries, grapes, watermelons, cantaloupe, oranges, grapefruit, and apples, among others. Unlimited Pickers has been in business for a few years, but has seen demand skyrocket in recent months (cf. recent legislation in Alabama to reduce the number of undocumented foreign workers). (All workers for Unlimited Pickers are either U.S. citizens or are legally authorized to work in the U.S.; Unlimited Pickers tries to help their workers complete the legal work-permit process.)

Customers, i.e., the farms who want to hire Unlimited Pickers teams, pay a small fee to the organization to reserve one or more teams of pickers. The workers are paid based on the quantity of the produce that they harvest, typically by the pound. (Note that some customers are large organizations, while others are quite small in the amount of produce grown.)

Unlimited Pickers would like for you to assist with the creation of a database program to help better manage their data and will include several queries. Some test data has been pulled from their paper-based business records and placed into a spreadsheet for you to use in designing and building the database. (The spreadsheet file is: HW 6 - Unlimited Pickers.)

Specifically they need for you to do the following:

1. Create the database, including loading the data from the spreadsheet into Access and establishing the Relationships. Label the database with your last name.

2. Create a query that will select records for customers who had their first pickup in July 2011. Sort into alphabetical order by Business Name. Save the query as July 2011 - First Picking Query.

3. Create a query that will select customers who had less than 10 pounds of produce picked for Grapes, Watermelons, or Strawberries. The query should include the Service Date, Customer Number, Business Name, Street, City, State, Zip, and the Weight fields from those attributes. Sort into ascending order by Customer Number. Save the query as Low Volume Query.

4. Create a query to determine the total weights of all produce picked for each customer (by type and total). This should include all produce for a customer. Sort by Business Name. Save the query as Customer Total Weight Query.

5. Create a query to calculate the payment for each picking for grapes and strawberries. Include the Business Name, Picking Date (ascending), and the Weight and value for both types of produce. Save the query as Produce Value Query.

6. Create a query and a report to calculate the total payment for apples and watermelon for each customer. Include the Business Name (ascending) and the payments. In the report, break it out by state.

Reference no: EM131322955

Design a database system for a small university

You are going to design a database system for a small university. The database will be used for scheduling classes. The university consists of multiple schools, which ea

Explain the three factors that influence the cost of a query

Discuss the reasons for converting SQL queries into relational algebra queries before optimization is done.  Explain the three factors that influence the cost of a query

Use data mining technique

The software MUST use DATA MINING TECHNIQUES (at least one like classification or association rule mining etc). The work involves analysing loads of the Hospitals data to se

Recognize business processes evaluated-dw-bi application

Identify the business processes which would be evaluated and tracked with a DW/BI application and state your reasoning or justification for including these in the DW/BI.

Technical paper on database

This Final Project Paper must be a technical paper on Database topics that have been pre-approved in earlier weeks by your instructor, using knowledge gained in the course.

Create a entity-relationship diagram

Create a Microsoft Visio entity-relationship diagram (ERD), based on the relations in the Entities and Attributes for Fleet Truck Maintenance file and any additional informa

Write sql queries for the books database

Write SQL queries for the books database that perform each of the following tasks: Select all authors from the Authors table with the columns in the order lastName, firstName

Prepare fully attributed data model

Use the results of the transcripts of an interview with IT consultant. Prepare a Fully Attributed Data Model. Add the data attributes for each entity."


Write a Review

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd