-- Create and populate tables
CREATE TABLE colours (id INTEGER PRIMARY KEY, name VARCHAR(5));
INSERT INTO colours (id, name) VALUES ( 1, 'red'  );
INSERT INTO colours (id, name) VALUES ( 2, 'white');
INSERT INTO colours (id, name) VALUES ( 3, 'blue' );

CREATE TABLE balls ( colour INTEGER REFERENCES colours );
INSERT INTO balls ( colour ) VALUES ( 2 );
INSERT INTO balls ( colour ) VALUES ( 2 );
INSERT INTO balls ( colour ) VALUES ( 3 );
INSERT INTO balls ( colour ) VALUES ( 2 );
INSERT INTO balls ( colour ) VALUES ( 1 );
INSERT INTO balls ( colour ) VALUES ( 3 );
INSERT INTO balls ( colour ) VALUES ( 3 );
INSERT INTO balls ( colour ) VALUES ( 2 );

-- Show the balls are unsorted
SELECT
	colours.name
FROM
	balls
	JOIN colours ON balls.colour = colours.id;

-- Show the balls in dutch flag order
SELECT
	colours.name
FROM
	balls
	JOIN colours ON balls.colour = colours.id
ORDER BY
	colours.id;

-- Tidy up
DROP TABLE balls;
DROP TABLE colours;