All employees may or may not be given a job_class. Job_class can be repeated in the employee table. If no first name is specified when inserting an employee record then a defa!lt of "!n#nown" will be entered. All employees m!st have a last name a hiredate and the hiredate m!st be $% 1&Jan&1''(.
)omplete the data dictionary below* Table: EMPLOYEE Column
Type
Len gth
Emp_+!m
emp_fname
emp_lname
Job_)lass
hiredate
PK
FK
Requie! "ot "ull
#nique
$ali!ation %Che&'
(e)ault
)omplete the data dictionary below* Table: JOB All chg_ho!r will defa!lt to a val!e of ,1-ho!r if no val!e is specified d!ring the insert. If a val!e is specified then it m!st be $% ,1-.
Column
Type
Len
PK
FK
gth
Requie!
#nique
"ot "ull
$ali!ation
(e)ault
%Che&'
job_code
job_class
chg_ho!r
/. )reate the EMP table from the sample data on the previo!s page. Also implement the following constraints. Column
Type
Len
PK
FK
gth
Emp_+!m
int
&ha
*+
emp_lname
&ha
*+
&ha
-
#nique
"ot "ull
$ali!ation
(e)ault
%Che&'
PK
emp_fname
Job_)lass
Requie!
un'no,n
""
unique
.ob /.ob_&o!e0
hiredate
!ate
""
123%3%344+
0. )reate the J2 table from the sample data on the previo!s page. Also implement the following constraints. Column
Type
Len
PK
gth
job_code
&ha
-
job_class
&ha
-+
!e&imal
56*
chg_ho!r
FK
Requie! "ot "ull
#nique
$ali!ation
(e)ault
%Che&'
PK
1235
35
-.
Add a new attrib!te to the J2 table named O7etimeChage that m!st always have a val!e.
3.
Add a constraint to the J2 table that will ens!re all )45_467 data is $% 8-.
9. Add a constraint that will add the val!e +A to all J2_):A;; col!mns if no val!e is specified when the record is inserted into the table.
<.
Add a constraint that always re=!ires a val!e entered into the )45_467 col!mn.
'. )hange the )45_467 col!mn to store n!mbers !p to 18 digits in length >incl!ding decimal points?.
1(.
@rop the constraint )4A75E_) from the )45_467 col!mn.
11.
@rop the col!mn named O7etimeChage from the J2 table.
18.
@rop the chg_ho!r col!mn and all of itBs data from the job table.
1/.
7emove the job table and all of itBs data.
10.
@rop the table emp and all rows of itBs data.
B( Data Manipulation $ommands: 1.
Insert the last / rows shown into the EMP table.
8.
6pdate the employee fname to Jim instead of James for employee 11<
/.
6pdate the @atabase @esigner chg_ho!r val!e by 1(C
0.
7emove the row from the job table for job code P75
-.
7emove the J2 table and all of itBs data
3.
7emove the employees from the employee table where the hiredate is before 1&Jan&'(
9.
7emove the emp table and all of itBs data
$( Select $ommands: 1.
;how the employee first and last names and their job class description and ho!rly charge.
8.
;ame =!estion as above b!t only show the records where the chg_ho!r is D1((
/.
;elect the ho!rs wor#ed and lastname and hiredate for all employees.
0. )reate a view called* 7_I+F which will select the ho!rs wor#ed and lastname and hiredate for all employees hired before 1&jan&(8.
-. ;elect employee fname and lname and all projects that they are wor#ing on. ;ave this select statement as a view called* EMP_P7JE)G; 3.
;elect employee fname and lname for every person wor#ing on project 1<.
9. ;elect all project names and job class descriptions that the project re=!ires. ;ave this select statement as a view called* EMP_J2_;I::;
<.
;elect all project names and job class descriptions that the project re=!ires for only the job code of @2@. ;ave this statement as a view called* @AGA2A;E_@E;I5+E7;
'. ;elect c!stomer first and last name credit limit and salesrep first and last name where the credit limit is $% 1-((.
