Given the Pet Database tables, answer the following questions: PET OWNER Owner ID OwnerLastName OwnerFirstName OwnerPhone...
Fantastic news! We've Found the answer you've been seeking!
Question:
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);
Expert Answer:
Answer rating: 100% (QA)
Q1 select PetBreedPetType from PET Q2 select pPetBree... View the full answer
Related Book For
Posted Date:
Students also viewed these databases questions
-
Write SQL Statements to (1) create the BREED table, (2) insert the data in Figure3-20 into the BREED table, (3) alter the PET_3 table so that PetBreed is a foreign key referencing BreedName in BREED,...
-
Jeremiah Wedgewood, the CFO, is adamant that the company needs to move ahead with the new division. While Josey also thinks that creating a new division with a new product line is a good idea, she is...
-
From the e-Activity, analyze management's role in the fraud and make recommendations as to how management could have prevented the fraud. Support your recommendation with examples. Imagine that you...
-
Refer to Exercises 11 and 13. Suppose that the sample proportion of students who did all their assigned homework last week is p = 57/100 = 0.57. Would this sample proportion provide convincing...
-
ZeeZee's Construction Company has the opportunity to select one of four projects (A, B, C, or D) or choose the null (do-nothing) alternative. Each project requires a single initial investment and has...
-
The management of Shatner Manufacturing Company is trying to decide whether to continue manufacturing a part or to buy it from an outside supplier. The part, called CISCO, is a component of the...
-
Discuss at least five of the formatting decisions you would have to make when typing a table. Compare the decisions you provided to those of two of your classmates. Do you agree or disagree with the...
-
Eta Corporation approaches Lily White, the CEO and sole shareholder of MuCo, regarding the acquisition of MuCo's cat toy division assets (worth $1.3 million). As selling the assets would create a...
-
During a proper requirements collection process, the requirements can be added iteratively and implicitly. True or False
-
You are the senior accountant for a business that regularly imports spare parts for a range of your products from overseas suppliers. You have been instructed by the CEO to look at ways you can save...
-
In which trading centers does 75 percent of all foreign exchange trading take place?
-
What is meant by the term ISO 9000?
-
You are the production director of a manufacturing company. In a highly competitive market, you carry out all of the major manufacturing tasks in a neighboring country as the wage levels there are 40...
-
Syd Gilman read the latest sales figures with a great deal of satisfaction. The vice-president of marketing at Hy Dairies Ltd., a large Canadian milk products manufacturer, was pleased to see that...
-
What is the difference between Four Probe Method and Two Probe Method? Explain with circuit diagrams.
-
A circular concrete shaft liner with Youngs modulus of 3.4 million psi, Poissons ratio of 0.25, unconfined compressive strength 3,500 psi and tensile strength 350 psi is loaded to the verge of...
-
Write a SQL statement to display the breed and type of all pets. PET_OWNER ( OwnerID , OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) PET ( PetID , PetName, PetType, PetBreed, PetDOB,?...
-
Apply the normalization process to the Veterinary Office ListVersion Two relation shown in Figure 1-31 to develop a set of normalized relations. Show the results of each of the steps in the...
-
Define the term database.
-
(a) The longitudinal data set "v4c" is in the vertical format; transform it into the horizontal format. (b) Transform the data set you obtained in part (a) back into the vertical format.
-
Perform some exploratory analysis on the DTS study described above. (a) Compute the mean and standard deviation of the HamD scores for the two treatment groups at each time point. (b) Treat repeated...
-
Plot the mean/SD of HIV knowledge of adolescent girls at baseline and three months post treatment stratified by treatment for the Sexual Health study.
Study smarter with the SolutionInn App