Oracle 12c Joan Casteel.

Chapter 4 - Create the SQL statements for ALL the Hands-On Assignments at the end of Chapter 4 (#1 - #10).

Combine all of the SQL statements into one .sql file named yourname_3ch4.sql. 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 / standards demonstrated in the textbook to make your script neat, clean and readable. After each problem, include code that would somehow verify your SQL statement worked properly. As a reminder I've posted a model / sample for assignment submissions.

It's important to follow the assignment naming conventions because I've written a script to prompt me for your name and then run your script.

Note: Many of the problems require you to in some way ALTER or MODIFY objects. Do NOT use a CREATE TABLE command for each problem. There are only 3 problems that require a CREATE TABLE command.

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 can't 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.

6. Add a constraint to make sure the Rep_ID value entered in the BOOK_STORES table is a 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? Make table modifications as needed so that you can add the required constraint.

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.

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

10. Issue the commands to disbale and then enable the CHECK constraint on the Bse_salary column.