Great. Just as you can have multiple columns in your CTE, you can also have multiple CTEs, some of which may be recursive, others not. The rule for syntax is as follows: if at least one CTE is recursive, start the whole query with WITH RECURSIVE, and then define your CTEs just as you would normally do. Take a look at the example below.
WITH RECURSIVE prime(x) AS (
SELECT 2
UNION
SELECT 3
UNION
SELECT 5
UNION
SELECT 7
UNION
SELECT 11
),
total(multiply,prime,score) AS (
SELECT
0,
0,
0
UNION
SELECT
multiply+1,
x,
x * (multiply+1)
FROM prime, total
WHERE multiply < 3
)
SELECT
*
FROM total
ORDER BY multiply, prime;
In the example, we first create a list of the first 5 prime numbers, and then we create a recursive CTE that calculates multiples of these prime numbers. Note that the word RECURSIVE is always used before the first CTE, even if that query is not recursive.
Look at the template. There is already a single CTE named letters defined for you. Your task is to complete the words CTE so that it generates all possible words made from those letters that have at most 5 letters. The shortest word should be an empty word ('').
The anchor member should be an empty string ''.
The recursive member should use the word you have so far and a new letter from letters. Use the concatenation symbol || to join new letters.
Remember about the termination check - use the function length(w) to that end.
