write an SQL statement to display the OwnerLastName, OwnerFirstName, and OwnerEmail of any owners of cats. Use a subquery with PET_OWNER and PET_3.

 

Never use plagiarized sources. Get Your Original Essay on
Computer Science Assignment
Hire Professionals Just from $11/Page
Order Now Click here

The following table schema is for the BREED table.
BREED(BreedName, MinWeight, MaxWeight, AverageLifeExpectancy)
*MinWeight and MaxWeight should store 1 decimal place.

PetBreed in PET_3 will be a foreign key to the BreedName in BREED.
PET_3(PetID, PetName, PetBreed, PetType, PetDOB, PetWeight, OwnerID)

Write an SQL statement to create the BREED table. Don’t forget the foreign key constraint; updates should cascade but deletes should not.

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 using a subquery. The PET_OWNER, PET_3, and BREED tables are below.

Write an SQL statement to display the OwnerLastName, OwnerFirstName, and OwnerEmail of any owners of cats. Use a JOIN. The PET_OWNER and PET_3 tables are below. Do not show duplicates.

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 using a JOIN. The PET_OWNER, PET_3, and BREED tables are below. Do not show duplicates.

Write an SQL statement to display the OwnerLastName, OwnerFirstName, PetName, PetType, PetBreed, and AverageLifeExpectancy for pets with a known PetBreed. Use PET_3. The PET_OWNER, PET_3, and BREED tables are below.

Write SQL statements to add these 3 new rows to the PET_OWNER table. Assume that OwnerID is a surrogate key and the DBMS will provide a value for it.

OwnerID

OwnerLastName

OwnerFirstName

OwnerPhone

OwnerEmail

5

Rogers

Jim

555-232-3456

Jim.Rogers@somewhere.com

6

Keenan

Mary

555-232-4567

Mary.Keenan@somewhere.com

7

Melnik

Nigel

555-232-5678

Nigel.Melnik@somewhere.com

 

Write SQL statements to add these 3 new rose to the PET_OWNER table. Assume that OwnerID is a surrogate key and the DBMS will provide a value for it. However, you do not have a value for OwnerEmail.

Write an SQL statement to change the value of Std. Poodle in BreedName of BREED to Poodle, Std. Assume cascading updates in the PET_3 table. (Try it first without a WHERE clause.)

Write an SQL statement to delete all rows of pets of PetType Cat in the PET_3 table. NOTE, do not use Anteater, as in the book. (Try it first without a WHERE clause.)

Write an SQL statement to add a PetWeight column to the PET table, given that this column allows NULLs. Assume a type of Numeric(4,1)

Write SQL statements to insert the following data into the PetWeight column of the PET table.

PetID

PetName

PetType

PetBreed

PetDOB

PetWeight

OwnerID

1

King

Dog

Std. Poodle

2009-02-27

25.5

1

2

Teddy

Cat

Cashmere

2010-02-01

10.5

2

3

Fido

Dog

Std. Poodle

2008-07-17

28.5

1

4

AJ

Dog

Collie Mix

2009-05-05

20.0

3

5

Cedro

Cat

Unknown

2007-06-06

9.5

2

6

Wooley

Cat

Unknown

NULL

9.5

2

7

Buster

Dog

Border Collie

2006-12-11

25.0

4

 

Write an SQL statement to change the PetWeight column of the PET table so it does not allow NULLs. Assume the column exists, and that it currently allows NULLs

Write an SQL statement to drop the PetWeight column in the PET table.

 

 

 

Need a custom written plagiarism free essay? Click here to order now.

Open chat
Lets chat on via WhatsApp
Hello, Welcome to our WhatsApp support. Reply to this message to start a chat.