SQL Tutorial: Update your database as the structure changes

Want to learn more? Take the full course at https://learn.datacamp.com/courses/introduction-to-relational-databases-in-sql at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work. --- Well done so far. You now have a database consisting of five different tables. Now it's time to migrate the data. Here's the current entity-relationship diagram, showing the five tables. At this moment, only the "university_professors" table holds data. The other four, shown in red, are still empty. In the remainder of this chapter, you will migrate data from the green part of this diagram to the red part, moving the respective entity types to their appropriate tables. In the end, you'll be able to delete the "university_professors" table. One advantage of splitting up "university_professors" into several tables is the reduced redundancy. As of now, "university_professors" holds 1377 entries. However, there are only 1287 distinct organizations, as this query shows. Therefore, you only need to store 1287 distinct organizations in the new "organizations" table. In order to copy data from an existing table to a new one, you can use the "INSERT INTO SELECT DISTINCT" pattern. After "INSERT INTO", you specify the name of the target table – "organizations" in this case. Then you select the columns that should be copied over from the source table – "unviversity_professors" in this case. You use the "DISTINCT" keyword to only copy over distinct organizations. As the output shows, only 1287 records are inserted into the "organizations" table. If you just used "INSERT INTO SELECT", without the "DISTINCT" keyword, duplicate records would be copied over as well. In the following exercises, you will migrate your data to the four new tables. By the way, this is the normal use case for "INSERT INTO" – where you insert values manually. "INSERT INTO" is followed by the table name and an optional list of columns which should be filled with data.