SQL Journey: Blog#10

So far, we are only "reading" from a given database or table using the SELECT command of SQL. In today's lesson, we will now start "writing" into a given database using the UPDATE and DELETE commands.

Challenge: Dynamic Documents

Given data:

CREATE table documents (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    content TEXT,
    author TEXT);
    
INSERT INTO documents (author, title, content)
    VALUES ("Puff T.M. Dragon", "Fancy Stuff", "Ceiling wax, dragon wings, etc.");
INSERT INTO documents (author, title, content)
    VALUES ("Puff T.M. Dragon", "Living Things", "They're located in the left ear, you know.");
INSERT INTO documents (author, title, content)
    VALUES ("Jackie Paper", "Pirate Recipes", "Cherry pie, apple pie, blueberry pie.");
INSERT INTO documents (author, title, content)
    VALUES ("Jackie Paper", "Boat Supplies", "Rudder - guitar. Main mast - bed post.");
INSERT INTO documents (author, title, content)
    VALUES ("Jackie Paper", "Things I'm Afraid Of", "Talking to my parents, the sea, giant pirates, heights.");

SELECT * FROM documents;

Step 1

We've created a database for a documents app, with rows for each document with it's title, content, and author. In this first step, use UPDATE to change the author to 'Jackie Draper' for all rows where it's currently 'Jackie Paper'. Then re-select all the rows to make sure the table changed like you expected.

Code:

UPDATE documents SET author = "Jackie Draper" WHERE author = "Jackie Paper";

SELECT * FROM documents;

Query Results:

id	title	content	author
1	Fancy Stuff	Ceiling wax, dragon wings, etc.	Puff T.M. Dragon
2	Living Things	They're located in the left ear, you know.	Puff T.M. Dragon
3	Pirate Recipes	Cherry pie, apple pie, blueberry pie.	Jackie Draper
4	Boat Supplies	Rudder - guitar. Main mast - bed post.	Jackie Draper
5	Things I'm Afraid Of	Talking to my parents, the sea, giant pirates, heights.	Jackie Draper

Step 2

Now you'll delete a row, being very careful not to delete all the rows. Only delete the row where the title is 'Things I'm Afraid Of'. Then re-select all the rows to make sure the table changed like you expected.

Code:

DELETE FROM documents WHERE title = "Things I'm Afraid Of";

SELECT * FROM documents;

Query Results:

id	title	content	author
1	Fancy Stuff	Ceiling wax, dragon wings, etc.	Puff T.M. Dragon
2	Living Things	They're located in the left ear, you know.	Puff T.M. Dragon
3	Pirate Recipes	Cherry pie, apple pie, blueberry pie.	Jackie Draper
4	Boat Supplies	Rudder - guitar. Main mast - bed post.	Jackie Draper