ch08

Teach Yourself SQL in 21 Days, Second Edition







- Day 8 -
Manipulating Data

Objectives
Today we discuss data manipulation. By the end of the day, you should understand:


How to manipulate data using the INSERT, UPDATE, and DELETE
commands

The importance of using the WHERE clause when you are manipulating data

The basics of importing and exporting data from foreign data sources


Introduction to Data Manipulation Statements
Up to this point you have learned how to retrieve data from a database using every
selection criterion imaginable. After this data is retrieved, you can use it in an
application program or edit it. Week 1 focused on retrieving data. However, you may
have wondered how to enter data into the database in the first place. You may also
be wondering what to do with data that has been edited. Today we discuss three SQL
statements that enable you to manipulate the data within a database's table. The
three statements are as follows:


The INSERT statement

The UPDATE statement

The DELETE statement


You may have used a PC-based product such as Access, dBASE IV, or FoxPro to enter
your data in the past. These products come packaged with excellent tools to enter,
edit, and delete records from databases. One reason that SQL provides data manipulation
statements is that it is primarily used within application programs that enable the
user to edit the data using the application's own tools. The SQL programmer needs
to be able to return the data to the database using SQL. In addition, most large-scale
database systems are not designed with the database designer or programmer in mind.
Because these systems are designed to be used in high-volume, multiuser environments,
the primary design emphasis is placed on the query optimizer and data retrieval engines.
Most commercial relational database systems also provide tools for importing and
exporting data. This data is traditionally stored in a delimited text file format.
Often a format file is stored that contains information about the table being imported.
Tools such as Oracle's SQL*Loader, SQL Server's bcp (bulk copy), and Microsoft Access
Import/Export are covered at the end of the day.





NOTE: Today's examples were generated
with Personal Oracle7. Please note the minor differences in the appearance of commands
and the way data is displayed in the various implementations.





The INSERT Statement
The INSERT statement enables you to enter data into the database. It
can be broken down into two statements:
INSERT...VALUES

and
INSERT...SELECT

The INSERT...VALUES Statement
The INSERT...VALUES statement enters data into a table one record at
a time. It is useful for small operations that deal with just a few records. The
syntax of this statement is as follows:
SYNTAX:
INSERT INTO table_name
(col1, col2...)
VALUES(value1, value2...)

The basic format of the INSERT...VALUES statement adds a record to a
table using the columns you give it and the corresponding values you instruct it
to add. You must follow three rules when inserting data into a table with the INSERT...VALUES
statement:


The values used must be the same data type as the fields they are being added
to.

The data's size must be within the column's size. For instance, you cannot add
an 80-character string to a 40-character column.

The data's location in the VALUES list must correspond to the location
in the column list of the column it is being added to. (That is, the first value
must be entered into the first column, the second value into the second column, and
so on.)


Example 8.1
Assume you have a COLLECTION table that lists all the important stuff
you have collected. You can display the table's contents by writing
INPUT:
SQL> SELECT * FROM COLLECTION;

which would yield this:
OUTPUT:
ITEM WORTH REMARKS
-------------------- --------- ----------------------------
NBA ALL STAR CARDS 300 SOME STILL IN BIKE SPOKES
MALIBU BARBIE 150 TAN NEEDS WORK
STAR WARS GLASS 5.5 HANDLE CHIPPED
LOCK OF SPOUSES HAIR 1 HASN'T NOTICED BALD SPOT YET

If you wanted to add a new record to this table, you would write
INPUTOUTPUT:
SQL> INSERT INTO COLLECTION
2 (ITEM, WORTH, REMARKS)
3 VALUES('SUPERMANS CAPE', 250.00, 'TUGGED ON IT');

1 row created.

You can execute a simple SELECT statement to verify the insertion:
INPUT/OUTPUT:
SQL> SELECT * FROM COLLECTION;

ITEM WORTH REMARKS
-------------------- --------- ----------------------------
NBA ALL STAR CARDS 300 SOME STILL IN BIKE SPOKES
MALIBU BARBIE 150 TAN NEEDS WORK
STAR WARS GLASS 5.5 HANDLE CHIPPED
LOCK OF SPOUSES HAIR 1 HASN'T NOTICED BALD SPOT YET
SUPERMANS CAPE 250 TUGGED ON IT

ANALYSIS:
The INSERT statement does not require column names. If the column names
are not entered, SQL lines up the values with their corresponding column numbers.
In other words, SQL inserts the first value into the first column, the second value
into the second column, and so on.
Example 8.2
The following statement inserts the values from Example 8.1 into the table:
INPUT:
SQL> INSERT INTO COLLECTION VALUES
2 ('STRING',1000.00,'SOME DAY IT WILL BE VALUABLE');

1 row created.

ANALYSIS:
By issuing the same SELECT statement as you did in Example 8.1, you can
verify that the insertion worked as expected:
INPUT:
SQL> SELECT * FROM COLLECTION;
OUTPUT:
ITEM WORTH REMARKS
-------------------- --------- ----------------------------
NBA ALL STAR CARDS 300 SOME STILL IN BIKE SPOKES
MALIBU BARBIE 150 TAN NEEDS WORK
STAR WARS GLASS 5.5 HANDLE CHIPPED
LOCK OF SPOUSES HAIR 1 HASN'T NOTICED BALD SPOT YET
SUPERMANS CAPE 250 TUGGED ON IT
STRING 1000 SOME DAY IT WILL BE VALUABLE

6 rows selected.

Inserting NULL Values
On Day 9, "Creating and Maintaining Tables," you learn how to create
tables using the SQL CREATE TABLE statement. For now, all you need to know
is that when a column is created, it can have several different limitations placed
upon it. One of these limitations is that the column should (or should not) be allowed
to contain NULL values. A NULL value means that the value is empty.
It is neither a zero, in the case of an integer, nor a space, in the case of a string.
Instead, no data at all exists for that record's column. If a column is defined as
NOT NULL (that column is not allowed to contain a NULL value),
you must insert a value for that column when using the INSERT statement.
The INSERT is canceled if this rule is broken, and you should receive a
descriptive error message concerning your error.





WARNING: You could insert spaces for a
null column, but these spaces will be treated as a value. NULL simply means
nothing is there.





INPUT:
SQL> insert into collection values
2 ('SPORES MILDEW FUNGUS', 50.00, ' ');
OUTPUT:
1 row inserted.

ANALYSIS:
Using '' instead of NULL inserted a space in the collection
table. You then can select the space.
INPUT/OUTPUT:
SQL> select * from collection
2 where remarks = ' ';

ITEM WORTH REMARKS
--------------------------- -------- ---------
SPORES MILDEW FUNGUS 50.00

1 row selected.

ANALYSIS:
The resulting answer comes back as if a NULL is there. With the output
of character fields, it is impossible to tell the difference between a null value
and a mere space.
Assume the column REMARKS in the preceding table has been defined as
NOT NULL. Typing
INPUT/OUTPUT:
SQL> INSERT INTO COLLECTION
2 VALUES('SPORES MILDEW FUNGUS',50.00,NULL);

produces the following error:
INSERT INTO COLLECTION
*
ERROR at line 1:
ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert






NOTE: Notice the syntax. Number data types
do not require quotes; NULL does not require quotes; character data types
do require quotes.





Inserting Unique Values
Many database management systems also allow you to create a UNIQUE column
attribute. This attribute means that within the current table, the values within
this column must be completely unique and cannot appear more than once. This limitation
can cause problems when inserting or updating values into an existing table, as the
following exchange demonstrates:
INPUT:
SQL> INSERT INTO COLLECTION VALUES('STRING', 50, 'MORE STRING');
OUTPUT:
INSERT INTO COLLECTION VALUES('STRING', 50, 'MORE STRING')
*
ERROR at line 1:
ORA-00001: unique constraint (PERKINS.UNQ_COLLECTION_ITEM) violated

ANALYSIS:
In this example you tried to insert another ITEM called STRING
into the COLLECTION table. Because this table was created with ITEM
as a unique value, it returned the appropriate error. ANSI SQL does not offer a solution
to this problem, but several commercial implementations include extensions that would
allow you to use something like the following:
IF NOT EXISTS (SELECT * FROM COLLECTION WHERE NAME = 'STRING'

INSERT INTO COLLECTION VALUES('STRING', 50, 'MORE STRING')

This particular example is supported in the Sybase system.
A properly normalized table should have a unique, or key, field. This field is
useful for joining data between tables, and it often improves the speed of your queries
when using indexes. (See Day 10, "Creating Views and Indexes.")





NOTE: Here's an INSERT statement
that inserts a new employee into a table:
SQL> insert into employee_tbl values
('300500177', 'SMITHH', 'JOHN');

1 row inserted.
After hitting Enter, you noticed that you misspelled SMITH. Not to fret!
All you have to do is issue the ROLLBACK command, and the row will not be
inserted. See Day 11, "Controlling Transactions," for more on the ROLLBACK
command.





The INSERT...SELECT Statement
The INSERT...VALUES statement is useful when adding single records to
a database table, but it obviously has limitations. Would you like to use it to add
25,000 records to a table? In situations like this, the INSERT...SELECT
statement is much more beneficial. It enables the programmer to copy information
from a table or group of tables into another table. You will want to use this statement
in several situations. Lookup tables are often created for performance gains. Lookup
tables can contain data that is spread out across multiple tables in multiple databases.
Because multiple-table joins are slower to process than simple queries, it is much
quicker to execute a SELECT query against a lookup table than to execute
a long, complicated joined query. Lookup tables are often stored on the client machines
in client/server environments to reduce network traffic.
Many database systems also support temporary tables. (See Day 14, "Dynamic
Uses of SQL.") Temporary tables exist for the life of your database connection
and are deleted when your connection is terminated. The INSERT...SELECT
statement can take the output of a SELECT statement and insert these values
into a temporary table.
Here is an example:
INPUT:
SQL> insert into tmp_tbl
2 select * from table;
OUTPUT:
19,999 rows inserted.

ANALYSIS:
You are selecting all the rows that are in table and inserting them into
tmp_tbl.





NOTE: Not all database management systems
support temporary tables. Check the documentation for the specific system you are
using to determine if this feature is supported. Also, see Day 14 for a more detailed
treatment of this topic.





The syntax of the INSERT...SELECT statement is as follows:
SYNTAX:
INSERT INTO table_name
(col1, col2...)
SELECT col1, col2...
FROM tablename
WHERE search_condition

Essentially, the output of a standard SELECT query is then input into
a database table. The same rules that applied to the INSERT...VALUES statement
apply to the INSERT...SELECT statement. To copy the contents of the COLLECTION
table into a new table called INVENTORY, execute the set of statements in
Example 8.3.
Example 8.3
This example creates the new table INVENTORY.
INPUT:
SQL> CREATE TABLE INVENTORY
2 (ITEM CHAR(20),
3 COST NUMBER,
4 ROOM CHAR(20),
5 REMARKS CHAR(40));
OUTPUT:
Table created.

The following INSERT fills the new INVENTORY table with data
from COLLECTION.
INPUT/OUTPUT:
SQL> INSERT INTO INVENTORY (ITEM, COST, REMARKS)
2 SELECT ITEM, WORTH, REMARKS
3 FROM COLLECTION;

6 rows created.

You can verify that the INSERT works with this SELECT statement:
INPUT/OUTPUT:
SQL> SELECT * FROM INVENTORY;

ITEM COST ROOM REMARKS
-------------------- --------- -------- ----------------------------
NBA ALL STAR CARDS 300 SOME STILL IN BIKE SPOKES
MALIBU BARBIE 150 TAN NEEDS WORK
STAR WARS GLASS 5.5 HANDLE CHIPPED
LOCK OF SPOUSES HAIR 1 HASN'T NOTICED BALD SPOT YET
SUPERMANS CAPE 250 TUGGED ON IT
STRING 1000 SOME DAY IT WILL BE VALUABLE

6 rows selected.






NOTE: The data appears to be in the table;
however, the transaction is not finalized until a COMMIT is issued. The
transaction can be committed either by issuing the COMMIT command or by
simply exiting. See Day 11 for more on the COMMIT command.





ANALYSIS:
You have successfully, and somewhat painlessly, moved the data from the COLLECTION
table to the new INVENTORY table!
The INSERT...SELECT statement requires you to follow several new rules:


The SELECT statement cannot select rows from the table that is being
inserted into.

The number of columns in the INSERT INTO statement must equal the number
of columns returned from the SELECT statement.

The data types of the columns in the INSERT INTO statement must be the
same as the data types of the columns returned from the SELECT statement.


Another use of the INSERT...SELECT statement is to back up a table that
you are going to drop, truncate for repopulation, or rebuild. The process requires
you to create a temporary table and insert data that is contained in your original
table into the temporary table by selecting everything from the original table. For
example:
SQL> insert into copy_table
2 select * from original_table;

Now you can make changes to the original table with a clear conscience.





NOTE: Later today you learn how to input
data into a table using data from another database format. Nearly all businesses
use a variety of database formats to store data for their organizations. The applications
programmer is often expected to convert these formats, and you will learn some common
methods for doing just that.





The UPDATE Statement
The purpose of the UPDATE statement is to change the values of existing
records. The syntax is
SYNTAX:
UPDATE table_name
SET columnname1 = value1
[, columname2 = value2]...
WHERE search_condition

This statement checks the WHERE clause first. For all records in the
given table in which the WHERE clause evaluates to TRUE, the corresponding
value is updated.
Example 8.4
This example illustrates the use of the UPDATE statement:
INPUT:
SQL> UPDATE COLLECTION
2 SET WORTH = 900
3 WHERE ITEM = 'STRING';
OUTPUT:
1 row updated.

To confirm the change, the query
INPUT/OUTPUT:
SQL> SELECT * FROM COLLECTION
2 WHERE ITEM = 'STRING';

yields
ITEM WORTH REMARKS
-------------------- --------- ------------------------------

STRING 900 SOME DAY IT WILL BE VALUABLE

Here is a multiple-column update:
INPUT/OUTPUT:
SQL> update collection
2 set worth = 900, item = ball
3 where item = 'STRING';

1 row updated.






NOTE: Your implementation might use a
different syntax for multiple-row updates.




NOTE: Notice in the set that 900
does not have quotes, because it is a numeric data type. On the other hand, String
is a character data type, which requires the quotes.





Example 8.5
If the WHERE clause is omitted, every record in the COLLECTION
table is updated with the value given.
INPUT/OUTPUT:
SQL> UPDATE COLLECTION
2 SET WORTH = 555;

6 rows updated.

Performing a SELECT query shows that every record in the database was
updated with that value:
INPUT/OUTPUT:
SQL> SELECT * FROM COLLECTION;

ITEM WORTH REMARKS
-------------------- --------- ------------------------------
NBA ALL STAR CARDS 555 SOME STILL IN BIKE SPOKES
MALIBU BARBIE 555 TAN NEEDS WORK
STAR WARS GLASS 555 HANDLE CHIPPED
LOCK OF SPOUSES HAIR 555 HASN'T NOTICED BALD SPOT YET
SUPERMANS CAPE 555 TUGGED ON IT
STRING 555 SOME DAY IT WILL BE VALUABLE

6 rows selected.

You, of course, should check whether the column you are updating allows unique
values only.





WARNING: If you omit the WHERE
clause from the UPDATE statement, all records in the given table are updated.






Some database systems provide an extension to the standard UPDATE syntax.
SQL Server's Transact-SQL language, for instance, enables programmers to update the
contents of a table based on the contents of several other tables by using a FROM
clause. The extended syntax looks like this:
SYNTAX:
UPDATE table_name
SET columnname1 = value1
[, columname2 = value2]...
FROM table_list
WHERE search_condition

Example 8.6
Here's an example of the extension:
INPUT:
SQL> UPDATE COLLECTION
2 SET WORTH = WORTH * 0.005;

that changes the table to this:
INPUT/OUTPUT:
SQL> SELECT * FROM COLLECTION;

ITEM WORTH REMARKS
-------------------- -------- ----------------------------
NBA ALL STAR CARDS 2.775 SOME STILL IN BIKE SPOKES
MALIBU BARBIE 2.775 TAN NEEDS WORK
STAR WARS GLASS 2.775 HANDLE CHIPPED
LOCK OF SPOUSES HAIR 2.775 HASN'T NOTICED BALD SPOT YET
SUPERMANS CAPE 2.775 TUGGED ON IT
STRING 2.775 SOME DAY IT WILL BE VALUABLE

6 rows selected.

ANALYSIS:
This syntax is useful when the contents of one table need to be updated following
the manipulation of the contents of several other tables. Keep in mind that this
syntax is nonstandard and that you need to consult the documentation for your particular
database management system before you use it.
The UPDATE statement can also update columns based on the result of an
arithmetic expression. When using this technique, remember the requirement that the
data type of the result of the expression must be the same as the data type of the
field that is being modified. Also, the size of the value must fit within the size
of the field that is being modified.
Two problems can result from the use of calculated values: truncation and overflow.
Truncation results when the database system converts a fractional number to an
integer, for instance. Overflow results when the resulting value is larger
than the capacity of the modified column, which will cause an error to be returned
by your database system.





NOTE: Some database systems handle the
overflow problem for you. Oracle7 converts the number to exponential notation and
presents the number that way. You should keep this potential error in mind when using
number data types.




TIP: If you update a column(s) and notice
an error after you run the update, issue the ROLLBACK command (as you would
for an incorrect insert) to void the update. See Day 11 for more on the ROLLBACK
command.





The DELETE Statement
In addition to adding data to a database, you will also need to delete data from
a database. The syntax for the DELETE statement is
SYNTAX:
DELETE FROM tablename
WHERE condition

The first thing you will probably notice about the DELETE command is
that it doesn't have a prompt. Users are accustomed to being prompted for assurance
when, for instance, a directory or file is deleted at the operating system level.
Are you sure? (Y/N) is a common question asked before the operation is performed.
Using SQL, when you instruct the DBMS to delete a group of records from a table,
it obeys your command without asking. That is, when you tell SQL to delete a group
of records, it will really do it!
On Day 11 you will learn about transaction control. Transactions are database
operations that enable programmers to either COMMIT or ROLLBACK
changes to the database. These operations are very useful in online transaction-processing
applications in which you want to execute a batch of modifications to the database
in one logical execution. Data integrity problems will occur if operations are performed
while other users are modifying the data at the same time. For now, assume that no
transactions are being undertaken.





NOTE: Some implementations, for example,
Oracle, automatically issue a COMMIT command when you exit SQL.





Depending on the use of the DELETE statement's WHERE clause,
SQL can do the following:


Delete single rows

Delete multiple rows

Delete all rows

Delete no rows


Here are several points to remember when using the DELETE statement:


The DELETE statement cannot delete an individual field's values (use
UPDATE instead). The DELETE statement deletes entire records from
a single table.

Like INSERT and UPDATE, deleting records from one table can
cause referential integrity problems within other tables. Keep this potential problem
area in mind when modifying data within a database.

Using the DELETE statement deletes only records, not the table itself.
Use the DROP TABLE statement (see Day 9) to remove an entire table.


Example 8.7
This example shows you how to delete all the records from COLLECTION
where WORTH is less than 275.
INPUT:
SQL> DELETE FROM COLLECTION
2 WHERE WORTH < 275;

4 rows deleted.

The result is a table that looks like this:
INPUT/OUTPUT:
SQL> SELECT * FROM COLLECTION;

ITEM WORTH REMARKS
-------------------- --------- ------------------------------
NBA ALL STAR CARDS 300 SOME STILL IN BIKE SPOKES

STRING 1000 SOME DAY IT WILL BE VALUABLE






WARNING: Like the UPDATE statement,
if you omit a WHERE clause from the DELETE statement, all rows
in that particular table will be deleted.





Example 8.8 uses all three data manipulation statements to perform a set of database
operations.
Example 8.8
This example inserts some new rows into the COLLECTION table you used
earlier today.
INPUT:
SQL> INSERT INTO COLLECTION
2 VALUES('CHIA PET', 5,'WEDDING GIFT');
OUTPUT:
1 row created.
INPUT:
SQL> INSERT INTO COLLECTION
2 VALUES('TRS MODEL III', 50, 'FIRST COMPUTER');
OUTPUT:
1 row created.

Now create a new table and copy this data to it:
INPUT/OUTPUT:
SQL> CREATE TABLE TEMP
2 (NAME CHAR(20),
3 VALUE NUMBER,
4 REMARKS CHAR(40));

Table created.
INPUT/OUTPUT:
SQL> INSERT INTO TEMP(NAME, VALUE, REMARKS)
2 SELECT ITEM, WORTH, REMARKS
3 FROM COLLECTION;

4 rows created.
INPUT/OUTPUT:
SQL> SELECT * FROM TEMP;

NAME VALUE REMARKS
-------------------- --------- ------------------------------
NBA ALL STAR CARDS 300 SOME STILL IN BIKE SPOKES
STRING 1000 SOME DAY IT WILL BE VALUABLE
CHIA PET 5 WEDDING GIFT
TRS MODEL III 50 FIRST COMPUTER

Now change some values:
INPUT/OUTPUT:
SQL> UPDATE TEMP
2 SET VALUE = 100
3 WHERE NAME = 'TRS MODEL III';

1 row updated.
INPUT/OUTPUT:

SQL> UPDATE TEMP
2 SET VALUE = 8
3 WHERE NAME = 'CHIA PET';

1 row updated.
INPUT/OUTPUT:
SQL> SELECT * FROM TEMP;

NAME VALUE REMARKS
-------------------- --------- ----------------------------
NBA ALL STAR CARDS 300 SOME STILL IN BIKE SPOKES
STRING 1000 SOME DAY IT WILL BE VALUABLE
CHIA PET 8 WEDDING GIFT
TRS MODEL III 100 FIRST COMPUTER

And update these values back to the original table:
INPUT:
INSERT COLLECTION
SELECT * FROM TEMP;
DROP TABLE TEMP;

ANALYSIS:
The DROP TABLE and CREATE TABLE statements are discussed in
greater detail on Day 9. For now, these statements basically do what their names
suggest. CREATE TABLE builds a new table with the format you give it, and
DROP TABLE deletes the table. Keep in mind that DROP TABLE permanently
removes a table, whereas DELETE FROM <TableName> removes only the
records from a table.
To check what you have done, select out the records from the COLLECTION
table. You will see that the changes you made now exist in the COLLECTION
table.
INPUT/OUTPUT:
SQL> SELECT * FROM COLLECTION;

NAME VALUE REMARKS
-------------------- -------- ----------------------------
NBA ALL STAR CARDS 300 SOME STILL IN BIKE SPOKES
STRING 1000 SOME DAY IT WILL BE VALUABLE
CHIA PET 8 WEDDING GIFT
TRS MODEL III 100 FIRST COMPUTER

ANALYSIS:
The previous example used all three data manipulation commands--INSERT,
UPDATE, and DELETE--to perform a set of operations on a table.
The DELETE statement is the easiest of the three to use.





WARNING: Always keep in mind that any
modifications can affect the referential integrity of your database. Think through
all your database editing steps to make sure that you have updated all tables correctly.






Importing and Exporting Data from Foreign Sources
The INSERT, UPDATE, and DELETE statements are extremely
useful from within a database program. They are used with the SELECT statement
to provide the foundation for all other database operations you will perform. However,
SQL as a language does not have a way to import or export of data from foreign data
sources. For instance, your office may have been using a dBASE application for several
years now that has outgrown itself. Now your manager wants to convert this application
to a client/server application using the Oracle RDBMS. Unfortunately for you, these
dBASE files contain thousands of records that must be converted to an Oracle database.
Obviously, the INSERT, UPDATE, and DELETE commands will
help you after your Oracle database has been populated, but you would rather quit
than retype 300,000 records. Fortunately, Oracle and other manufacturers provide
tools that will assist you in this task.
Nearly all database systems allow you to import and export data using ASCII text
file formats. Although the SQL language does not include this feature, SQL will not
do you (or your boss) much good when you have an empty database. We will examine
the import/export tools available in the following products: Microsoft Access, Microsoft
and Sybase SQL Server, and Personal Oracle7.
Microsoft Access
Microsoft Access is a PC-only database product that contains many of the features
of a relational database management system. Access also includes powerful reporting
tools, a macro language similar to Visual Basic, and the capability to import and
export data from various database and text file formats. This section examines this
last feature, particularly the capability to export to delimited text files. Delimited
means that each field is separated, or delimited, by some special character. This
character is often a comma, a quotation mark, or a space.
Access allows you to import and export various database formats, including dBASE,
FoxPro, and SQL Database. The SQL Database option is actually an ODBC data source
connection. (Microsoft ODBC is covered on Day 13, "Advanced SQL Topics.")
For this discussion, you want to select the Export option and then choose the Text
(Fixed Width) option.
After opening an Access database (with the File | Open), select Export. A Destination
dialog box (for Exporting) is displayed. Select the Text (Fixed Width) option. This
option allows you to output your Access tables to text files in which each data type
is a fixed width. For example, a character data field of length 30 will be output
to the file as a field 30 characters long. If the field's data takes up less space
than 30 characters, it will be padded with spaces. Eventually, you will be asked
to set up the export file format. Figure 8.1 shows the Import/Export Setup dialog
box.
Figure 8.1.
The Import/Export Setup dialog box.
Notice that in this dialog box you can select the Text Delimiter and the Field
Separator for your export file. As a final step, save the specification for use later.
This specification is stored internally within the database.
Microsoft and Sybase SQL Server
Microsoft and Sybase have jointly developed a powerful database system that is
very popular in client/server application development. The name of this system is
SQL Server. Microsoft has agreed to develop versions of the RDBMS for some platforms,
and Sybase has developed its version for all the other platforms (usually the larger
ones). Although the arrangement has changed somewhat in recent years, we mention
this agreement here to help you avoid confusion when you begin examining the various
database systems available on the market today.
SQL Server provides file import/export capabilities with the bcp tool. bcp is
short for "bulk copy." The basic concept behind bcp is the same as that
behind Microsoft Access. Unfortunately, the bcp tool requires you to issue commands
from the operating system command prompt, instead of through dialog boxes or windows.
Bcp imports and exports fixed-width text files. It is possible to export a file
using the Microsoft Access method described earlier and then import that same file
directly into an SQL Server table using bcp. bcp uses format files (usually with
an .FMT extension) to store the import specification. This specification
tells bcp the column names, field widths, and field delimiters. You can run bcp from
within an SQL database build script to completely import data after the database
has been built.
Personal Oracle7
Personal Oracle7 allows you to import and export data from ASCII text files containing
delimited or fixed-length records. The tool you use is SQL*Loader. This graphical
tool uses a control file (with the .CTL extension). This file is similar
to SQL Server's format (FMT) file. The information contained in this file tells SQL*Loader
what it needs to know to load the data from the file.
The SQL*Loader dialog box appears in Figure 8.2.
Figure 8.2.
The SQL*Loader dialog box.
Summary
SQL provides three statements that you can use to manipulate data within a database.
The INSERT statement has two variations. The INSERT...VALUES
statement inserts a set of values into one record. The INSERT...SELECT statement
is used in combination with a SELECT statement to insert multiple records
into a table based on the contents of one or more tables. The SELECT statement
can join multiple tables, and the results of this join can be added to another table.
The UPDATE statement changes the values of one or more columns based
on some condition. This updated value can also be the result of an expression or
calculation.
The DELETE statement is the simplest of the three statements. It deletes
all rows from a table based on the result of an optional WHERE clause. If
the WHERE clause is omitted, all records from the table are deleted.
Modern database systems supply various tools for data manipulation. Some of these
tools enable developers to import or export data from foreign sources. This feature
is particularly useful when a database is upsized or downsized to a different system.
Microsoft Access, Microsoft and Sybase SQL Server, and Personal Oracle7 include many
options that support the migration of data between systems.
Q&A


Q Does SQL have a statement for file import/export operations?
A No. Import and export are implementation-specific operations. In other
words, the ANSI committee allows individual manufacturers to create whatever features
or enhancements they feel are necessary.
Q Can I copy data from a table into itself using the INSERT command?
I would like to make duplicate copies of all the existing records and change the
value of one field.
A No, you cannot insert data into the same table that you selected from.
However, you can select the original data into a temporary table. (True temporary
tables are discussed on Day 14.) Then modify the data in this temporary table and
select back into the original table. Make sure that you watch out for unique fields
you may have already created. A unique field means that the particular field must
contain a unique value for each row of data that exists in its table.
Q You have stressed using caution when issuing INSERT, UPDATE,
and DELETE commands, but simple fixes seem to be available to correct whatever
I did wrong. Is that a fair statement?
A Yes. For example, a simple way to fix a misspelled name is to issue a
ROLLBACK command and redo the insert. Another fix would be to do an update
to fix the name. Or you could delete the row and redo the insert with the corrected
spelling of the name.
But suppose you inserted a million rows into a table and didn't notice that you
had misspelled a name when you issued the COMMIT command. A few weeks later,
someone notices some bad data. You have had two weeks' worth of database activity.
You would more than likely have to issue individual updates to make individual corrections,
instead of making any type of global change. In most cases you probably will not
know what to change. You may have to restore the database.


Workshop
The Workshop provides quiz questions to help solidify your understanding of the
material covered, as well as exercises to provide you with experience in using what
you have learned. Try to answer the quiz and exercise questions before checking the
answers in Appendix F, "Answers to Quizzes and Exercises."
Quiz


1. What is wrong with the following statement?





DELETE COLLECTION;





2. What is wrong with the following statement?





INSERT INTO COLLECTION
SELECT * FROM TABLE_2





3. What is wrong with the following statement?





UPDATE COLLECTION ("HONUS WAGNER CARD",
25000, "FOUND IT");





4. What would happen if you issued the following statement?





SQL> DELETE * FROM COLLECTION;





5. What would happen if you issued the following statement?





SQL> DELETE FROM COLLECTION;





6. What would happen if you issued the following statement?





SQL> UPDATE COLLECTION
SET WORTH = 555
SET REMARKS = 'UP FROM 525';





7. Will the following SQL statement work?





SQL> INSERT INTO COLLECTION
SET VALUES = 900
WHERE ITEM = 'STRING';





8. Will the following SQL statement work?





SQL> UPDATE COLLECTION
SET VALUES = 900
WHERE ITEM = 'STRING';



Exercises


1. Try inserting values with incorrect data types into a table. Note the
errors and then insert values with correct data types into the same table.
2. Using your database system, try exporting a table (or an entire database)
to some other format. Then import the data back into your database. Familiarize yourself
with this capability. Also, export the tables to another database format if your
DBMS supports this feature. Then use the other system to open these files and examine
them.











© Copyright, Macmillan Computer Publishing. All
rights reserved.

zanotowane.pl

doc.pisz.pl

pdf.pisz.pl

teen-mushing.xlx.pl