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.