Given the Pet Database tables, answer the following questions: PET OWNER Owner ID OwnerLastName OwnerFirstName OwnerPhone...

Transcribed Image Text:

Given the Pet Database tables, answer the following questions: PET OWNER Owner ID OwnerLastName OwnerFirstName OwnerPhone OwnerEmail 1 2 3 PET PetID 1 2 3 لا t 5 6 7 Downs James Frier Trent PetName King Teddy Fido PetType Dog Cat Dog AJ Dog Cedro Cat Wooley Cat Buster Dog Marsha Richard Liz Miles PetBreed Std. Poodle Cashmere Std. Poodle Collie Mix 555-537-8765 Marsha.Downs@somewhere.com 555-537-7654 Richard. James@somewhere.com 555-537-6543 Liz.Frier@somewhere.com Miles. Trent@somewhere.com NULL PetDOB 2011-02-27 PetWeight OwnerID 25.5 1 2012-02-01 10.5 2 2010-07-17 28.5 1 2011-05-05 20.0 2009-06-06 9.5 Unknown Unknown 9.5 Border Collie 2008-12-11 25.0 HULL 3 2 2 4 Using the attached script to create and fill the tables for the data retrieval queries of this assignment. (copy and paste the code from MySQL and screenshot from the results to the word document. Upload both SQL code and word document answers: Q1. Write an SQL statement to display the breed and type of all pets. (10 points) Place code and screenshot of results here Q2. Write an SQL statement to display the breed, type, and DOB for all pets having the type Dog and the breed Std. Poodle. (15 points) Place code and screenshot of results here Q3. Write an SQL statement to display the name, breed, and type for all pets that are not of type Dog or Fish. (15 points) Place code and screenshot of results here Q4. Write an SQL statement to display the pet ID, breed, and type for all pets having a four- character name starting with K. (15 points) Q5. Write an SQL statement to display the last name, first name, and email of any owner without a phone number. (15 points) Place code and screenshot of results here Q6. Write an SQL statement to display the name and breed of all pets, sorted by Pet Breed in ascending order and by Pet Name in descending order within Pet Breed. (15 points) Place code and screenshot of results here Q7. Write an SQL statement to display the average weight per pet breed. Consider breeds for which two or more pets are included in the database, but do not consider any pet having the breed of Unknown. (15 points) Place code and screenshot of results here /* In Class Activity Part 1 CREATE TABLE PET_OWNER( Owner ID Owner Last Name OwnerFirstName Owner Phone Owner Email CONSTRAINT ); CREATE TABLE PET( PetID PetName PetType PetBreed PetDOB PetWeight Owner ID CONSTRAINT CONSTRAINT PET_OWNER (Owner ID) ); Int Tables and Data*/ Int Char (25) Char (25) Char (12) VarChar (100) OWNER_PK Char (50) Char (25) VarChar (100) Date Numeric (4,1) Int PET_PK PET_OWNER_FK NULL, NOT NULL, NULL, NOT NULL auto_increment, NOT NULL, NOT NULL, NULL, NULL, PRIMARY KEY (Owner ID) ON DELETE CASCADE auto_increment NOT NULL, NOT NULL, NULL, NOT NULL, PRIMARY KEY (PetID), FOREIGN KEY (Owner ID) REFERENCES /***** PET OWNER DATA INSERT INTO PET_OWNER VALUES (null, 'Downs', 'Marsha', '555-537-8765', 'Marsha. Downs@somewhere.com'); INSERT INTO PET_OWNER VALUES (null, 'James', 'Richard', '555-537-7654', 'Richard. James@somewhere.com'); INSERT INTO PET_OWNER VALUES(null, 'Frier', 'Liz', '555-537-6543', 'Liz.Frier@somewhere.com'); INSERT INTO PET_OWNER (OwnerID, Owner Last Name, OwnerFirstName, Owner Email) VALUES (null, 'Trent', 'Miles', 'Miles. Trent@somewhere.com'); /***** PET DATA *** */ INSERT INTO PET VALUES (null, 'King', 'Dog', 'Std. Poodle', '2011-02-27', 25.5, 1); INSERT INTO PET VALUES(null, 'Teddy', 'Cat', 'Cashmere', '2012-02-01', 10.5, 2); INSERT INTO PET VALUES (null, 'Fido', 'Dog', 'Std. Poodle', '2010-07-17', 28.5, 1); INSERT INTO PET VALUES(null, 'AJ', 'Dog', 'Collie Mix', '2011-05-05', 20.0, 3); INSERT INTO PET VALUES (null, 'Cedro', 'Cat', 'Unknown', '2009-06-06', 9.5, 2); INSERT INTO PET (PetID, PetName, PetType, PetBreed, PetWeight, Owner ID) VALUES (null, 'Wooley', 'Cat', 'Unknown', 9.5, 2); INSERT INTO PET VALUES (null, 'Buster', 'Dog', 'Border Collie', '2008-12-11', 25.0, 4); Given the Pet Database tables, answer the following questions: PET OWNER Owner ID OwnerLastName OwnerFirstName OwnerPhone OwnerEmail 1 2 3 PET PetID 1 2 3 لا t 5 6 7 Downs James Frier Trent PetName King Teddy Fido PetType Dog Cat Dog AJ Dog Cedro Cat Wooley Cat Buster Dog Marsha Richard Liz Miles PetBreed Std. Poodle Cashmere Std. Poodle Collie Mix 555-537-8765 Marsha.Downs@somewhere.com 555-537-7654 Richard. James@somewhere.com 555-537-6543 Liz.Frier@somewhere.com Miles. Trent@somewhere.com NULL PetDOB 2011-02-27 PetWeight OwnerID 25.5 1 2012-02-01 10.5 2 2010-07-17 28.5 1 2011-05-05 20.0 2009-06-06 9.5 Unknown Unknown 9.5 Border Collie 2008-12-11 25.0 HULL 3 2 2 4 Using the attached script to create and fill the tables for the data retrieval queries of this assignment. (copy and paste the code from MySQL and screenshot from the results to the word document. Upload both SQL code and word document answers: Q1. Write an SQL statement to display the breed and type of all pets. (10 points) Place code and screenshot of results here Q2. Write an SQL statement to display the breed, type, and DOB for all pets having the type Dog and the breed Std. Poodle. (15 points) Place code and screenshot of results here Q3. Write an SQL statement to display the name, breed, and type for all pets that are not of type Dog or Fish. (15 points) Place code and screenshot of results here Q4. Write an SQL statement to display the pet ID, breed, and type for all pets having a four- character name starting with K. (15 points) Q5. Write an SQL statement to display the last name, first name, and email of any owner without a phone number. (15 points) Place code and screenshot of results here Q6. Write an SQL statement to display the name and breed of all pets, sorted by Pet Breed in ascending order and by Pet Name in descending order within Pet Breed. (15 points) Place code and screenshot of results here Q7. Write an SQL statement to display the average weight per pet breed. Consider breeds for which two or more pets are included in the database, but do not consider any pet having the breed of Unknown. (15 points) Place code and screenshot of results here /* In Class Activity Part 1 CREATE TABLE PET_OWNER( Owner ID Owner Last Name OwnerFirstName Owner Phone Owner Email CONSTRAINT ); CREATE TABLE PET( PetID PetName PetType PetBreed PetDOB PetWeight Owner ID CONSTRAINT CONSTRAINT PET_OWNER (Owner ID) ); Int Tables and Data*/ Int Char (25) Char (25) Char (12) VarChar (100) OWNER_PK Char (50) Char (25) VarChar (100) Date Numeric (4,1) Int PET_PK PET_OWNER_FK NULL, NOT NULL, NULL, NOT NULL auto_increment, NOT NULL, NOT NULL, NULL, NULL, PRIMARY KEY (Owner ID) ON DELETE CASCADE auto_increment NOT NULL, NOT NULL, NULL, NOT NULL, PRIMARY KEY (PetID), FOREIGN KEY (Owner ID) REFERENCES /***** PET OWNER DATA INSERT INTO PET_OWNER VALUES (null, 'Downs', 'Marsha', '555-537-8765', 'Marsha. Downs@somewhere.com'); INSERT INTO PET_OWNER VALUES (null, 'James', 'Richard', '555-537-7654', 'Richard. James@somewhere.com'); INSERT INTO PET_OWNER VALUES(null, 'Frier', 'Liz', '555-537-6543', 'Liz.Frier@somewhere.com'); INSERT INTO PET_OWNER (OwnerID, Owner Last Name, OwnerFirstName, Owner Email) VALUES (null, 'Trent', 'Miles', 'Miles. Trent@somewhere.com'); /***** PET DATA *** */ INSERT INTO PET VALUES (null, 'King', 'Dog', 'Std. Poodle', '2011-02-27', 25.5, 1); INSERT INTO PET VALUES(null, 'Teddy', 'Cat', 'Cashmere', '2012-02-01', 10.5, 2); INSERT INTO PET VALUES (null, 'Fido', 'Dog', 'Std. Poodle', '2010-07-17', 28.5, 1); INSERT INTO PET VALUES(null, 'AJ', 'Dog', 'Collie Mix', '2011-05-05', 20.0, 3); INSERT INTO PET VALUES (null, 'Cedro', 'Cat', 'Unknown', '2009-06-06', 9.5, 2); INSERT INTO PET (PetID, PetName, PetType, PetBreed, PetWeight, Owner ID) VALUES (null, 'Wooley', 'Cat', 'Unknown', 9.5, 2); INSERT INTO PET VALUES (null, 'Buster', 'Dog', 'Border Collie', '2008-12-11', 25.0, 4);

Answer rating: 100% (QA)

Q1 select PetBreedPetType from PET Q2 select pPetBree... View the full answer