Multiple Choice (20 marks)

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:

1. frequency of calls by item type
2. average time by item type by technician level
3. average time per call by item type
4. average time spent by technician by item type

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