jan/feb 11 - Section B

ok, this is my attempt.... not tested in Oracle... please comment.

1: sal = not column, annual salary to be displayed as "annual salary", * is used and not X and a comma is missing after last name

select last_name, job_id, start_Date
from employees
where last_name in ('Matos', 'Taylor'
order by start_Date asc

3 select last_name, round(months_between(sysdate, hire_Date),0) AS Motnths_Worked
from employees
order by Motnths_Worked

4 select last_name, commission_pct,
to_char(nvl(commission_pct),"No Comm" As comm
from employees

5 select max(Salary) - min(Salary) difference
from employees

6 select e.last_name, e.job_id, d.department_name, e.department_id
from employees e join departments d
on (e.department_id = d.department_id)
Join locations l
on (d.location_id = l.location_id)
and l.city = 'Toronto'

7HELP ????? Do we need to do two seperate set operators or one??

8 Create table emp_jobcard
(Emp_id Number (4) Constraint emp_id_pk primary key
constraint emp_id_fk foreign key references employees(employee_id),
J_date Date constraint date_nn not null,
job_Card_num number(4) constraint job_Card_uq unique,
j_hours_worked number(4),
comments varchar2(200),
overtime char(1) constraint overtime_ck check overtime <> 0)

9 create or replace view employee_vu as
select employee_id, last_name employee, department_id
from employees

10 select column_name, data_type, data_length, data_precision, data_Scale, nullable
from user_tab_columns
where table_name = upper(':Name'

11 create sequence dept_id_Seq
increment by 10
start with 200
maxvalue 1000
nocache
nocycle

insert into dept(dept_id, dept_name)
values (dept_id_seq.nextval, 'Administration'

12 - Please help.... I'm really stuck on this specifc section

13 create table employees2 AS
select employee_id, first_name, last_name, salary, department_id
from employees

Alter table employees2
drop employee_id, department_id

Alter table employees2
add ID(number(7), dept_id(number7)

Alter table employees2
drop first_name

alter table employees2
set unused dept_id

alter table employees2
drop unused columns

Drop table employees2
Select * recyclebin

Thanks for helping.