6.1Creating, Populating, and Maintaining a Database
In order for us to query a database, there must be data in the database. Also, sometimes existing data needs to be changed because of changes in the real world or because of data entry errors. Finally, it's sometimes necessary to delete data. In this Appendix we show you how you can populate and maintain a database.
SQL provides three operations— Insert, Update, Delete—for adding, changing, and deleting data in the database. Each of these operations can affect only one table at a time. There are no updates to joins of tables. Bear this in mind as we discuss each of these three operations.
Copy to New Table
In addition to the standard Insert and Update (discussed below), one powerful technique to load data into a database is by extracting the data from an existing table, creating a new table, and loading the data into the new table. The SQL language includes a statement that combines these three actions into one statement. (1) A new table can be created based on the chosen columns from an existing table. (2) Data is extracted from the existing table. (3) The rows of data extracted from the existing table are inserted into the newly created table.
Normally when we use the Select statement, we just want to view the data on the screen, or sometimes to print it out to a report. However, this technique can be used to make a complete copy of a table, or to create a new table with only certain columns and populate it with selected data. The syntax of this statement is:
SELECT [* or (list of column names)] INTO new-table-name FROM table-name [(where clause)]
This statement works just like the previous select statements you have already learned. It can SELECT from a single table or from joined tables. The brackets indicate optional clauses. The only difference is that it inserts the results into a new table. As noted by the syntax, you can either select all the columns from the existing table, or you can select only a few columns. The data type information is also pulled over from the selected columns. You will use this statement to create some test data in your own private database. Once you have some data in your own database, you can try the insert, update, and delete statements that are taught next.
The "CREATE TABLE new-table-name" clause creates a new table. It will use the column names and data types from the column information in the SELECT clause. If you want to change column names in the new table, you can add a list of columns like this "CREATE TABLE new-table-name [(list of column names)]". The brackets indicate optional clauses. The SELECT clause can be a simple, single table select, or a more complex query with joined tables. You can use this statement to create some test data in your own private database. Once you have some data in your own database, you can try the insert, update, and delete statements that are taught next.
There is one additional SQL statement that will be useful for you. If you execute the statement to create a new table, but decide that you want to delete the newly created table, you can use the DROP statement. The DROP statement is used to delete a table. Obviously you should be very very careful when using this statement. A dropped table is gone forever. The syntax is simply:
DROP TABLE table-name
When using Select statements, since you are only viewing the data, you may run and rerun the queries as many times as desired. And if you mess it up, you only get an error. However, when working with change statements, you have to be careful because you can really mess up the database. And there is no UNDO statement in SQL. You could end up with undesired modifications and no way to recover the lost or modified data. You should always double check your work, and consider ways to recover should the update query not do what you expect.
Another caveat is that you should not rerun your update queries. Sometimes rerunning your update queries might give you an error message. (Such as when you try to add a record with the same unique key that was already added.) Or you might make the change multiple times. (Such as when you increase a price by 10%. Running it multiple times would give you a compounded rate increase.)
The Insert operation has two versions. The first is used to enter a single row into a table. The second versions inserts data from a separate table into the first table.
Insert - Listed Values Version
The general form of the first version is:
INSERT INTO table-name [(list of column-names)] VALUES (list of values)
The square brackets ([ and ]) are not part of the statement; they merely indicate optional entries. That is, the list of column-names is optional, but if it's included, it must be enclosed in parentheses. Likewise, the list of values is enclosed in parentheses. If there is no list of columns, then the system will enter the values into the new row from left to right, starting with the first column. Here are some examples:
Insert into Customer (CustomerID, FirstName, LastName, State)
Values (500, 'John', 'Smith', 'NM')
This statement will add a new row to the Customer table. Its CustomerID will be 500, and the first and last names will be John Smith, respectively. The State will be New Mexico, shown in the abbreviated two character form. All other column values, being omitted, will be null.
A null value in a database column is merely a statement that we don't know what the value should be, or that no value applies to that particular column for this particular record. Examples of this are in the Manufacturer table where we have an Address 2 column. Most manufacturers don't have a value for this column, so it doesn't apply. Therefore, this column is null for those manufacturers. In the case of the customer, John Smith, whom we just added to the Customer table, the street address, city, and other data that we didn't enter probably exist, but we don't yet know what they are. Because we don't know, we don't enter them, and the database places null values in these columns. Null values are not 0 nor are they blank. They're "null." "Null" is a special database term, and a null value is simply a flag that tells us "unknown" or "not applicable."
We could also write this Insert statement as follows, and the result would be the same:
Insert into Customer (CustomerID, FirstName, LastName, StreetAddress, City, State, PostalCode, Country, Phone) Values (500, 'John', 'Smith', null, null, 'NM', null, null, null)
We can also omit the column list. But if we do, then we must state the null values in the value list:
Insert into Customer Values (500, 'John', 'Smith', null, null, 'NM', null, 'USA', null)
If we put null in quotes ('null'), then we would be entering a string of length four, and this would be considered an actual value instead of a null.
Let's practice using the INSERT statement. We will do this in two steps. First, we will extract some data from the Redcatd Customer table and put it into your own private database. Then we will execute the insert statement to add a new customer. We will look at the results after each step.
DROP TABLE MyCustomer; SELECT * INTO MyCustomer FROM redcatd.Customer WHERE CustomerID < 100; SELECT * FROM MyCustomer;
Notice that in this query box we have three SQL statements, and each statement is terminated with a semi-colon. The DROP statement will allow you to run this query multiple times. The first time you execute it, you will get an error message, because there is no table to drop. But each subsequent time you run it, it will first drop the existing MyCustomer table, and then recreate it. We also only extracted a few records so we can play with this table and see the results.
Now let's practice the INSERT statement.
Insert Into MyCustomer( CustomerID, FirstName, LastName, StreetAddress, City, State, PostalCode, Country) Values (500, 'John', 'Smith', null, null, 'NM', null, null); Select * From MyCustomer;
We see the newly added record. Again, if you run these statements multiple times. you will continue to add John Smith multiple times. This can be controlled by setting up unique key constraints. However, this topic is beyond the scope of this book. We cover it in Database Management and Design.
As a learning exercise for yourself, left click in the above code box and modify the code. Remove the list of column names and run the query using only the Values clause. Then rerun the query. You should see an additional John Smith record, or you can change the data and insert your own record.
Insert - Subquery Version
The second form of the Insert statement includes a query. We explain with an example. Suppose we want to archive our sale data by creating an archive table named SaleArchive. It would have identical structure to the Sale table, but would be used to store records of sales from the more-or-less distant past. To accomplish this, we would write an Insert statement as follows:
Insert into SaleArchive
Where SaleDate < '2014-01-01'
This statement would first perform the query indicated. It would then take its result set (consisting of every Sale record whose date was prior to the year 2014) and insert it into the SaleArchive table. The SaleArchive table must already exist, and in fact, may already have data in it. The SaleArchive table will now consist of all the Sale records previously inserted as well as the ones now inserted.
The general form for this SQL statement is:
Insert into table-name (select statement)
Note that to use this type of insert statement that the data must be consistent in both tables. In other words, the number of columns, the order of the columns, and the data types of the columns must be the same for both tables.
SELECT...INTO creates a new table from the schema of the FROM table. INSERT...INTO requires that the target table already exist.
The SQL Update statement is used to change the value of one or more columns in one or more records of a specified table. The generalized syntax is:
For simplicity, suppose customer Raquel Lopez got married and changed her last name to West. Then this statement would make the necessary change:
Set LastName = 'West'
Where FirstName = 'Raquel' and LastName = 'Lopez'
Oops! You're exactly right. If more than one is named Raquel Lopez, then all of them would now be named Raquel West in our Customer table. Obviously, we don't want that. So we have to be more precise. Instead of using her first and last name as the identifying characteristic, we use her CustomerID. Suppose Raquel's CustomerID is 30. Then this statement will work, because CustomerID uniquely identifies a customer record:
Set LastName = 'West'
Where CustomerID = 30
Let's try it.
UPDATE MyCustomer SET LastName = 'West' WHERE CustomerId = 30; SELECT * FROM MyCustomer;
As a practice exercise, why don't you update John Smith's record - the one you just added. Update his record and add a StreetAddress, a City, a PostalCode, a Country, and a Phone Number. Try it. You can do it with multiple statements, or all in one statement.
However, our mistake points out that you can change the value of more than one record in the table with a single Update statement. So, suppose we want to reduce the ListPrice of all black shoes by 5%. Then we could do so with:
Set ListPrice = ListPrice * .95
Where Color = 'Black'
If we reran the SQL statement to update Raquel's last name, that would work. It would just keep changing her last name to West. But what would happen if you reran the update to the Product table? Every time you reran it, the prices would be reduced by 5%. We repeat the potential harm that change statements can cause if not executed carefully.
We remove records with the Delete statement. Note the "from" keyword which is enclosed in brackets is optional.
DELETE [from] table-name
WHERE some_column = some_value
After we archive our Sale records with the multi-row Insert statement we showed you above, we may want to remove the newly archived records from the Sale table since they're already backed up in another table, and we don't want the Sale table to have an unwieldy size.
Delete From Sale
Where SaleDate < '2014-01-01'
Notice that we want the Where clause in this statement to be identical to the Where clause in the query of the Insert statement we used for archiving the Sale records. That way we delete exactly those records we had previously archived.
Obviously, we can delete an individual record as well by simply using the record's key in the Where clause. We would do this, for example, if we wanted to remove from the database a Manufacturer which has gone out of business.
Delete From Manufacturer
Where ManufacturerID = 105
Let's practice the delete statement by deleting John Smith's record that we inserted earlier. In fact, if we reran that insert multiple times, this statement will delete them all.
DELETE FROM MyCustomer WHERE CustomerID = 500; SELECT * FROM MyCustomer;
We should note that if the Where clause is omitted from an Update or Delete statement, then every row in the named table will be updated or deleted respectively. Therefore, it is absolutely essential that you include a Where clause in these statements unless you intend to update or delete all the records.
We emphasize this point. There is no UNDO function with database updates. When you update or delete data in a database, it is changed and the previous information is lost. You should always include a WHERE clause in an update or delete statement unless you are absolutely sure you want to affect every row. Of course, you should also be sure that the WHERE criteria are absolutely correct!