FQR - Database Queries Lesson

Database Queries Lesson

Introduction

You already know that databases store information in tables and different tables relate to each other by using primary and foreign keys. However, no matter how fancy and sophisticated storage may be, it would be useless to us if we could not get the information we need.

Have you ever stored something in your closet and then could not find it, because you did not organize your stuff properly? That’s why labeling stuff is important! That’s also how queries work. Queries pull specific information from multiple tables and organize it into a single datasheet. Just about any question you may have about your data can be answered using a query.

This lesson demonstrates how to create queries using database data.

 

Database Tables Review

Let’s briefly review what you have learned about database tables in a previous module. Databases work by storing data on different tables that are related to each other. Two tables are related when they share the primary key field. It’s important to understand this concept before we can discuss queries, the topic of this lesson.

For example, let’s say our database contains a table for employees and another table for employee phone numbers. Both tables have a primary key, EmployeeID.

Can you spot a primary key in the graphic below?

Queries

Now that you have a clear understanding of how table relationships work, let’s talk about queries. Queries pull specific information from multiple tables and organize it into a single datasheet. 

Database management software uses Structured Query Language (SQL), a domain-specific programming language designed for managing data held in a relational database management system. In this lesson, we will use spreadsheet features to mimic the query process. While this approach is not as robust as true database management, it does allow us to manipulate data in tables. 

Here are the steps: 

Open the Traffic Violations Database document we created in a previous lesson. (which is linked in book icon above) 

On the Master table worksheet, select all data and click Insert and then PivotTable. 

You should see MASTER under Table/Range. If you don’t, then type in MASTER. 

Select New Worksheet. 

Select Add this data to the Data Model 

Click OK 

You can now see all table fields on the right-hand side, under PivotTable Fields. 

ACTIVE is selected by default so click ALL, to see all tables. 

We can now see all tables and all fields, for each table. However, we need to connect all tables. 

Click Relationships under Analyze tab. 

Click New, make selections as shown, and click OK. As you can see, we are linking related tables to the master table, using the key field, License Number. 

12. Click New again, make selections as shown, and click OK.

13.You will now see table relationships. Click Close

14.Let’s run a query. Drag Violation Type and License Number fields to Rows.

15.You should now see a summary of violation types sorted by license number.

   

Queries can do much more than simply select data from different tables, though. They can also perform calculations and provide easy access to information about totals, averages, and counts.

Let’s add Violation Fee field, to display individual fees and the grand total, by simply dragging Violation Fee field to Values. You can now see fees sorted by license number and violation type.

 

  

Using Recommended PivotTables

Sometimes it’s easier to use the recommended PivotTable options, instead of trying to figure out which fields to use. Use the Traffic Violations Database Query document you created for this lesson to create a PivotTable from Violations table to display the number of violation types for each license number. View the demo video for step-by-step instructions.

 

  

One of the common issues database users are dealing with is missing data. Nested queries are one way to deal with missing data.

 

  IMAGES CREATED BY GAVS