How to Draw a Christmas Tree in SQL

You can use SQL to manipulate all kinds of data, from huge analytical queries to brief single-purpose statements. But you can also use SQL just for fun, without any business requirements stifling your creativity. So, get out your jolly hat and prepare to sing O Christmas Tree as we create some quirky art with plain old SQL.

Today, we’re going to generate some holiday-themed ASCII art, just for fun. That’s right. I said fun. This exercise has absolutely zero business value. If you’re looking to help your enterprise in any way, except perhaps by being a cheerful employee, you’d best move along, because we’re here to put our SQL skills to the ultimate test—drawing Christmas trees. To partake in this festive activity, you’ll need to have mastered some basic SQL skills. Hopefully, you’ve learned the following:

• • Basic SQL string functions
  • CTEs
  • Recursion

We’ll rely on all three of these to create interesting objects in SQL.

Boring Old Numbers

Before we can get to the fun stuff, we need to get the basics down. We’d like to generate some numbers just to warm you up. There are many ways to generate numbers in SQL, the most common of which is recursion. By following a simple pattern, you can produce a whole bunch of numbers.

To generate a simple data set of 10 numbers, we can write the following short take10 CTE:

WITH take10(list_of_numbers) AS 
(SELECT 0 FROM DUAL
  UNION ALL 
 SELECT 
  list_of_numbers+1 
  FROM take10
  WHERE list_of_numbers < 10)
SELECT * FROM take10;

Here’s the result set :

Note: I’m using Oracle SQL to demonstrate generators, hence the use of the dual table. If you’re using PostgreSQL or MySQL, a simple SELECT N clause without the FROM clause will suffice.

Planting a Tree

Alright, enough number talk — it’s time for some Christmas magic! I’d really like to have a nice Christmas tree to decorate my SQL IDE [Integrated Development Environment – Ed.] and keep myself jolly during this gloomy season. To generate a tree, we’ll need to leverage SQL string functions, which you’ve hopefully practiced on LearnSQL.com.

We’ll construct our tree using pines and a certain tree depth:

WITH small_tree(tree_depth,pine) AS (
  SELECT 1 tree_depth,rpad(' ',10,' ') || '*' pine
  FROM   dual
  UNION ALL
  SELECT small_tree.tree_depth +1 tree_depth,
  rpad(' ',10-small_tree.tree_depth,' ') || rpad('*',small_tree.tree_depth+1,'.') || lpad('*',small_tree.tree_depth,'.') pine
  FROM   small_tree
  where small_tree.tree_depth < 10
)
SELECT pine
FROM small_tree;

For our result set, we get this nice Christmas tree:

          *
         *.*
        *...*
       *.....*
      *.......*
     *.........*
    *...........*
   *.............*
  *...............*
 *.................*

If you’d like to personalize your tree, simply change the symbols you use for pines. If we replace our pines with asterisks:

WITH small_tree(tree_depth,pine) AS (
  SELECT 1 tree_depth,rpad(' ',10,' ') || '.' pine
  FROM   dual
  UNION ALL
  SELECT small_tree.tree_depth +1 tree_depth,
  rpad(' ',10-small_tree.tree_depth,' ') || rpad('.',small_tree.tree_depth+1,'*') || lpad('.',small_tree.tree_depth,'*') pine
  FROM   small_tree
  where small_tree.tree_depth < 10
)
SELECT pine
FROM small_tree;

We get a different tree:

          .
         .*.
        .***.
       .*****.
      .*******.
     .*********.
    .***********.
   .*************.
  .***************.
 .*****************.

If we’re in a truly festive mood, we can generate:

WITH small_tree(tree_depth,pine) AS (
  SELECT 1 tree_depth,
  rpad(' ',10,' ') || '*' 
  || rpad(' ',20,' ') || '*' 
  || rpad(' ',20,' ') || '*' 
  pine
  FROM   dual
  UNION ALL
  SELECT small_tree.tree_depth +1 tree_depth,
  rpad(' ',10-small_tree.tree_depth,' ') || rpad('*',small_tree.tree_depth+1,'.') || lpad('*',small_tree.tree_depth,'.') 
  || rpad(' ',20-small_tree.tree_depth-tree_depth,' ') || rpad('*',small_tree.tree_depth+1,'.') || lpad('*',small_tree.tree_depth,'.') 
  || rpad(' ',20-small_tree.tree_depth-tree_depth,' ') || rpad('*',small_tree.tree_depth+1,'.') || lpad('*',small_tree.tree_depth,'.') pine
  FROM   small_tree
  where small_tree.tree_depth < 10
)
SELECT pine
FROM small_tree;

A whole forest of trees:

          *                    *                    *
         *.*                  *.*                  *.*
        *...*                *...*                *...*
       *.....*              *.....*              *.....*
      *.......*            *.......*            *.......*
     *.........*          *.........*          *.........*
    *...........*        *...........*        *...........*
   *.............*      *.............*      *.............*
  *...............*    *...............*    *...............*
 *.................*  *.................*  *.................*

But our trees are missing some stumps! I’ll leave that challenge to you, dear reader. While we’re at it, go ahead and generate some nice stars and decorations—get creative! When you use recursion in SQL, the only limit you face is your imagination. Happy and festive Holidays from LearnSQL.com family!