The database to use is SQL Developer The management of “Just Lee Books” has appr

Question

The database to use is SQL Developer

The management of “Just Lee Books” has approved implementing a new commission policy and benefits plan for the account managers. The following changes need to be made to the existing database:

Two new columns must be added to the ACCTMANAGER table: one to indicate the commission classification assigned to each employee and another to contain each employee’s benefit code. The commission classification column should be able to store integers up to a maximum value of 999 and be named Commission_ID. The value of the Commission_ID column should be set to a value of 11 automatically if no value is provided when a row is added. The benefits code column should also accommodate integer values up to a maximum of 999 and be named Benefit_ID.

A new table, COMMRATE, must be created to store the commission rate schedule and must contain the following columns:

Commission_id: a numeric column similar to the one added to the ACCTMANAGER table.

Commission_Rank: a character field that can store a rank name allowing up to 15 characters

Commission_Rate: a numeric field that can store two decimal digits such as .01 or .03

A new table, BENEFITS, must be created to store the available benefit plan options and must contain the following columns:

Benefit_ID: a numeric column similar to the one added to the ACCTMANAGER table

Benefit_plan: a character field that can store a single character value

Benedit_provider: a numeric field that can store a three digit integer

Is_Active: a character field that can hold a value of Y or N.

INSTRUCTIONS

Create and execute SQL statements to address the changes needed to support the new commission and benefits data. In a word document, copy the SQL statements as well as the Oracle spooled output results.

Paper must be Word document file type.

Paper must include a cover page that includes necessary information identifying lesson number, assignment title, date, your full name, etc.

Embed screenshots within your paper.

Explanation / Answer

Adding two columns to the ACCTMANAGER table:

Creating column Commission_ID and Benefit_ID:

alter table ACCTMANAGER

add (Commission_ID number(3) default 11,

Benefit_ID number(3) );

Create table COMMRATE:

create table COMMRATE(Commission_id number(3), Commission_Rank varchar2(15), Commission_Rate number(4,2));

Create table BENEFITS :

create table BENFITS(Benefit_ID number(3), Benefit_plan varchar2(1), Benefit_provider number(3), Is_Active varchar2(1) check (upper(Is_Active) in ('Y','N')));

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.