oracle六个练习表,oracle练习用表.doc

oracle练习用表

alter session set nls_language=english;

CREATE TABLE s_dept(id NUMBER(7) CONSTRAINT s_dept_id_nn NOT NULL, name VARCHAR2(25) CONSTRAINT s_dept_name_nn NOT NULL, region_id NUMBER(7), CONSTRAINT s_dept_id_pk PRIMARY KEY (id), CONSTRAINT s_dept_name_region_id_uk UNIQUE (name, region_id));

INSERT INTO s_dept VALUES ( 10, 'Finance', 1);INSERT INTO s_dept VALUES ( 31, 'Sales', 1);INSERT INTO s_dept VALUES ( 32, 'Sales', 2);INSERT INTO s_dept VALUES ( 33, 'Sales', 3);INSERT INTO s_dept VALUES ( 34, 'Sales', 4);INSERT INTO s_dept VALUES ( 35, 'Sales', 5);INSERT INTO s_dept VALUES ( 41, 'Operations', 1);INSERT INTO s_dept VALUES ( 42, 'Operations', 2);INSERT INTO s_dept VALUES ( 43, 'Operations', 3);INSERT INTO s_dept VALUES ( 44, 'Operations', 4);INSERT INTO s_dept VALUES ( 45, 'Operations', 5);INSERT INTO s_dept VALUES ( 50, 'Administration', 1);COMMIT;

------------------------------------------

CREATE TABLE s_emp(id NUMBER(7) CONSTRAINT s_emp_id_nn NOT NULL, last_name VARCHAR2(25) CONSTRAINT s_emp_last_name_nn NOT NULL, first_name VARCHAR2(25), userid VARCHAR2(8), start_date DATE, comments VARCHAR2(255), manager_id NUMBER(7), title VARCHAR2(25), dept_id NUMBER(7), salary NUMBER(11, 2), commission_pct NUMBER(4, 2), CONSTRAINT s_emp_id_pk PRIMARY KEY (id), CONSTRAINT s_emp_userid_uk UNIQUE (userid), CONSTRAINT s_emp_commission_pct_ck CHECK (commission_pct IN (10, 12.5, 15, 17.5, 20)));

INSERT INTO s_emp VALUES ( 1, 'Velasquez', 'Carmen', 'cvelasqu', to_date('03-MAR-90 8:30', 'dd-mon-yy hh24:mi'), NULL, NULL, 'President', 50, 2500, NULL);INSERT INTO s_emp VALUES ( 2, 'Ngao', 'LaDoris', 'lngao', '08