1
00:00:00,210 --> 00:00:06,720
So this next section is a bit of a mixed bag hodgepodge of different topics that are important but aren't

2
00:00:06,720 --> 00:00:08,970
large enough to warrant their own sections.

3
00:00:08,970 --> 00:00:12,270
And the first of those topics we're covering is views.

4
00:00:12,450 --> 00:00:16,170
Now, if we go to the docs reviews, it might sound a little bit intimidating.

5
00:00:16,200 --> 00:00:21,210
Views are stored queries that, when invoked, produce a result set of view acts as a virtual table.

6
00:00:21,300 --> 00:00:24,690
But if that doesn't make sense to you, don't worry, we'll break it down.

7
00:00:24,690 --> 00:00:26,190
It is actually quite simple.

8
00:00:26,220 --> 00:00:33,540
At their core, views are some query that we give a name to and we can store so we don't have to write

9
00:00:33,540 --> 00:00:35,310
that query over and over and over.

10
00:00:35,430 --> 00:00:43,770
And when we call a view, when we execute or when we look at a view invoke a view, we will see the

11
00:00:43,770 --> 00:00:48,720
result of our stored query and it will feel like it's a table that we have stored.

12
00:00:48,720 --> 00:00:49,710
But it's not.

13
00:00:49,740 --> 00:00:51,660
It is a virtual table.

14
00:00:51,660 --> 00:00:56,160
So let me show you an example of a query where we might want to do this.

15
00:00:56,160 --> 00:01:04,860
So we have our reviews table, select star from reviews, and probably a lot of the time when I'm doing

16
00:01:04,860 --> 00:01:11,520
some analysis with this in the real world, I'm going to absolutely be joining it with the series table

17
00:01:11,520 --> 00:01:13,920
and probably also with the reviewer table.

18
00:01:13,920 --> 00:01:18,120
If not, I mean, maybe one of them at a time, but likely in a lot of scenarios.

19
00:01:18,120 --> 00:01:22,470
I might join all three together and we did that in the previous section.

20
00:01:22,470 --> 00:01:23,580
We saw how to do that.

21
00:01:23,580 --> 00:01:25,860
I'll just quickly type one approach.

22
00:01:25,860 --> 00:01:33,330
So we'll do a select star from reviews and then we'll join on or join with the table.

23
00:01:33,330 --> 00:01:42,510
How about series first and we'll join on where the series dot ID is the same as the reviews dot series

24
00:01:42,510 --> 00:01:43,320
ID.

25
00:01:44,220 --> 00:01:45,000
All right.

26
00:01:45,000 --> 00:01:46,860
So this would be our starting point.

27
00:01:47,010 --> 00:01:51,210
And then I also want to join on information about the reviewer.

28
00:01:51,210 --> 00:01:59,790
So so then I add another join join reviewers on and then reviewers dot ID is equal to the reviews table

29
00:01:59,790 --> 00:02:05,610
dot reviewer ID and that gives us all of our information.

30
00:02:06,030 --> 00:02:08,550
It's hard to look at just when it's printed out here.

31
00:02:08,550 --> 00:02:13,230
I'd have to zoom out, but let's narrow it down and say we only want to work with the I don't know,

32
00:02:13,230 --> 00:02:14,460
the title.

33
00:02:15,430 --> 00:02:23,890
And the released Is it released year or release year and the genre and the rating and the first name

34
00:02:23,890 --> 00:02:25,490
and the last name.

35
00:02:25,510 --> 00:02:28,860
Let's start with those different columns.

36
00:02:28,870 --> 00:02:29,650
There we go.

37
00:02:29,650 --> 00:02:30,870
We can look at it now.

38
00:02:30,880 --> 00:02:37,540
So what we have here is a fully fleshed out reviews table where we've joined on all the information

39
00:02:37,540 --> 00:02:41,440
from the series that's being reviewed and who left the review.

40
00:02:41,470 --> 00:02:42,310
The reviewer.

41
00:02:42,310 --> 00:02:45,700
And this might be a common jumping off point for a lot of our other queries.

42
00:02:45,700 --> 00:02:51,760
We might want to have all of this here and then do things like figure out, you know, the highest rated,

43
00:02:52,000 --> 00:02:58,930
I don't know, average score or break things down by grouped by genre and figure out which user likes

44
00:02:58,930 --> 00:03:04,300
animation the most or which reviewer has the highest average comedy reviews.

45
00:03:04,690 --> 00:03:11,260
Just silly examples, maybe contrived, but a common thing we might want to start with is this table.

46
00:03:11,260 --> 00:03:18,220
So instead of having to type this before I do anything else, because if I want to do a group by and

47
00:03:18,220 --> 00:03:23,380
then grouped by the series name, I can do that.

48
00:03:23,380 --> 00:03:29,080
But then later on, if I want to try something else where I'm grouping by the genre and the release

49
00:03:29,080 --> 00:03:34,000
here, then I would have to still do all of this joining, get all this information together.

50
00:03:34,000 --> 00:03:37,870
So instead of doing that, I can create a view views.

51
00:03:37,870 --> 00:03:44,170
Allow us to take a query like this that returns some results and we'll be able to store it and give

52
00:03:44,170 --> 00:03:48,370
it a name, and then we'll be able to treat it as if it were a true table.

53
00:03:48,370 --> 00:03:50,770
This isn't a real table in our database.

54
00:03:50,770 --> 00:03:55,900
This is the result of a query that joins together three tables, but we'll be able to treat it as a

55
00:03:55,900 --> 00:04:03,730
table when I create a view and this is the syntax for that create view and then I give it a name.

56
00:04:03,850 --> 00:04:10,090
I'm thinking maybe full reviews because this represents all the information for a given review fleshed

57
00:04:10,090 --> 00:04:11,170
out in each row.

58
00:04:11,170 --> 00:04:12,550
So full reviews.

59
00:04:12,880 --> 00:04:19,899
And then we say as and then the query that we want to store as full reviews.

60
00:04:20,440 --> 00:04:23,980
So when I run this now, let me clear my screen.

61
00:04:25,440 --> 00:04:26,970
It looks like everything went well.

62
00:04:27,000 --> 00:04:28,470
We haven't changed any tables.

63
00:04:28,470 --> 00:04:32,520
We haven't done anything to reviews or series or reviewers.

64
00:04:32,520 --> 00:04:35,870
But I now have this virtual table.

65
00:04:35,880 --> 00:04:39,870
In fact, by show tables, we see full reviews right here.

66
00:04:39,870 --> 00:04:42,950
But we never created a table called Full Reviews.

67
00:04:42,960 --> 00:04:48,660
Full reviews is just a virtual table, a view that we've set up using this query.

68
00:04:48,660 --> 00:04:57,330
So now I can do things like Select Star from full reviews and we get all of that information we got

69
00:04:57,330 --> 00:04:59,250
from this long query.

70
00:04:59,520 --> 00:05:04,860
But I don't have to just call it, you know, select star from full reviews and just use it in the same

71
00:05:04,860 --> 00:05:05,250
way.

72
00:05:05,250 --> 00:05:13,950
Every time I can use things like Group B, I can do A where where genre equals animation.

73
00:05:15,860 --> 00:05:18,250
I can treat it as if it were a table.

74
00:05:18,260 --> 00:05:19,790
And this is pretty powerful.

75
00:05:19,820 --> 00:05:24,920
It doesn't allow us to do anything new that we couldn't do before, but it allows us to write much shorter

76
00:05:24,920 --> 00:05:32,030
queries that make a lot more sense to look at instead of this massive thing at the beginning of every

77
00:05:32,030 --> 00:05:32,960
one of our queries.

78
00:05:32,990 --> 00:05:38,390
Now, if I want all the information for each review, all I do is select staff from full reviews.

79
00:05:38,690 --> 00:05:41,570
So again, we can do things like grouped by.

80
00:05:41,600 --> 00:05:43,400
Let's do a select star.

81
00:05:43,430 --> 00:05:46,400
Well, probably not star, but let's do select.

82
00:05:47,270 --> 00:05:47,750
I don't know.

83
00:05:47,750 --> 00:05:56,840
How about genre and the average rating from full reviews and we'll group by.

84
00:05:57,670 --> 00:05:58,390
Genre.

85
00:06:00,000 --> 00:06:05,520
And now we see the average rating for each one of those genres animation, comedy and drama.

86
00:06:05,610 --> 00:06:12,240
And again, we're using The View full reviews, which is a virtual table constructed with the information

87
00:06:12,240 --> 00:06:18,090
returned from this query select title release year, blah, blah, blah from reviews, join on series,

88
00:06:18,090 --> 00:06:19,380
join on reviewers.

89
00:06:19,380 --> 00:06:22,320
And it's really just a pleasure to use.

90
00:06:22,320 --> 00:06:23,340
It's so simple.

91
00:06:23,340 --> 00:06:28,260
Once we have stored that we can easily use it and treat it like a table.

92
00:06:28,260 --> 00:06:30,840
So that's an intro to use, but we still have a little more to cover.