1. Introducing queries
Welcome back. Now that we understand how data is organized in databases, we can begin drawing insights using SQL queries!
2. What is SQL useful for?
Recall from the last chapter that SQL is used to answer questions both within and across relational database tables. In the library database, we might use SQL to find which books James checked out from the library in 2022. In an HR database, we could query salaries for employees in Marketing and Accounting to determine whether pay across departments is comparable.
3. Best for large datasets
In many organizations, SQL is used as a complement to other tools such as spreadsheet applications.
We can use SQL queries to uncover trends in website traffic, customer reviews, and product sales. Which products had the highest sales last week? Which products get the worst review scores from customers? How did website traffic change when a feature was introduced?
4. Keywords
Let's write our first SQL code! To do that, we will need to learn a few keywords. Keywords are reserved words used to indicate what operation we'd like our code to perform.
The two most common keywords are SELECT and FROM.
Perhaps we'd like a list of every patron's name. The SELECT keyword indicates which fields should be selected - in this case, the name field.
The FROM keyword indicates the table in which these fields are located - in this case, the patrons table.
5. Our first query
Let's put these parts together and write a query. The SELECT statement appears first, followed by the FROM statement. We end the query with a semicolon to indicate that the query is complete.
Notice keywords are capitalized while keeping table and field names all lowercase.
Now let's take a look at the results of our query, often called a result set. The result set lists all patron names, just as we had hoped.
6. Selecting multiple fields
To select multiple fields, we can list multiple field names after the SELECT keyword, separated by commas. For example, to select card number and name, we'd list both field names in the order we'd like them to appear in our result set.
7. Selecting multiple fields
As you might expect, we can select three fields such as name, card_num, and total_fine, by listing all three field names after the SELECT keyword and separating them with commas.
8. Selecting all fields
What if we want to select all of the fields in the patrons table?
We could type out the four individual field names after the SELECT statement, but there's an easier way:
we can tell SQL to select all fields using an asterisk or star, also known as a wildcard character, instead.
9. Let's practice!
Let's get some hands-on experience writing our own queries!