Database Management Systems

© 2007 by Jerry Post
Database Exercises

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

Sally’s Pet Store

  1. List the female cats that are registered.
  2. List the customers who live in Vermont (VT).
  3. Which dogs have a list price greater than $270?
  4. Which dogs cost less than $100 or were registered and cost more than $250?
  5. List the customers who purchased something in January.
  6. List the suppliers who delivered cats in March.
  7. List the birds with gold in their color.
  8. Which employees sold items or animals between June 15 and June 30? (List each employee only once.)
  9. Which animals were born no more than 30 days after they were ordered?
  10. What is the most expensive (SalePrice) cat ever sold?
  11. Which dog was sold at the greatest discount (ListPrice-SalePrice)?
  12. What is the highest-value merchandise order (total of quantity*cost) ever placed?
  13. What is the most number of animals ever sold at one time (on the same sale)?
  14. List the number of sales that took place each month. Hint: You need to find the function that returns the month number of the SaleDate.
  15. What are the total values of sales of merchandise items by category?
  16. Which employee sold the most merchandise by value in June?
  17. Which supplier has received the most money (Cost) for cats?
  18. Which category of animal provides the highest average profit percentage (using SalePrice and Cost)?
  19. Who is the best customer measured by total value of merchandise purchases for the first half of the year?
  20. On average, which supplier is slowest to ship merchandise?
  21. What is the oldest cat on the day it was sold?
  22. Compare the average sale price of registered dogs to unregistered dogs?
  23. Has the company sold more female or male black cats?
  24. What is the most expensive merchandise item sold (SalePrice)?
  25. Which merchandise item was sold at the greatest percentage discount from list price?

    26. Rolling Thunder Bicycles

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

  26. List the pries of race bikes smaller than 53 cm shipped to Florida (FL) in 2004.
  27. Who bought road bikes in Massachusetts (MA) with a frame size greater than 58 cm in 2005?
  28. Who bought full suspension mountain bikes in Colorado (CO) in 2006 or bought mountain bikes in Texas (TX) that had red in the color in 2006?
  29. List employees who sold red road bikes to New York (NY) in the first quarter of 2005.
  30. What is the most expensive race bike sold in 2006?
  31. Who bought the least expensive mountain (or full suspension) bike in Idaho (ID) in 2005?
  32. List the components installed on bike serial number 32151, in descending order of price.
  33. What is the most expensive (ListPrice) component (not a frame) held in inventory and how many units are on hand?
  34. List the employees who painted road bicycles started in February 2006. Hint: You will have to change the join columns.
  35. List the manufacturers who sold us more than $100,000 (list price) on a single order in 2005.
  36. How many components were installed by Tudjman on March 17, 2006?
  37. Which customer received the greatest percentage discount off list price for a bicycle order in 2005?
  38. Compare the average sale price of race bikes in 2006 based on the type of down tube (e.g., Carbon, Aluminum, Titanium, or Cro-moly).
  39. How does the total weight of the Campy Record 2004 groupo compare to that of the Shimano Dura Ace 2003 groupo? Hint: You cannot use the Weight column in the Groupo table. Bonus point: what is the weight in pounds?
  40. What was the most popular model type (by count) sold in January 2006?
  41. In which state did the company sell the most race bikes with a frame size larger than 58 cm in 2005?
  42. Which employee had the most sales by value in March 2006?
  43. On average, from which manufacturer did it take the longest time to receive an order in 2005?
  44. Ignoring discounts and labor costs, what is the estimated profit made on components installed in May 2005?
  45. What was the most popular paint style for mountain (or full suspension) bikes ordered in August 2006?
  46. Which customer(s) purchased the most number of bicycles in a single month? Hint: Use a function that returns the month and year from a date.
  47. Which manufacturer provided the highest average percent discount on orders placed in 2005?
  48. On average, which type of component weighs the most for road bikes versus mountain bikes?
  49. In 2006, what was the average price of bikes sold with the help of retail stores compared to those sold direct (StoreID of 1 or 2)? Hint: Use two separate queries.
  50. In 2005, which employee gave the highest total value of discounts on bicycle sales?

    51. Corner Med

  51. List the physicians and their emergency phone numbers sorted by name.
  52. List the patients and their phone numbers who visited the clinic between July 1, 2007 and July 7, 2007.
  53. List the male patients under 30 years old who were treated by Nurse Cameron in June 2007. Hint: Use a function to subtract dates by years.
  54. List all of the patients who were diagnosed with a “dog bite” in 2007.
  55. List all of the procedure codes that refer to the uvula.
  56. List all of the potential diagnoses involving swimming.
  57. List the patients who did not have insurance coverage for a visit in May 2007.
  58. List all of the employees and procedures they performed for Debra Santini in 2007.
  59. List all of the employees who have performed a procedure in the 51xx category (any procedure beginning with 51) in the second half of 2007.
  60. List the patients who had Medicare coverage for at least one visit in 2007. List each person only once if there were multiple visits.
  61. How many patient visits occurred in May 2007?
  62. Which patient had the most visits in 2007?
  63. What is the total amount of revenue generated (not necessarily paid) in June 2007?
  64. How much money was received from each insurance company in the first quarter of 2007?
  65. Diagnostic codes are grouped by the initial letters or numbers of the code. The top-level group is the first character of the code. Count the number of patients diagnosed with each of the top-level codes. Hint: Use a function to return just the first character of the code.
  66. On average, how many days did it take to receive money from each insurance company in 2007?
  67. Which employee billed the most revenue charges for 2007?
  68. What is the most number of days it took our company to submit a bill to an insurance company in 2007?
  69. What is the most number of employees who treated a patient in one visit in 2007?
  70. What is the most number of patients seen in one day in August 2007?