SQL statement

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.

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.