Publish Date - November 22nd, 2022
|Last Modified - March 7th, 2023
Another really interesting SQL Hackerrank problem, which forces you to break down a problem and build a procedure. While I know there are multiple ways to handle this problem (like most), I feel like this way allows you to flex multiple skills and try out some different functions.
The problem
P(R) represents a pattern drawn by Julia in R rows. The following pattern represents P(5):
* * * * *
* * * *
* * *
* *
*
Write a query to print the pattern P(20).
The solution
-- For an N amount of rows (with an input) print the descending amount of stars.
DELIMITER $$
CREATE PROCEDURE stars(r INT)
BEGIN
WHILE r >= 1 DO
SELECT repeat('* ', r);
SET r = r - 1;
END WHILE;
END; $$
CALL stars(20);
As stated before, I built out a procedure which called stars which executes when called. You need to know the inner workings of how SQL procedures work, which can easily be learned by watching some tutorials on Youtube, or learning SQL on Udemy.
This problem first confused me greatly, because I thought I could just print the stars out (which you can do and that’s extremely tedious). If you wanted to could just print out a bunch of stars.
DELIMITER ;
SELECT '* * * * * * * * * * * * * * *' as '20';
SELECT '* * * * * * * * * * * * * *' as '19';
SELECT '* * * * * * * * * * * * *' as '18';
SELECT '* * * * * * * * * * * *' as '17';
-- All the way to 1. Which returns this:
* * * * * * * * * * * * * * *
* * * * * * * * * * * * * *
* * * * * * * * * * * * *
* * * * * * * * * * * *Not very clean or reusable code though. Therefore, I used 5 major components to solve this question, so that it could be done programmatically for any number inputted. Those components are the following:
- DELIMITER – Due to the need to execute multiple lines of queries (including the procedure call at the end).
- PROCEDURE itself, which allows you to start and end a block of SQL code.
- A DO WHILE loop which run for a set amount of time dictated.
- REPEAT function, which actually does all the heavy lifting and the printing of the stars (*).
- SET, which sets the actual incrementation
I’ve added comments to the procedure to walk you through it:
-- For an N amount of rows (with an input) print the descending amount of stars.
DELIMITER $$
CREATE PROCEDURE stars(r INT) -- setting the procedure with stars() with a variable int passed in.
BEGIN -- start the procedure
WHILE r >= 1 DO -- DO WHILE loop for r >= 1 based off r.
SELECT repeat('* ', r); -- Add a stars per number variables passed in 20 = 20 stars.
SET r = r - 1; -- set each r one less than each pass at the loop
END WHILE; -- END the while loop
END; $$ -- END the procedure.
CALL stars(20); -- Call the procedure with the value.
Conclusion
A tougher one for sure, but doing it this way really cements how to handle procedures and the neat things you can do in SQL! There is a very similar question, “Draw the Triangle 2” that takes this to the next level!
Feel free to check out my articles on related technologies: