Hey welcome to the solution video for this section. Realistically, it is impossible for students to access some of these features. Writing to the database to the public data warehouse as part of mode isn't included in the free version, and creating your own database and setting all of this stuff up is really outside of the scope of this class. So these solution videos are really just worked examples, and you'll have a quiz at the end. They'll take you through some of the points that I want you to come out of this section knowing. So in this section, we're going to make a view item events table. We already wrote the query for this, and later on in the class you can use that table that's in the public warehouse and pretend that you made it yourself. So here let's just remember what our query look like, forgetting the view item events. Some stuff has changed, it's a little different because I'm on a private database with a slightly different schema and I'm looking at MySQL here. We aren't going to stress about that because we don't have to get this code running, we just have to answer some questions about it. So, there is a quick way to make a table, and that is using this create table as statement. I've just stuck this syntax up top and I can run it, and.I get a table. It takes a second because we're running this query on the entire events table. It says no rows returned. That's some kind of error? No, it's not. We've actually just asked SQL to put the data into the table rather than returning it and displaying it. So, that's fine. But we still do need to check this table. So let's poke around and see what it looks like, what we've made here. So, we're going to start with just running describe. It's giving me an error, I left this limit 100 box checked. That doesn't really make sense with a describe, and here I'm just selecting the one line I want to run. So, you can see some stuff makes sense. The event ID is a string. The event time here isn't listed as a timestamp or any kind of date thing. The item ID is also not an integer. Because we've pivoted all of this data in theory, the event ID should be a primary key and it should not be possible for it to be null. So, you can't tell this just by selecting start from the table, but let's just look at what we get. If we did select star from this table that I built, it will look like this, which is pretty convenient. This is sub table that you would otherwise have to be recreating all over the place in the rest of this class. The last thing I can do, and something that should not be taken lightly if this is the table that you haven't built, is I can drop this table and I can completely erase it from the database. This is not something you should do with a table that you aren't the owner of. So, you can see now even when I ask what's in this table? I will get an error. This table does not exist. So, everything that I just did is gone when I drop the table. So, we might need to at this point edit the query a little bit to make sure that the data types are coming out right. So, for example here I've slapped on this timestamp. I'm going to gloss over this here but I'm taking time to make this an extra step because I want to make sure that you know that editing a table that lots of people uses really delicate and it makes sense to test all of your little steps, all of your little change. You'll notice that in the previous steps, the table name I used underscore one at the end, I made it a little bit less glamorous because I didn't want anyone to get confused and try to use it. I am the only person working in this database and I still named it something not glamorous. So let's move on to the creating table statement work. Set down for a second. So, here I get to have everything in my wish-list. We'll start building up this table using the full syntax we talked about in the concept video. I can make sure that the data types are exactly the way that I want them. So I want the event ID to be not nullable, I want it to be the primary key, I want the user ID and the item ID to be integers. Yeah, I want it all. So, I can run this table, and it will just create the table. Okay, I've done a few things wrong. One thing I've left this limit box checked, it doesn't make sense for that to be there. This is just a feature of mode. You'll notice when we ran the create table ask statement it took forever to run. That's because it was running on the whole table. So this does generally save you time except when you forget to uncheck it. Then there's another error near referrer, I had forgotten the comma. So, here now we can actually create the table. Here, we again get no rows returns. That's not an error message. We haven't actually done anything to put anything in the table yet. So, often it does actually make sense to include the create table statement along with the insert into statement. So, I've just copied it up here, and let's see if we can get this to run now that we have our query here. So, now we get an error message, the view item events table already exists. That's not really a problem, right? Well, it is when I tried to run this create table statement. So I'm going to put in if not exists and run it again. Then it just ignores this part of the SQL if it's not working, and I've got my query that I worked on in this section. This is that time where I definitely also want to make sure the limit 100 box is unchecked if you're using mode. Great, it succeeded. Let's go on to another example. Here, I have just commented out the event ID. I want to show you what it looks like when we just don't have the right shape data to put in to the table. So, okay, that's a pretty good error message, column count doesn't match the value count at row one. So, row one here is specific to the select statement I think, I'm not sure. So, the keyword that I want to pull out is that the column count doesn't match, and I know exactly why, it's because I counted a part of it. Now, let's get on to the stage where we do everything right. So, I've been using insert into, and what this does is it takes the data that I query and adds it into the table. So, it could be adding in a whole bunch of duplicate data because I keep running this over and over again. If instead what I wanted to do is replace everything in the table, I could use this command replace into, and that would clear out the table and just put the new stuff in and replace into is MySQL specific syntax, however it's called, insert overwrite instead of insert into. So, don't pay attention too much to that specific syntax. Again, we get no rows returned, but that is exactly what we would expect from the screen. So, the next thing for you to do is to answer some quiz questions so that I know you're ready to create tables, and behave responsibly when you get right access to a database.
