-- Collection of code snippets by Arne Vajhøj 
-- posted to eksperten.dk, usenet and other places (2002-now) 
CREATE TABLE stuff (id INTEGER NOT NULL PRIMARY KEY,name VARCHAR(50),parent INTEGER);

INSERT INTO stuff VALUES(1,'tools',0);
INSERT INTO stuff VALUES(2,'hammer',1);
INSERT INTO stuff VALUES(3,'screwdriver',1);
INSERT INTO stuff VALUES(4,'fruits',0);
INSERT INTO stuff VALUES(5,'yellow fruits',4);
INSERT INTO stuff VALUES(6,'green fruits',4);
INSERT INTO stuff VALUES(7,'apple',6);
INSERT INTO stuff VALUES(8,'banana',5);
INSERT INTO stuff VALUES(9,'grape',6);

SELECT * FROM stuff START WITH name = 'grape' CONNECT BY PRIOR parent = id;
SELECT * FROM stuff START WITH name = 'hammer' CONNECT BY PRIOR parent = id;

DROP TABLE stuff;
