Write a script to create the commands that will run the following queries/problem scenarios.

1. Create the LARGE_SLIP table with the following structure:

Column	Type	Length	Decimal Places	Nulls Allowed	Description
MARINA_NUM	VARCHAR	4	Â	No	Primary Key
SLIP_NUM	VARCHAR	4	Â	No	Primary Key
RENTAL_FEE	DECIMAL	8	2	Â	Â
BOAT_NAME	VARCHAR	50	Â	Â	Â
OWNER_NUM	VARCHAR	4	Â	Â	Â

1. To set the combination Primary Key of Marina_Num and Slip_Num for this table, complete the following directions below when you are writing the create table code:
   Do not declare the Primary Key with each column declaration. After you have declared the Owner_Num field, then add a comma , hit enter to get a new line and then type the following constraint information.
2. CONSTRAINT LARGE_SLIP_MNum_SNum_PK PRIMARY KEY (Marina_Num, Slip_Num));
3. Insert into the LARGE_SLIP table the marina number, slip number, rental fee, boat name, and owner number for those slips whose length is 40 feet. NOTE: Do not use individual inserts, but only use one command instead.
4. Alexamara has increased the rental fee of each large slip by $150. Update the rental fees in the LARGE_SLIP table accordingly.
5. After increasing the rental fee or each large slip by $150, Alexamara decides to decrease the rental fee of any slip whose fee is more than $4,000 by one percent. Update the rental fees in the LARGE_SLIP table accordingly.-
6. Insert a new row into the LARGE_SLIP table. The marina number is 1, the slip number is A4, the rental fee is $3,900, the boat name is Bilmore, and the owner number is FE82.
7. Delete all slips in the LARGE_SLIP table for which the owner number is TR72.
8. The name of the boat in marina 1 and slip A1 is in the process of being changed to an unknown name. Change the name of this boat in the LARGE_SLIP table to null.
9. Add to the LARGE_SLIP table a new character column named CHARTER that is one character in length. (This column will indicate whether the boat is available for chartering.) Set the value for the CHARTER column on all rows to N.
10. Change the CHARTER column in the LARGE_SLIP table to Y for the slip containing the boat named Our Toy.
11. Change the length of the BOAT_NAME column in the LARGE_SLIP table to 60 characters.
12. Change the RENTAL_FEE column in the LARGE_SLIP table to reject nulls.
13. Delete the LARGE_SLIP table from the database

 

What to Hand In

Create a SQL script and upload it back to the Assignments link.

 

CIT 214 - Homework 2- Alexamara Marina GroupUpdating Data and Altering Table StructureWrite a script to create the commands that will run the following queries/problem scenarios.1.Create the LARGE_SLIP table with the following structure:ColumnTypeLengthDecimalPlacesNullsAllowedDescriptionMARINA_NUMVARCHAR4NoPrimaryKeySLIP_NUMVARCHAR4NoPrimaryKeyRENTAL_FEEDECIMAL 82BOAT_NAMEVARCHAR50OWNER_NUMVARCHAR42.To set the combination Primary Key of Marina_Num and Slip_Num for this table, complete the following directionsbelow when you are writing the create table code:Do not declare the Primary Key with each column declaration. After you have declared the Owner_Num field, thenadd a comma , hit enter to get a new line and then type the following constraint information.

Attachments:

• 1496028044-SQLHomework2 1.docx