Store your data

1. Store your data

In the last two chapters, you've learned how to find the data you want and process it to get the results you need. In this chapter, you will learn how to store and manage these results as new tables in your database.

2. Three ways to store your data

In SQL there are fundamentally three ways in which you can store your data. You can create a table using new data, or create a table or a view using data that you've gathered via a query.

3. Create a TABLE using new data

Let's begin with the first case, creating a table using new data. Imagine that you wanted perform an analysis to understand the relationship between how far away people live and how often they rent from your store. If you take a peek at the columns you have in your address table you'll see that you don't have any information about your how far away your customers live. To perform this analysis you would need to add a table that contains this information.

4. Create a TABLE using new data

If you are interested in using zipcode-based data, your new data would likely look like this.

5. Create a TABLE using new data

To populate this table you will need to take two steps: First, you'll need to create an empty table and define the columns that will exist with in it like so. Notice that when a column is created its data type must be specified. In this case we define the postal code column to be an integer and the distance column to be a float. Once the table is created you need to INSERT new data into it. The INSERT INTO command requires that you first specify the table where the data should be added, then the columns you plan to populate and finally the row values, surrounded by parenthesis that you plan to add.

6. Create a TABLE using existing data

The process is much simpler when the data you need already exists in your database. In this example you want to store the list of family friendly films. So you query your database for a list of film ids and titles where the films are rated G. To save the data from this query in a table you simply need to add the above line like so.

7. Create a TABLE using existing data

You should notice two things in this command. First, you did not need to specify the data types for your new table, this is because the data types are taken directly from the original columns queried. Second, this step is very similar to the VIEW you created in an earlier chapter.

8. Create a VIEW using existing data

And just like in the previous chapter, you can store your query as a view. So what is the difference between creating a TABLE and creating a VIEW.

9. TABLE vs VIEW

The way I like to think of it is that a TABLE stores data while a VIEW stores a query. When you create a table using existing data, you create a snapshot of this data, meaning that if any of the underlying existing data changes your new table will remain the same. By contrast, since a VIEW stores the query, working with the view will always allow you to retrieve the most recent data from the tables it uses. From a different perspective, since a new table is a new entry in your database, you can work with it like any other table, which includes modification of the data. To modify data in a VIEW would require a much more involved process of updating the tables the VIEW depends on.

10. Time to store your data.

Now that you've learned about the different ways in which you can store your data its time to practice.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.