SQL查询，全写出的高分奖励

真是太多了，呵呵！！

1、
Create table yourname_employees(
employee_id NUMBER(6) not null,
first_name VARCHAR2(20),
last_name VARCHAR2(25),
email VARCHAR2(25),
phone_number VARCHAR2(20),
hire_date DATE,
job_id VARCHAR2(10),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
manager_id NUMBER(6),
department_id NUMBER(4)
)

2、查询emplyees表，将部门中员工数量大于10人的部门挑选出来，并将其中每个部门的工资合计计算出来。
select department_id from emplyees group by department_id having count(1)>10
select department_id, sum(salary) from emplyees group by department_id having count(1)>10

1.desc employees,desc departments
2.create yourname_employees as select * from employees
3.select department_id,sum(salary) from DEPARTMENTS where department_id in( select department_id from employees group by department_id having count(*)>10)group by department_id
4.update yourname_employees set salary=salary+200,manager_id=(select manager_id from employees where first_name='King' and last_name='Steven') where employee_id=106
5.delete from yourname_employees where salary>10000 and job_id like '%REF%'
6.declare
cursor aaa is select employee_id,first_name,last_name,salary,commission_pct from yourname_employees where department_id=80
7.for r in aaa loop
begin
if r.commission_pct is null then
insert into test_emp values(0.1);
else
insert into test_emp values(0.2);
end if;
EXCEPTION
WHEN OTHERS THEN
null;
END;
end loop;

时间有限 ，你看下面这个吧

/**将部门中员工数量大于10人的部门挑选出来，并将其中每个部门的工资合计计算出来。*/
select d.department_id as '部门ID' ,d.department_name as '部门名成',(select sum(salary) from employees where department_id=d.department_id) as '部门工资'
from departments as d where d.department_id in
(select department_id from employees as e where
(select count(*) from employees where department_id = e.department_id group by department_id) > 10 group by department_id)

作业题还是要自己写啊！！

额 太多了...