In: Accounting
Multiple Choice
Query Questions (use the tables below to answer the 5 query questions below)
The Business. A retail company sells electronics items by phone and on its web site. The company records information about each item sold in a table called Sales. A few rows of the Sales table in the database are shown below:
Sales table (primary key = SaleID and ItemID)
SaleID |
ItemID |
CustomerID |
ItemType |
SaleDate |
101 |
151 |
52 |
4 |
1/1/2019 |
101 |
176 |
52 |
1 |
1/1/2019 |
102 |
227 |
532 |
1 |
1/1/2019 |
Service Calls.The company provides various warranty services by telephone. The company estimates the number of minutes of warranty services that will be required. This is shown in the table called TimeEstimate. EstimatedTime is the average number of minutes of estimated service time by TechnicianType for each item sold, organized by the ItemType.
TimeEstimate table (primary key = ItemType and TechnicianType)
ItemType |
TechnicianType |
EstimatedTime |
1 |
1 |
30 |
1 |
2 |
20 |
1 |
3 |
10 |
2 |
1 |
30 |
2 |
2 |
20 |
Technician types are stored in a table called Employee.
Employee table (primary key = EmployeeID)
EmployeeID |
Lastname |
Firstname |
TechnicianType |
HireDate |
53 |
Smith |
Mark |
3 |
3/4/20014 |
55 |
Jones |
Nathan |
2 |
3/10/2014 |
56 |
Brooks |
Mary |
1 |
3/12/2014 |
Technician cost by minute is stored in table TechnicianCost, which is shown below.
TechnicianCost table (primary key = TechnicianType)
TechnicianType |
CostperMinute |
LevelDesc |
1 |
$1.00 |
Staff |
2 |
$2.00 |
Senior |
3 |
$3.00 |
Manager |
Estimated technician expense is defined as the sum over all items sold of TechnicianCost.CostperMinute times TimeEstimate.EstimatedTime by TechnicianType.
The minutes of actual service provided is recorded in a table called ActualService. The table contains all the warranty calls for the items in the Sales table.
ActualService table (primary key = ItemID, DateService, and StartTime)
ItemID |
DateService |
StartTime |
EmployeeID |
Minutes |
151 |
1/3/2019 |
11:43 |
56 |
18 |
151 |
1/8/2019 |
10:36 |
62 |
16 |
176 |
1/3/2019 |
8:56 |
56 |
12 |
Actual technician expense is calculated as the sum over all items sold of TechnicianCost.CostperMinute times ActualService.Minutes by TechnicianType.
The Issue. The service manager believes that actual expenses for warranty service are exceeding budgeted expenses. To investigate this, several queries will be completed. The following questions involve potential queries.
1. Some technicians revealed that most of their recent calling time related to one or two item types. To follow up on this information, you should investigate:
a. 1 and 2
b. 1 and 3
c. 2 and 4
d. 3 and 4
e. none of the above
3. Assume that the number of minutes of service provided exceeds the number of minutes that were estimated. Also, the total cost of the minutes provided exceeds the budgeted cost of the estimated minutes. This indicates a need for you to investigate the time and cost differences for:
a. each item type, each technical type, and each technician
b. each item type and each technical type
c. each technician type and each technician
d. each item type and each technician
e. none of the above
1. Some technicians revealed that most of their recent calling time related to one or two item types. To follow up on this information, you should investigate:
Actual expenses for warranty are exceeding budgeted expenses because of the average time by item type by technician level & average time spent by technician by item type.
Hence correct answere will be (2) & (4)
3. Assuming that the number of minutes of service provided exceeds the number of minutes that were estimated, the total cost of the minutes provided exceeds the budgted cost of the estimated minutes, it can be implied that the costs are mainly driven by two factors; item type and the technical type of person involved.
Hence, option B "each item type and each technical type" is correct.