So this next section is a bit of a mixed bag hodgepodge of different topics that are important but aren't
large enough to warrant their own sections.
And the first of those topics we're covering is views.
Now, if we go to the docs reviews, it might sound a little bit intimidating.
Views are stored queries that, when invoked, produce a result set of view acts as a virtual table.
But if that doesn't make sense to you, don't worry, we'll break it down.
It is actually quite simple.
At their core, views are some query that we give a name to and we can store so we don't have to write
that query over and over and over.
And when we call a view, when we execute or when we look at a view invoke a view, we will see the
result of our stored query and it will feel like it's a table that we have stored.
But it's not.
It is a virtual table.
So let me show you an example of a query where we might want to do this.
So we have our reviews table, select star from reviews, and probably a lot of the time when I'm doing
some analysis with this in the real world, I'm going to absolutely be joining it with the series table
and probably also with the reviewer table.
If not, I mean, maybe one of them at a time, but likely in a lot of scenarios.
I might join all three together and we did that in the previous section.
We saw how to do that.
I'll just quickly type one approach.
So we'll do a select star from reviews and then we'll join on or join with the table.
How about series first and we'll join on where the series dot ID is the same as the reviews dot series
ID.
All right.
So this would be our starting point.
And then I also want to join on information about the reviewer.
So so then I add another join join reviewers on and then reviewers dot ID is equal to the reviews table
dot reviewer ID and that gives us all of our information.
It's hard to look at just when it's printed out here.
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,
the title.
And the released Is it released year or release year and the genre and the rating and the first name
and the last name.
Let's start with those different columns.
There we go.
We can look at it now.
So what we have here is a fully fleshed out reviews table where we've joined on all the information
from the series that's being reviewed and who left the review.
The reviewer.
And this might be a common jumping off point for a lot of our other queries.
We might want to have all of this here and then do things like figure out, you know, the highest rated,
I don't know, average score or break things down by grouped by genre and figure out which user likes
animation the most or which reviewer has the highest average comedy reviews.
Just silly examples, maybe contrived, but a common thing we might want to start with is this table.
So instead of having to type this before I do anything else, because if I want to do a group by and
then grouped by the series name, I can do that.
But then later on, if I want to try something else where I'm grouping by the genre and the release
here, then I would have to still do all of this joining, get all this information together.
So instead of doing that, I can create a view views.
Allow us to take a query like this that returns some results and we'll be able to store it and give
it a name, and then we'll be able to treat it as if it were a true table.
This isn't a real table in our database.
This is the result of a query that joins together three tables, but we'll be able to treat it as a
table when I create a view and this is the syntax for that create view and then I give it a name.
I'm thinking maybe full reviews because this represents all the information for a given review fleshed
out in each row.
So full reviews.
And then we say as and then the query that we want to store as full reviews.
So when I run this now, let me clear my screen.
It looks like everything went well.
We haven't changed any tables.
We haven't done anything to reviews or series or reviewers.
But I now have this virtual table.
In fact, by show tables, we see full reviews right here.
But we never created a table called Full Reviews.
Full reviews is just a virtual table, a view that we've set up using this query.
So now I can do things like Select Star from full reviews and we get all of that information we got
from this long query.
But I don't have to just call it, you know, select star from full reviews and just use it in the same
way.
Every time I can use things like Group B, I can do A where where genre equals animation.
I can treat it as if it were a table.
And this is pretty powerful.
It doesn't allow us to do anything new that we couldn't do before, but it allows us to write much shorter
queries that make a lot more sense to look at instead of this massive thing at the beginning of every
one of our queries.
Now, if I want all the information for each review, all I do is select staff from full reviews.
So again, we can do things like grouped by.
Let's do a select star.
Well, probably not star, but let's do select.
I don't know.
How about genre and the average rating from full reviews and we'll group by.
Genre.
And now we see the average rating for each one of those genres animation, comedy and drama.
And again, we're using The View full reviews, which is a virtual table constructed with the information
returned from this query select title release year, blah, blah, blah from reviews, join on series,
join on reviewers.
And it's really just a pleasure to use.
It's so simple.
Once we have stored that we can easily use it and treat it like a table.
So that's an intro to use, but we still have a little more to cover.