New Database Management Systems Lab Record by uwb18250


More Info
									                  MIS 2502 – Database Management Systems
Lab 4 – Mathematical Functions, Alias, and Joins, Update and Delete in SQL - Fall


In this lab, we will be referencing the same database created in Lab 3. In the space below each
question, enter your code and your output. You will not receive points for the question if you
do not show your query code and output. To ensure that the query result fits appropriately,
follow the directions on page 136-137 of your textbook on how to format a text column.

1. List the client_id, pet‟s name, primary_color, and date of birth for all pets who are a color
   between 1 and 5. Make the column headings “Client”, “Pet Name”, “Color” and “Birth Date”,
   respectively. Sort the output by client and then the pet‟s birth date.

2. The vet wants to evaluate which animals have high temperatures. For each appointment for
   an animal with a temperature equal to or above 101.8, show the animal_id, the pet‟s
   temperature and the amount over 101.8 in another column (called “Over 101.8”).

3. Show each client‟s name and his/her pet‟s name, gender, and date of birth if the pet‟s name
   is „Juno‟.

4. The vet would like to run the same information as above but would like to see the description
   of Juno‟s breed shown, as well.

5. Show the pet‟s name, last name, gender, species description for all female spayed cats.
   Write your query based on the word cat, not the id.

Page 1 of 2
                  MIS 2502 – Database Management Systems
Lab 4 – Mathematical Functions, Alias, and Joins, Update and Delete in SQL - Fall

6. The vet would like to modify the above query to also show the animal‟s color (i.e. gray, black,
   tan, etc.), and breed description. Add column headings as “Species”, “Color”, “Breed”. Sort
   the output by the client‟s last name first and then pet‟s name (ascending).

7. Some of the pets at the Animal Hospital are a bit on the chunky side. The vet would like to
   implement a healthy weight program. She‟d like to send a report to all the clients with chubby
   cats (those over 18 pounds). The listing should include the client‟s name, cat‟s name,
   appointment date, cats‟ current weight, the amount of weight the animal needs to lose
   (calculated as 20% less than the cat‟s current weight, and the target weight (the current
   weight minus the weight to be lost). Of note, there may be multiple records for a cat with
   multiple appointments. Make the client headings as shown below.

8. Insert a new record in the breed table for Basset Hound with an id of 18. List all the records
   in the breed table (this should include the new record) sorted by the breed description.

9. Delete the basset hound record from the breed table. Show the query to delete the record
   and the output. Show all the records in the breed table (this should not include the basset

10. I typed the following query and the error message below appeared. Explain what this
    message means and how I can correct this error.
select name, client_lname, client_fname
from vet_client, animal
where client_id = client_id;

Page 2 of 2

To top