IBM Netezza Sample SQL

gistfile1.txt

```sql
-- netezza sample sql

INSERT INTO films SELECT * FROM tmp;

INSERT INTO emp_copy WITH employee AS (select * from
emp) SELECT * FROM employee;

UPDATE emp_copy SET grp = 'gone' WHERE id =
(WITH employee AS (select * from emp) SELECT id FROM employee WHERE id
= 1);

DELETE FROM emp_copy WHERE id IN
(WITH employee AS (SELECT * FROM emp_copy where grp = 'gone')
SELECT id FROM employee);


WITH manager (mgr_id, mgr_name, mgr_dept) AS
	(SELECT id, name, grp
	FROM emp_copy
	WHERE mgr = id AND grp != 'gone'),
employee (emp_id, emp_name, emp_mgr) AS
	(SELECT id, name, mgr_id
	FROM emp_copy JOIN manager ON grp = mgr_dept),
mgr_cnt (mgr_id, mgr_reports) AS
	(SELECT mgr, COUNT (*)
	FROM emp_copy
	WHERE mgr != id
	GROUP BY mgr)
SELECT *
FROM employee JOIN manager ON emp_mgr = mgr_id 
	JOIN mgr_cnt ON emp_mgr = mgr_id 
WHERE emp_id != mgr_id
ORDER BY mgr_dept;
```