Query that uses the "SCOTT" schema (tables EMP, DEPT, BONUS, SALGRADE, DUMMY) as CTEs, in BigQuery SQL

scott-big-query-cte

with dummy as (
  select 0 as dummy
), dept AS (
  select 10 as deptno, 'ACCOUNTING' as dname, 'NEW YORK' as loc union all
  select 20, 'RESEARCH',   'DALLAS'  union all
  select 30, 'SALES',      'CHICAGO' union all
  select 40, 'OPERATIONS', 'BOSTON'
), emp AS (
  select 7839 as empno, 'KING' as ename, 'PRESIDENT' AS JOB, null AS mgr, date '1981-11-17' as hiredate, 5000 as sal, null as comm, 10 as deptno union all
  select 7698, 'BLAKE',  'MANAGER',   7839, '1981-05-01', 2850, null, 30 union all
  select 7782, 'CLARK',  'MANAGER',   7839, '1981-06-09', 2450, null, 10 union all
  select 7566, 'JONES',  'MANAGER',   7839, '1981-04-02', 2975, null, 20 union all
  select 7788, 'SCOTT',  'ANALYST',   7566, date_add('1987-07-13', interval -85 day),  3000, null, 20 union all
  select 7902, 'FORD',   'ANALYST',   7566, '1981-12-03', 3000, null, 20 union all
  select 7369, 'SMITH',  'CLERK',     7902, '1980-12-17',  800, null, 20 union all
  select 7499, 'ALLEN',  'SALESMAN',  7698, '1981-02-20', 1600,  300, 30 union all
  select 7521, 'WARD',   'SALESMAN',  7698, '1981-02-22', 1250,  500, 30 union all
  select 7654, 'MARTIN', 'SALESMAN',  7698, '1981-09-28', 1250, 1400, 30 union all
  select 7844, 'TURNER', 'SALESMAN',  7698, '1981-09-08', 1500,    0, 30 union all
  select 7876, 'ADAMS',  'CLERK',     7788, date_add('1987-07-13', interval -51 day), 1100, null, 20 union all
  select 7900, 'JAMES',  'CLERK',     7698, '1981-12-03',  950, null, 30 union all
  select 7934, 'MILLER', 'CLERK',     7782, '1982-01-23', 1300, null, 10
), salgrade as (
  select 1 as grade,  700 as losal, 1200 as hisal union all
  select 2, 1201, 1400 union all
  select 3, 1401, 2000 union all
  select 4, 2001, 3000 union all
  select 5, 3001, 9999)
select d.deptno, avg(sal),
 (select avg(sal) from emp where deptno = d.deptno) as avg_sal
from dept as d
join EMP as e using (deptno)
where e.job <> 'ANALYST'
group by d.deptno;