Assignment:
Turn in the SQL to answer the following questions (I do not need the tables, just the SQL).
1. Write SQL to correctlycreate the OCEAN table as specified below and to insert the data into the OCEAN table as given below
OCEAN:
|
Ocean
|
AverageDepth
|
PercentOfSurface
|
|
Pacific
|
13740
|
48
|
|
Atlantic
|
12254
|
28
|
|
Gulf
|
5299
|
1
|
Ocean is the primary key
2. Write SQL to correctly create the BEACH table as specified below (including the foreign key constraint) and write SQL to insert the data into the BEACH table as give below
BEACH:
|
Beach
|
State
|
Ocean
|
|
Manele Bay
|
Hawaii
|
Pacific
|
|
Myrtle Beach
|
South Carolina
|
Atlantic
|
|
Nantucket
|
Massachusetts
|
Atlantic
|
|
Clearwater Beach
|
Florida
|
Gulf
|
|
Coronado
|
Californa
|
Pacific
|
Beach is the primary key, ocean is a foreign key, set up a cascade on insert and cascade on delete for the foreign key referential integrity constraint
3. Using an update statement, update the Ocean Column to say "Gulf of Mexico" instead of "Gulf"
4. Write an SQL statement to delete the Nantucket record.
5. Write the SQL necessary to drop the Ocean table.
6. Write the SQL necessary to recreate the ocean table in the same way it was create for question 1 but add the constraint that PercentOfSurface must be Between 0 and 100. (insert the data into the table as well).
7. Write the SQL necessary to create a view that contains all of the beach data and ocean data (recompose the tables in a view).