Day 3

Our agenda for today is given below. Will we finish everything listed here? Only the future knows. But we've laid some plans to ensure we can use our time together wisely.

Info

We have updated this agenda based on our experience on Wednesday morning!

Ready?? Let's go!

Learning Objective

at the end of today's class, you will have read your data into your tables, and you will have created select, insert, update, and delete queries in SQL!

1. Get Ready

Today's goal is to pick up where we left off on our day 2 lesson. If we finished up that material, then we'll just start here!

If you would like to use our files, you can access csv files to import, and/or db files that contain the tables and relations we discuss in our tutorials, beginning with creating SELECT queries. Here's how.

Be sure to back up your work before we begin, using the File → Save Project dialog.

1

Then you can open your working copy of the database, using File → Open Project

2

3. In-Class

3.1. Designing Your Tables

While you can import your csv file first, and use the material in that file to design your tables, we recommend setting up your table design before importing data.

Tip

This pattern lets you set up your tables to work well, and will ensure that when you do import your data, it will be in the correct form to use later.

First, we want to make sure you know about the table-building screen in DB Browser for SQLite, and what settings we recommend.

3

In this screen, NN means not nullable. Use this for any column that cannot have blanks.

Tip

Until you've imported your csv file, do not check NN for the id column.

PK means Primary Key. Click this to identify the unique identifier for each row in your table.

Tip

Do make a column in your table called "id", and click PK to set that as the primary key.

AI means Auto-Increment. Click this to tell DB Browser to automatically populate your Primary Key column with integers.

Tip

Do set your id column to Auto-Increment

U means Unique. You can click this to ensure that no two rows in your table have the same value in that column.

Tip

Don't set your id column to be Unique before you import your csv file.

In sum we recommend that you:

• create a column called id, which is set as PK and AI (only).
• create the rest of your columns based on the kind of information in each (text, integer, etc), and whether or not you want to ensure that there are no blanks - if so, set NN.
• add columns to hold foreign keys using column names like user_id if you want to connect to the primary key in the users table, or word_id if you want to connect to the primary key in the words table (for example.)

3.2. Preparing your csv files for import.

Warning

Thanks to Skye Anderson, we have confirmed that using MS Excel to manage your csv files is virtually doomed to failure if you have any special characters in your data. Instead of Excel, use Google Sheets, or Apple Numbers, or NotePad, or Text Edit, or, I don't know, stone tablets.

In your csv file, you'll want to:

• make sure your file has exactly the same name as the table you designed;
• make sure your file has exactly the same number of columns as the table you designed; and that your column headers are exactly the same as your column names in the table you designed.
• your primary key column should have the correct header (i.e. id), but the column should otherwise be blank.
• your foreign key column could be blank, or, if you know what the primary keys in the associated table are gonna be, it can contain those. If you don't know that yet, you can leave the values blank. The field type should be INTEGER for this column, and the keys should be integers.

3.3. Importing your csv files into your tables.

In DB Browser for SQLite, go to File → Import→ Table from CSV File...

4

Then browse to your file, and import it! Tell the import that the first line of your file is the column names, and check the box that says trim. This will ensure that your information is imported without hidden blank characters that could mess you up later.

If your file name matches the table name exactly, and you have exactly the right number of columns, and the column names in your csv file exactly match your table column names, and your data don't violate any of the constraints you set up, it'll work!

Info

When you go to import your data, you may well get error messages. That's normal, and we just trouble-shoot as we go. Eventually, your csv file will match your table correctly and the import will work.

Hey, you might note a weird thing that happens once you've created your tables - if you set an AI (Auto-Increment) field, you'll see a table called sqlite_sequence.

5

This is a table that was automatically created by SQLite, and it tracks the auto-incremented fields. It keeps track of the current highest value, so it will always know to start from the next integer when you add a new row to the table.

3.4. Configuring Table Relations via Foreign Keys

Once all of your tables are populated, you're ready to relate your tables by connecting the Primary Key of one table to another table as Foreign Keys. Here's how.

Select the table on which you want to create a Foreign Key. In our example, we'll work in a table called words, and relate it to a table called users by using a column called user_id as our Foreign Key.

In DB Browser for SQLite, highlight the table you want to work on and select Modify Table

6

Select the field you want to use and highlight it.

7

Scroll to the right. I know, it looks like nothing's there, but something is definitely there!

Double click in the blank void below the Foreign Key column, until you see this:

8

The first dropdown will show you the list of other tables in your database. Select the table you want to relate this one to. The second dropdown will show you the fields in that table. Select the Primary Key of that table (probably id).

9

Your tables are now related and you'll be able to do fancy things with them!

3.5. Doing Fancy Things!

We'll begin to use SQL in tutorial 4 🚀

When you're done, you'll know how to retrieve information from a single table in your database.

We'll continue through tutorial 5🚀 to learn how to retrieve only information that meets some criteria.

When you're done, you'll know how to limit your results using WHERE clauses.

and tutorial 6🚀 to learn how to retrieve information in a particular order.

When you're done, you'll know how to order your results using ORDER BY clauses

You can work through tutorial 7🚀 to limit the number of records to return.

When you're done, you'll know how to retrieve a pre-determined number of results.

Our tutorial 8🚀 introduces the topic of JOINs - the most important and maybe fanciest topic of all.

4. Modifying your data with SQL

The remaining tutorials focus on writing commands to insert new rows into your database table(s) 🚀; and how to delete rows or whole tables 🚀; and to update your data 🚀

They might be pretty straightforward for you to work through, but of course we're always happy to help!

4. Our Notes

We've got a set of notes that explain many of the concepts we're learning by working through today's tutorials. You might find them useful, but you don't have to use them if you don't feel the need.

The topics there include:

• Learn about Transactional Integrity
• Create, Read, Update, Delete - or CRUD - functions in databases
• Inserts
• Deletes
• Updates

Questions or need help?

You can contact John, Gus, or Amy and we'll do the best we can to help!

Next Steps

Check your understanding of the content by completing the review activity for Day 3

Head on over to day 4. 🚀