Video Transcript
Hey everyone, I will draw the triangle 1 from alternative queries from the SQL section of HackerRync.
This problem asks us to print an asterisk space a number of times.
For example, if you say p of 5, that means the first line will have to print asterisk space 5 times.
Then the next line would be 4 times and so on until we reach only one asterisk.
The problem asks us to do p of 20, that is 20 asterisks separated by a space, followed by 19 asterisks, 18 and so on up until the last line that's going to be only one asterisk.
So how can we do that?
Well, first, teach you how to do just the very first one.
If you do select and use the function repeat with a star space and you pass the number 20 here.
Let's see what we get.
So the result is asterisk space repeated 20 times.
That would satisfy the very first line.
But then we got to do it for 19 and then 18, 17 up until one, right?
And I would even argue if I look at this last line, it doesn't seem to have a space after the asterisk.
So to be more technically correct, the last one you wouldn't have to repeat at all.
It would be just the asterisk without any space.
But obviously you could write these one by one.
You can write 20 copy this, do 19 and so on.
But that's not the smart way.
We don't want to do it that way.
We want to do it programmatically.
So we're going to write a program that will do that automatically.
So we don't have to write it.
And I'm going to use a loop for that.
And it's the while loop.
To do the while loop, I have to do it inside a star procedure.
So I cannot just do it right away.
So let's get going here.
So instead of 20, I'm going to put a placeholder variable i.
And that i will change.
So it will start from 20, and then it will become 19, and then it will become 18,
and so on up until one.
Okay, so I'm going to define that variable here.
So I'm going to say declare i, that's the name of the variable,
type int an integer, and the initial value that is the default
is going to be in this case 20.
And then what we're going to do here is a loop.
Now this loop, we're going to have to do the star procedure later.
So just wait a little bit.
So I'm going to do while i is greater than zero do.
We're going to do something inside.
And then at the end, I'm going to say and while let me go in here.
And because we're doing a loop, what is a loop?
I is initially 20.
So we go here and check is 20 greater than zero true because it's true.
It executes whatever's in the block here.
In this case, it's going to do repeat star space with 20 as the value.
Now after that, if we didn't have anything to change the value of i,
it will keep repeating that forever.
It will check 20 greater than zero again.
Yeah, do the same thing.
Do the same thing and so on.
So we have to prevent that from happening, that infinite loop.
So I'm going to set i to be i minus one.
Okay, so 20 becomes 19.
And then on next iteration, 19 becomes 18 and so on.
So say set the value of i is going to be i minus one semicolon.
So that is initially 20.
Once we do the first iteration, it's going to take 20 minus one 19 and assign that to i.
So the next time we check i greater than zero, i is going to be 19.
So 19 greater than zero true.
We're going to do the select repeat star space with 19 as the value.
And so on is going to do with 18 17 so on up until when i is one, right?
I one greater than zero true do only one asterisk and then i minus one would be zero.
So finally it checks again zero greater than zero.
That's false.
Therefore we end this loop.
We don't do anything.
We go and move forward after this block.
Okay, but to make this work, we need to make it a store procedure.
So we're going to close this whole thing in a star procedure.
So we're going to say the following.
So create procedure, give it any name you want.
I can call it a while underscore proc.
Then give parentheses and then I'm going to say begin in a new line and after the whole thing the block of code that I want to execute the statements.
I'm going to say end.
Now to make this work.
This is just the declaration of how the procedure would go.
You have to actually execute the procedure.
So you have to call it.
So I'm going to call space the name that you give here while underscore proc and parentheses semicolon.
Now there's a little problem that we're going to run if we run it like that the semicolon here.
And that's because if you look at the documentation, here's for defined store programs.
This is how you do it.
There's this problem if you use my SQL client to define a store program, containing semicolon characters, a problem arises by default.
My SQL itself recognizes semicolon as a statement delimiter.
Okay, so every time at the end of the line we add a semicolon, right?
But we're going to get a problem if we create a start procedure.
So what we have to do is we must redefine the delimiter temporarily to cause my SQL to pass the entire start program definition to the server.
So basically you have to say delimiter and give it some character, for example, dollar dollar or slash slash.
And then this is going to be the delimiter instead of the semicolon at the end of the line.
And then you do your procedure, definition and so on.
And then at the end you use that delimiter.
And then finally to bring it back to the original delimiter that semicolon, you do the limiter space semicolon.
So we're going to need that otherwise we'll get a problem.
Okay, so instead of the semicolon here, I'm going to use a dollar dollar as the delimiter.
So at the end of the statement of the procedure, I use dollar dollar.
Now to make that work before the procedure, I have to say delimiter space dollar dollar.
Okay, and then after the procedure is done, I can say delimiter semicolon again to bring it back to its original value.
So the next line we use semicolon for the delimiter as we have it here.
And there you go. We got the first line with 20 asterisks followed by space and then 18 and then 19 and then 18 and so on up until the last line that's asterisks followed by space.
So I see here asterisks at the end this one works fine.
If you want to be more technical correct and not have a space after the asterisk when there's only one, you can add here in the repeat.
You can have an if the I is equal to one that is only one asterisk.
We want to only show one asterisk without a space afterward. Otherwise else we do the repeat as usual.
So that way the last one, the very last one, you only got a asterisk and no space after that.
That one works the same way for the purposes of the solution to this problem.
So we want to click submit code.