SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE public.emp ( empno integer NOT NULL, ename character varying(10), job character varying(9), mgr integer, hiredate date, sal integer, comm integer, deptno integer, CONSTRAINT emp_sal_ck CHECK ( (sal > 0) ) ); INSERT INTO public.emp VALUES ( 7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20 ); INSERT INTO public.emp VALUES ( 7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30 ); INSERT INTO public.emp VALUES ( 7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30 ); INSERT INTO public.emp VALUES ( 7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20 ); INSERT INTO public.emp VALUES ( 7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30 ); INSERT INTO public.emp VALUES ( 7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30 ); INSERT INTO public.emp VALUES ( 7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10 ); INSERT INTO public.emp VALUES ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20 ); INSERT INTO public.emp VALUES ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10 ); INSERT INTO public.emp VALUES ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30 ); INSERT INTO public.emp VALUES ( 7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20 ); INSERT INTO public.emp VALUES ( 7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30 ); INSERT INTO public.emp VALUES ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20 ); INSERT INTO public.emp VALUES ( 7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10 ); CREATE TABLE public.dept ( deptno integer NOT NULL, dname character varying(14), loc character varying(13) ); INSERT INTO public.dept VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO public.dept VALUES (20, 'RESEARCH', 'DALLAS'); INSERT INTO public.dept VALUES (30, 'SALES', 'CHICAGO'); INSERT INTO public.dept VALUES (40, 'OPERATIONS', 'BOSTON'); CREATE TABLE public.jobhist ( empno integer NOT NULL, startdate date NOT NULL, enddate date, job character varying(9), sal integer, comm integer, deptno integer, chgdesc character varying(80), CONSTRAINT jobhist_date_chk CHECK ( (startdate <= enddate) ) ); INSERT INTO public.jobhist VALUES ( 7369, '1980-12-17', NULL, 'CLERK', 800, NULL, 20, 'New Hire' ); INSERT INTO public.jobhist VALUES ( 7499, '1981-02-20', NULL, 'SALESMAN', 1600, 300, 30, 'New Hire' ); INSERT INTO public.jobhist VALUES ( 7521, '1981-02-22', NULL, 'SALESMAN', 1250, 500, 30, 'New Hire' ); INSERT INTO public.jobhist VALUES ( 7566, '1981-04-02', NULL, 'MANAGER', 2975, NULL, 20, 'New Hire' ); INSERT INTO public.jobhist VALUES ( 7654, '1981-09-28', NULL, 'SALESMAN', 1250, 1400, 30, 'New Hire' ); INSERT INTO public.jobhist VALUES ( 7698, '1981-05-01', NULL, 'MANAGER', 2850, NULL, 30, 'New Hire' ); INSERT INTO public.jobhist VALUES ( 7782, '1981-06-09', NULL, 'MANAGER', 2450, NULL, 10, 'New Hire' ); INSERT INTO public.jobhist VALUES ( 7788, '1987-04-19', '1988-04-12', 'CLERK', 1000, NULL, 20, 'New Hire' ); INSERT INTO public.jobhist VALUES ( 7788, '1988-04-13', '1989-05-04', 'CLERK', 1040, NULL, 20, 'Raise' ); INSERT INTO public.jobhist VALUES ( 7788, '1990-05-05', NULL, 'ANALYST', 3000, NULL, 20, 'Promoted to Analyst' ); INSERT INTO public.jobhist VALUES ( 7839, '1981-11-17', NULL, 'PRESIDENT', 5000, NULL, 10, 'New Hire' ); INSERT INTO public.jobhist VALUES ( 7844, '1981-09-08', NULL, 'SALESMAN', 1500, 0, 30, 'New Hire' ); INSERT INTO public.jobhist VALUES ( 7876, '1987-05-23', NULL, 'CLERK', 1100, NULL, 20, 'New Hire' ); INSERT INTO public.jobhist VALUES ( 7900, '1981-12-03', '1983-01-14', 'CLERK', 950, NULL, 10, 'New Hire' ); INSERT INTO public.jobhist VALUES ( 7900, '1983-01-15', NULL, 'CLERK', 950, NULL, 30, 'Changed to Dept 30' ); INSERT INTO public.jobhist VALUES ( 7902, '1981-12-03', NULL, 'ANALYST', 3000, NULL, 20, 'New Hire' ); INSERT INTO public.jobhist VALUES ( 7934, '1982-01-23', NULL, 'CLERK', 1300, NULL, 10, 'New Hire' ); CREATE SEQUENCE public.next_empno START WITH 8000 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; SELECT pg_catalog.setval('public.next_empno', 8000, false); ALTER TABLE ONLY public.dept ADD CONSTRAINT dept_dname_uq UNIQUE (dname); -- -- TOC entry 2700 (class 2606 OID 28615564) -- Name: dept dept_pk; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.dept ADD CONSTRAINT dept_pk PRIMARY KEY (deptno); -- -- TOC entry 2702 (class 2606 OID 28615572) -- Name: emp emp_pk; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.emp     ADD CONSTRAINT emp_pk PRIMARY KEY (empno); ALTER TABLE ONLY public.jobhist     ADD CONSTRAINT jobhist_pk PRIMARY KEY (empno, startdate); -- -- TOC entry 2705 (class 2606 OID 28615573) -- Name: emp emp_ref_dept_fk; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.emp     ADD CONSTRAINT emp_ref_dept_fk FOREIGN KEY (deptno) REFERENCES public.dept(deptno); -- -- TOC entry 2707 (class 2606 OID 28615589) -- Name: jobhist jobhist_ref_dept_fk; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.jobhist     ADD CONSTRAINT jobhist_ref_dept_fk FOREIGN KEY (deptno) REFERENCES public.dept(deptno) ON DELETE SET NULL; -- -- TOC entry 2706 (class 2606 OID 28615584) -- Name: jobhist jobhist_ref_emp_fk; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.jobhist     ADD CONSTRAINT jobhist_ref_emp_fk FOREIGN KEY (empno) REFERENCES public.emp(empno) ON DELETE CASCADE; -- -- TOC entry 2844 (class 0 OID 0) -- Dependencies: 202 -- Name: TABLE dept; Type: ACL; Schema: public; Owner: postgres -- GRANT ALL ON TABLE public.dept TO PUBLIC; -- -- TOC entry 2845 (class 0 OID 0) -- Dependencies: 203 -- Name: TABLE emp; Type: ACL; Schema: public; Owner: postgres -- GRANT ALL ON TABLE public.emp TO PUBLIC; -- -- TOC entry 2846 (class 0 OID 0) -- Dependencies: 204 -- Name: TABLE jobhist; Type: ACL; Schema: public; Owner: postgres GRANT ALL ON SEQUENCE public.next_empno TO PUBLIC;