Introduction to SQL Queries: SELECTing Rows FROM Your Data

Welcome back. Now that we understand how data is organized in databases, we can begin drawing insights using SQL queries. 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. In many organizations, SQL is used as a complement to other tools such as spreadsheet applications. If the data we're interested in can fit in a spreadsheet and does not have many relationships to other data of interest, we can analyze it in a spreadsheet. But for sprawling and diverse data, such as the data related to a retail platform, organizing the data in a database is best. Then we 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? SQL shines when an organization has lots of data with complex relationships. 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 our library has. 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. Let's put these parts together. Here's how the query should be written. The select statement appears first, followed on the next line by the from statement. It's best practice to end the query with a semicolon to indicate that the query is complete. We also capitalize keywords 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. Note that we have not changed our database by writing this query. The tables, including the patrons table, are exactly the same as before. We wrote the query in order to share our results. We can save the SQL code we have written so that our collaborators can use it to query the database themselves. We'll cover saving queries in a later lesson. 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. Notice that this does not have to match the order the fields are presented in the table. Listing name before cardnum means that name appears first in the results. As you might expect, we can select three fields such as Name, Cardnum, Parent, and Total. Fine by listing all three field names after the select keyword and separating them with commas. What if we'd like to select all four fields in the Patrons table, We could list out the four field names after the select statement, but there's an even easier way. We can tell SQL to select all fields using an asterisk in place of the four field names. Sam.