This is part one of a series based on the workshop I gave at CodeVA’s Full STEAM Ahead (FSA) Conference called “Make It Happen with APIs: The Great Data Journey and the Awesomeness of APIs.” FSA is an annual, local conference in the Greater Richmond Area for girls in middle school and rising 9th graders. FSA seeks to inspire girls through workshops focused on subjects within science, technology, engineering, arts, and math.
Below, I’ll cover several subjects—servers, databases, querying data, APIs—and while we barely scratch the surface on these topics, my hope is that I provide enough of a foundation to spark interest in learning more about application development. And, hopefully, inspire a young girl or two to consider a career in software engineering.
Let’s dig in: The Database.
Our data journey begins at the source: the server. You may have heard the term “server” before, but what does it actually mean? A server can be a physical piece of hardware, like your phone or laptop computer. Physical servers look like the photo you see there on the right.
They’re typically stored in special closets or data warehouses where they can be maintained and kept safe. A server can also be virtual, meaning that it exists on the internet, spread out across multiple linked servers. This virtual environment of linked servers is what is known as “the cloud.”
So, whenever you hear someone say “the cloud” in the context of computing, you’ll know they’re referring to multiple servers that are linked together and connected to the internet.
Since we’re talking about data in this workshop, the type of server that we’re interested in is the database server. A database server contains databases and gives computers ways to access those databases. You can also configure a database server to perform backups of your databases. In other words, it will regularly store copies of the databases. That way, if something were to happen to the original database, you’d have a backup to use instead. Like a spare tire for a car.
Now, onto the databases themselves.
We’ve learned about the servers where databases live. But what exactly is a database? A database is a system that contains a structured collection of data. There’re lots of different kinds of databases, but the one that we use most frequently in web application development is called a Relational Database.
Relational data is organized by using tables that contain columns and rows, like a spreadsheet. The tables are related by any data that they share. For example, if you and one of your friends both love playing Minecraft, you could say you’re related by a common interest. In a similar way, data tables can be related if they share the same data values.
Data tables should look familiar to you if you’ve ever used a spreadsheet or used tables for organizing data in math and science. They have named columns and a data type associated with each column. They also have rows. Each row in a table represents a set of data, and has a unique ID to distinguish it from all the other rows.
This is an example of what a relational database table looks like. This table is used for storing information about different fruits that you can buy in the grocery store. Whenever you check out of a grocery store, the computer that the cashier uses will access information from database tables like this one.
As you can see, there are 5 rows of data and 4 columns: ID, product name, PLU number, and price.
+----+------------------------+--------------+---------+ | id | product_name | plu_number | price | +----+------------------------+--------------+---------+ | 1 | Passion Fruit | 3038 | 3.00 | | 2 | Grapefruit | 3092 | 1.00 | | 3 | Kiwi Fruit | 4301 | 2.00 | | 4 | Figs | 3397 | 3.00 | | 5 | Persimmon | 4492 | 3.00 | +----+------------------------+--------------+---------+ 5 rows in set
When we create a table, we must give each column a name and say what type of data we want it to store. For example, the product name column has a data type called “variable character” or VarChar for short. This data type is used for storing characters that can vary in length. The ID column has an integer data type, which is used for storing numbers.
And, as you can see, every row contains the information for each fruit.
Next, we’re going to move on to see how we can query data from a database.
So, how do we talk to a database and ask it to give us the data we want? The database doesn’t understand plain English. We can’t just type in a command that says, “Hi database, can you look in the ‘Cats’ table and give me all the records for female kittens that also have stripes?” If we do that, we’ll get an error back from the database, and it will ask us to use proper SQL syntax, which is what it does understand. So, let’s see what a real SQL query looks like…
SELECT * FROM cats WHERE markings = 'stripes' AND gender = 'female' AND age_months <= 6; ... +----+----------+----------+--------+------------+--------+ | id | name | markings | color | age_months | gender | +----+----------+----------+--------+------------+--------+ | 4 | Yoda | Stripes | Grey | 1 | female | | 7 | Gigi | Stripes | Orange | 2 | female | | 12 | PingPong | Stripes | Brown | 1 | female | | 14 | Regina | Stripes | Orange | 3 | female | +----+----------+----------+--------+------------+--------+ 4 rows in set (0.0004 sec)
What you see here is an example of a SQL query that we can give to the database. SQL stands for Structured Query Language, and it’s the most commonly used language that software developers use to communicate with databases. SQL queries are written instructions that we give to a database to tell it what to do with the data. In this case we’re telling it to SELECT some data for us. Specifically, we want it to select all the values in rows of the cats table where the cat’s markings equal “stripes,” and the cat’s gender equals “female” and the cat’s age in months is less than or equal to 6.
We’ve learned that a query is an instruction that we give to the database, in a language that the database understands, called SQL. These queries allow us to communicate with the database and tell it to do certain things with our data…like selecting, creating, updating, or deleting data.
Now, we’re actually going to write some SQL! We’ll use this online app that simulates a database and provides an interface for making data queries.
- Click the link that says ‘Fork’ in the top nav bar.
- Click the link that says ‘Run’ in the top nav bar.
Clicking ‘Fork’ will give you a copy of the original page and will allow you to make changes without affecting the original page. You can always go back to the original link and make another fork if you want to start over.
Ok, so now that we have a forked copy, let’s look at the text on the left side. We have some SQL statements. These statements create two new database tables, and then these statements insert data into those tables. On the right side we have the SQL query that selects the data and returns it to us.
Let’s try running the program and see what happens. As you can see, it returns the data that we’ve asked for. This statement is saying “Give me all the data in the favorite_foods table.” And that’s exactly what it did.
So, let’s try inserting some new data and then querying it.
- Go to the last statement on the left and replace the word ‘avocado’ with a different food.
- Replace the description that says ‘delicious in a salad’ with a different description. Make sure to keep the single quotes around the values.
- Click ‘Run’ again. You should see your new food appear in the chart.
- Change the query on the right to:
SELECT * FROM favorite_foods WHERE food_name = 'your food name here'
- Click ‘Run’ again!
- Bonus: try adding more data and/or changing your query to see what happens.
Now, let’s change our query to get only the food that we just added. All we have to do is add on to the query we already have.
We want to add on to our existing query, so that the query now looks like this:
SELECT * FROM favorite_foods WHERE food_name = ‘avocado’;
Then hit ‘Run’ again. You should now see only the new food that you added to the table.
That wraps up part one of the workshop. In my next post, we’ll explore how to use APIs to work with data.
If these subjects are new to you, I hope you found this post informative. If this guide is a little too novice for you, please consider sharing it with someone young or inexperienced who may get more out of it. It’s on us to foster interest and passion in application development and software engineering.