SQL Series...18

Manipulating Database Data

In principle, database manipulation is quite simple. Understanding how to add data to a table isn’t difficult — you can add your data either one row at a time or in a batch. Changing, deleting, or retrieving table rows is also easy in practice. The main challenge to database manipulation is selecting the rows that you want to change, delete, or retrieve. Sometimes, retrieving data is like trying to put together a jigsaw puzzle with pieces that are mixed in with pieces from a hundred other puzzles. The data that you want may reside in a database containing a large volume of data that you don’t want. Fortunately, if you can specify what you want by using an SQL SELECT statement, the computer does all the searching for you.

Retrieving Data

The data manipulation task that users perform most frequently is retrieving selected information from a database. You may want to retrieve the contents of one row out of thousands in a table. You may want to retrieve all the rows that satisfy a condition or a combination of conditions. You may even want to retrieve all the rows in the table. One particular SQL statement, the SELECT statement, performs all these tasks for you.

The simplest use of the SELECT statement is to retrieve all the data in all the rows of a specified table. To do so, use the following syntax:

SELECT * FROM CUSTOMER ;

The asterisk (*) is a wildcard character that means ever ything. In this context, the asterisk is a shorthand substitute for a listing of all the column names of the CUSTOMER table. As a result of this statement, all the data in all the rows and columns of the CUSTOMER table appear on-screen.

SELECT statements can be much more complicated than the statement in this example. In fact, some SELECT statements can be so complicated that they’re virtually indecipherable. This potential complexity is a result of the fact that you can tack multiple modifying clauses onto the basic statement. In this article, I briefly discuss the WHERE clause, which is the most commonly used method to restrict the rows that a SELECT statement returns.

A SELECT statement with a WHERE clause has the following general form:

SELECT column_list FROM table_name

WHERE condition ;

The column list specifies which columns you want to display. The statement displays only the columns that you list. The FROM clause specifies from which table you want to display columns. The WHERE clause excludes rows that do not satisfy a specified condition. The condition may be simple (for example, WHERE CUSTOMER_STATE = ‘NH’), or it may be compound (for example, WHERE CUSTOMER_STATE=’NH’ AND STATUS=’Active’).

The following example shows a compound condition inside a SELECT statement:

SELECT FirstName, LastName, Phone FROM CUSTOMER

WHERE State = ‘NH’

AND Status = ‘Active’ ;

This statement returns the names and phone numbers of all active customers living in New Hampshire. The AND keyword means that for a row to qualify for retrieval, that row must meet both conditions: State = ‘NH’ and Status = ‘Active’.

Creating Views

The structure of a database that’s designed according to sound principles — including appropriate normalization — maximizes the integrity of the data. This structure, however, is often not the best way to look at the data. Several applications may use the same data, but each application may have a differ-ent emphasis. One of the most powerful features of SQL is its capability to display views of the data that are structured differently from how the data-base tables store the data. The tables you use as sources for columns and rows in a view are the base tables. This section looks at views in the context of retrieving and manipulating data.

A SELECT statement always returns a result in the form of a virtual table. A view is a special kind of virtual table. You can distinguish a view from other virtual tables because the database’s metadata holds the definition of a view. This distinction gives a view a degree of persistence that other virtual tables don’t possess. You can manipulate a view just as you can manipulate a real table. The difference is that a view’s data doesn’t have an independent exis-tence. The view derives its data from the table or tables from which you draw the view’s columns. Each application can have its own unique views of the same data.

That database con-tains five tables: CLIENT, TESTS, EMPLOYEE, ORDERS, and RESULTS. Suppose the national marketing manager wants to see from which states the com-pany’s orders are coming: Part of this information lies in the CLIENT table, part lies in the ORDERS table. Suppose the quality-control officer wants to

compare the order date of a test to the date on which the final test result came in. This comparison requires some data from the ORDERS table and some from the RESULTS table. To satisfy needs such as these, you can create views that give you exactly the data you want in each case.

From tables

For the marketing manager, you can create the view shown in Figure 

		CLIENT Table
		ClientName
		Address1
		Address2
		City
		State
		PostalCode
		Phone
		Fax
		ContactPerson						ORDERS_BY_STATE View
										ClientName
										State
		ORDERS Table								OrderNumber
The
ORDERS_		OrderNumber
BY_STATE
		ClientName
view for the
		TestOrdered
marketing
		Salesperson
manager.
		OrderDate

The following statement creates the marketing manager’s view:

CREATE VIEW ORDERS_BY_STATE

(ClientName, State, OrderNumber)

AS SELECT CLIENT.ClientName, State, OrderNumber

FROM CLIENT, ORDERS

WHERE CLIENT.ClientName = ORDERS.ClientName ;

The new view has three columns: ClientName, State, and OrderNumber. ClientName appears in both the CLIENT and ORDERS tables and serves as the link between the two tables. The new view draws State information from the CLIENT table and takes the OrderNumber from the ORDERS table. In the preceding example, you explicitly declare the names of the columns in the new view. This declaration is not necessary if the names are the same as the names of the corresponding columns in the source tables. The example in the following section shows a similar CREATE VIEW statement but with the view column names implied rather than explicitly stated.

With a selection condition

The quality-control officer requires a different view from the one that the marketing manager uses, as shown by the example in Figure

		ORDERS Table
		OrderNumber
		ClientName
		TestOrdered
		Salesperson
		OrderDate							REPORTING_LAG View
										OrderNumber
The										OrderDate
		RESULTS Table								DateReported
REPORTING_
LAG view		ResultNumber
for the
		OrderNumber
quality-
		Result
control
		DateReported
officer.
		PreliminaryFinal

Here’s the code that creates the view in Figure:

CREATE VIEW REPORTING_LAG

AS SELECT ORDERS.OrderNumber, OrderDate, DateReported

FROM ORDERS, RESULTS

WHERE ORDERS.OrderNumber = RESULTS.OrderNumber

AND RESULTS.PreliminaryFinal = ‘F’ ;

This view contains order-date information from the ORDERS table and final-report-date information from the RESULTS table. Only rows that have an ‘F’ in the PreliminaryFinal column of the RESULTS table appear in the REPORTING LAG view.

With a modified attribute

The SELECT clauses in the examples in the two preceding sections contain only column names. You can include expressions in the SELECT clause as well. Suppose VetLab’s owner is having a birthday and wants to give all his customers a 10-percent discount to celebrate. He can create a view based on the ORDERS table and the TESTS table. He may construct this table as shown in the following code example:

CREATE VIEW BIRTHDAY

(ClientName, Test, OrderDate, BirthdayCharge)

AS SELECT ClientName, TestOrdered, OrderDate,

StandardCharge * .9

FROM ORDERS, TESTS

WHERE TestOrdered = TestName ;

Notice that the second column in the BIRTHDAY view — Test — corresponds to the TestOrdered column in the ORDERS table, which also corresponds to the TestName column in the TESTS table. Figure shows how to create this view.

ORDERS Table

OrderNumber

BIRTHDAY View

ClientName

ClientName

TestOrdered

Salesperson

Test

OrderDate

OrderDate

The view

BirthdayCharge

created to

TESTS Table

show

birthday

TestName

*0.9

discounts.

StandardCharge

You can build a view based on multiple tables, as shown in the preceding examples, or you can build a view based on only one table. If you don’t need some of the columns or rows in a table, create a view to remove these elements from sight and then deal with the view rather than the original table. This approach ensures that users see only the parts of the table that are rele-vant to the task at hand.

Another reason for creating a view is to provide security for its underlying tables. You may want to make some columns in your tables available for inspection while hiding others. You can create a view that includes only those columns that you want to make available and then grant broad access to that view, while restricting access to the tables from which you draw the view. 

Updating Views

After you create a table, that table is automatically capable of accommodat-ing insertions, updates, and deletions. Views don’t necessarily exhibit the same capability. If you update a view, you’re actually updating its underlying table. Here are a few potential problems when updating views:

Some views may draw components from two or more tables. If you update such a view, how do you know which of its underlying tables gets updated?

A view may include an expression for a SELECT list. How do you update an expression?

Suppose that you create a view by using the following statement:

CREATE VIEW COMP AS

SELECT EmpName, Salary+Comm AS Pay

FROM EMPLOYEE ;

Can you update Pay by using the following statement?

UPDATE COMP SET Pay = Pay + 100 ;

No, this approach doesn’t make any sense because the underlying table has no Pay column. You can’t update something that doesn’t exist in the base table.

Keep the following rule in mind whenever you consider updating views: You can’t update a column of a view unless it corresponds to a column of an underlying base table.

Adding New Data

Every database table starts out empty. After you create a table, either by using SQL’s DDL or a RAD tool, that table is nothing but a structured shell containing no data. To make the table useful, you must put some data into it. You may or may not have that data already stored in digital form.

If your data is not already in digital form, someone will probably have to enter the data manually, one record at a time. You can also enter data by using optical scanners and voice recognition systems, but the use of such devices for data entry is relatively rare.

If your data is already in digital form but perhaps not in the format of the database tables that you use, you need to translate the data into the appropriate format and then insert the data into the database.

If your data is already in digital form and in the correct format, it’s ready for transferring to a new database.

Depending on the current form of the data, you may be able to transfer it to your database in one operation, or you may need to enter the data one record at a time. Each data record that you enter corresponds to a single row in a database table.

Adding data one row at a time

Most DBMSs support form-based data entry. This feature enables you to create a screen form that has a field for every column in a database table. Field labels on the form enable you to determine easily what data goes into each field. The data-entry operator enters all the data for a single row into the form. After the DBMS accepts the new row, the system clears the form to accept another row. In this way, you can easily add data to a table one row at a time.

Form-based data entry is easy and less susceptible to data-entry errors than is a list of comma-delimited values. The main problem with form-based data entry is that it is nonstandard; each DBMS has its own method of creating forms. This diversity, however, is not a problem for the data-entry operator. You can make the form look generally the same from one DBMS to another. The application developer is the person who must return to the bottom of the learning curve every time he or she changes development tools. Another possible problem with form-based data entry is that some implementations may not permit a full range of validity checks on the data that you enter.

The best way to maintain a high level of data integrity in a database is to keep bad data out of the database. You can prevent the entry of some bad data by applying constraints to the fields on a data-entry form. This approach enables you to make sure that the database accepts only data values of the correct type and that fall within a predefined range. Applying such constraints can’t prevent all possible errors, but it does catch some of them.

If the form-design tool in your DBMS doesn’t enable you to apply all the validity checks that you need to ensure data integrity, you may want to build your own screen, accept data entries into variables, and check the entries by using appli-cation program code. After you’re sure that all the values entered for a table row are valid, you can then add that row by using the SQL INSERT command.

If you enter the data for a single row into a database table, the INSERT com-mand uses the following syntax:

INSERT INTO table_1 [(column_1, column_2, ..., column_n)]

VALUES (value_1, value_2, ..., value_n) ;

As indicated by the square brackets ([ ]), the listing of column names is optional. The default column list order is the order of the columns in the table. If you put the VALUES in the same order as the columns in the table, these elements go into the correct columns — whether you explicitly specify those columns or not. If you want to specify the VALUES in some order other than the order of the columns in the table, you must list the column names, putting the columns in an order that corresponds to the order of the VALUES.

To enter a record into the CUSTOMER table, for example, use the following syntax:

INSERT INTO CUSTOMER (CustomerID, FirstName, LastName, Street, City, State, Zipcode, Phone)

VALUES (:vcustid, ‘David’, ‘Taylor’, ‘235 Nutley Ave.’, ‘Nutley’, ‘NJ’, ‘07110’, ‘(201) 555-1963’) ;

The first VALUE, vcustid, is a variable that you increment with your program code after you enter each new row of the table. This approach guarantees that you have no duplication of the CustomerID. CustomerID is the primary key for this table and, therefore, must be unique. The rest of the values are data items rather than variables that contain data items. Of course, you can hold the data for these columns in variables, too, if you want. The INSERT state-ment works equally well either with variables or with an explicit copy of the data itself as arguments of the VALUES keyword.

Adding data only to selected columns

Sometimes you want to note the existence of an object, even if you don’t have all the facts on it yet. If you have a database table for such objects, you can insert a row for the new object without filling in the data in all the columns. If you want the table in first normal form, you must insert enough data to dis-tinguish the new row from all the other rows in the table.  Inserting the new row’s primary key is suffi-cient for this purpose. In addition to the primary key, insert any other data that you have about the object. Columns in which you enter no data contain nulls.

The following example shows such a partial row entry:

INSERT INTO CUSTOMER (CustomerID, FirstName, LastName)

VALUES (:vcustid, ‘Tyson’, ‘Taylor’) ;

You insert only the customer’s unique identification number and name into the database table. The other columns in this row contain null values.

Adding a block of rows to a table

Loading a database table one row at a time by using INSERT statements can be tedious, particularly if that’s all you do. Even entering the data into a care-fully human-engineered ergonomic screen form gets tiring after a while. Clearly, if you have a reliable way to enter the data automatically, you’ll find occa-sions in which automatic entry is better than having a person sit at a key-board and type.

Automatic data entry is feasible, for example, if the data already exists in elec-tronic form because somebody has already manually entered the data. If so, you have no compelling reason to repeat history. The transfer of data from one data file to another is a task that a computer can perform with a minimum of human involvement. If you know the characteristics of the source data and the desired form of the destination table, a computer can (in principle) per-form the data transfer automatically.

Copying from a foreign data file

Suppose that you’re building a database for a new application. Some data that you need already exists in a computer file. The file may be a flat file or a table in a database created by a DBMS different from the one you use. The data may be in ASCII or EBCDIC code or in some arcane proprietary format. What do you do?

The first thing you do is hope and pray that the data you want is in a widely used format. If the data is in a popular format, you have a good chance of find-ing a format conversion utility that can translate the data into one or more other popular formats. Your development environment can probably import at least one of these formats. If you’re really lucky, your development envi-ronment can handle the data’s current format directly. On personal comput-ers, the Access, dBASE, and Paradox formats are probably the most widely used. If the data that you want is in one of these formats, conversion should be easy. If the format of the data is less common, you may need to go through a two-step conversion.

As a last resort, you can turn to one of the professional data-translation services. These businesses specialize in translating computer data from one format to another. They have the capability of dealing with hundreds of formats — most of which nobody has ever heard of. Give one of these ser-vices a tape or disk containing the data in its original format, and you get back the same data translated into whatever format you specify.

Transferring all rows between tables

A less severe problem than dealing with foreign data is taking data that already exists in one table in your database and combining that data with data in another table. This process works great if the structure of the second table is identical to the structure of the first table — that is, every column in the first table has a corresponding column in the second table, and the data types of the corresponding columns match. If so, you can combine the con-tents of the two tables by using the UNION relational operator. The result is a virtual table containing data from both source tables. 

Transferring selected columns and rows between tables

Generally, the structure of the data in the source table isn’t identical to the structure of the table into which you want to insert the data. Perhaps only some of the columns match — and these are the columns that you want to transfer. By combining SELECT statements with a UNION, you can specify which columns from the source tables to include in the virtual result table. By including WHERE clauses in the SELECT statements, you can restrict the rows that you place into the result table to those that satisfy specific condi-tions.

Suppose that you have two tables, PROSPECT and CUSTOMER, and you want to list everyone living in the state of Maine who appears in either table. You can create a virtual result table with the desired information by using the fol-lowing command:

SELECT FirstName, LastName

FROM PROSPECT

WHERE State = ‘ME’

UNION

SELECT FirstName, LastName

FROM CUSTOMER

WHERE State = ‘ME’ ;

Here’s a closer look:

The SELECT statements specify that the columns included in the result table are FirstName and LastName.

The WHERE clauses restrict the rows included to those with the value ‘ME’ in the State column.

The State column isn’t included in the results table but is present in both the PROSPECT and CUSTOMER tables.

The UNION operator combines the results from the SELECT on PROSPECT with the results of the SELECT on CUSTOMER, deletes any duplicate rows, and then displays the result.

Another way to copy data from one table in a database to another is to nest a SELECT statement within an INSERT statement. This method (a subselect) doesn’t create a virtual table but instead duplicates the selected data. You can take all the rows from the CUSTOMER table, for example, and insert those rows into the PROSPECT table. Of course, this only works if the structures of the CUSTOMER and PROSPECT tables are identical. Later, if you want to isolate those customers who live in Maine, a simple SELECT with one condition in the WHERE clause does the trick, as shown in the following example:

INSERT INTO PROSPECT

SELECT * FROM CUSTOMER

WHERE State = ‘ME’ ;

Even though this operation creates redundant data (you’re now storing cus-tomer data in both the PROSPECT table and the CUSTOMER table), you may want to do it anyway to improve the performance of retrievals. Beware of the redundancy, however, and to maintain data consistency, make sure that you don’t insert, update, or delete rows in one table without inserting, updating, or deleting the corresponding rows in the other table. Another potential prob-lem is the possibility that the INSERT might generate duplicate primary keys. If even one prospect has a primary key ProspectID that matches the corre-sponding primary key, CustomerID, of a customer that is inserted into the PROSPECT table, the insert operation will fail.

Updating Existing Data

You can count on one thing in this world — change. If you don’t like the current state of affairs, just wait a while. Before long, things will be different. Because the world is constantly changing, the databases used to model aspects of the world also need to change. A customer may change her address. The quantity of a product in stock may change (because, you hope, someone buys one now and then). A basketball player’s season performance statistics change each time he plays in another game. These are the kinds of events that require you to update a database.

SQL provides the UPDATE statement for changing data in a table. By using a single UPDATE statement, you can change one, some, or all the rows in a table. The UPDATE statement uses the following syntax:

UPDATE table_name

SET column_1 = expression_1, column_2 = expression_2,

..., column_n = expression_n

[WHERE predicates] ;

The WHERE clause is optional. This clause specifies the rows that you’re updat-ing. If you don’t use a WHERE clause, all the rows in the table are updated. The SET clause specifies the new values for the columns that you’re changing.

Consider the CUSTOMER table shown in Table 

Table		CUSTOMER Table
Name	City	Area Code	Telephone
Abe Abelson	Springfield	(714)	555-1111
Bill Bailey	Decatur	(714)	555-2222
Chuck Wood	Philo	(714)	555-3333
Don Stetson	Philo	(714)	555-4444
Dolph Stetson	Philo	(714)	555-5555

Customer lists change occasionally — as people move, change their phone numbers, and so on. Suppose that Abe Abelson moves from Springfield to Kankakee. You can update his record in the table by using the following UPDATE statement:

UPDATE CUSTOMER

SET City = ‘Kankakee’, Telephone = ‘666-6666’

WHERE Name = ‘Abe Abelson’ ;

This statement causes the changes shown in Table 

Table	CUSTOMER Table after UPDATE to One Row
Name	City	Area Code	Telephone
Abe Abelson	Kankakee	(714)	666-6666
Bill Bailey	Decatur	(714)	555-2222
Chuck Wood	Philo	(714)	555-3333
Don Stetson	Philo	(714)	555-4444
Dolph Stetson	Philo	(714)	555-5555

You can use a similar statement to update multiple rows. Assume that Philo is experiencing explosive population growth and now requires its own area code. You can change all rows for customers who live in Philo by using a single UPDATE statement, as follows:

UPDATE CUSTOMER

SET AreaCode = ‘(619)’

WHERE City = ‘Philo’ ;

The table now looks like the one shown in Table 

Table                 CUSTOMER Table after UPDATE to Several Rows

Name	City	Area Code	Telephone
Abe Abelson	Kankakee	(714)	666-6666
Bill Bailey	Decatur	(714)	555-2222
Chuck Wood	Philo	(619)	555-3333
Don Stetson	Philo	(619)	555-4444
Dolph Stetson	Philo	(619)	555-5555

Updating all the rows of a table is even easier than updating only some of the rows. You don’t need to use a WHERE clause to restrict the statement. Imagine that the city of Rantoul has acquired major political clout and has now annexed not only Kankakee, Decatur, and Philo, but also all the cities and towns in the database. You can update all the rows by using a single statement, as follows:

UPDATE CUSTOMER

SET City = ‘Rantoul’ ;

Table shows the result.

Table	CUSTOMER Table after UPDATE to All Rows
Name	City	Area Code	Telephone
Abe Abelson	Rantoul	(714)	666-6666
Bill Bailey	Rantoul	(714)	555-2222
Chuck Wood	Rantoul	(619)	555-3333
Don Stetson	Rantoul	(619)	555-4444
Dolph Stetson	Rantoul	(619)	555-5555

The WHERE clause that you use to restrict the rows to which an UPDATE state-ment applies can contain a subselect. A subselect enables you to update rows in one table based on the contents of another table.

For example, suppose that you’re a wholesaler and your database includes a VENDOR table containing the names of all the manufacturers from whom you buy products. You also have a PRODUCT table containing the names of all the products that you sell and the prices that you charge for them. The VENDOR table has columns VendorID, VendorName, Street, City, State, and Zip. The PRODUCT table has ProductID, ProductName, VendorID, and SalePrice.

Your vendor, Cumulonimbus Corporation, decides to raise the prices of all its products by 10 percent. To maintain your profit margin, you must raise your prices on the products that you obtain from Cumulonimbus by 10 percent. You can do so by using the following UPDATE statement:

UPDATE PRODUCT

SET SalePrice = (SalePrice * 1.1)

WHERE VendorID IN

(SELECT VendorID FROM VENDOR

WHERE VendorName = ‘Cumulonimbus Corporation’) ;

The subselect finds the VendorID that corresponds to Cumulonimbus. You can then use the VendorID field in the PRODUCT table to find the rows that you need to update. The prices on all Cumulonimbus products increase by 10 percent, whereas the prices on all other products stay the same.

Transferring Data

In addition to using the INSERT and UPDATE statements, you can add data to a table or view by using the MERGE statement. You can MERGE data from a source table or view into a destination table or view. The MERGE can either insert new rows into the destination table or update existing rows. MERGE is a convenient way to take data that already exists somewhere in a database and copy it to a new location.

For example, consider the VetLab database. Suppose some people in the EMPLOYEE table are salespeople who have taken orders, whereas others are non-sales employees or salespeople who have not yet taken an order. The year just concluded has been profitable, and you want to share some of that success with the employees. You decide to give a bonus of $100 to everyone who has taken at least one order and a bonus of $50 to every-one else. First, you create a BONUS table and insert into it a record for each employee who appears at least once in the ORDERS table, assigning each record a bonus value of $100 by default.

Next, you want to use the MERGE statement to insert new records for those employees who have not taken orders, giving them $50 bonuses. Here’s some code that builds and fills the BONUS table:

CREATE TABLE BONUS (

EmployeeName CHARACTER (30)

Bonus NUMERIC

PRIMARY KEY,

DEFAULT 100 ) ;

INSERT INTO BONUS (EmployeeName)

(SELECT EmployeeName FROM EMPLOYEE, ORDERS

WHERE EMPLOYEE.EmployeeName = ORDERS.Salesperson

GROUP BY EMPLOYEE.EmployeeName) ;

You can now query the BONUS table to see what it holds:

SELECT * FROM BONUS ;

EmployeeName Bonus

------------ -------------

Brynna Jones 100

Chris Bancroft 100

Greg Bosser 100

Kyle Weeks 100

Now by executing a MERGE statement, you can give $50 bonuses to the rest of the employees:

MERGE INTO BONUS

USING EMPLOYEE

ON (BONUS.EmployeeName = EMPLOYEE.EmployeeName)

WHEN NOT MATCHED THEN INSERT

(BONUS.EmployeeName, BONUS,bonus)

VALUES (EMPLOYEE.EmployeeName, 50) ;

Records for people in the EMPLOYEE table that do not match records for people already in the BONUS table are now inserted into the BONUS table. Now a query of the BONUS table gives the following:

SELECT * FROM BONUS ;

EmployeeName	Bonus
--------------	-----------
Brynna Jones	100
Chris Bancroft	100
Greg Bosser	100
Kyle Weeks	100
Neth Doze	50
Matt Bak	50
Sam Saylor	50
Nic Foster	50

The first four records, which were created with the INSERT statement, are in alphabetical order by employee name. The rest of the records, added by the MERGE statement, are in whatever order they were in, in the EMPLOYEE table.

Deleting Obsolete Data

As time passes, data can get old and lose its usefulness. You may want to remove this outdated data from its table. Unneeded data in a table slows per-formance, consumes memory, and can confuse users. You may want to trans-fer older data to an archive table and then take the archive offline. That way, in the unlikely event that you ever need to look at that data again, you can recover it. In the meantime, it doesn’t slow down your everyday processing. Whether you decide that obsolete data is worth archiving or not, you eventu-ally come to the point where you want to delete that data. SQL provides for the removal of rows from database tables by use of the DELETE statement.

You can delete all the rows in a table by using an unqualified DELETE state-ment, or you can restrict the deletion to only selected rows by adding a WHERE clause. The syntax is similar to the syntax of a SELECT statement, except that you use no specification of columns. If you delete a table row, you remove all the data in that row’s columns.

For example, suppose that your customer, David Taylor, just moved to Tahiti and isn’t going to buy anything from you anymore. You can remove him from your CUSTOMER table by using the following statement:

DELETE FROM CUSTOMER

WHERE FirstName = ‘David’ AND LastName = ‘Taylor’ ;

Assuming that you have only one customer named David Taylor, this state-ment makes the intended deletion. If any chance exists that you have two customers who share the name David Taylor, you can add more conditions to the WHERE clause (such as STREET or PHONE or CUSTOMER_ID) to make sure that you delete only the customer you want to remove.