Removing Data with DELETE

We know how to add data to the database; now we will learn how to remove it. Removal is quite simple. The DELETE command can quickly eliminate any or all rows from a table. The command DELETE FROM friend will delete all rows from the table friend. The query DELETE FROM friend WHERE age = 19 will remove only those rows that have an age column equal to 19.

Here is a good exercise. Use INSERT to insert a row into the friend table, use SELECT to verify that the row has been properly added, then use DELETE to remove the row. This exercise combines the ideas you learned in the previous sections. Figure  shows an example.   

        test=> SELECT * FROM friend;
            firstname    |       lastname       |      city       | state | age 
        -----------------+----------------------+-----------------+-------+-----
         Mike            | Nichols              | Tampa           | FL    |  19
         Cindy           | Anderson             | Denver          | CO    |  23
         Sam             | Jackson              | Allentown       | PA    |  22
        (3 rows) 
         
        test=> INSERT INTO friend VALUES ('Jim', 'Barnes', 'Ocean City','NJ', 25);
        INSERT 19056 1
        test=> SELECT * FROM friend;
            firstname    |       lastname       |      city       | state | age 
        -----------------+----------------------+-----------------+-------+-----
         Mike            | Nichols              | Tampa           | FL    |  19
         Cindy           | Anderson             | Denver          | CO    |  23
         Sam             | Jackson              | Allentown       | PA    |  22
         Jim             | Barnes               | Ocean City      | NJ    |  25
        (4 rows) 
         
        test=> DELETE FROM friend WHERE lastname = 'Barnes';
        DELETE 1
        test=> SELECT * FROM friend;
            firstname    |       lastname       |      city       | state | age 
        -----------------+----------------------+-----------------+-------+-----
         Mike            | Nichols              | Tampa           | FL    |  19
         Cindy           | Anderson             | Denver          | CO    |  23
         Sam             | Jackson              | Allentown       | PA    |  22
        (3 rows)