Database:Deleting rows of data -SQL DELETE
Expand using link to the right to see the meta section with instructor's notes.
This rather small topic deals with the SQL DELETE statement for deleting rows of data in a table.
Expect this lecture to take around 10-15 minutes. Again, if you require your students to see the video lecture in advance, you will be able to go faster and can spend more time on questions and answers, interacting with your students.
See the videos and presentation, and review the exercises and suggested solutions online.
Topics addressed or used in the online exercises
- Setting up a database from an SQL text file backup (importing data)
- Re-visitting relative and absolute paths when working in the command line
- Starting SQLite3 from the command line, giving an existing database (binary) file as the argument
- Re-visitting dropping a table and importing data again
- Deleting rows using a simple WHERE clause on one column (e.g. all cars whose make is Honda)
- Checking that a delete worked using SELECT in combination with COUNT(*)
- Note - aggregation isn’t taught in this course as a main topic, but rather indirectly like in this exercise - Feel free to add examples and lectures about aggregate functions such as COUNT, SUM, AVG etc
- INSERT and UPDATE are re-visited
- What happens if you chose a WHERE clause that is too broad (e.g. using the color column for deleting Hondas, when there are blue cars of more makes than just Honda
- These online exercises also include a Check your progress-section with answers on a different page
- Again, stress that the WHERE clause is important, with a faulty one, you will delete nothing, too little, too much or even everything
- Using dump to create a backup is one way to manage mistakes
- Stress that as long as the student can start over from the database files provided, it is not dangerous to experiment! The computer won’t break if mistakes are made during the course. Encourage experimentation.
- Explain the syntax of the DELETE statement and in particular why it can’t contain a “*” since * means “all columns” while the DELETE statement “operates on whole rows”
- The exercises starts off by letting the student create a new directory and copy files from a previous exercise - so expect some students to have problems with concepts such as “relative path” etc
- Be prepared during the exercise session (if our exercises are used) that there might be questions from the students about the aggregate COUNT(*) function call, command line stuff like relative paths, arguments to commands etc.
Full frontal - Code example up-front
sqlite> DELETE FROM eu_member WHERE country = "Great Britain";
This chapter deals with the SQL DELETE statement for deleting rows of data in a table. We need to know also how to delete complete rows of data from a table. The syntax is very simple (the hardest part seems to be, judging by our experience from previous students, not to add an asterisk after the DELETE keyword) but again, we need to be careful with the WHERE clause, or we might end up deleting every row in the table.
See the videos and presentation, read the pages linked to below (under External links) and move on to the exercises in the next chapter.
Note that the videos are in Swedish for now. The presentation, however, is in English
- SQL DELETE - Radera rader av data (Swe)
- SQL DELETE - Radera rader av data livekodning (Swe)
- Presentation (PDF) Deleting data - SQL DELETE (Eng)
- W3Schools - SQL DELETE
- ZetCode Inserting, updating, and deleting data in SQLite (The part on DELETE is enough here)