1
00:00:05,560 --> 00:00:11,740
If you don't already have SQL Server Management Studio open, go ahead and open it now and close any

2
00:00:11,740 --> 00:00:13,270
open query windows.

3
00:00:13,480 --> 00:00:19,900
I'm going to open a new query window, and I'm just going to paste in a simple query actually two.

4
00:00:19,900 --> 00:00:24,460
But the bottom one is being hidden for now because that's the query that we're going to want to write

5
00:00:24,460 --> 00:00:26,050
as part of this exercise.

6
00:00:26,050 --> 00:00:32,590
Now on the top I just have a simple query, and you should be able to look at that now and tell what

7
00:00:32,590 --> 00:00:34,240
the expected results are.

8
00:00:34,270 --> 00:00:40,120
We are just selecting the last and first name, salary and department ID from the employees table who

9
00:00:40,120 --> 00:00:43,600
work in Department 300 and is ordered by the last name.

10
00:00:43,600 --> 00:00:48,970
But let's say instead of this, we want our results to look like this.

11
00:00:48,970 --> 00:00:55,180
We want one column for our employee name, which is first name with a space and then the last name.

12
00:00:55,180 --> 00:01:02,650
And instead of annual salary, we want monthly income, which is going to be 1/12 of the salary.

13
00:01:02,650 --> 00:01:07,630
And there is a dollar sign in front of it and there's no decimal place.

14
00:01:07,630 --> 00:01:13,780
So let's look at how we would get these results step by step from our initial query.

15
00:01:14,260 --> 00:01:17,020
So I'm just going to copy this query.

16
00:01:17,710 --> 00:01:19,480
And I'll do a new query window.

17
00:01:19,480 --> 00:01:22,270
Paste it in and hit execute.

18
00:01:22,270 --> 00:01:24,580
And here's our initial results.

19
00:01:24,580 --> 00:01:26,440
First let's work on the names.

20
00:01:26,440 --> 00:01:28,690
And I'll introduce a new concept here.

21
00:01:28,690 --> 00:01:32,950
We want to show first name space last name as a string.

22
00:01:32,950 --> 00:01:39,040
And so what we'll do is first we're just going to swap these around.

23
00:01:39,490 --> 00:01:42,190
And that gets them in the right order.

24
00:01:42,190 --> 00:01:47,500
We can use mathematical operators on strings as well as numbers and SQL.

25
00:01:47,500 --> 00:01:54,520
And there is a link in the lecture resources that has your arithmetic operators in Transact-sql.

26
00:01:54,520 --> 00:01:59,800
And we have plus minus multiply divide also modulo.

27
00:01:59,830 --> 00:02:03,310
We can use these on strings as well as numbers.

28
00:02:03,310 --> 00:02:09,910
I can just say first name plus and then put a space and then another plus.

29
00:02:09,910 --> 00:02:16,120
And then we'll use an alias as let's just call this employee.

30
00:02:16,120 --> 00:02:23,200
And because of context SQL sees that it's two strings where the plus sign is it knows to concatenate

31
00:02:23,200 --> 00:02:23,980
the strings.

32
00:02:23,980 --> 00:02:27,670
If they were numbers, it would actually do a mathematical add.

33
00:02:27,670 --> 00:02:32,290
Next, let's get the salary to be a monthly income.

34
00:02:32,290 --> 00:02:40,060
Well, if this is an annual salary, then salary divided by 12 is going to be your monthly income.

35
00:02:40,060 --> 00:02:46,210
And we can just put that down as an alias and we'll see where we're at.

36
00:02:46,210 --> 00:02:46,600
Yep.

37
00:02:46,600 --> 00:02:47,800
There's that.

38
00:02:48,040 --> 00:02:50,650
Now we want to get rid of the decimals.

39
00:02:50,650 --> 00:02:53,320
And this is a floating point number.

40
00:02:53,320 --> 00:02:54,730
That means it's got a decimal.

41
00:02:54,730 --> 00:02:59,350
And there is another link in your resources.

42
00:02:59,350 --> 00:03:03,700
And it has to do with the casting convert operators or functions.

43
00:03:03,700 --> 00:03:09,670
Cast basically just takes an expression and forces it into another data type.

44
00:03:09,670 --> 00:03:14,350
So we can change a number to a string, a string to a number, things like that.

45
00:03:14,350 --> 00:03:22,060
We can also use our convert function to convert information from one type to another.

46
00:03:22,060 --> 00:03:28,510
And if you scroll down and look at this, it gives you a lot of examples and a lot of technical information

47
00:03:28,510 --> 00:03:30,550
on pretty much every data type.

48
00:03:30,550 --> 00:03:38,440
But going back to our query, we can cast our salary times 12, which is a floating point.

49
00:03:38,800 --> 00:03:43,450
And we can just say as int which is integer.

50
00:03:43,450 --> 00:03:46,600
And that will take away the decimal point.

51
00:03:47,320 --> 00:03:48,910
And I'll hit execute.

52
00:03:48,910 --> 00:03:51,040
So that takes away our decimal point.

53
00:03:51,040 --> 00:03:54,250
But we don't have our dollar sign.

54
00:03:54,250 --> 00:04:00,460
And so in order to do a dollar sign we would have to add the dollar sign character to it.

55
00:04:00,460 --> 00:04:08,260
And then we would also have to make this a string as well, in order to concatenate a string like we

56
00:04:08,260 --> 00:04:09,850
did with our first and last name.

57
00:04:09,850 --> 00:04:13,600
So we can just kind of nest this and say cast.

58
00:04:13,600 --> 00:04:17,800
And then this whole thing which ends up being our integer.

59
00:04:17,800 --> 00:04:23,500
And I can just say as varchar, which is how we do strings in SQL.

60
00:04:23,500 --> 00:04:29,530
And then we are going to add a dollar sign in front, and I'll put a space there and do a plus.

61
00:04:29,530 --> 00:04:31,390
So now this is a string.

62
00:04:31,390 --> 00:04:33,910
And the output of this is a string.

63
00:04:33,910 --> 00:04:36,910
And so I can add these two things together.

64
00:04:36,910 --> 00:04:38,920
So I will just hit execute.

65
00:04:38,920 --> 00:04:41,890
And now I have my dollar sign.

66
00:04:42,710 --> 00:04:48,530
So going back to our original script, you can see that's pretty much what we started with.

67
00:04:48,560 --> 00:04:50,870
I've kind of indented each line.

68
00:04:50,870 --> 00:04:58,640
I like doing this because it does make it easier to read, and the output is formatted exactly the way

69
00:04:58,640 --> 00:04:59,480
that we like it.

70
00:04:59,480 --> 00:05:05,630
So there's a lot that you can do with your columns and your with your columns and your select statements.

71
00:05:05,660 --> 00:05:09,140
Now there is another way to show numbers as currency.

72
00:05:09,170 --> 00:05:15,950
It involves some string functions such as format and looking at some other data types such as currency.

73
00:05:15,950 --> 00:05:20,330
So we are going to cover that in a lecture a little later in the course.

74
00:05:21,020 --> 00:05:27,380
Coming up next is an assignment where you'll be using math operators and cast functions in a challenge

75
00:05:27,380 --> 00:05:30,020
to format some results in the way that we wanted.

76
00:05:30,020 --> 00:05:32,240
So I'll see you in the next assignment.