If you don't already have SQL Server Management Studio open, go ahead and open it now and close any
open query windows.
I'm going to open a new query window, and I'm just going to paste in a simple query actually two.
But the bottom one is being hidden for now because that's the query that we're going to want to write
as part of this exercise.
Now on the top I just have a simple query, and you should be able to look at that now and tell what
the expected results are.
We are just selecting the last and first name, salary and department ID from the employees table who
work in Department 300 and is ordered by the last name.
But let's say instead of this, we want our results to look like this.
We want one column for our employee name, which is first name with a space and then the last name.
And instead of annual salary, we want monthly income, which is going to be 1/12 of the salary.
And there is a dollar sign in front of it and there's no decimal place.
So let's look at how we would get these results step by step from our initial query.
So I'm just going to copy this query.
And I'll do a new query window.
Paste it in and hit execute.
And here's our initial results.
First let's work on the names.
And I'll introduce a new concept here.
We want to show first name space last name as a string.
And so what we'll do is first we're just going to swap these around.
And that gets them in the right order.
We can use mathematical operators on strings as well as numbers and SQL.
And there is a link in the lecture resources that has your arithmetic operators in Transact-sql.
And we have plus minus multiply divide also modulo.
We can use these on strings as well as numbers.
I can just say first name plus and then put a space and then another plus.
And then we'll use an alias as let's just call this employee.
And because of context SQL sees that it's two strings where the plus sign is it knows to concatenate
the strings.
If they were numbers, it would actually do a mathematical add.
Next, let's get the salary to be a monthly income.
Well, if this is an annual salary, then salary divided by 12 is going to be your monthly income.
And we can just put that down as an alias and we'll see where we're at.
Yep.
There's that.
Now we want to get rid of the decimals.
And this is a floating point number.
That means it's got a decimal.
And there is another link in your resources.
And it has to do with the casting convert operators or functions.
Cast basically just takes an expression and forces it into another data type.
So we can change a number to a string, a string to a number, things like that.
We can also use our convert function to convert information from one type to another.
And if you scroll down and look at this, it gives you a lot of examples and a lot of technical information
on pretty much every data type.
But going back to our query, we can cast our salary times 12, which is a floating point.
And we can just say as int which is integer.
And that will take away the decimal point.
And I'll hit execute.
So that takes away our decimal point.
But we don't have our dollar sign.
And so in order to do a dollar sign we would have to add the dollar sign character to it.
And then we would also have to make this a string as well, in order to concatenate a string like we
did with our first and last name.
So we can just kind of nest this and say cast.
And then this whole thing which ends up being our integer.
And I can just say as varchar, which is how we do strings in SQL.
And then we are going to add a dollar sign in front, and I'll put a space there and do a plus.
So now this is a string.
And the output of this is a string.
And so I can add these two things together.
So I will just hit execute.
And now I have my dollar sign.
So going back to our original script, you can see that's pretty much what we started with.
I've kind of indented each line.
I like doing this because it does make it easier to read, and the output is formatted exactly the way
that we like it.
So there's a lot that you can do with your columns and your with your columns and your select statements.
Now there is another way to show numbers as currency.
It involves some string functions such as format and looking at some other data types such as currency.
So we are going to cover that in a lecture a little later in the course.
Coming up next is an assignment where you'll be using math operators and cast functions in a challenge
to format some results in the way that we wanted.
So I'll see you in the next assignment.