1. Calculate the total cost of the items you have on-hand.
(Number of each item is meaningful and should be taken into account). 
select sum([on hand] * [cost]) as 
[total cost]
from item
The answer is: $2,183.90

2. Calculate the total profit if you sold all the items on-hand.
(Again the number of each item is also to be taken into account.) 

select sum( [on hand] * ([selling 
price] - [cost]) )
from item
(Do it, or think about it, in two stages.)

The answer is: $664.92.

3. Calculate the average cost of the items on-hand.

(Definition) One calculates the average cost by

  1. summing up individual costs and
  2. dividing that by the number of types of items you have.
The number of each item you have on-hand is not relevant.

What matters is the number of types of items and their individual costs. 

select avg(cost)
from item
The answer is: $15.31

4. Display the items that cost below average.

(Take the average to be the one calculated in the previous query). 

select [item id], description, cost
from item
where cost <= (select avg(cost) from item);
We've done this kind of exercises on Thu before the exam.

The answer is:

item id description cost
3663 Baseball Cap $9.25
3683 Coffee Mug $1.85
4563 Glasses (4) $8.20
5953 Knit Cap $4.95
6189 Sports Towel $3.58
7930 Tee Shirt $8.90
(One can easily verify this query by hand).

5.Calculate the total cost of the "Beverage Holders" items you have on-hand.

(The number of items of each kind you have on hand is to be taken into account).

Start from this: 

select [item id], description, cost 
from 
item, vendor
where vendor.[vendor code] = item.[vendor code] and vendor.name = "Beverage Holders"
The answer eventually is: 
SELECT sum(cost) AS [total cost]
FROM item, vendor
WHERE vendor.[vendor code]=[item].[vendor code] AND vendor.name="Beverage Holders";
Here's another version: 
select sum(cost), vendor.name
from vendor, item
where vendor.[vendor code] = item.[vendor code]
group by vendor.name
having vendor.name="Beverage Holders"
 6. What's the profit if you sell all "Beverage Holders" items you have?

Definition: It is the

  1. difference between the total cost as calculated in the previous question, and
  2. the amount of money you would be getting if you sold all the items you had
  3. at the listed price. Calculate and list that.
Here's a bit more than what you need: 
select sum([on hand] * ([selling price] - [cost])) as profit, vendor.name
from vendor, item
where vendor.[vendor code] = item.[vendor code] 
group by vendor.name
 This produces the following answer:
profit name
$282.15 Arnie Cheer
$73.40 Beverage Holders
$309.37 Logo Greats
7. Calculate the average item cost per vendor (three averages, for the three vendors).

(The number of items of each kind currently in the store is not relevant, just as in query 3.)

Too easy, by now. 

select avg(cost) as [average cost], vendor.name
from vendor, item
where vendor.[vendor code] = item.[vendor code]
group by vendor.name
This gives:
average cost name
$18.03 Arnie Cheer
$5.03 Beverage Holders
$17.74 Logo Greats