Programming Languages Chapter 6 Guide Sql Ninth Edition Page Updating Data Glance Instructors Manual Table

A Guide to SQL, Ninth Edition Page 6–1

Chapter 6

Updating Data

At a Glance

Instructor’s Manual Table of Contents

• Overview

• Objectives

• Teaching Tips

• Quick Quizzes

• Class Discussion Topics

• Additional Projects

• Additional Resources

• Key Terms

A Guide to SQL, Ninth Edition Page 6–2

Lecture Notes

Overview

In this chapter, students learn how to create a new table from an existing table and how to

make changes to the data in a table. Students use the UPDATE command to change data in

one or more rows in a table and use the INSERT command to add new rows. They use the

DELETE command to delete rows. Students also use nulls in update operations. They learn

how to change the structure of a table in a variety of ways and how to drop existing tables.

Students learn how to use the COMMIT and ROLLBACK commands to make changes

permanent and to return changes to their original state. Finally, they learn how to drop a

table.

Chapter Objectives

In this chapter, students learn about:

• Creating a new table from an existing table

• Changing data using the UPDATE command

• Adding new data using the INSERT command

• Deleting data using the DELETE command

• Using nulls in an UPDATE command

• Changing the structure of an existing table

• Using the COMMIT and ROLLBACK commands to make permanent data updates or to

reverse updates

• Understanding transaction and the role of COMMIT and ROLLBACK in supporting

transactions

• Dropping a table

Teaching Tips

Creating a New Table from an Existing Table

1. Use Example 1 and Figure 6-1 to illustrate creating a new table called

LEVEL1_CUSTOMER using the CREATE TABLE command.

3. Use Example 2 and Figures 6-2 and 6-3 to illustrate using the INSERT INTO and SELECT

commands to place data from the CUSTOMER table into the LEVEL1_CUSTOMER table.

Teaching

Tip

Make sure students create the LEVEL1_CUSTOMER table. In this chapter, all

changes and deletions are made to this table.

A Guide to SQL, Ninth Edition Page 6–3

Changing Existing Data in a Table

1. Data in a database is dynamic. It is constantly changing. To keep data current, use the

UPDATE command to change rows on which a specific condition is true.

3. Discuss the format for the UPDATE command, which is the word UPDATE followed by

the name of the table to be updated. The next portion of the command consists of the word

4. Use Example 4 and Figures 6-6 and 6-7 to explain how to use a compound condition to

update a row.

5. Point out that you also can use the existing value in a column to calculate an update value.

Quick Quiz 1

1. The SQL command to describe a new table is _____.

2. The SQL command to change data in a table is _____.

3. A(n) _____ clause can be included in an UPDATE command to indicate the row(s) on

which the change is to take place.

Answer: WHERE

Adding New Rows to an Existing Table

1. You can use the INSERT command to add additional rows to an existing table. This is the

same command that was used in Chapter 3 to add data to a new table.

3. Point out the Note on page 172.

Autocommit, Commit and Rollback

1. Make sure that students understand the Autocommit transaction mode. By default, both

2. When the data in a table is updated, the updates are only temporary and can be reversed

reservations are other good examples to use for understanding transactions.

3. During the current work session, however, the user can commit (save) the changes

immediately by executing the COMMIT command.

4. To abandon (not save) the changes made during the current work session, the user can roll

back (reverse) the changes by executing the ROLLBACK command. Any updates made

5. Point out the Access Note on page 173. Access does not support the COMMIT or

ROLLBACK commands.

6. Point out the SQL Server Note on page 173.

Teaching

Tip

Point out that COMMIT and ROLLBACK are important in client/server

computing.

Transactions

1. A transaction is a logical unit of work. A transaction can be viewed as a sequence of steps

(1) Before beginning the updates for a transaction, commit any previous updates by

executing the COMMIT command.

(3) If you can complete all updates successfully, execute the COMMIT command after

completing the final update.

Teaching

Tip

You may want to spend time discussing transactions before discussing COMMIT

and ROLLBACK. Students need to understand the sequence in which tasks are

carried out.

Two good examples to use for transactions are an ATM machine and credit card

transactions. With an ATM withdrawal, ask the students what is input (magnetic

strip on card, keypad), what is processing (checking the account number,

checking pin, checking money amount, subtracting from account), and what is

A Guide to SQL, Ninth Edition Page 6–5

Quick Quiz 2

1. The SQL command to save changes to data immediately is _____.

2. A(n) _____ is a logical unit of work.

3. _____ is the default transaction mode for Oracle.

Changing and Deleting Existing Rows

1. Note that, in this section, Autocommit is still disabled. See the Note on page 174.

3. Use Example 6 and Figure 6-11 to illustrate deleting a row.

5. Review the embedded Q&A on page 176. Students need to understand that, if there is no

condition in a DELETE statement, then all rows in the table are deleted.

Executing a Rollback

1. Use Example 7 and Figures 6-13 and 6-14 to illustrate rollbacking the change and deletion

made in Example 6.

3. Point out the Note on page 177. In the remaining examples, the Autocommit feature is

enabled.

Changing a Value in a Column to Null

1. Use Example 8 and Figures 6-15 and 6-16 to illustrate changing the value in a column to

null.

3. Mention the SQL Server Note on page 179.

Changing a Table’s Structure

2. Use Example 9 and Figure 6-17 to illustrate changing a table’s structure by adding a

column to an existing table.

3. The ALTER TABLE command includes an ADD clause that defines the new column.

except for use as permitted in a license distributed with a certain product or service or otherwise on a password–

protected website for classroom use.

5. Use Example 10 and Figures 6-20 through 6-22 to illustrate updating individual rows. Use

7. To change characteristics of a column, use the MODIFY clause with the ALTER TABLE

command.

9. Mention the Access and SQL Server User Notes on page 183.

11. Use Figure 6-26 to show the revised structure of the table.

13. Mention the two Notes on page 185 regarding nulls.

Teaching

Tip

The ALTER TABLE statement has many clauses that allow you to change the

characteristics of columns and even rename a table. See the link in the Additional

Resources section for more information.

Making Complex Changes

1. Mention that, in some cases, there is a need to change a table’s structure in ways that are

2. In these situations, the CREATE TABLE command can be used to describe a new table and

then insert values into it using the INSERT command combined with an appropriate

SELECT statement.

Dropping a Table

1. To delete a table that is no longer needed, use the DROP TABLE command.

3. Point out that, when a DROP TABLE command is executed, a table and all its data are

permanently removed from the database.

Quick Quiz 3

1. You can change a table’s structure in SQL by using the _____ command.

2. To change the characteristics of existing columns, use the _____ clause of the ALTER

TABLE command.

3. To delete a table, use the _____ command.

Class Discussion Topics

1. The DROP TABLE command deletes a table. When would you want to delete a table?

Additional Projects

1. Assign students to teams and then assign each team a business activity (ordering tickets

2. A savepoint is a way of implementing subtransactions. Have students research this topic

Additional Resources

1. Concepts of Database Management, Eighth Edition by Philip Pratt and Mary Last

Key Terms

➢ ADD clause: The clause of the ALTER TABLE command used to add a column to a table

➢ ALTER TABLE: The command used to change a table’s structure

➢ COMMIT: The SQL command used to save update changes to a table

➢ DELETE: The command to delete data from a database

A Guide to SQL, Ninth Edition Page 6–8

➢ ROLLBACK: The SQL command used to reverse update changes to a table