MIS 3500 Database Management Systems * Asper School of Business

MIS 3500 Database Management Systems * Asper School of Business

Instructor: Bob Travica

Homework 3 Solution

Updated 2015, Feb. 12

Use the database Pet Store 2010, which you can find in notes for classes on querying or via the link below, to write the queries described below. You queries should run and deliver correct results to be awarded a full credit. The statement compliance to SQL standards is not graded. The answers should be stored in a Word file and mailed to your instructor.

1. List merchandise items whose quantity is 11 or less, and show their IDs, names, quantity, and supplier names and phone numbers. Sort the list on the item quantity. Provide your SQL statement and the output of the query.

Output: /
ItemID / Description / Available Items / Supplier Name / Phone /
21 / Bird Cage-Medium / 5 / Hughes / (615) 798-8614
4 / Dog Kennel-Extra Large / 6 / Parrish / (606) 033-1460
1 / Dog Kennel-Small / 11 / Love / (606) 282-6165
24 / Aquarium-100 gal / 11 / Harrison / (606) 121-7621
24 / Aquarium-100 gal / 11 / Hughes / (615) 798-8614

SQL statement:

SELECT DISTINCT Merchandise.ItemID, Merchandise.Description, Merchandise.QuantityOnHand As [Available Items], Supplier.Name As [Supplier Name], Supplier.Phone

FROM

Merchandise INNER JOIN

((OrderItem INNER JOIN

(MerchandiseOrder INNER JOIN Supplier ON MerchandiseOrder.SupplierID=Supplier.SupplierID)

ON OrderItem.PONumber=MerchandiseOrder.PONumber))

ON Merchandise.ItemID=OrderItem.ItemID

WHERE Merchandise.QuantityOnHand <= 11

ORDER BY Merchandise.QuantityOnHand;

SQL statement, older standard:

SELECT DISTINCT Merchandise.ItemID, Merchandise.Description, Merchandise.QuantityOnHand As [Available Items], Supplier.Name As [Supplier Name], Supplier.Phone

FROM Merchandise, OrderItem, MerchandiseOrder, Supplier

WHERE

Merchandise.ItemID=OrderItem.ItemID AND

OrderItem.PONumber=MerchandiseOrder.PONumber AND

MerchandiseOrder.SupplierID=Supplier.SupplierID

AND

Merchandise.QuantityOnHand <= 11

ORDER BY Merchandise.QuantityOnHand;

Tested understanding of:

- Select DISTINCT

- 4 tables join

Marking: If the query runs and delivers the correct result, 5 marks.

Otherwise:

-2 for incorrect output;

-0.5 to -1 for partly incorrect output;

-1 for all incorrect or missing query line;

-0,5 mark for a partly incorrect query line.

2. What are the totals by male and female gender of the animals adopted in the first half of 2010 (January 1 to June 30)? Also find out and display both the total of donations and the average donation per animal gender. Name the output columns meaningfully. Provide your SQL statement and the output of the query.

SELECT Animal.Gender, Count(Animal.SaleID) AS [Animals Adopted],

SUM(Animal.Donation) AS [Donations Jan-June], AVG(Animal.Donation)

AS [Average Donation ($)]

FROM Sale INNER JOIN Animal ON Animal.SaleID=Sale.SaleID

WHERE Sale.SaleDate Between #january 1, 2010# And # june 30, 2010#

GROUP BY Animal.Gender;

Output: /
Gender / Animals Adopted / Donations Jan-June / Average Donation ($) /
Female / 54 / $8,587.34 / $159.02
Male / 61 / $10,082.08 / $165.28

Marking: If the query runs and delivers the correct result, 5 marks. Otherwise, same as for question 1.

Tested understanding of:

-  COUNT function on key column

-  SUM and AVG functions

-  2 tables join

-  Naming displayed columns

3. Write a query that will list identifications of merchandise orders and totals for each order for a month the user can choose when running the query. Columns of the output table should be named in a meaningful way. Provide your SQL statement and the output of your query for one month of your choice.

(Hint: For the month you may want to use the function for referencing months by numbers 1-12,

in the format Month(date-column), where “date-column” is the name of a referenced date attribute.)

SELECT MerchandiseOrder.PONumber, Sum([OrderItem.Quantity] * [OrderItem.Cost])

AS [Order Totals ($)]

FROM MerchandiseOrder INNER JOIN OrderItem ON MerchandiseOrder.PONumber =

OrderItem.PONumber

WHERE Month([MerchandiseOrder].[OrderDate])=[Enter month 1-12]

GROUP By MerchandiseOrder.PONumber;

Output: Shows merchandise totals for orders in month 2 (February).

PONumber / Order Totals ($) /
16 / 757.53
19 / 2742.64
22 / 1798.28

Marking: If the query in any of the above versions runs and delivers the correct result, 5 marks. Otherwise, same as for question 1.

Tested understanding of:

-  Multiplication of columns in SELECT line

-  Calculated attribute (order total)

-  2 tables join

-  Parameter query with date function

-  Output column naming

Shipping costs are not included in the query above, which is acceptable. To include shipping costs, the query above can be saved as “Total Month Order” and then called by another query, as follows: