CREATE TABLE categories (    -- father of main knot is NULL
ucat integer NULL,    -- a father knot of a tree, it has many son knots
lcat integer NOT NULL,     -- the son knot. has only one father knot
name varchar (30), 
PRIMARY KEY (lcat),
FOREIGN KEY (ucat) REFERENCES categories);   -- recursive definition
 
-- Some samples
INSERT INTO categories VALUES (NULL,1,'Categories');
INSERT INTO categories VALUES (1,2,'Software');
INSERT INTO categories VALUES (1,3,'Hardware');
INSERT INTO categories VALUES (1,4,'Network');
INSERT INTO categories VALUES (2,5,'Linux');
INSERT INTO categories VALUES (3,6,'PC');
INSERT INTO categories VALUES (3,7,'Printer');
INSERT INTO categories VALUES (6,8,'LCD');
commit
 
-- for example select all subcategories of category hardware
SELECT * FROM categories
WHERE ucat = ALL (SELECT lcat FROM categories
WHERE name = 'Hardware');
 
/*  Result
ucat   lcat   name
------------------
3      6      PC
3      7      Printer
*/