Access Practice: Extra Credit -5 points

ADOPT table

AdoptID / AdoptionDate / Dog ID / Owner / Address / City / State / Zip Code
1 / 11/4/2001 / 4 / Jack McDonald / 143 N. Water St. / Columbus / OH / 43283
2 / 10/13/2001 / 1 / Karen Donnelly / 8243 Cambridge Dr. / Worthington / OH / 43294
3 / 2/14/2002 / 7 / Marilyn Martin / 47 Simpson Ct. / Columbus / OH / 43435
4 / 12/18/2001 / 6 / Dorothy Oz / 1350 Kansas Ln. / Columbus / OH / 43210

DOG table

Dog ID / Name / Breed / Sex / Birthdate / Arrived / Personality
1 / Nellie / Cocker Spaniel / F / 1/1/1996 / 7/3/2001 / calm and quiet, loves kids
2 / Cinder / Black Lab / M / 12/18/2000 / 2/3/2002 / playful, chases cats
3 / Spot / Dalmatian / M / 3/14/1998 / 6/5/2001 / hyper, needs room to run
4 / Oscar Mayer / Dachshund / M / 7/4/2000 / 5/5/2001 / lap dog, likes to take walks
5 / Nipper / Shetland Sheepdog / M / 8/9/1999 / 10/13/2001 / loves treats, well-trained
6 / Shelby / Mixed / F / 4/4/2001 / 1/13/2002 / good with other dogs, likes tug-of-war
7 / Buster / Poodle / M / 3/18/2001 / 2/11/2002 / not good with kids, hyper, likes to fetch

VET table

Adopt# / Date / Vaccines / Nails Clip / Sick
1 / 12/1/2001 / $5 / no
2 / 11/30/2001 / 1 / $0 / yes
4 / 3/20/2002 / 1 / $0 / no
1 / 2/13/2002 / $5 / yes
2 / 12/5/2001 / $0 / yes
2 / 4/4/2002 / 2 / $0 / no
4 / 6/10/2002 / 1 / $0 / no

Description: The ADOPT table specifies who adopts a dog and which dog they adopt and other personal information. It is assumed that each person will adopt only one dog at a time. The DOG table lists information about each dog that is up for adoption. The dog may have already been adopted or not, depending on if they have a listing on the ADOPT table. The VET table specifies the visits the adoptive owners have made to the vet and for what reasons. Notice that the vaccine column has some blank (i.e. null) values which means that the dog did not receive a vaccine during that visit.

QUERIES:

  1. Write a query to list the names of all the dogs that are not “hyper”.
  2. Write a query to list the names of the dogs that have been adopted.
  3. Write a query to determine the cost of visiting the vet for each vet visit. The base cost for the visit is $40, the cost of each vaccine is $10 and the cost for a nail clip is given. List the owner’s name and address as well as the cost of each visit.
  4. What would you change in the above query to give the total cost for each owner who had to take their adopted dog to the vet?
  5. Write a query to list the names of the dogs that visited the vet because they were sick. The names of the dogs can be listed multiple times.
  6. What would you change in the above query if the problem stated that no duplicate dog names should be listed but instead you are to include the number of visits for each dog as a field in the resulting dynaset?Be sure to assume that some dogs could have the same name.

7-10. Write 4 query problems and their answers. Create one query with multiple sorted fields; create one using both AND as well as OR criteria combined; have fun with the other two ;o)