Oracle PL/SQL With Connect By Example

With Connect By Example

CREATE TABLE employees (
name VARCHAR2(25),
department NUMBER(2));

INSERT INTO employees VALUES ('ANDY',4);
INSERT INTO employees VALUES ('KATE',2);
INSERT INTO employees VALUES ('ANNA',1);
INSERT INTO employees VALUES ('GERARD',2);
INSERT INTO employees VALUES ('AUDRINA',3);
COMMIT;
 
SELECT * FROM employees ;
 
CREATE TABLE top_employees AS
SELECT name FROM employees 
WHERE 1=2;
 
SELECT * FROM top_employees;
 
INSERT INTO top_employee
WITH test AS (
SELECT ROWNUM test
FROM dual
CONNECT BY LEVEL <= (SELECT MAX(department) FROM employee))
SELECT name
FROM employees, test
WHERE test <= department
ORDER BY name;
 
SELECT * FROM top_employees;