Create the SQL statements Include any statements so the script can be run multiple times. For example, if a table is being created in the script, include a statement at the beginning to drop the table first. Include a comment at the top with your name and the assignment name. Include a comment to designate each separate problem number. Do not allow the system to assign the constraint name or points will be deducted. Please utilize suggested readability conventions / make your script neat, clean and readable. After each problem, include code that would somehow verify your SQL statement worked properly.

Constraint Naming Conventions Assign a name to each constraint that is created in this assignment. Do not allow the system to assign the constraint name or points will be deducted. It's very probable that system assigned names would be different in your schema than in mine. If the constraints are given a specific name, then everyone knows how to refer to them. Follow the standard industry convention as follows; tablename_columnname_constraintType where constraintType is a two letter abbreviation as follows _pk = primary key constraint _fk = foreign key constraint _uk = unique constraint _ck = check constraint _nn = not null constraint

For example, the constraint name for problem #1 will be: storereps_id_pk NOTE: Please remember that the data stored in the data dictionaries are case sensitive. Be mindful of this when selecting from the tables user_constraints or user_cons_columns.

1. Modify the following SQL command so that the Rep_ID column is the PRIMARY KEY for the table and the default value of Y is assigned to the Comm column. ( The Comm column indicates whether the sales representative earns commission.)

CREATE TABLE store_reps

(rep_ID NUMBER(5),

last VARCHAR2(15),

first VARCHAR2(10),

comm CHAR(1) );

2.Change the STORE_REPS table so that NULL values cant be entered in the name columns (First and Last).

3. Change the STORE_REPS table so that only a Y or N can be entered in the Comm column.

4.Add a column named Base_salary with a datatype of NUMBER(7,2) to the STORE_REPS table. Ensure that the amount entered is above zero.

5.Create a table named BOOK_STORES to include the columns listed in the following chart. CREATE TABLE Book_stores

6.Add a constraint to make sure the Rep_ID value entered in the BOOK_STORES table is valid value contained in the STORE_REPS table. The Rep_ID columns of both tables were initially created as different datatypes. Does this cause an error when adding the constraint? Yes. Make table modifications as needed so that you can add the required constraint.

Column Name Datatype Constraint Comments

Store_ID Number(8) PRIMARY KEY column

Name VARCHAR2(30) Should be UNIQUE and NOT NULL

Contact VARCHAR2(30)

Rep_ID VARCHAR2(5)

7. Change the constraint created in Assignment #6 so that associated rows of the BOOK_STORES table are deleted automatically if a row in the STORE_REPS table is deleted.

8. Create a table named REP_CONTRACTS containing the columns listed in the following chart. A composite PRIMARY KEY constraint including the Rep_ID, Store_ID, and Quarter columns should be assigned. In addition, FOREIGN KEY constraints should be assigned to both the Rep_ID and Store_ID columns.

Column Name Datatype

Store_ID NUMBER(8)

Name NUMBER(5)

Quarter CHAR(3)

Rep_ID NUMBER(5)

9. Produce a list of information about all existing constraints on the STORE_REPS table.

10. Issue the commands to disable and then enable the CHECK constraint on the Base_salary column.