Please create the tables and add all the data to the tables so I can directly co

Question

Please create the tables and add all the data to the tables so I can directly copy it to mysql developer because I already have the answers(queries) to these questions 1. 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 2. Create a new table containing these four columns: Emp#, Lastname, Firstname, and Job_class. The table name should be EMPLOYEES. The Job_class column should be able to store character strings up to a maximum length of four, but the column values shouldn't be padded if the value has less than four characters. The Emp# column contains a numeric ID and should allow a five-digit number. Use column sizes you consider suitable for the Firstname and Lastname columns. 3. Add two columns to the EMPLOYEES table. One column, named EmpDate, contains the date of employment for each employee, and its default value should be the system date The second column, named EndDate, contains employees' date of termination. 4. Modify the Job_class column of the EMPLOYEES table so that it allows storing a maximum width of two characters. This is a MYSOL QUESTION PLEASE CREATE THE TABLES AS ASKED

Explanation / Answer

I have created the create table scripts as instructed in the question, and also given the sample data of 5 records for both the tables EMPLOYEES and ACCTMANAGER

*********************************CREATE TABLE SCRIPTS******************************

EMPLOYEES

create table EMPLOYEES(Emp# number(5,0), Lastname varchar2(12), Firstname varchar2(12), Job_Class char(4));

ACCTMANAGER

CREATE TABLE acctmanager
(amid CHAR(4),
amfirst VARCHAR2(12) NOT NULL,
amlast VARCHAR2(12) NOT NULL,
amedate DATE DEFAULT SYSDATE,
amsal NUMBER(8,2),
amcomm NUMBER(7,2) DEFAULT 0,
region CHAR(2),
CONSTRAINT acctmanager_amid_pk PRIMARY KEY (amid),
CONSTRAINT acctmanager_region_ck
CHECK (region IN ('N', 'NW', 'NE', 'S', 'SE', 'SW', 'W', 'E')));

***************************************END**************************************************

And the created tables are altered based on the requirement asked

*****************************ALTER TABLE SCRIPTS***************************************

EMPLOYEES

ALTER TABLE EMPLOYEES ADD (EmpDate DATE DEFAULT SysDate,EndDate DATE);

Alter Table Employees Modify (Job_Class char(2));

ACCTMANAGER

alter table ACCTMANAGER add(Comm_id number(2) default 10 constraint lowchk1 check(comm_id>=0) , Ben_id number(2) constraint lowchk2 check(ben_id>=0));

********************************************END************************************************

And as requested some of records are inserted in both the tables

******************************DATA INSERT SCRIPT************************************

EMPLOYEES

INSERT INTO EMPLOYEES (emp# , lastname , firstname ,job_class) VALUES (1234 ,'Huff' , 'Jamal' ,'PR');
INSERT INTO EMPLOYEES (emp# , lastname , firstname ,job_class) VALUES (1235 ,'Willaim' , 'SmithField' ,'JR');
INSERT INTO EMPLOYEES (emp# , lastname , firstname ,job_class) VALUES (1236 ,'Emma' , 'Roberts' ,'TR');
INSERT INTO EMPLOYEES (emp# , lastname , firstname ,job_class) VALUES (1237 ,'Lusia' , 'Jones' ,'CR');
INSERT INTO EMPLOYEES (emp# , lastname , firstname ,job_class) VALUES (1238 ,'John' , 'David' ,'LR');

ACCTMANAGER

INSERT INTO ACCTMANAGER (amid, amfirst, amlast, amedate, amsal, amcomm, region, comm_id, ben_id) VALUE ('T500', 'NICK', 'TAYLOR', '2009-09-05', 42000, '3500, 'NE', 9,

9);
INSERT INTO ACCTMANAGER (amid, amfirst, amlast, amedate, amsal, amcomm, region, comm_id, ben_id) VALUE ('L500', 'MANDY', 'LOPEZ', '2009-10-01', 47000, '1500, 'NW', 12,

80);
INSERT INTO ACCTMANAGER (amid, amfirst, amlast, amedate, amsal, amcomm, region, comm_id, ben_id) VALUE ('J500', 'SAMMIE', 'JONES', '2009-09-24', 39500, '2000, 'N',

10, 12);
INSERT INTO ACCTMANAGER (amid, amfirst, amlast, amedate, amsal, amcomm, region, comm_id, ben_id) VALUE ('D500', 'SCOTT', 'DAVIS', '2009-09-24', 53000, '6000, 'SE', 55,

66);
INSERT INTO ACCTMANAGER (amid, amfirst, amlast, amedate, amsal, amcomm, region, comm_id, ben_id) VALUE ('M500', 'PEG', 'OHARA', '2009-09-24', 46000, '2000, 'SW', 78,

40);

**************************************END***************************************************

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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