CAUTION! It’s very easy to delete more data than you intended if you’re not careful. Make sure you save your INSERT statements from Task 1.2 so that you can re-create any data that you delete during this task.

Sometimes you also need to remove data from a table. You can do this using SQL’s DELETE statement, which has the following general form:

delete from <table name>
[where <condition>];

For example, the following will delete the Scientist with the ID 12345:

delete from Scientist
where Scientist_Num = '12345';

Check the contents of your Scientist table, run the above DELETE statement, then check the table contents again. (Remember that you may need to modify column names and values to be compatible with your table.) Have the contents of the table changed, and does this match what you expected to happen? If not, why not?

Deleting data isn’t always this straightforward, though. Run the three INSERT statements below, modifying column names and values as necessary to match your tables. (Hint: To run multiple statements, select all of them in the SQL worksheet, then click the Run Statement button. The selected statements will run in sequence. The run script button, which is third down in the toolbar, and looks like a document with a curled edges will run all uncommented code from top to bottom.)

insert into Scientist (Scientist_Num, Surname, Other_Names, Email, Mobile_Phone)
    values ('54321', 'Jones', 'Heather', 'hjones@wet.co.nz', '+64 27 297 1619');

insert into Site (Site_ID, Region, Description, Latitude, Longitude,
                  Catchment_Area, Catchment_Height, Altitude)
    values ('DN20', 'Dunedin', 'Leith at Clyde St. Br.', -45.867093, 170.516081,
        null, null, null);

insert into Sample (Scientist_Num, Site_ID, Recorded_On, Comments)
    values ('54321', 'DN20', to_date('25/11/2016', 'DD/MM/YYYY'), null);

Now run the following statement:

delete from Site
    where Site_ID = 'DN20';

What happens, and why?

How can you work around this?

After all that inserting and deleting, your data may have become a little messy. It wouldn’t hurt at this point to delete everything from all three tables and rebuild the data from scratch using the INSERT statements that you saved earlier. (You did save them, didn’t you? ) Just delete the data, don’t drop the tables.