Advanced Challenge The management of JustLee Books has approved implementing a n

Question

Advanced Challenge The management of JustLee 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 benefits code. The commission classification column should be able to store integers up to a maximum value of 99 and be named Comm_id. The value of the Comm_id column should be set to a value of 10 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 99 and be named Ben_id. 97 Table Creation and Management Copyright 2016 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.

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

• Comm_id: A numeric column similar to the one added to the ACCTMANAGER table

• Comm_rank: A character field that can store a rank name allowing up to 15 characters

• 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:

• Ben_id: A numeric column similar to the one added to the ACCTMANAGER table • Ben_plan: A character field that can store a single character value

• Ben_provider: A numeric field that can store a three-digit integer • Active: A character field that can hold a value of Y or N Required: Create the SQL statements to address the changes needed to support the new commission and benefits data.

Just need the SQL statements o make he following changes.

Thanks

Welcome James _ Black eChegg Study l Guided sx ORACLE ® 12c SQL wAC-Chapter 4 A swer.cxDisarplus Release 10.2.0 L X × > ctorantuto.com/upload/Oracle/books/oracle-12c-sgl.pdf : Apps D Bootyquak Bookmarks D Point Reduction and The ideal growing cc D CAKE BIRTH Shroomery PF TEK Shroomery-casing D How to Legally Acce Delete the JL EMPS table so that it can be restored. Restore the JL EMPS table and verify that it's available again. Advanced Challenge The management of JustLee 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 databas0: 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 benefits code. The commission classification column shouk be able to store integers up to a maximum value of 99 and be named Comm id. The value of the Comm id column should be set to a value of 10 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 99 and . Table Creation and Management A new table, COMMRATE, must be created to store the commission rate schedule and must contain the tolowing coluns: .Comm id: A numeric column simlar to the one added to the . ACCTMANAGER table .Comm rank: A character fiold that can store a rank name allowing up to 15 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 tollowing columns: .Ben id: A numeric column simlar to the one added to the ACCTMANAGER table . Ben plan: A character field that can store a single character value .Ben provider: A numeric field that can store a three-digit integer Active: A character field that can hold a value of Y or N Required: Create the SQL statements to address the changes needed to support the new commission and benefits data. Type here to search 616 PM 3/10/2018

Explanation / Answer

CREATE TABLE ACCTMANAGER(
    Comm_id INT DEFAULT 10 CHECK(Comm_id<100),
    Ben_id INT CHECK(Ben_id<100)
);

CREATE TABLE COMMRATE(
    Comm_id INT DEFAULT 10 CHECK(Comm_id<100),
    Comm_rank VARCHAR(15),
    Rate DECIMAL(2,2)
);

CREATE TABLE BENEFITS(
    Ben_id int CHECK(Ben_id<100),
    Ben_Plan CHAR(1),
    Ben_provider INT(3),
    Active ENUM('Y', 'N')
);

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

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