SQL stands for Structured Query Language - see, you're halfway through the first sentence of this course, and you're already half asleep! Simply put, SQL is a way for us humans to communicate with databases. And "database" is a pretty generic term for "way for a computer to store data". There are lots of ways for a computer to store data, including (but certainly not limited to):

  • CSV, JSON, or other structured text file
  • Spreadsheet
  • Document database
  • Relational database management system (RDBMS)

That last one -- relational databases or RDBMS -- is the one we're going to concentrate on in this course. While there are many software applications that store data in other ways, including those listed, a vast majority of business-class software applications use some flavor of relational database to store data. These "flavors" are vast - some are open-source solutions, and some are proprietary and commercial. Examples of these are:

  • MySQL (open-source)
  • PostgreSQL (open-source)
  • SQLite (open-source)
  • IBM DB2 (closed-source, commercial)
  • Microsoft SQL Server (closed-source, commercial)
  • Oracle Database (closed-source, commercial)

And, although there are a large number of these flavors, developed by many different companies and organizations, the core of the SQL language is a standard that may be used to communicate with any of them.

Let's dive right in.

The Basics

Relational databases store data in tabular format. If you've ever used Microsoft Excel, Google Sheets, or any other spreadsheet application to store a list of anything, you've probably invented a way to organize data that is very similar to how databases do it - perhaps:

  • Your data is stored in a grid format
  • The columns of the grid represent the attributes of the thing you are storing
  • There is a row in the grid for each one of the things, and maybe that's called a record

For example, if I were to build a spreadsheet of my favorite movies, it might look something like this:

# Title Year Rating
1 Star Wars 1977 PG
2 Apollo 13 2004 PG
3 The Matrix 2007 R

Pretty straightforward. And, if I want to add more attributes to my data -- say, I want to add the studio that produced the movie -- I'd simply add to the columns of data.

# Title Year Rating Studio
1 Star Wars 1977 PG Lucasfilm
2 Apollo 13 2004 PG Universal
3 The Matrix 2007 R Warner Bros.

And, if I want to add more movies, I'd add to the rows of data.

# Title Year Rating Studio
1 Star Wars 1977 PG Lucasfilm
2 Apollo 13 2004 PG Universal
3 The Matrix 2007 R Warner Bros.
4 Forrest Gump 1994 PG-13 Paramount

As simple as this example is, this is precisely how data is structured in a relational database system. To offer up some definitions - the columns are known as the fields or attributes, the rows are known as records, and the rows+columns together is a table. When we have a collection of many tables, that's a database.

In practice, the main difference between a spreadsheet (e.g. Excel) and a database system is that Excel already includes a standard front-end by which an end-user (like you) is able to read and write to the data. Since the use-cases for RDBMSes are much more varied and programmable than a spreadsheet, there's no such standard for relational databases. Instead, we use SQL to talk with the database, and to pass the results on to our front-end of choice, whether that's a custom web application, a PDF report, a commercial front-end like Tableau, or whatever else might be needed.

Using SQL to read data

In this lesson, we'll be looking at ways we can use SQL to read data from a relational database. In a later lesson, we will be looking at using SQL to write to and modify a database.

The SELECT statement

We read data from a database by using the SELECT statement in SQL. Let's look at an example. Suppose we own an e-commerce store - we'd probably have a a database table used to store the products we sell. This table would likely be named Products and look something like this:

Name Price Department
Camera $299 Electronics
Sofa $699 Furniture
Dining Table $1299 Furniture
Toaster $79 Housewares

The SELECT statement allows us to ask the database questions. What products do we sell? How much does the Toaster cost? How many products cost more than $500?

It would be nice if the SQL language knew how to answer your question, in exactly the way you asked it. But, no, it's a computer, so we have to follow a simple formula so that you can ask the question in a way that SQL can understand:

  • Identify the table that holds the information you need
  • Identify the columns that hold the information you need
  • Add any other conditions
  • Then, ask the question in SQL

For instance, if my question is: what are the names of the products we sell?

  • Identify the table that holds the information you need - Products
  • Identify the columns that hold the information you need - Name
  • Add any other conditions - none
  • Then, ask the question:

SELECT Name FROM Products;

There are 3 things about this SELECT statement that I'll point out:

  • It ends with a semicolon ; - this depends on the flavor of RDBMS and the front-end being used, but many databases will require the semicolon at the end of the SQL statement, so all the examples we'll use will include it
  • The column and table names are case-sensitive; again, this will depend on the specific database system, but for illustration's sake, all examples will match the table/column name casing in the database
  • The SELECT and FROM clauses are in ALL CAPS; this is NOT a requirement in SQL, but clauses in examples will be written this way, as it is often more readable

The result that we'll receive is actually another, temporary table - i.e. one that is not part of the database structure or schema as-designed, but is simply a temporary subset of the larger table we've targeted. I like to think of this as a "👶baby table" - a much smaller table that looks a lot like the larger, original table, but only holds the data we asked for:

Name
Camera
Sofa
Dining Table
Toaster

To be clear, "👶 baby table" is not an official SQL term... it's just what I like to call it.

Now, it's up to you to decide if the results provided in this "👶 baby table" are everything you need, or if you need to modify your question in order to get more information.

Hmmm, it would nice if I could see the price too. Can I get a list of all the products we sell, along with the price?

Back to the formula:

  • Identify the table that holds the information you need - Products
  • Identify the columns that hold the information you need - Name and Price
  • Add any other conditions - none
  • Then, ask the question:

SELECT Name, Price FROM Products;

Name Price
Camera $299
Sofa $699
Dining Table $1299
Toaster $79

Sorting

Now that we've seen how to use the SELECT statement in SQL to retrieve rows of data from a database table, let's take one step toward doing something more meaningful. It's pretty rare that we'll want to get all records in a database table, in exactly the order that the records were inserted - this is what we've done thus far.

We'll often want our records in our 👶 baby table to be in a specific order - perhaps alphabetically or sorted by a numeric value. This is going to be based on how the data is ultimately consumed - e.g. in a PDF report for a stakeholder in your company, displayed on-screen for a user of a web application, and so on. Let's continue with our question and answer format for this one:

Can we order the results by price?

  • Identify the table that holds the information you need - Products
  • Identify the columns that hold the information you need - Name and Price
  • Add any other conditions - sort by Price
  • Then, ask the question:

SELECT Name, Price FROM Products ORDER BY Price;

Name Price
Toaster $79
Camera $299
Sofa $699
Dining Table $1299

As we can see, ascending order (from the least to the greatest) is the default when sorting a numeric value. (It's alphabetically from A-Z with a non-numeric value.)

Can we order by the more expensive products first?

  • Identify the table that holds the information you need - Products
  • Identify the columns that hold the information you need - Name and Price
  • Add any other conditions - sort by Price in descending order
  • Then, ask the question:

SELECT Name, Price FROM Products ORDER BY Price DESC;

Name Price
Dining Table $1299
Sofa $699
Camera $299
Toaster $79

Limiting

Another common use-case is that we want to limit the number of rows in our 👶baby table - what we've done so far is just fine for our very small table with 4 rows of data. But what if our Products table contained 10 million rows? The SELECT statements we've written thus far would be very slow, and not likely to be very helpful or meaningful to the person receiving the results. So we must often reduce our results, and that usually means refining the question we're asking.

What is the single most expensive product we sell?

  • Identify the table that holds the information you need - Products
  • Identify the columns that hold the information you need - Name and Price
  • Add any other conditions - sort by Price in descending order and only return 1 row
  • Then, ask the question:
SELECT Name, Price
FROM Products
ORDER BY Price DESC
LIMIT 1;

Same results as before - but only returns a single record, answering our question. Also note that we're separating each fragment of the SQL statement here -- AKA clauses -- by putting each on its own line. As our SQL becomes more complex, I've found this easier to read and comprehend -- especially when first learning -- so we'll do this from now on.

Try it: the baseball database

Let's play ball! Open your copy of the baseball repository in its Codespace (the one you made in class; reopen it from github.com/codespaces rather than making a new one), run bin/rails server in the terminal, and open the port it offers. The app opens on the Run SQL page, a box that runs whatever SQL you type into it. The database has three tables:

teams (id, year, name, park, wins, losses)
players (id, first_name, last_name, bats, throws)
stats (id, team_id, player_id, games, at_bats, runs, hits, doubles, triples, home_runs, rbis)

Try it:

SELECT first_name, last_name FROM players;

Don't forget that semi-colon! You should see the first and last names of all 20,358 players in the database. Then try the question from class, the five winningest seasons ever, using the formula above: which table, which columns, sorted how, how many rows?

If you would rather work in the terminal, save a query in a file in queries/ and run it with bin/query queries/your-file.sql. And if you want to see the database itself, with no page in front of it, bin/rails db opens SQLite's own prompt (type .quit to leave). We will live there in week 5.

Filtering Data

The ability to filter data is probably the most powerful and often-used feature of SQL. For many people, gaining insights from arbitrary sets of data is often the objective of learning SQL in the first place.

We've already learned about the LIMIT clause, which simply whittles down the size of our result set -- the 👶 baby table -- by some number that we provide. However, we often want to specify criteria for filtering the results, rather than a set number like 10. What are our best-selling products? Which sales tactics are producing the best results this month? How many orders did we receive for the new widget this week?

For questions like these, we must use the WHERE clause. The WHERE clause allows us to add more criteria to the questions we ask. Let's jump into an example using our Products table:

Which products cost more than $500?

  • Identify the table that holds the information you need - Products
  • Identify the columns that hold the information you need - Name and Price
  • Add any other conditions - Price is more than $500
  • Then, ask the question:

SELECT Name, Price FROM Products WHERE Price > 500;

Name Price
Dining Table $1299
Sofa $699

A WHERE clause is going to always have a column name (in this case, Price), a comparison operator (> or "greater than") and a value to compare (500). Because the column in question contains numbers, it's going to compare the Price of every row in the table against the numeric value 500 and filter the results to only include those with a price greater than that number. Let's look at what happens with a non-numeric comparison:

Which products are sold in the Furniture department?

  • Identify the table that holds the information you need - Products
  • Identify the columns that hold the information you need - Name and Price again, but we probably want Department now too
  • Add any other conditions - Department is Furniture
  • Then, ask the question:
SELECT Name, Price, Department 
FROM Products 
WHERE Department = "Furniture";
Name Price Department
Sofa $699 Furniture
Dining Table $1299 Furniture

Again, we have a column name (in this case, Department), a comparison operator (= or "exactly equals") and a value to compare ("Furniture"). Because Department is non-numeric, the value to compare must be in quotes. This is necessary to distinguish the comparison value from columns name, other SQL clauses, variables, and the like.

Separate multiple filters/conditions with the AND clause. So if we wanted the answer to:

Which products are sold in the furniture department and cost more than $500?

SELECT Name, Price, Department 
FROM Products 
WHERE Department = "Furniture"
  AND Price > 500;

Matching a pattern

= means exactly equal, and sometimes that is not what you want. LIKE matches a pattern, where % means "anything, or nothing, here." In the baseball database:

SELECT year, name, park
FROM teams
WHERE park = 'Wrigley Field';

returns 107 rows, every season played at the ballpark in Chicago. Change the last line to:

WHERE park LIKE '%Wrigley%';

and you get 108. The extra one is the 1961 Los Angeles Angels, who played their first season at a different ballpark, Wrigley Field (LA). Neither query is wrong; they answer different questions. The question you asked and the question you typed are not always the same question, and the only way to know which one you got is to read the WHERE.

Try it: filtering

On the Run SQL page:

  • Every team that has played at Wrigley Field. Then change = to LIKE and find the extra row.
  • Which team won the most games in 2002? You will probably write ORDER BY wins DESC LIMIT 1. Then change the 1 to a 2. Be honest with yourself about whether you would have checked.

A Star is Born

We've already seen how SQL queries require us to provide the columns that we want to be returned in the 👶 baby table - and how we often need to think about what columns those are when providing clean, concise output. Occasionally though, the columns don't really matter. In these cases, we can use the asterisk or star (*) to denote that we want all columns in a table. Consider a Reviews table, which contains reviews about our products. If we simply want all columns, all rows from this table, we could write a query that looks like:

SELECT * from reviews;

And the result would be everything -- all columns and all rows -- from that table.

Product Rating Body
Camera 4 Takes nice pictures!
Sofa 5 Comfy!
Camera 5 Best camera ever!!!
Toaster 3 Makes toast.

In practice, we're rarely going to use it like this, unless a table contains just a few columns, or when we don't care about concise output, like when we're testing our queries. Or, within an aggregate function, which is where the optional next post picks up.

Order of Clauses

We've seen enough clauses now to talk about the order they go in. Because of the way SQL is executed, the order in which the clauses are written does matter:

  • SELECT - the columns you want in the result
  • FROM - the table(s) involved
  • WHERE - the conditions or constraints
  • ORDER BY - columns by which the results shall be ordered, either numerically or alphabetically
  • LIMIT - how many rows to return

To put it all together, in the baseball database:

Which three teams won the most games in the 2000s?

SELECT year, name, wins
FROM teams
WHERE year >= 2000 AND year < 2010
ORDER BY wins DESC
LIMIT 3;

Run it, then change the 3 to a 4. The 2002 Yankees are third with 103 wins. The 2002 Oakland Athletics also won 103, and they're fourth only because of the order the rows happen to sit in. LIMIT cuts where you tell it to, whether or not that's a sensible place to cut.

Going further (optional)

That's everything we covered in class, and everything you need to read the SQL a chat writes for you in this course. If you're curious about the math side of SQL, counting, averaging and grouping, it's in the optional next post. Otherwise, on to SQL 2: Table Relationships.