So we spent some time discussing the basics of databases and how to retrieve data from tables, but there's something else we can do a SQL too, and that's actually create new tables and store data within them. In this lesson, you'll learn how to be able to discuss situations where it's beneficial to create new tables, create new tables within an existing database, write data in a new table, and define whether columns can accept null values or not. The ability to create tables and store data in them is really beneficial as a data scientist because you're always making models and building predictions. You may want to take those predictions that you create and write them back to a database. This ensures that someone else could then pick up those predictions and use them in a dashboard they're creating, or maybe you want to create a dashboard or visualize it with another tool that can be hooked up and used with that database. It's also helpful if you're extracting data off the web or scraping it from somewhere and you want to store this data in a database with the rest of your information. That way, you can then join it back together. As we've previously discussed, the data scientist isn't usually the one in charge of managing the entire database, that usually left to the DBA or some type of administrator. However, they may have capabilities to be able to write and create their own tables. So it's important to have a basic understanding of how this works. In order to do this, there is a statement that we use called CREATE TABLE. So in this example, I want to create a table that is about the different shoes I have on my shoe rack. Maybe I want to start to look at things like how long I've had the shoe, the different brands, how much I pay for them, or look for shoes that I have that are similar or maybe different colors. I may even want to look and see if I'm missing any shoes for my wardrobe. For this statement, you're going to need a table name. Then you also need the name and definition of the columns. And this, you also need to define the data type. To do this, we're going to write the statement CREATE TABLE. We'll put the name of the table and then in brackets, we will separate out the list of columns that we're going to name this table. In this example, I have the columns as shoe Id, the brand, the shoe type, the color, the price, and the description. After I list the column, I'm going to define the data type. For this, I want this to be a character with 10. Then, I'm defining some specifications around that column. The shoe Id is going to be my primary key in this example. You can see by the other columns I've also defined the data type how many characters or decimals I will allow to be inserted into this column. Then, I also put whether or not I'm allowing null values in this. If I'm not specifying, then it's assuming that null values are accepted. In this example, it's a pretty simple example because the syntax for creating these tables varies greatly by relational database management system that you're using. This will give you the basic structure to create your table. However, it's important to look at the specifications of your relational database management system you're using, so that you can get the correct syntax for this. An important thing to note when creating these tables is defining whether a column can contain a null value or is a primary key. Every column you have in a table can either accept null values or not, and you need to be defined this capability. As shown in this example, I've created a table with several columns, some of which cannot accept null values. Those are not null lines. And the one column that can accept null value, the one line that has null in it. It's important to not confuse null values with empty strings. Null values really are the absence of everything, whereas empty strings, there's actually a value there. It might be spaces or something like that. Another thing that's important to remember is anything that you're defining as a primary key cannot accept null values. So the one line listed here as a primary key wouldn't be able to accept null values for this example. As previously defined in the example, I said that shoe Id was a primary key. Therefore, this can never have an empty value or not any value. The other thing that's important to remember is that if you indicate that a column cannot be null, then you are going to get an error if you do not enter a value into that column when you're inserting data into it. This is just a check to make sure that in the columns that you've determined are non-nulls, values will always be present. Otherwise, you'll get an error returned. In order to get the data into the table after you've defined the table, the columns, and the data types you want to add into it, there are really two ways to do this. The first way is with the insert statement. You can say INSERT INTO Shoes, so I'm saying, I want to put this data into the shoes table. I want to put the values and after that. So in this, I have put the values into a single parentheses. I've listed them out in order. Now this works fine, however I wouldn't recommend using this first example. How this first example is going to work is that it's going to take the first value indicated and put it in the first column. The second value will go to the second column, and the third value into the third column, and so on and so forth. And it will put them in order. As I said before, it works, however it's not recommended. You have no guarantee of what data is going into which column. So it's a lot better to be a little bit more specific about this. For this, what you want to do is you want to use the same statement INSERT INTO Shoes. Before I put the values, I'm going to list the columns that I want it to insert into. Here, I'm listing it in the same order, Shoe Id, Brand, Type, Color, Price, and Description. However, this time after that, I'm also indicating the values that I want to go in that same order. This can be really beneficial if you want to insert just a few values into a column. So for this example, I could remove the first three, so I could remove the Type, Brand, and Id. I would just remove those by crossing those out. Then it also remove the first three values here. Now I'm guaranteed that the values Pink, 695.00 and then NULL are going to go into Color, Price, and Description. I will recommend using this method. It's a little bit safer because you have more control. You know exactly where the data is going and into which column. Okay, so let's stop there for now. In this video, you learned all about how to create tables using SQL. We discuss situations where that's useful thing to do. We talked about the syntax for creating a new table and went over how to store data in it. There's another thing you can do with tables, and that is to create a temporary table. We'll go over this in our next lesson.
