Back to articles list December 21, 2017 - 4 minutes read How to Draw a Christmas Tree in SQL Aldo Zelen Aldo is a data architect with a passion for the cloud. From leading a team of data professionals to coding a data warehouse in the cloud, Aldo has experience with the whole lifecycle of data-intensive projects. Aldo spends his free time forecasting geopolitical events in forecasting tournaments. Tags: common table expressions CTE extras recursive queries SQL basics sql string functions WITH 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 : list_of_numbers 0 1 2 3 4 5 6 7 8 9 10 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! Tags: common table expressions CTE extras recursive queries SQL basics sql string functions WITH You may also like What Is Vertabelo’s SQL Cheat Sheet? Rock the SQL! You don’t have to be a programmer to master SQL. Download the SQL Cheat Sheet and find quick answers for the common problems with SQL queries. Read more You Want to Learn SQL? You've Come to the Right Place! If you want to learn SQL basics or enhance your SQL skills, check out LearnSQL.com for a wide range of SQL courses and tracks. Read more Simplify SQL Code: Recursive Queries in DBMS Are you repeating the same query in every report? Are they getting too complicated? Use recursive queries to simplify SQL code! Read more Long SQL Query vs. Recursive SQL Query Explore recursive CTE hierarchy in SQL server and find out how to tune your SQL query performance. Read more How Recursive Common Table Expressions Work Recursive Common Table Expressions are immensely useful when you're querying hierarchical data. Let's explore what makes them work. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.