Script to create the "SCOTT" schema (tables EMP, DEPT, BONUS, SALGRADE, DUMMY), in a format suitable for pasting into DuckDB's shell

scott-duckdb.sql

-- DuckDB script to create Oracle's "SCOTT" schema with tables
-- EMP, DEPT, BONUS, SALGRADE, DUMMY. Originally Oracle's demobld.sql.
--
-- In a format suitable for pasting into DuckDB's shell:
-- https://shell.duckdb.org
create table dept (
  deptno  decimal(2,0) not null,
  dname   varchar(14),
  loc     varchar(13));
create table emp (
  empno    decimal(4,0) not null,
  ename    varchar(10),
  job      varchar(9),
  mgr      decimal(4,0),
  hiredate date,
  sal      decimal(7,2),
  comm     decimal(7,2),
  deptno   decimal(2,0) not null);
create table bonus (
  ename  varchar(10),
  job    varchar(9),
  sal    decimal,
  comm   decimal);
create table salgrade (
  grade  decimal,
  losal  decimal,
  hisal  decimal);
create table dummy (
  dummy  decimal);
insert into dummy values (0);
insert into dept (deptno, dname, loc) values
  (10, 'ACCOUNTING', 'NEW YORK'),
  (20, 'RESEARCH',   'DALLAS'),
  (30, 'SALES',      'CHICAGO'),
  (40, 'OPERATIONS', 'BOSTON');
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values
  (7839, 'KING',   'PRESIDENT', null, date '1981-11-17',      5000, null, 10),
  (7698, 'BLAKE',  'MANAGER',   7839, date '1981-05-01',      2850, null, 30),
  (7782, 'CLARK',  'MANAGER',   7839, date '1981-06-09',      2450, null, 10),
  (7566, 'JONES',  'MANAGER',   7839, date '1981-04-02',      2975, null, 20),
  (7788, 'SCOTT',  'ANALYST',   7566, date '1987-07-13' - 85, 3000, null, 20),
  (7902, 'FORD',   'ANALYST',   7566, date '1981-12-03',      3000, null, 20),
  (7369, 'SMITH',  'CLERK',     7902, date '1980-12-17',       800, null, 20),
  (7499, 'ALLEN',  'SALESMAN',  7698, date '1981-02-20',      1600,  300, 30),
  (7521, 'WARD',   'SALESMAN',  7698, date '1981-02-22',      1250,  500, 30),
  (7654, 'MARTIN', 'SALESMAN',  7698, date '1981-09-28',      1250, 1400, 30),
  (7844, 'TURNER', 'SALESMAN',  7698, date '1981-09-08',      1500,    0, 30),
  (7876, 'ADAMS',  'CLERK',     7788, date '1987-07-13' - 51, 1100, null, 20),
  (7900, 'JAMES',  'CLERK',     7698, date '1981-12-03',       950, null, 30),
  (7934, 'MILLER', 'CLERK',     7782, date '1982-01-23',      1300, null, 10);
insert into salgrade (grade, losal, hisal) values
  (1,  700, 1200),
  (2, 1201, 1400),
  (3, 1401, 2000),
  (4, 2001, 3000),
  (5, 3001, 9999);