Chat with us, powered by LiveChat Create the following queries using the animal hospital database (see attached document) Please make sure my query’s are correct - Essayabode
Register

14 Feb Create the following queries using the animal hospital database (see attached document) Please make sure my query’s are correct

Posted at 02:07h in Information Systems by

Create the following queries using the animal hospital database (see attached document) Please make sure my query's are correct – if not please re-do them.

Query1: Get the AnimalID, Breed, AnimalName of all the dogs who are 3 years old.  

Query2: Get the number of animal visits by each animal (AnimalID).

Hint: You can refer to database lab tutorial query 10 for the use of Totals.

Query3: Get the first name and last name of all the clients whose first name starts with letter B or whose last name contains letter e.

Hint: You can refer to database lab tutorial query 3 for the use of wildcard character.

Query4: Get the AnimalID and Names of the animals who had at least two visits with Doctor Hopkins.

Query5: For each ClientID, get the total balance that the client still needs to pay for all the visits together. The balance for each visit is calculated as the difference between Fee and Paid.

Hint: For calculating the balance, you may refer to database lab tutorial query 12 for the use of [ ], etc.

AnimalID ClientID Species Breed Age AnimalName
1 1 Horse Quarterhorse 4 Blaze
2 2 Dog Chihuahua 3 Spot
3 2 Cat Mix 6 Nirmal
4 3 Cat Mix 8 Big
5 4 Dog Mix 5 Rover
6 5 Dog Collie 6 Sarah
7 6 Goat Nubian 4
8 6 Cow Herford 7
9 6 Cow Herford 8
10 7 Horse Morgan 11 Isty
11 8 Dog Doberman 6 Jake
12 9 Dog Terrier 5 Curley
13 10 Cat Manx 4 Little
14 11 Llama 3 Matisse
15 12 Hog 2 Petunia
16 13 Horse Saddlebred 9 Chug
17 13 Horse Saddlebred 8 Lightning
18 14 Cat Mix 4 Garfield
19 15 Cat Mix 7 Mittens
20 16 Dog Mix 6 Bozo
21 17 Dog Mix 5 Butler
22 18 Dog Collie 10 Shadow
23 19 Dog Chihuahua 5 Stimpy
24 19 Cat Persian 6 Queenie
25 20 Dog Terrier 4 Zippo
26 21 Cat Mix 8 Jimbo
27 22 Cat Persian 6 Sheba
28 23 Dog Terrier 4 Groucho
29 24 Sheep 6
30 25 Dog Mix 2 Hero
31 25 Cat Mix 1 Bartholemew
32 25 Cat Mix 1 Skippy
33 25 Cat Persian 7 Jezebel
34 26 Ostrich Blue 2
35 27 Dog Mix 4 Fluffy
36 28 Cat Mix 5 Tiger
37 29 Cat Mix 1 Sunshine
38 30 Dog Doberman 6 Friendly
39 31 Dog Collie 7 Lady
40 32 Cat Mix 8 Rainbow
41 33 Horse Saddlebred 4 Stonewall
42 33 Horse Morgan 1 Double Trouble
43 33 Horse Quarterhorse 8 Lucky
44 34 Cat Mix 3 Puffy
45 34 Cat Mix 5 Fluffy
46 35 Dog Mix 6 Tramp
47 36 Horse Mix 7 Sport
48 37 Llama 4 Inca
49 38 Hog 2
50 39 Dog Poodle 6 Alphonse
51 40 Dog Blue Heeler 4 Beowolf
52 41 Cat Mix 6 Bert
53 42 Cat Mix 1 Ernie
54 43 Dog Spaniel 9 Rex
55 44 Horse Pony 4 Daisy
56 44 Horse Quarterhorse 9 Jackson
57 6 Cow Angus 1
58 26 Cow Holstein 3
59 37 Emu Standard 1
60 26 Cow Angus 1
61 40 Dog Labrador 1 Harley
ClientID ClientFirstName ClientLastName ClientAddress ClientCity ClientPhone Account
1 Jennifer Logan 324 Eastside Ave Cawston 456-5443 false
2 Ken London 900 Wiltshire Blvd Cawston 456-3154 false
3 Britt Lizette 14-3214 27 Avenue Cawston 654-8852 true
4 Diana Stapleton 5232 Ida Road Seymour 441-6532 true
5 Lloyd Ray 124 E. 39 Street Cawston 332-9090 false
6 Duane Terrace 44-9090 Salmon St Cawston 123-9960 false
7 Bruce Moore 670 Monteith Rd Cawston 334-0987 true
8 Dennis Williams 5512 Lakeside Drive Brookwood 454-6622 false
9 Wayne Moravec RR1 Yankee Flats Abbot 434-0980 false
10 Pattie Dene 4403 Ida Road Seymour 441-6655 false
11 Gregory Paulette 775 39 Street Cawston 332-1505 false
12 Owen Belgo 8770 Delhart Ave Cawston 235-6541 true
13 Kevin Pavan 441 Potter Drive Cawston 551-6622 false
14 Wendy Schneider 133 Buckland St Brookwood 441-6023 false
15 Carl Herbert 902 Uplands Drive Cawston 551-6327 false
16 Stephanie Michael 443 Shelley Ave Cawston 665-9977 false
17 Jakob Schmidt 775 Ida Road Seymour 442-5102 false
18 Pam Stebner 345 Wiltshire Blvd Cawston 455-6632 false
19 Ingrid Corey 22-4587 42 Ave Cawston 125-6545 false
20 Leanne Stein 220 Lakeside Drive Brookwood 554-6500 false
21 Carmen Vallance 451-6625 38 St Cawston 451-3352 true
22 Lorne Valliere 545 Potter Drive Cawston 554-3652 false
23 Gerald Vanderburg 21 Eastside Ave Cawston 456-3211 false
24 Hank Gallagher RR3 Yankee Flats Abbot 225-6333 true
25 Elizabeth Norman 554 Cascade Pl Cawston 445-6632 false
26 Ron Widmann 756 Clara Drive Cawston 252-6411 false
27 Lonnie Wilkins 141 Uplands Drive Cawston 441-6655 false
28 Roland Sherwood 712 Shannon Way Cawston 225-5544 false
29 Bud Charming 8805 Lakeside Drive Brookwood 564-3332 false
30 Arnie Cramer 2061 Buchanan Cawston 455-6001 true
31 Nick Donnelly 123 Shelley Ave Cawston 665-3324 false
32 Timothy Collins 4456 Delhart Ave Cawston 255-3685 false
33 Allan Young 767 Renfrew St Cawston 124-9987 false
34 Joan Clements 213 Potter Drive Cawston 665-4422 false
35 Graham Sanderson 4305 Beach Ave Cawston 445-3328 false
36 Barry Desjardins 206 14 Avenue Cawston 332-6410 false
37 Linda Burke 477 Canyon Drive Cawston 124-6654 false
38 Darren James 62 Eastside Ave Cawston 456-1102 false
39 Spence Butler 855 Downie Cawston 335-6410 true
40 Lyle Adair 332 Ackerman Drive Cawston 441-6320 true
41 Dorothy Clifford 365-6630 38 St Cawston 451-6701 false
42 Jose Zurba 66 Cranbrooke Rd Cawston 225-3001 false
43 Lorna Peterson 201 14 Avenue Cawston 332-5413 false
44 Brendan Woods 233 Yuma St Cawston 441-2235 false
DoctorID DoctorName HomePhone
1 Dvorak 554-6684
2 Jesse 665-8744
3 Hopkins 441-6060
4 Lamaze 250-6300
5 Wilson 441-6544
6 Smith 992-2344
7 Chen 323-3456
Date AnimalID DoctorID Duration Treatment Fee Paid Travel
5/20/99 1 1 45 Prescription ¤ 35.00 ¤ 20.00 true
5/21/99 2 1 30 Prescription ¤ 40.00 ¤ 20.00 false
5/23/99 2 4 60 Surgery ¤ 150.00 ¤ 150.00 false
5/21/99 3 1 15 Check-up ¤ 25.00 ¤ 25.00 false
5/20/99 4 5 45 Miscellaneous ¤ 30.00 ¤ 30.00 false
5/19/99 5 3 45 First Aid ¤ 40.00 ¤ 0.00 false
5/20/99 5 1 45 Prescription ¤ 35.00 ¤ 20.00 true
5/22/99 5 1 45 Prescription ¤ 35.00 ¤ 20.00 true
5/23/99 5 1 30 First Aid ¤ 45.00 ¤ 45.00 false
5/22/99 6 1 30 Prescription ¤ 40.00 ¤ 40.00 false
5/19/99 7 2 45 First Aid ¤ 40.00 ¤ 0.00 false
5/19/99 8 2 15 Consultation ¤ 20.00 ¤ 10.00 true
5/20/99 8 5 45 Miscellaneous ¤ 30.00 ¤ 30.00 false
5/22/99 8 5 45 Miscellaneous ¤ 30.00 ¤ 30.00 false
5/21/99 9 2 90 Surgery ¤ 300.00 ¤ 0.00 true
5/19/99 10 5 60 Surgery ¤ 175.00 ¤ 175.00 true
5/21/99 10 1 30 Vaccination ¤ 55.00 ¤ 55.00 true
5/22/99 10 1 15 Check-up ¤ 25.00 ¤ 25.00 true
5/23/99 10 3 30 Prescription ¤ 40.00 ¤ 0.00 true
5/18/99 11 3 45 First Aid ¤ 50.00 ¤ 50.00 false
5/20/99 11 3 90 Surgery ¤ 225.00 ¤ 100.00 false
5/21/99 11 5 15 Check-up ¤ 25.00 ¤ 25.00 false
5/22/99 12 5 15 Consultation ¤ 20.00 ¤ 20.00 false
5/22/99 13 4 30 Prescription ¤ 40.00 ¤ 40.00 false
5/18/99 14 4 30 Vaccination ¤ 45.00 ¤ 10.00 true
5/20/99 14 5 60 Check-up ¤ 55.00 ¤ 55.00 true
5/18/99 15 5 45 First Aid ¤ 50.00 ¤ 50.00 false
5/19/99 15 1 60 Surgery ¤ 160.00 ¤ 100.00 false
5/20/99 15 3 90 Surgery ¤ 225.00 ¤ 100.00 false
5/22/99 15 3 90 Surgery ¤ 225.00 ¤ 100.00 false
5/23/99 15 5 45 First Aid ¤ 50.00 ¤ 50.00 false
5/18/99 16 1 60 Surgery ¤ 150.00 ¤ 0.00 true
5/19/99 16 2 45 Prescription ¤ 40.00 ¤ 40.00 true
5/20/99 16 3 45 First Aid ¤ 60.00 ¤ 35.00 true
5/18/99 17 1 60 Check-up ¤ 55.00 ¤ 55.00 true
5/23/99 17 5 60 First Aid ¤ 60.00 ¤ 0.00 true
5/18/99 18 3 30 Vaccination ¤ 45.00 ¤ 10.00 true
5/20/99 18 5 60 Check-up ¤ 55.00 ¤ 55.00 true
5/22/99 18 3 15 Check-up ¤ 25.00 ¤ 25.00 false
5/23/99 18 4 30 Vaccination ¤ 45.00 ¤ 10.00 true
5/18/99 19 5 15 Check-up ¤ 25.00 ¤ 0.00 false
5/20/99 19 4 60 Surgery ¤ 175.00 ¤ 75.00 false
5/18/99 20 1 60 Surgery ¤ 150.00 ¤ 0.00 true
5/20/99 20 3 45 First Aid ¤ 60.00 ¤ 35.00 true
5/22/99 20 3 15 Check-up ¤ 25.00 ¤ 25.00 false
5/23/99 20 1 60 Surgery ¤ 150.00 ¤ 0.00 true
5/18/99 21 1 60 Check-up ¤ 55.00 ¤ 55.00 true
5/20/99 21 4 30 Prescription ¤ 40.00 ¤ 40.00 false
5/23/99 21 1 60 Check-up ¤ 55.00 ¤ 55.00 true
5/19/99 22 4 30 Miscellaneous ¤ 30.00 ¤ 15.00 false
5/20/99 22 3 30 First Aid ¤ 50.00 ¤ 25.00 false
5/21/99 22 3 30 Vaccination ¤ 50.00 ¤ 0.00 false
5/23/99 22 2 45 First Aid ¤ 40.00 ¤ 40.00 false
5/18/99 23 1 15 Check-up ¤ 25.00 ¤ 0.00 false
5/20/99 23 4 60 Surgery ¤ 175.00 ¤ 75.00 false
5/22/99 23 4 60 Surgery ¤ 175.00 ¤ 75.00 false
5/23/99 23 5 15 Check-up ¤ 25.00 ¤ 0.00 false
5/21/99 24 3 30 Check-up ¤ 45.00 ¤ 45.00 false
5/18/99 25 4 30 First Aid ¤ 50.00 ¤ 50.00 false
5/20/99 25 4 60 Surgery ¤ 150.00 ¤ 50.00 false
5/22/99 25 4 30 Prescription ¤ 40.00 ¤ 40.00 false
5/20/99 26 2 30 First Aid ¤ 45.00 ¤ 20.00 false
5/22/99 26 3 30 First Aid ¤ 50.00 ¤ 25.00 false
5/18/99 27 1 15 Check-up ¤ 25.00 ¤ 0.00 false
5/21/99 27 4 15 Check-up ¤ 25.00 ¤ 15.00 false
5/23/99 27 1 15 Check-up ¤ 25.00 ¤ 0.00 false
5/21/99 28 5 45 First Aid ¤ 40.00 ¤ 40.00 false
5/18/99 29 4 30 First Aid ¤ 50.00 ¤ 50.00 false
5/19/99 29 4 30 Prescription ¤ 40.00 ¤ 40.00 false
5/20/99 29 4 60 Surgery ¤ 150.00 ¤ 50.00 false
5/22/99 29 4 60 Surgery ¤ 150.00 ¤ 50.00 false
5/23/99 29 4 30 First Aid ¤ 50.00 ¤ 50.00 false
5/19/99 30 4 60 Surgery ¤ 150.00 ¤ 150.00 false
5/20/99 30 2 30 First Aid ¤ 45.00 ¤ 20.00 false
5/22/99 30 2 30 First Aid ¤ 45.00 ¤ 20.00 false
5/21/99 31 5 45 First Aid ¤ 40.00 ¤ 20.00 false
5/22/99 32 4 30 Miscellaneous ¤ 45.00 ¤ 10.00 false
5/20/99 33 2 60 Surgery ¤ 175.00 ¤ 175.00 false
5/19/99 34 3 15 Consultation ¤ 20.00 ¤ 0.00 true
5/20/99 35 1 45 Prescription ¤ 40.00 ¤ 40.00 false
5/20/99 36 2 60 Consultation ¤ 50.00 ¤ 25.00 false
5/20/99 37 3 45 Prescription ¤ 40.00 ¤ 40.00 false
5/22/99 37 2 60 Surgery ¤ 175.00 ¤ 175.00 false
5/19/99 38 4 30 Prescription ¤ 40.00 ¤ 40.00 false
5/20/99 39 1 45 Prescription ¤ 40.00 ¤ 40.00 false
5/21/99 39 3 30 Miscellaneous ¤ 30.00 ¤ 30.00 false
5/22/99 39 1 45 Prescription ¤ 40.00 ¤ 40.00 false
5/19/99 40 1 15 Check-up ¤ 25.00 ¤ 20.00 false
5/20/99 40 2 60 Consultation ¤ 50.00 ¤ 25.00 false
5/22/99 40 2 60 Consultation ¤ 50.00 ¤ 25.00 false
5/20/99 41 5 45 First Aid ¤ 40.00 ¤ 40.00 true
5/22/99 41 3 45 Prescription ¤ 40.00 ¤ 40.00 false
5/21/99 42 2 60 Surgery ¤ 175.00 ¤ 100.00 true
5/19/99 43 5 90 Surgery ¤ 350.00 ¤ 200.00 true
5/18/99 44 2 30 First Aid ¤ 45.00 ¤ 0.00 false
5/20/99 45 5 45 First Aid ¤ 40.00 ¤ 40.00 true
5/22/99 45 1 45 Prescription ¤ 30.00 ¤ 15.00 false
5/22/99 46 2 60 Surgery ¤ 150.00 ¤ 100.00 false
5/22/99 47 4 15 Check-up ¤ 25.00 ¤ 0.00 true
5/18/99 48 2 30 First Aid ¤ 45.00 ¤ 0.00 false
5/22/99 48 5 45 Prescription ¤ 40.00 ¤ 40.00 true
5/23/99 48 2 30 First Aid ¤ 45.00 ¤ 0.00 false
5/22/99 49 3 30 Prescription ¤ 40.00 ¤ 15.00 false
5/21/99 50 4 15 Check-up ¤ 25.00 ¤ 12.00 false
5/22/99 51 1 30 Vaccination ¤ 45.00 ¤ 45.00 false
5/22/99 52 5 90 Surgery ¤ 250.00 ¤ 250.00 false
5/22/99 53 4 15 Consultation ¤ 20.00 ¤ 20.00 false
5/18/99 54 2 30 Check-up ¤ 45.00 ¤ 45.00 false
5/18/99 58 2 30 Check-up ¤ 45.00 ¤ 45.00 false
5/23/99 58 2 30 Check-up ¤ 45.00 ¤ 45.00 false
5/23/99 59 3 45 First Aid ¤ 50.00 ¤ 0.00 true
5/23/99 60 3 30 Vaccination ¤ 55.00 ¤ 55.00 true
5/23/99 61 4 15 Vaccination ¤ 35.00 ¤ 35.00 false
SELECT Animals.AnimalID, Animals.Breed, Animals.AnimalName, Animals.Age FROM Animals WHERE (((Animals.Age)>3));
SELECT Animals.Species, Sum(Visits.AnimalID) AS SumOfAnimalID FROM Animals INNER JOIN Visits ON Animals.AnimalID = Visits.AnimalID GROUP BY Animals.Species;
SELECT Clients.ClientFirstName, Clients.ClientLastName FROM Clients WHERE (((Clients.ClientFirstName)="B%")) OR (((Clients.ClientLastName) Like "*E*"));
SELECT Animals.AnimalID, Animals.AnimalName, Visits.DoctorID, Visits.AnimalID, Doctors.DoctorName FROM Doctors INNER JOIN (Animals INNER JOIN Visits ON Animals.AnimalID = Visits.AnimalID) ON Doctors.DoctorID = Visits.DoctorID GROUP BY Animals.AnimalID, Animals.AnimalName, Visits.DoctorID, Visits.AnimalID, Doctors.DoctorName HAVING (((Visits.DoctorID)="3") AND ((Visits.AnimalID)=11 Or (Visits.AnimalID)=15 Or (Visits.AnimalID)=18 Or (Visits.AnimalID)=20 Or (Visits.AnimalID)=22) AND ((Doctors.DoctorName)="hopkins"));
SELECT Clients.ClientID, Sum(Visits.Fee) AS SumOfFee, Sum(Visits.Paid) AS SumOfPaid FROM Clients, Visits GROUP BY Clients.ClientID;

Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.

Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.

Do you need an answer to this or any other questions?