Script to create, in Google BigQuery, Oracle's "SCOTT" schema (tables EMP, DEPT, BONUS, SALGRADE, DUMMY), in a format suitable for pasting into Cloud Console

scott-big-query.sql

-- Script to create, in Google BigQuery,
-- Oracle's "SCOTT" schema with tables
-- EMP, DEPT, BONUS, SALGRADE, DUMMY.
--
-- In a format suitable for pasting into Cloud Console.
-- Before you run this script, create a dataset called 'Scott' in your current project.
--
drop table if exists scott.dept;
drop table if exists scott.emp;
drop table if exists scott.bonus;
drop table if exists scott.salgrade;
drop table if exists scott.dummy;
create table scott.dept(
  deptno   int64 not null,
  dname    string,
  loc      string)
as
  select 10, 'ACCOUNTING', 'NEW YORK' union all
  select 20, 'RESEARCH',   'DALLAS'   union all
  select 30, 'SALES',      'CHICAGO'  union all
  select 40, 'OPERATIONS', 'BOSTON';
create table scott.emp(
  empno    int64 not null,
  ename    string,
  job      string,
  mgr      int64,
  hiredate date,
  sal      decimal,
  comm     decimal,
  deptno   int64 not null)
as
  select 7839, 'KING',   'PRESIDENT', null, date '1981-11-17', 5000, null, 10 union all
  select 7698, 'BLAKE',  'MANAGER',   7839, date '1981-05-01', 2850, null, 30 union all
  select 7782, 'CLARK',  'MANAGER',   7839, date '1981-06-09', 2450, null, 10 union all
  select 7566, 'JONES',  'MANAGER',   7839, date '1981-04-02', 2975, null, 20 union all
  select 7788, 'SCOTT',  'ANALYST',   7566, date_add(date '1987-07-13', interval -85 day),  3000, null, 20 union all
  select 7902, 'FORD',   'ANALYST',   7566, date '1981-12-03', 3000, null, 20 union all
  select 7369, 'SMITH',  'CLERK',     7902, date '1980-12-17',  800, null, 20 union all
  select 7499, 'ALLEN',  'SALESMAN',  7698, date '1981-02-20', 1600,  300, 30 union all
  select 7521, 'WARD',   'SALESMAN',  7698, date '1981-02-22', 1250,  500, 30 union all
  select 7654, 'MARTIN', 'SALESMAN',  7698, date '1981-09-28', 1250, 1400, 30 union all
  select 7844, 'TURNER', 'SALESMAN',  7698, date '1981-09-08', 1500,    0, 30 union all
  select 7876, 'ADAMS',  'CLERK',     7788, date_add(date '1987-07-13', interval -51 day), 1100, null, 20 union all
  select 7900, 'JAMES',  'CLERK',     7698, date '1981-12-03',  950, null, 30 union all
  select 7934, 'MILLER', 'CLERK',     7782, date '1982-01-23', 1300, null, 10;
create table scott.bonus(
  ename    string,
  job      string,
  sal      decimal,
  comm     decimal);
create table scott.salgrade(
  grade    int64,
  losal    decimal,
  hisal    decimal)
as
  select 1,  700, 1200 union all
  select 2, 1201, 1400 union all
  select 3, 1401, 2000 union all
  select 4, 2001, 3000 union all
  select 5, 3001, 9999;
create table scott.dummy (
  dummy    int64)
as
  select 0;
select *
from scott.emp
 join scott.dept using (deptno)
order by emp.empno;