Day 2: 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 to Create SELECT Queries in SQL

SQL (pronounced either ess-kyu-el or sequel) is a computer language was designed specifically for building, maintaining and using relational databases. It is designed to allow us to create very precise and very efficient commands that the machine understands.

We have a brief introduction to SQL queries in our sql explainer. SQL queries can be used to retrieve and view data from our database; but we can also use them to add rows, modify (or 'update') rows, delete rows, modify the structure of a table or of the whole database, even to create a database and add tables.

Today, however, we'll focus only on queries that allow us to search and retrieve data from our tables. This means that we will focus on queries that use the command SELECT. The syntax of these queries typically looks like this:

SELECT * FROM originators;

The query begins with the command SELECT, then a modifier that tells us what should be selected, using a wildcard character * which means everything, followed by the name of a table that contains the information we want to see. The query ends with a semicolon ;.

Tip

SQL is a computer language, and these languages don't work exactly like natural languages do. They work 100% literally, and they are very, very picky. The use of semicolons in SQL is super important, and so is the use of upper vs. lower case letters in some, but not all, database applications. When you're learning, it's useful to type commands in yourself, doing your best to copy them exactly.

You might not want to view every row in your table, and SQL includes a variety of modifiers you might include.

• 'WHERE' introduces a pattern that must match in order for the command to apply; this is very important for queries that search for particular records;

SELECT * FROM words WHERE english="dragonfly.";

Tip

The equal sign looks for exact matches, so the query above will give us every row in the words table that contains exactly dragonfly. (no spaces, with the period) in the column english. But it won't give us rows where the value in the english column is dragonfly, or ** dragonfly.** or dragonflys or The dragonfly is ... That equal sign is an operator, and luckily there are other operators that can be used to match part of a text string. You might find the operator LIKE to be more useful than = for looking for text strings. There's a handy tutorial about LIKE at W3Schools

• ORDER BY tells the computer how to order the results. This is important for queries in which you want to see the results sorted in a particular way.

SELECT * FROM words ORDER BY english;

Will show you all the rows in your words table, in alphabetical order based on the english column.

Tip

Remember that if you want to use a different sort order than is standard for the character set you're using, you would specify that in a collation sequence. Here's our explainer on character sets, collation sequences, and cases

• COUNT tells the computer to tell you how many records match your query. This is useful if you want to know how many rows you have in a given table, or that match a given set of criteria.

SELECT COUNT(*) FROM words

The query above would tell you how many words you have in your words table.

2. Queries for Two Tables

Your database contains at least two tables, and we will want to be able to retrieve information from both of them. In order to do this, we'll use JOIN clauses. A JOIN clause uses a column that relates the two (or more) tables (you might review our discussion of primary and foreign keys) here.

A query that would produce the result described in that discussion looks like this. Note that because we don't want to see the id from the words table, instead of SELECT * we have to name the columns we want retrieve.

Info

In queries where we are looking at multiple tables, we use table-name.column-name to identify a column from a particular table.

SELECT words.root, words.nicodemus, words.english, originators.originator
FROM words 
INNER JOIN originators ON words.originator_id=originators.id;

Tip

This query looks scary, but don't be intimidated! The SELECT part is long because we only wanted some of the columns from our words table. Then there's this whole INNER JOIN thingy. Let's talk about this for a minute.

3. What is JOIN and why are there different kinds?

Conceptually, a JOIN is just a relationships between two tables, like we talked about in the primary and foreign keys explainer. But those relationships can get kinda fancy. We used INNER JOIN above because it seemed like the easiest one to start with, and it might be the most useful.

If you'd like to learn about the different JOIN types, we've got a tiny discussion here

Back to Day 2

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