All right.
Welcome to the next section.
So what we're going to do here is basically take the schema design we came up with in the last section
for a roughly Instagram ish application with photos and users and comments and tags and follows and
all of that.
We're going to take that schema and implement it, and I'm going to give you a bunch of data to put
in your database a couple of thousand of things to enter.
I wrote a giant file and what we'll do is put that in there so we all have the same starting place.
And then we're going to pretend that we're the company and we're trying to figure out certain things
like do we have too many bots on our application?
Are there user accounts that are inactive?
Are there user accounts that have never posted a photo?
Maybe we could send them an email campaign encouraging them to post a photo or what hashtags tend to
be most successful, that kind of stuff.
So all of this section, every video will be asking a question, which is basically a nice way of saying
it'll be an exercise, but we're now posing it rather than me just giving you a table, which is what
we've been doing, where it's a table.
And I just ask you to recreate this.
I'm now going to pose the question as if we work at a fictional company and we're trying to find something
out.
For example, it might be something like we're running a new ad campaign and we need to figure out which
five hashtags get the most likes or something like that.
So that's how this will work.
Every video in the section will be a question, and rather than having all of the solutions at the end,
making a giant video that takes like half an hour to go through, I'm just going to break them up.
So every video will have the question at the beginning and then a following solution.
But the first thing we need to do is insert all of our data.
So included in this section is this file here, starter data, SQL.
And there's two components.
The first one is setting up our schema and there's nothing new here.
We talked about all of this in the last section, but if you were following along and doing it yourself,
you may have named something slightly differently.
Like instead of photo tags, you may have called it tagging, or instead of, you know, I don't know.
Instead of follow me and follower and follows, maybe you called it a following or relationship or something.
So in order to give you this data here to insert, I needed to make sure that we all have the same schema,
even if just one thing is named differently, it's a problem.
So the first part of this is just a bunch of create tables.
All the tables we discussed, as well as dropping the existing Instagram clone database, creating a
new database, and then using it if you wanted to.
If you want to keep your current Instagram clone database, if you've been working with that, just
give this a different name, whatever you want.
Well, but you need to do it to all three of these.
So we set up the schema, but then the fun part, which is all these insert statements and it doesn't
look that impressive when you look at it in cloud nine because it doesn't wrap them on to new lines.
And if I start scrolling, you'll see I'm scrolling really fast.
But look down here at the progress bar.
I've barely made a dent and this is not a great way to visualize the data.
So instead, here I am in Sublime text and here's the file that I generated.
It's the same exact file it's just displayed all here.
Rather than having to scroll across, I have to scroll down so we won't do any sort of grand tour of
the data.
But I will say this I spent a lot of time trying to figure out how I wanted this to work.
Obviously I didn't type this myself.
I would have taken like days probably.
Instead I wrote some code which still took probably an entire day and a half to get this how I wanted,
because I didn't want just randomize data.
If I just wanted to insert a bunch of random users and posts and comments that have no meaning to it,
I could have done that pretty quickly, but instead I tried to design this so that there is certain
personas to our to our users.
So some users post a lot.
So they have a lot of comments, a lot of posts.
Some users are lurkers, meaning that they don't really do anything except look at people, maybe like
certain things at best, but they don't comment and they don't post.
Then we have things like bots, which on Instagram are a real problem, where bots are accounts that
exist basically to go out and like a bunch of things, comment on things, usually the same exact comment,
but they don't post things.
And then we also have like celebrity accounts who have very few people that they actually follow, but
they have tons of followers and they're not very active with commenting or liking with they're very
active with posting.
So then we've got things like hashtags and I didn't want to just do a bunch of random hashtags.
I tried to actually come up with combinations of hashtags that would be common.
So things like smile and style and fashion might be together often, but then we might also have smile
and party or concert.
Those might go together, or we might have landscape.
And Sunrise and Sunset and Beach could be a combination for a beach photo at sunset.
But then we could also have beach with party or beach with beauty or something like that.
So that's just kind of scratching the surface.
But I tried to design a lot of that in there so that we had somewhat realistic data with that said things
like comments.
The text of a comment is just random lorem ipsum text.
So there's nothing there that's going to be meaningful about the comment itself.
What's more meaningful is the relationship between the comment and who posted it and if they have a
lot of comments or they don't have many comments and that sort of thing.
So that's enough of that.
So even with all the effort that I've put in here, there's still only 100 or so or exactly 100 users,
about 250 something photos.
But then when we start talking about likes, there's thousands and comments, there's tons and following
or follows.
Whatever we called it, there's tons and tags, there's only maybe 20 hashtags.
But then when you apply them to all of those photos, 200 photos, we get thousands to your thousands
of instances.
So all you have to do is get that into a file and then execute that file.
Scroll back over.
It'll take too long.
Oh, boy.
Almost there.
Here we go.
So execute this file and I'll just do that now with source Instagram slash starter data.
And that should just be the path that corresponds to wherever that file is and whatever you named it.
So it takes a little bit compared to what we've seen so far.
So if you take a look, we've got a bunch of tables being created, seven, I think, and then a bunch
of things being inserted.
100 257 7623.
7488 8780 221 and then 501.
So this is still a very small data set compared to, I mean, tiny, minuscule compared to Instagram.
This is maybe like a minute of Instagram activity or probably less than that, a couple of seconds as
far as the number of photos and stuff, but it's still an order of magnitude greater than what we've
been working with.
One other thing that I'll show you is that we haven't really seen giant amounts of data like this.
And if you try and do, let's do a select star from likes, which I think we have a lot of you actually,
if you if you start to scroll through it, you'll see that we're not going to be able to see all of
them.
There is a number that we that the terminal or that the CLI will print out for us and then it just gives
up or decides not to display all of them.
That doesn't mean they're not there.
So if you did a count, for example.
Count start from like we get 8000 and if you join them with another table, it will join all of them.
It just won't display everything.
So for some reason you need it to display every single.
Like it would be better not to do it using the CLI, but to do it from another file or some code somewhere
anyways.
So that's it.
We've got the data set up if you'd like.
Play around with it, explore it a bit, and then we'll start with our fun challenges in the next video.