1
00:00:00,240 --> 00:00:00,800
Okay.

2
00:00:00,810 --> 00:00:06,060
So in this video, we're just going to go over the solutions to the exercises I introduced in the previous

3
00:00:06,060 --> 00:00:06,630
video.

4
00:00:06,810 --> 00:00:10,980
And the first one here is to print the total number of books in the database.

5
00:00:10,980 --> 00:00:12,570
So we'll need to use count.

6
00:00:13,290 --> 00:00:18,270
And all that will do is write a select count.

7
00:00:19,470 --> 00:00:22,320
Star from books just like that.

8
00:00:22,620 --> 00:00:24,300
And we get 19.

9
00:00:25,470 --> 00:00:25,950
All right.

10
00:00:25,950 --> 00:00:26,730
And that's it.

11
00:00:28,290 --> 00:00:32,720
The next one is to print out how many books were released in each year.

12
00:00:32,729 --> 00:00:39,300
So we'll need to use a group by group by release year and then use count in conjunction with that.

13
00:00:41,190 --> 00:00:47,010
So let's do a select and we'll just do count.

14
00:00:47,850 --> 00:00:57,600
Star from books group by released here just like that.

15
00:00:59,480 --> 00:01:00,320
Now on its own.

16
00:01:00,320 --> 00:01:01,850
This isn't that useful.

17
00:01:02,090 --> 00:01:11,300
So I'll go ahead and add in select release year comma count so that we get the year released and then

18
00:01:11,450 --> 00:01:15,710
the count and you can see most years have one book released.

19
00:01:16,040 --> 00:01:17,630
2001 has three.

20
00:01:17,630 --> 00:01:18,080
2000.

21
00:01:18,080 --> 00:01:19,220
Three has two.

22
00:01:20,490 --> 00:01:21,180
All right.

23
00:01:22,320 --> 00:01:23,400
So the next problem.

24
00:01:24,490 --> 00:01:28,030
Print out the total number of books in stock.

25
00:01:28,420 --> 00:01:35,740
So this one you'll actually need to use some and you'll need to sum all the stock quantities, which

26
00:01:35,740 --> 00:01:37,900
is something I guess we haven't really used.

27
00:01:39,830 --> 00:01:40,270
Oop.

28
00:01:40,360 --> 00:01:41,530
It's not how you spell from.

29
00:01:42,670 --> 00:01:44,210
We haven't used stock.

30
00:01:44,240 --> 00:01:45,310
Oh, my cat.

31
00:01:45,640 --> 00:01:46,930
You got to chill out, blue.

32
00:01:48,870 --> 00:01:53,910
It's a stock quantity is not a piece of data that we've used very often, but it is here.

33
00:01:54,720 --> 00:01:57,870
We've neglected it a little bit, but it is there as you can see.

34
00:01:58,320 --> 00:02:05,670
So we just want to add them all together and that's just going to be select some stock quantity.

35
00:02:06,390 --> 00:02:07,680
Quantity.

36
00:02:09,630 --> 00:02:10,919
From books.

37
00:02:12,720 --> 00:02:13,390
There we go.

38
00:02:13,410 --> 00:02:16,380
We have 2450 books in stock.

39
00:02:16,890 --> 00:02:21,240
So the next problem here is to find the average release here for each author.

40
00:02:21,240 --> 00:02:28,370
So let me take grouped by author and then we'll need to use average AVG to calculate the average release

41
00:02:28,380 --> 00:02:28,740
year.

42
00:02:29,250 --> 00:02:33,930
So let's give it a shot, average release year for each author.

43
00:02:34,590 --> 00:02:42,330
So we'll need to do a select average and then release year.

44
00:02:44,720 --> 00:02:49,270
From Books group by and then we're grouping by author.

45
00:02:49,280 --> 00:02:56,000
So we'll do l name, comma, author f name again because of the Harris issue.

46
00:02:57,320 --> 00:02:58,430
What are their names?

47
00:02:58,610 --> 00:03:00,230
Dan Harris and Frieda Harris.

48
00:03:01,340 --> 00:03:02,540
So if we do that.

49
00:03:02,540 --> 00:03:02,990
Oh, my.

50
00:03:04,710 --> 00:03:05,970
My cat again.

51
00:03:07,140 --> 00:03:08,070
Oh, boy.

52
00:03:08,430 --> 00:03:10,230
Well, I've taken care of that.

53
00:03:10,650 --> 00:03:13,350
And I don't mean wow.

54
00:03:13,950 --> 00:03:15,900
Apparently have not taken care of it.

55
00:03:16,410 --> 00:03:17,030
All right.

56
00:03:17,050 --> 00:03:17,820
Hang on.

57
00:03:18,300 --> 00:03:20,580
So back to the problem at hand.

58
00:03:20,850 --> 00:03:21,930
This works.

59
00:03:22,230 --> 00:03:28,890
We're getting average years, but to make it a little bit better, we should also add in the author

60
00:03:29,220 --> 00:03:34,230
f name, comma, author l name, just so we can make a bit more sense of the data.

61
00:03:34,530 --> 00:03:38,490
So now we can see Raymond Carver's average released year, 1985.

62
00:03:40,310 --> 00:03:40,810
Let's see.

63
00:03:40,820 --> 00:03:43,880
Neil Gaiman since 2000, 6.6667.

64
00:03:44,750 --> 00:03:45,290
All right.

65
00:03:45,440 --> 00:03:47,180
So that's all there is to that problem.

66
00:03:48,710 --> 00:03:50,690
Now we get to the one that's a little different.

67
00:03:50,690 --> 00:03:54,410
Find the full name of the author who wrote the longest book.

68
00:03:54,980 --> 00:03:56,540
So there's a couple of ways of doing it.

69
00:03:56,810 --> 00:04:00,460
The the way that doesn't qualify as a solution.

70
00:04:00,470 --> 00:04:04,010
The two step process would be first to find the longest book.

71
00:04:04,790 --> 00:04:14,210
So that would be select max pages from books like this.

72
00:04:14,210 --> 00:04:15,620
634.

73
00:04:15,980 --> 00:04:24,350
And then at this point, we can make a second query select and we want to concatenate first name and

74
00:04:24,350 --> 00:04:30,710
last name so we do can cat author f name, comma, author l name.

75
00:04:30,710 --> 00:04:32,660
And we want a space in between.

76
00:04:35,440 --> 00:04:36,250
Like that.

77
00:04:38,930 --> 00:04:44,090
From books where pages equals 634.

78
00:04:44,720 --> 00:04:45,710
And there we go.

79
00:04:45,740 --> 00:04:47,000
We get Michael Chabon.

80
00:04:47,300 --> 00:04:53,570
However, we want to do it in one step so we can either do this sub query route.

81
00:04:54,080 --> 00:04:56,360
So we'll do select King Cat author.

82
00:04:56,600 --> 00:05:00,290
However, instead of we'll do from books.

83
00:05:00,890 --> 00:05:04,280
But instead of just doing web pages is 634.

84
00:05:04,310 --> 00:05:10,400
We'll do where pages equals and then we use parentheses for the subquery.

85
00:05:11,060 --> 00:05:12,650
Hopefully you remember this part.

86
00:05:12,650 --> 00:05:15,530
And then in here all that we do is write our query.

87
00:05:15,530 --> 00:05:24,050
So that's going to be select max pages from books.

88
00:05:25,720 --> 00:05:27,190
And we get the same thing.

89
00:05:27,370 --> 00:05:34,840
Michael Chabon And then there's the other way of going about it, which is to not use Max at all and

90
00:05:34,840 --> 00:05:42,460
circumvent that which is actually faster to use order by and then limit it to one so we could do the

91
00:05:42,460 --> 00:05:44,050
same thing so I can cat.

92
00:05:44,500 --> 00:05:45,850
Author f name.

93
00:05:46,120 --> 00:05:48,070
Author ll name from books.

94
00:05:49,210 --> 00:05:53,050
And then we just do an order by pages.

95
00:05:53,710 --> 00:05:55,540
Ascending limit one.

96
00:05:57,630 --> 00:06:00,710
And I believe have that backwards.

97
00:06:00,720 --> 00:06:03,090
We want the longest book, not the shortest one.

98
00:06:03,510 --> 00:06:04,260
There we go.

99
00:06:05,130 --> 00:06:10,650
So we want it in descending order so that we get the longest one at the beginning.

100
00:06:11,520 --> 00:06:13,440
And that's going to be 634.

101
00:06:13,650 --> 00:06:16,080
So if we if we get rid of this limit one.

102
00:06:17,960 --> 00:06:26,000
And actually, if we get rid of limit one and let's do pages comma.

103
00:06:26,420 --> 00:06:26,840
All right.

104
00:06:26,840 --> 00:06:32,180
So you can see now we've got 634 all the way down to 176.

105
00:06:32,180 --> 00:06:34,160
And I was I had a backwards.

106
00:06:35,300 --> 00:06:38,280
So both are valid ways of getting the solution.

107
00:06:38,300 --> 00:06:44,810
This one is a little bit well, it's a little faster because we're running one query.

108
00:06:45,200 --> 00:06:49,280
This version is more complex, more advanced, I guess.

109
00:06:49,880 --> 00:06:55,640
But I would definitely stick to using this when possible, using order by.

110
00:06:56,420 --> 00:06:56,960
All right.

111
00:06:56,960 --> 00:06:59,900
Let's move on to our final challenge here.

112
00:07:00,290 --> 00:07:01,550
Make this happen.

113
00:07:02,000 --> 00:07:04,400
So we've got a couple of things going on.

114
00:07:04,850 --> 00:07:05,600
We've got.

115
00:07:06,670 --> 00:07:07,240
A year.

116
00:07:07,240 --> 00:07:09,460
So things let's see, we've got a year.

117
00:07:09,640 --> 00:07:14,470
The number of books in that given year and the average pages for all those books for that given year.

118
00:07:14,470 --> 00:07:18,940
So things are grouped by year and we have three aliases going on.

119
00:07:19,810 --> 00:07:20,920
So let's give it a shot.

120
00:07:21,310 --> 00:07:30,040
So we'll start off by selecting year or released year and giving it the alias of year.

121
00:07:33,460 --> 00:07:35,740
Then the next thing is the count.

122
00:07:35,830 --> 00:07:38,080
Remember, we're going to have a group bye at the end.

123
00:07:38,530 --> 00:07:43,480
So we're doing all of this, writing the three things or selecting before we actually have the group

124
00:07:43,480 --> 00:07:43,860
by.

125
00:07:43,870 --> 00:07:46,390
But at the end we're going to have Group B released here.

126
00:07:46,810 --> 00:07:49,330
So this here number books is count.

127
00:07:49,330 --> 00:07:51,520
It's a count of the number of books released that year.

128
00:07:52,120 --> 00:08:02,710
So I'll indent and just do count star as and I need quotes here because we have that octa Thorpe I think

129
00:08:02,710 --> 00:08:06,360
is what that's called the hash symbol as well as a space.

130
00:08:06,370 --> 00:08:07,900
So we need quotes for our alias.

131
00:08:07,900 --> 00:08:11,230
If we want to match this, then we have average pages.

132
00:08:12,830 --> 00:08:16,180
So it's going to be the same thing as far as our alias is concerned.

133
00:08:16,190 --> 00:08:23,050
So we need average number of pages as a VG pages, is that correct?

134
00:08:23,060 --> 00:08:23,750
There we go.

135
00:08:25,480 --> 00:08:36,039
OC from books and I'll indent this one to group by and we want to group by released.

136
00:08:36,039 --> 00:08:39,309
Hear it enter and pray.

137
00:08:40,450 --> 00:08:43,059
And it looks like it worked out.

138
00:08:43,090 --> 00:08:47,140
1945 had one book, 181 pages long.

139
00:08:47,920 --> 00:08:49,690
Take a look at 2001.

140
00:08:49,690 --> 00:08:54,820
There was three books and the average number of pages is 443 and a third.

141
00:08:55,540 --> 00:08:56,290
All right.

142
00:08:56,590 --> 00:09:00,760
So that matches what I had here, which means that we got it right.

143
00:09:01,690 --> 00:09:03,280
Don't ask why you would do this.

144
00:09:03,640 --> 00:09:05,200
Just don't ask.

145
00:09:06,480 --> 00:09:07,800
That's it for this section.

146
00:09:07,800 --> 00:09:10,290
And yeah, we'll see you in the next section.