Since I won't be at the final class, I am posting this for my "team-mates" Victoria and Ed (right now it's incomplete, but I hope to have it finished by the time class starts at 6:30.....):
-------------------------------------------------------------------------------------------
#7.
create sequence emp_seq start with 207;
#8.
alter table emp
add constraint pk_emp_empid
primary key (employee_id);
#9.
alter table dept
add constraint pk_dept_depid
primary key (department_id);
#10.
alter table emp
add constraint fk_emp_depid
foreign key (department_id)
references dept (department_id);
#11.
insert into emp
(employee_id, last_name, email, hire_date, job_id, department_id)
values
(emp_seq.nextval, 'Steinmetz', 'curt@nowhere.com', sysdate, 'IT_PROG', 60);
#13:
select first_name||' '||last_name "Full Name", department_name
from emp join dept
using (department_id)
order by department_name;
#14.
select first_name||' '||last_name, salary, employee_id
from emp
where salary >= (select max(salary) from emp);
#15
select last_name, job_id, department_id, department_name, city
from emp join dept
using (department_id)
join location
using (location_id)
where upper(city) like 'SEATTLE';
(NOTE: REPLACE 'SEATTLE' WITH 'TOKYO')
#16.
select last_name,
to_char(hire_date, 'MM/DD/YYYY') "Hire Date",
to_char(salary, '$999,999.99') "Salary"
from emp
#17., #18, #19
create table emp5
as select * from emp
where 1 = 2;
create table emp8
as select * from emp
where 1 = 2;
create table empall
as select * from emp
where 1 = 2;
#20.
insert all
when department_id = 50 then into emp5 values
(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
when department_id = 80 then into emp8 values
(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
when last_name is not null then into empall values
(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID
from emp
where department_id =
(select department_id from emp
where upper(last_name) = 'MATOS');
