Draw The Triangle 2 | SQL Alternative Queries

Lesson 43

Courses / HackerRank SQL Problem Solving Solution Challenges

Draw The Triangle 2 | SQL Alternative Queries | HackerRank Solution

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

Summary

# Triangle Drawing in SQL

## Overview
This guide explains how to create a stored procedure in SQL to draw a triangle pattern using asterisks. The goal is to print a series of lines, where each line contains asterisks followed by a space, increasing from 1 to 20 asterisks.

## Steps

1. **Understanding the Requirement**:
   - Each line in the triangle requires an increasing number of asterisks followed by a space.
   - Example output:
     ```
     *
     **
     ***
     ...
     ********************
     ```

2. **Using Repeat Function**:
   - Initially, you can use the `REPEAT` function to produce the asterisk line.
   - For example, `SELECT REPEAT('* ', n);` where `n` is the number of asterisks to print.

3. **Avoiding Manual Repetition**:
   - Instead of manually writing 20 lines for each number, use a loop.
   - We'll create a loop that runs from 1 to 20, incrementing the count each time.

4. **Setting Up the Loop**:
   - Declare an integer variable `i` initialized to 1.
   - Use a `WHILE` loop that checks if `i <= 20`.
   - Inside the loop, execute the `REPEAT` function to print the asterisks.

5. **Creating the Procedure**:
   - Wrap the logic in a stored procedure.
   - Define the procedure using a custom delimiter to avoid conflicts with semicolons inside the procedure.

6. **Changing the Delimiter**:
   - Temporarily change the delimiter before creating the procedure to avoid SQL errors.
   - Example: `delimiter $$` before the procedure and `delimiter ;` after.

7. **Example Code**:
   ```sql
   DELIMITER $$

   CREATE PROCEDURE while_proc()
   BEGIN
       DECLARE i INT DEFAULT 1;
       WHILE i <= 20 DO
           SELECT REPEAT('* ', i);
           SET i = i + 1;
       END WHILE;
   END$$

   DELIMITER ;

   CALL while_proc();

Output:
- Executing the procedure will print lines from 1 to 20 asterisks, with spaces in between.

Conclusion

With the above procedure, you can efficiently generate the triangle pattern using SQL, demonstrating the capability of loops and stored procedures in SQL.

Video Transcript

Hey everyone, I will do draw the triangle 2 from the section SQL alternative queries in hacker rank. So the question asks us to print an asterisk followed by space. A certain number of times in the case for P5 as an example, that means the very first one is going to be an asterisk followed by space. Then the second one is going to be two asterisks followed by space. And so on up until the five, that means five asterisk space. So to do this, I'll teach you first how you can repeat asterisks. Then we're going to put that in a loop, while loop, so that we can do multiple times instead of us having to write 20 times as the question wants us. And then finally, we have to place that in the star procedure to be able to run. So let's go here. If you do select and the function is repeat star, and 20, for example, that will let me put a space after the asterisks. That's going to repeat this string, the asterisk space 20 times. So if I run code, that's what we get. So this would be the very last row in this problem. If I do 19, it's going to be asterisk space 19 times concatenated. And if I do 18 and so on up until one, which is basically essentially the same thing as without the repeat. You could do that to solve the problem and cut and write these things 20 times. But that's not the smart way to do this. You should not do it that way. As programmers, I should do it with a loop. I'm going to call this I. So I'm going to add a variable there that's going to change its value and every iteration that is a starts from one and goes up to 20. So the first time is going to be one repetition of the asterisk space. The next time I is going to become I plus one, which is two. So that's going to be asterisk space, asterisk space. So two times, right? And so on for three, four, five up until 20. So to do that, we must declare I as a variable. So the name is I, the type is going to be int an integer. And the initial value in this case is going to be one. So I'm going to say default space one semicolon. Now I'm going to have to place this inside a loop so it can repeat the same thing over and over as long as some condition is true. So I'm going to say while I last then or equal to 20 do. And then I'm going to do whatever I want here inside. And then I'm going to say and while at the end, semicolon. Now this is nice. The first time I is one, one is less than or equal to 20 true because that is true. That executes everything between the do and the end while. So in this case, it would print repeat asterisk space with I being one, which is pretty much just asterisk space for the first one. And then the next time I have to do for I two, but to make that happen, I have to set the value of I. So set I to be I plus one. That is I initially one becomes two. Then at the end of the block for the while, we have to check the condition again is two less than or equal to 20 true because it's true. We execute everything between the do and end while again. So that would repeat the asterisk star two times. And then I equals I plus three, meaning I is becoming two plus one. That's three, three less than or equal to 20. True, repeat the same thing again. And so on up until I is going to be 20. So 20 less than or equal to 20 true. So it's going to be the repeat 20 times and then 20 plus one is 21, 21 less than or equal to 20. That's false because it's false. It's finally stops executing the statement for the while and keeps going in the next line after line five. Okay, but to make that work, we need to create a star procedure and then execute it. So we're going to take this whole thing place inside a begin and then end here. Now I have to say create procedure and give it any name you want. I would say while underscore proc parentheses, and then I have to call the procedure. So call with the name that you give here in this case while proc parentheses, and we cool. Now we are not done yet because there's a slight issue with my sequel that if you read the docs for defining stored programs, here's how they define the store procedure, you're going to run it through this problem. If you use the my sequel client program to define a star program containing semicolon characters, problem arises by default my sequel itself recognizes the semicolon as state and the limiter. So the semicolon at the end of every statement in my sequel, it's called a delimiter. But we're going to have a problem when you define the star procedure. So in order to get around the problem, we must redefine the delimiter temporarily to be something other than the semicolon. For example, we're going to use dollar dollar or slash slash. And then after defining the procedure, we revert the delimiter back to a semicolon. Here's what they do them the delimiter slash slash do the procedure slash slash to finish the procedure because that's like the semicolon that we used to have. And then finally, they bring it back to the limit to be semicolon and then call the procedure. So we're going to do exactly that. So going back here, instead of the semicolon at the end of the create procedure are going to say, I'm going to use dollar dollar. And then before the create the seizure line, I have to say delimiter space dollar dollar. So I can change from semicolon to dollar dollar. And then once I do my procedure, I can revert back to the limiter semicolon. And then I can continue all my statements with the semicolon at the end. Okay, let's run the code. And there you go. That's the solution. You can see one star two stars, three with a space between up until 20.