Notes

-- this is a comment (all text preceeded with two dashes is ignored by parser)

-- CREATE SCHEMA scott
-- AUTHORIZATION postgres;
-- COMMENT ON SCHEMA scott
-- IS 'Oracle s test schema';

-- DROP TABLE scott.emp;
-- DROP TABLE scott.dept;
-- DROP TABLE scott.bonus;
-- DROP TABLE scott.salgrade;
-- DROP TABLE scott.dummy;


CREATE TABLE scott.dept
(
deptno INTEGER primary key,
dname VARCHAR(14),
loc VARCHAR(13)
);


INSERT INTO scott.dept VALUES (10, 'ACCOUNTING', 'NEW YORK' );
INSERT INTO scott.dept VALUES (20, 'RESEARCH', 'DALLAS' );
INSERT INTO scott.dept VALUES (30, 'SALES', 'CHICAGO' );
INSERT INTO scott.dept VALUES (40, 'OPERATIONS', 'BOSTON' );


CREATE TABLE scott.emp
(
empno INTEGER primary key,
ename VARCHAR(10) not null,
job VARCHAR(9),
mgr INTEGER,
hiredate DATE,
sal NUMERIC,
comm NUMERIC,
deptno INTEGER
);


INSERT INTO scott.emp VALUES
(7369, 'SMITH', 'CLERK', 7902,'1980-DEC-17', 800::text::numeric, NULL, 20);
INSERT INTO scott.emp VALUES
(7499, 'ALLEN', 'SALESMAN', 7698,'1981-FEB-20', 1600::text::numeric, 300::text::numeric, 30);
INSERT INTO scott.emp VALUES
(7521, 'WARD', 'SALESMAN', 7698,'1981-FEB-22', 1250::text::numeric, 500::text::numeric, 30);
INSERT INTO scott.emp VALUES
(7566, 'JONES', 'MANAGER', 7839,'1981-APR-2', 2975::text::numeric, NULL, 20);
INSERT INTO scott.emp VALUES
(7654, 'MARTIN', 'SALESMAN', 7698,'1981-SEP-28', 1250::text::numeric, 1400::text::numeric, 30);
INSERT INTO scott.emp VALUES
(7698, 'BLAKE', 'MANAGER', 7839,'1981-MAY-1', 2850::text::numeric, NULL, 30);
INSERT INTO scott.emp VALUES
(7782, 'CLARK', 'MANAGER', 7839,'1981-JUN-9', 2450::text::numeric, NULL, 10);
INSERT INTO scott.emp VALUES
(7788, 'SCOTT', 'ANALYST', 7566,'1982-DEC-9', 3000::text::numeric, NULL, 20);
INSERT INTO scott.emp VALUES
(7839, 'KING', 'PRESIDENT', NULL,'1981-NOV-17', 5000::text::numeric, NULL, 10);
INSERT INTO scott.emp VALUES
(7844, 'TURNER', 'SALESMAN', 7698,'1981-SEP-8', 1500::text::numeric, 0::text::numeric, 30);
INSERT INTO scott.emp VALUES
(7876, 'ADAMS', 'CLERK', 7788,'1983-JAN-12', 1100::text::numeric, NULL, 20);
INSERT INTO scott.emp VALUES
(7900, 'JAMES', 'CLERK', 7698,'1981-DEC-3', 950::text::numeric, NULL, 30);
INSERT INTO scott.emp VALUES
(7902, 'FORD', 'ANALYST', 7566,'1981-DEC-3', 3000::text::numeric, NULL, 20);
INSERT INTO scott.emp VALUES
(7934, 'MILLER', 'CLERK', 7782,'1982-JAN-23', 1300::text::numeric, NULL, 10);

CREATE TABLE scott.bonus
(
ename VARCHAR(10) primary key,
job VARCHAR(9),
sal NUMERIC,
comm NUMERIC
);

CREATE TABLE scott.salgrade
(
grade INTEGER primary key,
losal INTEGER,
hisal INTEGER
);

INSERT INTO scott.salgrade VALUES (1, 700, 1200);
INSERT INTO scott.salgrade VALUES (2, 1201, 1400);
INSERT INTO scott.salgrade VALUES (3, 1401, 2000);
INSERT INTO scott.salgrade VALUES (4, 2001, 3000);
INSERT INTO scott.salgrade VALUES (5, 3001, 9999);

CREATE TABLE scott.dummy
(dummy INTEGER);

INSERT INTO scott.dummy VALUES (0);

ALTER TABLE scott.emp
ADD CONSTRAINT emp_fkey_deptno
FOREIGN KEY (deptno) REFERENCES scott.dept(deptno)
ON UPDATE NO ACTION ON DELETE SET NULL;
ALTER TABLE scott.emp
ADD CONSTRAINT emp_nn_deptno
CHECK (emp.deptno is not null);
ALTER TABLE scott.emp
ADD CONSTRAINT emp_chk_sal
CHECK (sal between 100 and 9999);
ALTER TABLE scott.emp
ADD CONSTRAINT emp_fkey_manager
FOREIGN KEY (mgr) REFERENCES scott.emp(empno)
ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE scott.dept

ADD CONSTRAINT dept_unq_dnameloc UNIQUE (dname, loc);