1.

create table k820_employee
(
ssn varchar(11) not null primary key,
lastname varchar(40) not null,
firstname varchar(30) null,
department_code integer(3) not null,
annual_salary varchar(20) null,
hire_date varchar(10) null
);

2.

create table k820_Department
(
department_code integer(3) not null primary key,
department_name varchar(30) not null
);

3.

INSERT INTO k820_employee
(ssn, lastname, firstname, department_code, annual_salary, hire_date)
values
(
"111-22-3333",
"Smith",
"John",
234,
"$50,000",
"1999-10-15"
);

update k820_employee set annual_salary=replace(annual_salary,'$','')
update k820_employee set annual_salary=replace(annual_salary,',','')

INSERT INTO k820_employee
(ssn, lastname, firstname, department_code, annual_salary, hire_date)
values
(
"222-33-4444",
"Jones",
"Mary",
234,
"$56,000",
"1998-01-02"
);

update k820_employee set annual_salary=replace(annual_salary,'$','')
update k820_employee set annual_salary=replace(annual_salary,',','')

4.

INSERT INTO k820_Department
(department_code, department_name)
values
(
234,
"Information Services"
);

INSERT INTO k820_Department
(department_code, department_name)
values
(
456,
"Systems Group"
);

5.

SELECT lastname, firstname, hire_date from k820_employee ORDER BY lastname;

6.

SELECT AVG( annual_salary ) FROM k820_employee;

7.

9.



10.

DROP TABLE k820_Department;