Question

Instructions

• All documents and SQL code must be zipped into a single file and uploaded to Blackboard
• All SQL code must run error-free for full credit. The SQL file will be uploaded by the instructor into SQLPlus to check for syntax – so ensure that your file does not contain any syntax errors.
• All SQL code must be commented appropriately

The Prince William County Humane Center wants a database for storing information on their pets.

1. Create the table(s) needed to store pet information. At a minimum, the table(s) should capture the following information on the pets   (20 points)
   1. Name of pet
   2. Type of pet (dog, cat …etc.)
   3. Breed (dachshund, beagle, terrier …etc)
   4. Gender
   5. Date in
   6. Date out
2. Write the SQL statement(s) to insert records for a minimum of 40 sample pets (20 points)

3. Write the SQL statement(s) to obtain the following information (60 points)
   1. List of all pets
   2. List of all types of pets
   3. List of all breeds of dogs
   4. Least popular breed of cat

NOTE: Provide all your work as a single SQL file, with comments embedded. There must not be any errors in the SQL file

Answer 1

pet.sql

create table pet_info(petID number(4) primary key,petname varchar(20),type_of_pet varchar(20),breed varchar(20),gender varchar(10));
desc pet_info;
insert into pet_info values(101,'viral','cat','Russian Blule','male');
insert into pet_info values(102,'Jenny','cat','Maine Coon','female');
insert into pet_info values(103,'Jourge','cat','Russian Blule','male');
insert into pet_info values(104,'Jackson','dogs','English Mastiff','male');
insert into pet_info values(105,'Jimmy','dogs','Boxer','female');
Select * from pet_info;
create table pet_appoint(appointid number(4) primary key,petID number(4),datein date,dateout date);
desc pet_appoint;
insert into pet_appoint values(201,102,'10-jan-2019','11-jan-2019');
insert into pet_appoint values(202,101,'10-jan-2019','11-jan-2019');
insert into pet_appoint values(203,103,'10-jan-2019','11-jan-2019');
Select * from pet_appoint;

Select distinct type_of_pet from pet_info;
Select distinct breed from pet_info where type_of_pet='dogs';

Select breed from pet_info where type_of_pet='cat' group by breed having count(*)=1;

output:

if you still have any Problem regarding this question please comment and if you like my code please appreciate me by thumbs up thank you.........