oracle问题：怎么写？

Question

1.建一个员工信息表employee，表中id(员工代码)、sex(员工性别)、name(姓名)、departmentid(部门代码)、address(住址)、birthdate(生日)、postcode(邮编)、salary(薪水)、workdate(入职日期)、remark(备注信息)。其中postcode、remark可以为空，薪水
需为number类型，生日、入职日期为date型。以员工代码为主键。
如图：这是面试题，怎么？

Answer 1

楼主为何不采纳？
1.
CREATE TABLE employee
(
id VARCHAR2(04) NOT NULL, /* 员工代码 */
sex VARCHAR2(01) NOT NULL, /* 员工性别 */
name VARCHAR2(10) NOT NULL, /* 姓名 */
departmentid VARCHAR2(04) NOT NULL, /* 部门代码 */
address VARCHAR2(60) NOT NULL, /* 住址 */
birthdate DATE NOT NULL, /* 生日 */
postcode VARCHAR2(06) , /* 邮编 */
salary NUMBER(10,2) NOT NULL, /* 薪水 */
workdate DATE NOT NULL, /* 入职日期 */
remark VARCHAR2(200) , /* 备注信息 */
CONSTRAINT employee_pk PRIMARY KEY (id)
);

2.
INSERT INTO employee VALUES ('0023', 'M', '张飞', 'A001', '上海市南京路1号', TO_DATE('1980-01-01', 'yyyy-mm-dd'), '200000', 10000, TO_DATE('2001-01-01', 'yyyy-mm-dd'), '备注1');

INSERT INTO employee VALUES ('1023', 'F', '小乔', 'A003', '北京市人民路1号', TO_DATE('1988-05-01', 'yyyy-mm-dd'), '100000', 8000, TO_DATE('2005-01-01', 'yyyy-mm-dd'), '备注2');

3.
select count(*) "员工总数", sum(salary) "薪水总额" from employee;

4.
select departmentid, round((sysdate - max(birthdate))/365) "最小年龄", round((sysdate - min(birthdate))/365) "最大年龄" from employee group by departmentid;

5.
select departmentid "部门", sum(decode(sex, 'M', 1, 0)) "男员工数", sum(decode(sex, 'F', 1, 0)) "女员工数" from employee group by departmentid order by departmentid;

6.
create index employee_idx on employee(workdate);

7.
update employee set workdate=to_date('20071231','yyyymmdd') where id='0023';

8.
delete from employee where id='1023';

9.
commit;

10.
insert into employee_bak using (select * from employee);

11.
select a.*, b.* from employee a, duty b where a.id=b.id and b.level > 10;

12.
select * from (select id, name, workdate from employee order by workdate ) where rownum <=3;

13.drop index employee_idx;