I have the following instructions: Table # 1 Name: Employee Columns and Data Typ
ID: 3751360 • Letter: I
Question
I have the following instructions:
Table # 1 Name: Employee Columns and Data Types: • empNumber (char(8)), firstName (varchar(25)), lastName varchar(25)), ssn (char(9)), address (varchar(50)), state (char(2)), zip (char(5)), jobCode (char(4)) , dateOfBirth (date), certification(bit), salary(money) )
Table # 2 Name: Job: Columns and Data Types: • jobCode (char(4), jobdesc(varchar(50))
Next You will write the script to create constraints on these two tables. The following constraints must be created: • A Primary Key named PK_EmpNumber on the empNumber column in the Employee table. • A Primary Key named PK_JobCode on the jobCode column in the Job table. • A Foreign Key constraint named FK_JOB on the Employee table’s jobCode column which upholds referential integrity to the Job table’s primary key. • A Legal Value constraint on the Employee table named EMP_STATECHECK on the state column which can only be in either CA or FL. • A Legal Value constraint on the Job table named JOB_JOBCODE on the job column which only have one of the values ‘CAST’, ‘ENGI’, ‘INSP’ or ‘PMGR’
Step 2: Insert the data Write the Insert statements to populate 3 sample employees. Make up any sample data for your employees. Make sure your data doesn’t violate any constraints.
Write the Insert statements to populate the following available jobs codes and job descriptions: CAST Cast Member ENGI Engineer INSP Inspector PMGR Project Manager
AND i have done the following code:
create table Employee (empNumber char(8), firstName varchar(25), lastName varchar(25), ssn char(9), address varchar(50), state char(2), zip char(5), jobCode char(4) , dateOfBirth date, certification bit, salary money);
create table Job( jobCode char(4), jobdesc varchar(50));
ALTER TABLE Employee alter column empNumber char NOT NULL
alter table Employee add constraint PK_EmpNumber PRIMARY KEY(empNumber);
ALTER TABLE Job alter column jobCode char NOT NULL
alter table Job add constraint PK_JobCode PRIMARY KEY(jobCode);
alter table Employee add constraint FK_JOB FOREIGN KEY(jobCode) REFERENCES Job(jobCode);
alter table Employee add constraint EMP_STATECHECK CHECK(state in ('CA','FL'));
alter table Job add constraint JOB_JOBCODE CHECK(jobCode in ('CAST','ENGI','INSP','PMGR'));
insert into Employee values('1234567','Johna','Hill','98654321','ontorio','CA','12345','CAST','25-08-1974',1,10000);
insert into Employee values('3214568','King','Loe','97854321','ontorio','FL','32145','CAST','25-08-1954',1,50000);
insert into Employee values('3214876','Jay','Stain','96754123','mexico','CA','32541','ENGI','25-08-1994',0,5000);
insert into Job values('CAST','Cast Member'); insert into Job values('ENGI','Engineer');
insert into Job values('INSP', 'Inspector');
insert into Job values('PMGR','Project Manager');
but i get errors when i try to do these:
alter table Employee add constraint FK_JOB FOREIGN KEY(jobCode) REFERENCES Job(jobCode); error: Column 'Job.jobCode' is not the same length or scale as referencing column 'Employee.jobCode' in foreign key 'FK_JOB'. Columns participating in a foreign key relationship must be defined with the same length and scale. insert into Employee values('1234567','Johna','Hill','98654321','ontorio','CA','12345','CAST','25-08-1974',1,10000); insert into Employee values('3214568','King','Loe','97854321','ontorio','FL','32145','CAST','25-08-1954',1,50000); insert into Employee values('3214876','Jay','Stain','96754123','mexico','CA','32541','ENGI','25-08-1994',0,5000); error: Msg 8152, Level 16, State 14, Line 37 String or binary data would be truncated. The statement has been terminated. Msg 8152, Level 16, State 14, Line 38 String or binary data would be truncated. The statement has been terminated. Msg 8152, Level 16, State 14, Line 39 String or binary data would be truncated. The statement has been terminated.
and the same errors above for these lines of code : insert into Job values('CAST','Cast Member'); insert into Job values('ENGI','Engineer'); insert into Job values('INSP', 'Inspector'); insert into Job values('PMGR','Project Manager');
How do i solve these errors?
Explanation / Answer
Create view vw_ReadytoRetire as select empNumber, firstName, lastName from Employee where datediff(yy, dateOfBirth, GETDATE()) >62;
Create view vw_EmployeeAvgSalary as select avg(salary), jobCode from Employee group by jobCode;
Step4:- Creating Indexes
Create index IDX_LastName on Employee (lastName) ;
Create index IDX_ssn on Employee (ssn) ;