SQL Tutorial: Update your database as the structure changes

Key Takeaways

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 and 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 organization's 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 University 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 organisation's table if you just used insert in to select without the distinct keyword duplicate records would be copied over as well and the following exercises you'll 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 then follows the values keyword and the actual values you want to insert before you start migrating the table you need to fix some stuff in the last lesson I created the affiliations table for you unfortunately I made a mistake in this process can you spot it the way the organization column is spelled is not consistent with the American style spelling of this table using an S instead of a set in the first exercise after the video you'll correct this with the known alter table syntax you do this with the rename column command by specifying the old column name first and then a new column name that is renamed column all name to new name also the university short name column is not even needed here so I want you to delete it tax for this is again very simple you use a drop column command followed by the name of the column dropping columns is straightforward when the tables are still empty so it's not too late to fix this error but why is it an error in the first place well I queried the university professors table and saw that there are 551 unique combinations of first names last names and associated universities I then queried the table again and lonely looked for unique combinations of first and last names turns out this is also 551 records this means that the columns first name and last name uniquely identify a professor so the university short name column is not needed in order to reference a professor in the affiliations table you can remove it and this will reduce the redundancy in your database again in other words the columns first name last name function and organization are enough to store the affiliation a professor has with a certain organization time to prepare the database for data migration after this you'll