Question: Data table for a few SQL questions : Use the following tables for your answers: PET_OWNER (OwnerID, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) PET_3 (PetID, PetName, PetType,

Data table for a few SQL questions :

Use the following tables for your answers:

PET_OWNER (OwnerID, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail)

PET_3 (PetID, PetName, PetType, PetBreed, PetDOB, PetWeight, OwnerID)

BREED (BreedName, MinWeight, MaxWeight, AverageLifeExpectancy)

OwnerID in PET_3 must exist in OwnerID in OWNER.

PetBreed in PET_3 must exist in BreedName in BREED.

Sample data for these tables are shown below. For each SQL statement you write, show the results based on these data.

PET_OWNER Data

OwnerID

OwnerLastName

OwnerFirstName

OwnerPhone

OwnerEmail

1

Downs

Marsha

555-537-8765

marsha@sw.com

2

James

Richard

555-537-7654

richard@sw.com

3

Frier

Liz

555-537-6543

liz@sw.com

4

Trent

Miles

miles@sw.com

PET_3 Data

PetID

PetName

PetType

PetBreed

PetDOB

PetWeight

OwnerID

1

King

Dog

Std. Poodle

27-Feb-11

25.5

1

2

Teddy

Cat

Cashmier

01-Feb-12

10.5

2

3

Fido

Dog

Std. Poodle

17-Jul-10

28.5

1

4

AJ

Dog

Collie Mix

05-May-11

20.0

3

5

Cedro

Cat

Unknown

06-Jun-09

9.5

2

6

Woolley

Cat

Unknown

9.5

2

7

Buster

Dog

Border Collie

11-Dec-08

25.0

4

BREED Data

BreedName

MinWeight

MaxWeight

AverageLifeExpectancy

Border Collie

15.0

22.5

20

Cashmier

10.0

15.0

12

Collie Mix

17.5

25.0

18

Std. Poodle

22.5

30.0

18

Unknown

Question 1: Write an SQL statement to display the OwnerLastName, OwnerFirstName, and OwnerEmail of any owner of a pet that has an AverageLifeExpectancy value greater than 15. Use a join.

Question 2: Write an SQL statement to display the OwnerLastName, OwnerFirstName, PetName, PetType, PetBreed, and AverageLifeExpectancy for pets with a known PetBreed. Use a join.

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!

Q:

Write the SQL statements necessary to remove the PET_OWNER table from the database. Assume that the PET table is to be removed. ? Do not run these commands in an actual database! PET_OWNER ( OwnerID...

Q:

Answer question 3.35, but use a join using JOIN ON syntax. PET_OWNER ( OwnerID , OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) PET ( PetID , PetName, PetType, PetBreed, PetDOB,? OwnerID )...

Q:

Write an SQL statement to count the number of distinct breeds. PET_OWNER ( OwnerID , OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) PET ( PetID , PetName, PetType, PetBreed, PetDOB,? OwnerID...

Q:

Answer question 3.36, but use a join using JOIN ON syntax. PET_OWNER ( OwnerID , OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) PET ( PetID , PetName, PetType, PetBreed, PetDOB,? OwnerID )...

Q:

Write SQL statements to add a PetWeight column like the one in PET_3 to the PET table, given that this column is NOT NULL. Again, assume that PetWeight is Numeric(4,1). Use the PetWeight data from...

Q:

Write SQL statements to insert data into the PetWeight column you created in question 3.47. Use the PetWeight from the PET_3 table. PET_OWNER ( OwnerID , OwnerLastName, OwnerFirstName, OwnerPhone,...

Q:

Write an SQL statement to add a PetWeight column like the one in PET_3 to the PET table, given that this column is NULL. Again, assume that PetWeight is Numeric(4,1). PET_OWNER ( OwnerID ,...

Q:

Write the required SQL statements to create the PET_3 table. Assume that PetWeight is Numeric(4,1). PET_OWNER ( OwnerID , OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) PET ( PetID , PetName,...

Q:

Write an SQL statement to add a CHECK constraint to the PET table so that the weight data recorded in the PetWeight column you added to the table in either question 3.47 or 3.49 is less than 250....

Q:

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,...

Q:

The following account summarizes the financial position of Rodriguez Resources, Inc., on May 31, 2012: May 31, 2012 financial position: Assets = Liabilities + Stockholders Equity Accounts + Common +...

Q:

THE TASK: I. To evaluate (graphically and in words, no calculations necessary) the impact of the following policy option: imposing price controls on fuel (which consumers have little choice but to...

Q:

Zaphod Beeblebrox earned an average rate of return of J 2 = 9 % . Calculate how many half - years it will take for his money to double.

Q:

P-1) (100 Pts.) A chemical manufacturing company (CMC) has a contract for the procurement of the neccssaly chemicals from four suppliers. The chemicals purchased from Supplier A are priced at $20...

Q:

(Appendices) Why do readers of financial statements prefer the separate disclosure of gross sales revenue and sales returns and allowances to the disclosure of a single net sales revenue amount? LO61

Q:

(Appendices) Name the two allowance methods used to compute uncollectible account expense. For each method, how is uncollectible account expense computed and what does the balance of the allowance...

Q:

(Appendices) INCOME EFFECTS OF UNCOLLECTIBLE ACCOUNTS. The credit manager and the accountant for Goldsmith Company are attempting to assess the effect on net income of writing off $100,000 of...