Day 2

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 Tuesday morning!

Ready?? Let's go!

Learning Objectives

At the end of today's class, you will have a database with at least two tables, your database will be backed up and secured, and you may have created your first two queries using SQL.

1. Get Ready

Today's goal is to pick up where we left off on our day 1 lesson.

We hope you'll be able to use your own data if you brought it. It will need to be broken into two tables (which is the same as two spreadsheets), each one of those exported to a .csv file.

As always, you're welcome to use our database for today's class if you'd prefer.

3. In-Class

We will take the first up to half of our workshop today to do the following:

• review our work from day 1 with an eye to expectation-setting; then
• briefly share a real world example of database design for a dictionary-type application; then
• work with one or more volunteers to discuss design patterns relevant to their project;

We will then use the last half of the session for you to work individually or in teams through the necessary tutorials in order to advance your project:

• Use the information from tutorial 2: Create Your Database to create your database in DB Browser for SQLite; then

• Use the information from tutorial 2: Create Your First Table to create your tables in DB Browser for SQLite.

Tip

Tutorial 2 shows you how to build your tables in two different ways: (1) using the DB Browser for SQLite interface; and (2) by writing and executing SQL code. We'll focus on method (1) together, and then get our toes wet in SQL code later.

• Use the information from tutorial 3: Import Your Data Into the Database Tables to import your csv files into your tables.

• We'll need to meet Structured Query Language, aka SQL.

• once this is done, you'll be ready to move through the next steps listed here.

3.1. Back up and Secure your Database

The database you've created for this workshop lives on the laptop you built it on, and nowhere else. We never want to have important stuff stored in only one place!

Take a moment to make a copy of the current state of your database in some other location - a thumb drive (as its own backup location, or to pass on to a friend so they can save the file on their computer), or, if you're OK with using a cloud service, someplace like Drive, Dropbox, One Drive, or similar.

Tip

You probably can't email your database to yourself, since the file you need will have the extension .db, and that's going to get filtered out of any email application.

To back up your database:

• find and open your DB Browser for SQLite application:

• under file select Save As. Save the database to a second location.

• then, under file select Open Database, and select the .db file that you were working on previously.

Info

We have a tutorial that teaches you how to add more data to your database using SQL, and also how to back up and restore your database at the command line. We can walk through this today, or maybe save it until day 3. We'll see.

Now you're ready to safely move on to today's activities!

3.2 Tutorials

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

Finally, 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.

And then we'll be done for this second day!

4. Bonus Tutorial

We will almost for sure not get to tutorial 8🚀 today.

This tutorial introduces the topic of JOINs - the most important and maybe fanciest topic of all. We'll discuss this together on day 3 for sure.

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.

These notes include:

• Learn to create queries in SQL
• Queries for two tables
• What is JOIN and why are there different kinds?

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 2

You are officially ready to continue on to Day 3. 🚀