Question

1. Create a query using the Customer Information data that will select recordsfor customers who had their first pickup in May 2004. Sort the records by customer’s Last Name. Save the query as May Pickup Query. 2. Create a query on Pickup Records and Customer Information to determine the total weights of paper and other products each customer has had picked up. Use the CUSTOMER Last Name and First Name in the query. Save the query as Customer Weight Query. 3. Create a query using the Name, Street, Address, and Weight fields from the Pickup Records and Customer Information tables. Enter the criteria that will select customers with less than 10 poundsin either recyclable field. Save the query as Low Volume Query.

Answer 1

To select records for customers who had their first pickup in May 2004 and sort them by last name, you can use the May Pickup Query. To determine the total weights of paper and other products each customer has had picked up, you can use the Customer Weight Query. To select customers with less than 10 pounds in either recyclable field, you can use the Low Volume Query.

Step-by-step explanation:

To create the May Pickup Query, you can use the following SQL statement:

SELECT * FROM CustomerInformation WHERE FirstPickup LIKE '05-2004%' ORDER BY LastName;

To create the Customer Weight Query, you can use the following SQL statement:

SELECT CustomerInformation.LastName, CustomerInformation.FirstName, SUM(PickupRecords.PaperWeight) AS TotalPaperWeight, SUM(PickupRecords.OtherWeight) AS TotalOtherWeight FROM CustomerInformation INNER JOIN PickupRecords ON CustomerInformation.CustomerID = PickupRecords.CustomerID GROUP BY CustomerInformation.LastName, CustomerInformation.FirstName;

To create the Low Volume Query, you can use the following SQL statement:

SELECT CustomerInformation.Name, CustomerInformation.Street, CustomerInformation.Address, PickupRecords.Weight FROM CustomerInformation INNER JOIN PickupRecords ON CustomerInformation.CustomerID = PickupRecords.CustomerID WHERE PickupRecords.Recyclable1 < 10 OR PickupRecords.Recyclable2 < 10;

Answer 2

To create the May Pickup Query, use the SQL code provided with the proper table and field names. For the Customer Weight Query, join the Customer Information and Pickup Records tables and use the SUM function to calculate the total weight for each customer. For the Low Volume Query, join the Customer Information and Pickup Records tables and set criteria for weight and recyclable fields.

Step-by-step explanation:

May Pickup Query:

To create the May Pickup Query, you can use the following SQL code:

SELECT * FROM CustomerInformation WHERE FIRST_PICKUP LIKE '05/01/2004%' ORDER BY LAST_NAME ASC;

Customer Weight Query:

To create the Customer Weight Query, you can use the following SQL code:

SELECT CUSTOMER.Last_Name, CUSTOMER.First_Name, SUM(PICKUP.Weight) AS Total_Weight FROM CustomerInformation AS CUSTOMER JOIN PickupRecords AS PICKUP ON CUSTOMER.Customer_ID = PICKUP.Customer_ID GROUP BY CUSTOMER.Last_Name, CUSTOMER.First_Name;

Low Volume Query:

To create the Low Volume Query, you can use the following SQL code:

SELECT CUSTOMER.Name, CUSTOMER.Street, CUSTOMER.Address, PICKUP.Weight FROM CustomerInformation AS CUSTOMER JOIN PickupRecords AS PICKUP ON CUSTOMER.Customer_ID = PICKUP.Customer_ID WHERE PICKUP.Weight < 10 AND (PICKUP.Recyclable1 < 10 OR PICKUP.Recyclable2 < 10);