Database Management Systems

© 2007 by Jerry Post
Database Exercises

Chapter 1 2 3 4 5 6 7 8 9 10 11 12

Pet Store tables

Sally’s Pet Store

  1. Which supplier has the highest average percentage of shipping cost per value of merchandise order?
  2. Which breed of cats has the store never carried?
  3. What is the average merchandise value per sale as well as the average number of items sold?
  4. Estimate shipping cost per animal by dividing shipping cost by the number of animals per order. Compute the profit margin as the list price minus the cost minus the estimated shipping cost. List the animals with a negative margin.
  5. Compute the average profit margin for each merchandise item (ignoring shipping costs). Compute the weighted average cost of an item, and subtract this value from the list price for each item.
  6. Create a view (saved query) that computes the total value of a sale, including both animals and merchandise. To be useful later, your query should return the animal, merchandise, and overall totals. Warning: Some sales have only animals and some only merchandise. Hint: Use a Left Join with the Sale table to include all rows.
  7. Which employee has the highest average sales per order? Hint: Use the view created in Exercise 6.
  8. Which merchandise items had the largest quantity on hand at the start of July 1? Hint: Compute total purchases then subtract total sales.
  9. Which employees sold animals, but no merchandise in February?
  10. Which merchandise items hold more than the average value of inventory quantity on hand? Compute the value as QOH times list price.
  11. Similar to Exercise 10, but make a statistical comparison to identify the merchandise items with an inventory value more than two standard deviations above the average value. Bonus point: assuming inventory follows a random normal distribution, what is the probability of an item being more than two standard deviations above the mean?
  12. Which customers who bought more than the average value of animals (per customer) also bought more than the average value of merchandise? Hint: Use the view created in Exercise 6 to compute the total purchases by customer.
  13. Which employees ordered merchandise from suppliers in May, but not animals?
  14. What is the total number and value of merchandise orders placed with each supplier? Hint: You must first compute the total value per order.
  15. Create a crosstab query to display the total value of sales per month per employee. Hint: Use the view created in Exercise 6. Hint: Use Crosstab in Access, Decode in Oracle, or CASE in SQL Server (as in Figure 5.25). Hint: Use a function to return the month number from a date.
  16. Which merchandise items with fewer than 500 units on hand have not been ordered in 2007? Use an OUTER JOIN to answer the question.
  17. Create the table shown to categorize customers based on sales. Write a query that lists each total sales by each customer and displays the proper label. Hint: Use the view created in Exercise 6.
    CategoryLowHigh
    Weak0200
    Good200800
    Best80010,000
  18. List the employees and their total merchandise sales expressed as a percentage of total merchandise sales for all employees.
  19. List the dogs that took longer than average dog to sell. Do not include dogs that were preordered (sale date before the order date).
  20. List the employees who report to Gibson.
  21. Write an INSERT query to add the Tokinese cat to the Breed table.
  22. Write a query to decrease the list price by 10 percent of any item that has a QOH greater than 14 and a price greater than $50.
  23. Use SQL to create a Vets table with columns for VetID, LastName, FirstName, and PhoneNumber. Be sure to set VetID as the primary key.
  24. Use an INSERT command to copy five names from the Customer table and insert them into the Vets table created in Exercise 23 using CustomerID values 11-15.
  25. Delete any entries from the Vets table with an area code of 502.

    26. Rolling Thunder Bicycles
    Rolling Thunder Bicycles tables
    Write the SQL statements that will answer questions 26 through 50 based on the tables in the Rolling Thunder database.

  26. In which state were customers less likely to use a retail store to order a bike? Hint: Look at StoreID values of 1 or 2 and compute the percentage of sales made without retail stores.
  27. What is the price per gram of weight savings in switching between the latest Shimano LX crank and an XT crank?
  28. As a percentage of total number of days (OrderDate to ShipDate), did construction (StartDate versus ShipDate) represent a substantially higher percentage for different model types between 2000 and 2005?
  29. Using the latest DateInstalled for a bike part, what was the average number of days to build a race bike in 2005?
  30. By percentage of bikes, were Campy (Campagnolo) groupos installed on more race bikes or road bikes in 2005? Hint: Look at just the crank.
  31. Compare the average percentage of wheel weight to total component weight for road and race bikes versus mountain (and full suspension) bikes in 2004.
  32. Using one query (with a subquery), list the percentage value of sales by model type for 2005.
  33. In 2005, what percentage of the bikes was shipped in less than three days from the order date?
  34. Based on the down tube material, list the percentage of bikes ordered in 2005 constructed of each type of material.
  35. Which customers bought race bikes after they had already purchased mountain or full suspension bikes?
  36. List customers who bought at least one bike before 2000, but have not bought a bike since then.
  37. Which components have an inventory value on hand (based on ListPrice) greater than $10,000 but have not been installed on a bike in 2005?
  38. List customers who bought bikes in both 2004 and 2005.
  39. List the employee name and the transaction date and amount for any employee who participated in either a customer transaction valued over $6,000 or a manufacturer transaction valued over $100,000 in 2005. Hint: Use a UNION query.
  40. Write a crosstab query (see Figure 5.25 if you are not using Microsoft Access), to display total sales by model type and year. Hint: Use a function to return the year from a date.
  41. List the customers who have purchased at least one road bike and one mountain bike (at any time). Use a subquery.
  42. List all of the employees who report to Venetiaan.
  43. In which years did the average build time for the year exceed the overall average build time for all years?
  44. List the customers who have bought hard-tail mountain bikes but not yet purchased a full-suspension mountain bike.
  45. Use SQL to create a new CustomerSize table as shown.
  46. Write the query to insert the rows of data row into the table shown in Exercise 45.
    SizeRoadLowRoadHighMTBLowMTBHigh
    Small45521415.5
    Medium525715.517
    Big57651723
  47. Write a query to change the RoadLow value for the small bike to 40 in the table for Exercise 45.
  48. Using the table in Exercise 45, write a query that assigns the appropriate size label to each bicycle, then create a query to count the number of bicycles built in 2004 and 2005 that fall into each size category by model type
  49. Write a query to delete the first row in the table for Exercise 46.
  50. Write a query to delete the entire table created for Exercise 46.

    51. Corner Med
    Corner Med tables

  51. List the visits/cases where the company needs to reimburse the patient for overpayment—where the insurance company payment plus the patient payment exceeds the amount charged.
  52. What was the average number of visits per patient in 2007?
  53. What percent of the visits resulted in more then $1000 in charges?
  54. List the percentage of revenue generated by each physician in 2007. Use separate queries.
  55. What percentage of the patient visits were not covered by insurance in 2007?
  56. Which patients did not have visits in 2007? Use an outer join.
  57. Which insurance company paid the lowest percentage of the total amount charged?
  58. In 2007, how many visits involved three or more procedures?
  59. For 2007, what was the average number of patients per month seen by each employee?
  60. Which patients with an ICD9 diagnosis code beginning with E8 have had a procedure starting with a 9 code at the time of the diagnosis or later?
  61. Use SQL to create a table (InsuranceDelay) that can be used to categorize insurance company payment delays.
    CategoryMinDaysMaxDays
    Month3060
    Late6090
    TooLong901000
  62. Write the SQL statements to load the three rows of data into the table in Exercise 61.
  63. Use the categories defined by the table in Exercise 61 to count the number of items in each category for each of the insurance companies—based on the number of days between submitting the bill and receiving payment. Write the final query as a cross tab to show the number of bills that fall into each category for each insurance company.
  64. Write the SQL command to delete all of the rows in the table for Exercise 61.
  65. Write the SQL command to remove the table created in Exercise 61.