Skip to content

Days 3 and 4: Our Notes

This page includes our notes on today's tutorials. You can use them to review, to practice, or you can ignore them entirely!

1. Learn about Transactional Integrity

There are built-in features in database applications that help you keep your information and your database safe even when you (or other people with access to do so) make changes to the data. These features enforce transactional integrity, which is something we should learn about before we begin making changes to your data via queries.

2. Create, Read, Update, Delete: Growing your Database

Databases are managed using CRUD operations - Create, Read, Update and Delete - and as you design your database you can choose different ways to manage each kind of operation.

We learned about some (but not all) parts of Create in day1, and Read in day2 of this workshop!

Today, we'll focus on Insert, Update and Delete. We'll talk about them in what might seem to be a weird order, but hopefully the method to our madness will be clear enough by the end of the day.

2.1. Insert

On day 1 we learned how to create a database, and a table, and then we used INSERT statements to add our first rows of data. INSERT is the create function that makes new rows of data in an existing table. Since you will probably want to be able to add rows to your tables as your project grows, we'll review and extend our practice with INSERT queries here.

The template for an INSERT INTO command to add a new row to a single table is:

INSERT INTO table (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Imagine we have this words table already set up:

idrootnicodemusenglishoriginator_id
1aahello.3
2bcbutsboots.3
3bstbilsodragonfly.1
4cčtsechthunder.2

So we can add a row into our words table with a command like this:

INSERT INTO words (root, nicodemus, english, originator_id)
VALUES ("hl", "hnhalaatse'","raspberry.", 3);
The operation is INSERT INTO, after which we specify the name of the table, and then, in parentheses, the names of the columns into which we are going to add data. Note that we don't list the id column, because the database will automatically generate those values when the inserts are done.

Note that we've wrapped the root, nicodemus and english values with double-quotes. This is important for two reasons - first because these are text columns, and strings of text are generally enclosed in quotes. Second, because these values could contain spaces, or characters like apostrophe that the database might otherwise think are meant to indicate commands, wildcards, or math functions.

We did not wrap the originator_id in quotes because that column holds integer values. No quotes are allowed in integers.

The resulting table should look like this:

idrootnicodemusenglishoriginator_id
1aahello.3
2bcbutsboots.3
3bstbilsodragonfly.1
4cčtsechthunder.2
5hlhnhalaatse'raspberry.3

Tip

The id column is automatically updated by the database software when the new line is added. If you haven't made any other changes to the table, it's most likely that the new record will get an id that's the next integer after the last row in the table (in this case, the new record got numbered 5). But if you have done other INSERT, DELETE or UPDATE commands on the table, the id of the new row might not be consecutive. That's OK, because the id just has to be unique, and there are an infinite number of integers for the computer to use. The database will likely not reuse id values that were assigned to rows in the past, even if those rows are gone now.

We were actually a little more explicit than necessary in this command. If you want to insert a value in every column of a single table (other than those that the database automatically creates), you do not have to list the column names. You can just list the values, in order, like this:

INSERT INTO words
VALUES ("hl", "hnhalaatse'","raspberry.", 3);
Note that when you execute an SQL command like this one, the command will either succeed entirely, or fail entirely, and that's good! The database guarantees transactional integrity, and will not let you get half way through the insertion, and accidentally create a partial row that violates your table schema.

You can find more detailed information on different kinds of INSERT statements in our tutorials! 🚀

2.2. Delete

In SQL there are really two kinds of delete: DELETE commands are used to remove rows from tables; DROP is used to delete whole tables or even whole databases.

When practicing DELETE and DROP commands especially, it's good practice to back up your database first, so that you'll be able to restore anything you've accidentally removed. We have instructions for this at the top of this page.

When you're working with a database that's not just for practice, you can protect yourself against unintended deletions by implementing database logging. Logging is a system for recording all of the actions in a database, along with time and date stamps, user information, etc., so that you can restore or roll back those errors.

We won't be able to show you how to implement logging in this workshop, but it should probably be a subject of future lessons in this course.

2.2.1. Delete a subset of rows from a table

The schema for an SQL command to delete one or many rows from a single table, based on some criterion, is below.

DELETE FROM table WHERE condition;

DELETE FROM is the operation, replace table with the name of the relevant table, and then develop a WHERE clause. WHERE clauses are filters (think searches) so that the command only applies to rows that match the filter we set up.

WHERE clauses work differently depending on the data type of the column they're looking at. If you're trying to find information in an integer or date column, you can use WHERE clauses with operators like these:

  • = produces an exact match;
  • > matches all numbers or dates greater than the value in the WHERE clause; >= matches those that are greater than or equal to that value;
  • < matches those less than the value stated, <= matches those less than or equal to it;
  • BETWEEN lets you find values between two such values;
  • you can use either <> or != to mean is not equal to.

If you're looking for information stored in text strings, you'll more likely use operators like these:

  • LIKE allows you to match a pattern within a string, and it combines with wildcard characters so that you can specify some pretty fancy patterns:

  • % matches any number of characters.

  • _ matches exactly one character.

Tip

In SQLite, LIKE is case-insensitive by default. Case-sensitive LIKE requires a fancier command, but it is possible.

This'll make more sense, of course, if we can see a few examples, which we'll execute against this table:

idrootnicodemusenglishoriginator_id
1aahello.3
2bcbutsboots.3
3bstbilsodragonfly.1
4cčtsechthunder.2
5hlhnhalaatse'raspberry.3

The command:

DELETE FROM words WHERE originator_id=2;
Would produce this result:

idrootnicodemusenglishoriginator_id
1aahello.3
2bcbutsboots.3
3bstbilsodragonfly.1
5hlhnhalaatse'raspberry.3

The command:

DELETE FROM words WHERE english LIKE "%boot%";
Would delete any rows in which the english column contained the substring boot, regardless of what other characters appear before or after it. In our table below, this has removed only one row, the one that had id of 2.:

idrootnicodemusenglishoriginator_id
1aahello.3
3bstbilsodragonfly.1
5hlhnhalaatse'raspberry.3

2.2.2. Delete all the rows from a table

Are you sure?

If you are, here's the schema.

DELETE FROM table_name;

Executing this command

DELETE FROM originators;

Leaves an empty table in our database, with its column names and constraints intact.

idrootnicodemusenglishoriginator_id

2.2.3 Delete the whole table (using DROP)

Are you sure?

If so, here's the command to remove a table entirely:

DROP TABLE table_name;
If I want to get rid of that whole originators table, I can use

DROP TABLE originators;
and it is gone.

Tip

You can practice your SQL skills by rebuilding your originators table, and adding the deleted words back into your words table by writing your own SQL statements

You can find more detailed information on different kinds of DELETE statements in our tutorials! 🚀

2.3. Update

UPDATE in SQL means modification of an existing row or rows of data. We've saved UPDATE until last, because in some ways UPDATE feels like a combination of DELETE (the old information) and INSERT (the new). In fact, some databases are designed to make modifications of existing data look like updates, but behind the scenes they actually just turn modification requests into a combination of DELETE and INSERT statements.

Tip

Decisions like how best to handle modification requests will be based on the developers' preferences, the needs of the database application, and decisions about how to ensure that the database is safe against accidental or erroneous modification or deletion of data. This can be managed in a number of ways, for now, we'll just let you know that there's more to be learned about it!

SQL provides one-step modification of existing data with a SETcommand that (typically) contains a WHERE condition.

Here's the basic schema:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Let's imagine that we have this words table:

idrootnicodemusenglishoriginator_id
1aahello.3
2bcbutsboots.3
3bstbilsodragonfly.1
4cčtsechthunder.2
5hlhnhalaatse'raspberry.3

And we've decided that we want to change the cč root to the tsch based on the fact that c should be written ts and č should be written ch in this language. We could run this command:

UPDATE words
SET root = "tsch"
WHERE root LIKE "cč";
The outcome of this command would be:

idrootnicodemusenglishoriginator_id
1aahello.3
2bcbutsboots.3
3bstbilsodragonfly.1
4tschtsechthunder.2
5hlhnhalaatse'raspberry.3

If we had more words in the table that used the root originally written cč, the command would replace every single one of them. It would not replace root values like tcč, because the WHERE clause does not include any wildcard characters.

Info

UPDATE commands in SQL can be scary because they really will replace all and only the things you tell them to replace. It's really important to learn and practice WHERE clauses so that you can be certain you'll only update the things you want to update!

Note that an UPDATE command with no WHERE clause in it will update everything in the column you select. Only use such commands if you really want to make all of the values in some column be updated to the same value!

You can find more detailed information on different kinds of UPDATE statements in our tutorials! 🚀

Back to Day 4

If you'd like you can head back to Day 4. 🚀