Implement a Relational Database with SQL

You have a database and all of its structure in place, now you need data to make it useful. You have a couple of options to get the tables populated: direct data entry or the use of scripts.

Populate Tables Through Direct Table Entry

Similar to entering data into a spreadsheet, you would enter the data into the appropriate fields based on data types. This method can be a bit more error prone since the user is typing from memory, and they need to be certain they are entering into the correct fields. This is similar for adding a new record or editing an existing one. Updates are automatically saved once you click in a different row on the table. You can see the primary key autonumber generated fields being populated as you click off the row. Note that you are not allowed to enter data into an identity or autonumber generated column.

This won’t be a problem as long as the data is in the same order as the table in the DBMS system. Be sure to add an extra column if you have an identity column, date timestamp, or any other field that is auto-populated by the database. The database needs the correct number of columns, and it does not matter if you have any meaningful data in those corresponding fields since the clipboard will not paste any data into the auto-populated field.

To do this, you would right-click on the row that you want to delete, select delete, and answer yes to the popup alerting you to the row deletion. One or multiple rows can be deleted following the same steps.

Let's have a look at this approach in action:

Populate Tables Through the Use of Scripts

It's recommended that you use scripts to populate the tables for any database. This reduces human error and may save hours of manual entry labor. The data must be in the same order as the fields listed in the insert statement, and all fields requiring data must be present. Auto-populated fields can be omitted since the database will handle that operation for you.

Scripts are very portable and a quick way to restore database objects that have been dropped, or run a test on a possible production rollout. You could test database changes on a backup copy of your customer’s data and work out any bugs or issues before sending them the final scripts or executing them yourself. This can save debugging time in the customer’s production environment along with downtime while you search for the cause of any errors.

Let's have a look at this approach in action:

Before we move on, let's also practice using a pre-populated script:

Manage Your Tables With INSERT, DELETE, and ALTER

Once your tables, table data, and other structures are in place, it often becomes necessary to make changes to table attributes - sometimes inserting new columns, other times making adjustments to the data type or removing an attribute altogether. Insert statements allow you to insert new records into the table, provided the fields being inserted are of the correct number and data type. In the statement, we would have three fields listed and three values, which all appear to be text data. 

For example:

INSERT INTO Students (LastName, MiddleInitial, FirstName)
VALUES (‘Port’, ‘N’, ‘Dave’)

You would use the delete statement to remove any records you don’t need anymore. It would be a good idea to use filters and where clauses to avoid deleting more records than you need. Drop (similar to delete) is used to delete tables from your database. Use this only if you are very sure you no longer need the table and related data!

For example:

DELETE FROM Students
WHERE LastName = Smith

The alter statement is used to update your table as well as add or remove columns from your database. The following would alter your table and insert a new column:

ALTER TABLE Student
ADD Email varchar(250)

You can just as easily remove a column with a statement similar to: ALTER TABLE Student DROP COLUMN Email.

Let’s Recap!

• You can populate tables by hand, by copy and paste, or with scripts.

• Scripts are the preferred method for data entry, as they reduce input typos.

• Insert allows you to insert new records into the table.

• Delete permanently deletes data from your tables.

• Alter is an SQL keyword that allows you to make changes to your table structure.

With our tables populated, we have a functioning database! Let's recap everything to make sure you haven't missed a step.