sqlserver-kit/Sample_Databases/Oracle_Employee_Database.sql at master · dazman66/sqlserver-kit

64 lines (57 loc) · 2.64 KB

Source link: https://github.com/ktaranov/sqlserver-kit/blob/master/Sample_Databases/Oracle_Employee_Database.sql

Changed data type to more unviversal: varchar2 -> varchar, number -> int

Tested on SQL Server 2019 and Postgres 12.2

Original link: https://livesql.oracle.com/apex/livesql/file/content_O5AEB2HE08PYEPTGCFLZU9YCV.html

Code style for PostgresSQL - python like.

CREATE DATABASE oracle_employee;

DROP TABLE employee;

DROP TABLE department;

CREATE TABLE department (

id int NOT NULL

, name varchar(100) NOT NULL

, CONSTRAINT pk_department PRIMARY KEY (id)

);

CREATE TABLE employee (

id int NOT NULL

, department_id INT NOT NULL

, chief_id int NULL

, name varchar(100) NOT NULL

, salary DECIMAL(19,2) NOT NULL

, CONSTRAINT pk_employee PRIMARY KEY (id)

);

ALTER TABLE employee ADD CONSTRAINT fk_employee__department_id FOREIGN KEY (department_id) REFERENCES department(id);

ALTER TABLE employee ADD CONSTRAINT fk_employee__cheief_id FOREIGN KEY (chief_id) REFERENCES employee(id);

INSERT INTO department (id, name) VALUES(10, 'ACCOUNTING');

INSERT INTO department (id, name) VALUES(20, 'RESEARCH');

INSERT INTO department (id, name) VALUES(30, 'SALES');

INSERT INTO department (id, name) VALUES(40, 'OPERATIONS');