Fundamentals of databases: DELETE

From Wikibooks, open books for an open world
Jump to navigation Jump to search

PAPER 2 - ⇑ Fundamentals of databases ⇑

← INSERT DELETE Data definition language →


Sometimes you might fall out with friends on Facebook so that you don't even want them to see your restricted page. You'd want to delete your relationship (it's actually likely that Facebook retains the connection but flags it as 'deleted', that isn't important here). The police might also find that a crook is in fact innocent and then want to delete their criminal record. We need a DELETE command to do all of these things, to permanently delete records from databases. Imagine that we find out that Geoff was framed and he is completely innocent:

DELETE FROM crooks      
WHERE name = 'Geoff'
Exercise: DELETE statements

The police have agreed an amnesty with all heavily scarred (4+) women and have decided to delete them from the database. Write a statement to do this.

Answer:

DELETE FROM crooks
WHERE numScars >= 4 AND gender = 'female'

The police want to delete all minors (people under 16) from their records. Do it!

Answer:

DELETE FROM crooks
WHERE DoB >= 1999

You can use wildcards within a selection statement. For example, to delete all records: DELETE * FROM Customer. You should use such wildcards carefullyǃ