Introduction to Data Analysis

Querying a Database  

Introduction

After creating one or more tables in a database and populating it (them) with values, one of the next steps you would take is to examine or analyze the values in the database. Analyzing the values in a table is also referred to as querying.

To query the records of a database, you can use Boolean algebra combined with some operators. Boolean Algebra works on logical statements. A statement is a sentence that acknowledges a fact or a possibility. That fact is eventually evaluated as being true or false. There are three main types of logical statements:

• Some statements can clearly be evaluated as being true or as being false (or at least they would hardly change). Examples include: "An hour contains 80 minutes", "Student A is a girl", "Gabon was colonized by Portugal", or "Argentina won the 1998 World Cup"
• Some statements are true at one time but can change to false at another time. Examples include: "Melbourne is the capital of Australia", "It is now 12:00 PM", or "It is raining"

• Some statements cannot be easily evaluated. Such statements could be vague. Examples include: "This person is too young", "It is going to be a sunny day tomorrow". Some other statements, although they can logically be evaluated, because of the way they are stated, they appear as vague. Examples include: "Santiago is a large country" (first of all, Santiago is not a country; even if Santiago were a country, what is the criterion for evaluating that a country is large?)

These are the types of evaluations you make when analyzing the records of your database.

In Lesson 2, we saw how to create a table and how to populate it with a few records. In Lesson 3, we saw how to present the data of a table to a user but through a form. In that lesson, we presented all the records of a table to a user. A query is a technique of using all data or only selecting a few records to present to the user. Data used on a query can originate from a table, another query, or a combination of tables and/or queries.

The universal or the most popular language used to query a database is called the Structured Query Language and abbreviated SQL.

SQL can be pronounced Sequel or S. Q. L. In our lessons, we will consider the Sequel pronunciation. For this reason, the abbreviation will always be considered as a word, which would result in "A SQL statement" instead of "An SQL statement". Also, in our lessons, we will regularly write, "The SQL" instead of "The SQL" language, as the L already represents Language.

Like most other database environments, Microsoft Access supports SQL. Like every computer language, the SQL comes with its syntax, vocabulary, and rules. The SQL is equipped with keywords that tell it what to do and how to do it.

The most fundamental word used in SQL is called SELECT. As its name indicates, when using SELECT, you must specify what to select.

There are various ways you create a query in Microsoft Access.

The Query Wizard offers the simplest approach to creating a query where in step by step you specify the data that the query will make available. The wizard presents the tables that are part of the database and you select which fields you need. Such a query is called a Select Query.

To use the Query Wizard, on the Ribbon, you can click the Create tab and, in the Queries section, click Query Wizard . This would display the New Query dialog box:

On the New Query dialog box, you can click Simple Query Wizard and click OK. The first page of the Simple Query Wizard expects you to choose the origin of the query as a table or an already created query.

When creating a query, in reality you create a SQL expression but Microsoft Access takes care of creating a SQL statement behind the scenes for you. As mentioned already, when creating a query, you must select a table. In SQL, this is equivalent to the following formula:

SELECT What FROM WhatObject;

The FROM keyword is required. The WhatObject of our formula is the name of the table or query you would select from the wizard. An example would be:

SELECT What FROM Employees;

The SQL is not case-sensitive. This means that SELECT, Select, and select represent the same word. To differentiate SQL keywords from "normal" language or from the database objects, it is a good idea to write SQL keywords in uppercase.

A SQL statement must end with a semi-colon.

The What factor of our formula represents the field(s) you select from a table or query.

1. Start Microsoft Access
2. From the resources that accompany our lessons, open the Bethesda Car Rental1 database
3. On the Ribbon, click Create
4. To create a query, in the Queries section, click the Create Wizard button
5. In the New Query dialog box, click Simple Query Wizard and click OK
6. In the Tables/Queries combo box, select Table: Company Assets
7. From the Available Fields list box, double-click Category, Make, Model, Date Acquired, and Purchase Price
    
   
8. Click Next
9. Change the name of the query to AssetsInventrory
   
   
10. Click Finish

Query Design

Query design consists of selecting the fields that would be part of a query. We previously learned that fields could be added to a query by using the Query Wizard. Fields can also be added by designing a query. To proceed with this approach, the query should be displayed in Design View. You can also write a SQL statement to select the fields for a query:

• To display a query in Design View, from the Navigation Pane, you can right-click a query and click Design View
• To start designing a new query, in the Queries section of the Create tab of the Ribbon, click Query Design

This would display the Show Table dialog box that allows you to specify the table or query that holds the fields you want to use in the intended query.

When a query is displaying in Design View, the Design tab of the Ribbon displays the buttons used for a query: 

When a query is displaying in Design View, to access its code:

• On the Ribbon, click the arrow of the View button and click SQL View

• Right-click the query's title bar and click SQL View
• Right-click anywhere in the query window and click SQL View

When starting a new query, you must specify where data would come from. If you are manually writing your SQL statement, from our previously seen syntax, you must replace the WhatObject factor by the name of a table or query. If you are visually creating the query, the Design View displays a list of already existing tables in the Tables tab, and a list of already created queries in the Queries property page:

A simple query can have its data originate from a single table. In the Show Table dialog box, to choose the table that holds the information needed for this query, you can click that table and click Add. You can also double-click it. After selecting a table, some tables, a query, or some queries, you can click the Close button of the Show Table dialog box. If the Show Tables dialog box is closed or for any reason you want to display it:

• In the Query Setup section of the Design tab of the Ribbon, click the Show Table button
• Right-click anywhere on the query window and click Show Table...

Practical Learning: Introducing Query Design

1. On the Ribbon, click Create and, in the Queries section, click Query Design
2. Notice that you are presented with a list of existing tables.
   On the Show Table dialog box, click Cars
3. Click Add
4. Click Close

The Query window is presented like a regular window. If the database is set to show overlapped windows, its title bar displays its system button on the left section. This can be used to minimize, maximize, restore, move, resize, or close the window. Like all Microsoft Access window objects, the title bar displays a special menu when right-clicked:

The right section of the title bar displays the classic system buttons of a regular window.

In the top wide area of the Query window, the query displays an object (table(s), query (queries)) or a group of objects that was selected to create the query. The lower portion of the query displays boxes that would be used to perform various operations related to the query. The upper and the lower sections of the query window are separated by a splitter bar that you can use to resize them by dragging the splitter bar up or down:

To create the fields for a query, you use the table(s) or query(queries) displayed in the upper section of the window. Once you have decided on the originating object(s), you can select which fields are relevant for your query:

• To select one field from the list, just click it
• To select many fields on the same range, you can click one of them, press and hold Shift. Then click one field on the other end of the desired range
• To select fields at random, click one of the desired fields, press and hold Ctrl; then click each one of the desired fields
• To select all fields, you can click the * line on the list of fields

To make a field participate in a query, you have various options:

• Once you have made your selection on the list in the top part of the query window, you can drag it and drop it in the bottom section of the query window
• Instead of dragging a field or all fields, you can either double-click a field to add it to the query, or double-click the line with * to add all fields to the query
• In the bottom part of the query window, click an empty Field box to show a combo box. Then click the arrow of that combo box and select an item from the list:

In the SQL, to add one column to a statement, replace the What factor of our formula with the name of the column. An example would be:

SELECT FirstName FROM Employees;

If you want to include more than one field from the same table, separate them with a comma. For example, to select the first and last names of a table named Employees, you would write the statement as follows:

SELECT FirstName, LastName FROM Employees;

To include everything from the originating table or query, use the asterisk * as the What factor of our formula. Here is a statement that results in including all fields from the Employees table:

SELECT * FROM Employees;

The name of a field can be delimited by square brackets to reduce confusion in case the name is made of more than one word. The square brackets provide a safeguard even if the name is in one word. Based on this, to create a statement that includes the first and last names of a table named Employees, you can write it as follows:

SELECT [FirstName], [LastName] FROM [Employees];

To identify a field as belonging to a specific table or query, you can associate its name to the parent object. This association is referred to as qualification. To qualify a field, type the name of the object that is holding the field, then add a period followed by the name of the field. The basic syntax of a SELECT statement would be:

SELECT WhatObject.WhatField FROM WhatObject;

Imagine you want to get a list of people by their last names from data stored in the Employees table. Using this syntax, you can write the statement as follows:

SELECT Employees.LastName FROM Employees;

Or

SELECT [Employees].[LastName] FROM [Employees];

In the same way, if you want to include many fields from the same table, qualify each and separate them with a comma. To list the first and last names of the records from the Employees table, you can use the following statement:

SELECT Employees.FirstName, Employees.LastName FROM Employees;

Or

SELECT [Employees].[FirstName], [Employees].[LastName] FROM [Employees];

If you want to include everything from a table or another query, you can qualify the * field as you would any other field. Here is an example:

SELECT Employees.* FROM Employees;

Or

SELECT [Employees].* FROM [Employees];

You can also use a combination of fields that use square brackets and those that do not:

SELECT FirstName, [LastName] FROM Employees;

The most important rule is that any column whose name is in more than one word must be included in square brackets.

You can also use a combination of fields that are qualified and those that are not

SELECT [Employees].[FirstName], LastName FROM [Employees];

Practical Learning: Selecting Fields to Build a Query

1. From the list of fields, click and drag Make, then drop it anywhere on the first empty field on the lower section of the view:
    
   
2. Click Car Year
3. Press and hold Shift, then click Doors and release Shift. Notice that three fields have been selected
4. Drag the group of items and drop it on the empty column right to Make
    
   
5. Notice all the selected fields that have been added to the query
    
   
6. To view the SQL code of the query, right-click its title bar and click SQL View
   

   SELECT Cars.Make, Cars.[Car Year], Cars.Category, Cars.Doors
   FROM Cars;

7. To save and close the query, right-click its title bar and click Close
8. When asked whether you want to save the query, click Yes
9. Type Cars Information as the name of the query and press Enter

In the Navigation Pane, a query is represented by an icon and a name.

Executing a query consists of viewing its results but the action or outcome may depend on the type of query. To view the result of a query:

• If the query is currently closed, from the Navigation Pane:
  • You can double-click it
  • You can right-click it and click Open
• If the query is already opened and it is in Design View, on the Ribbon:
  • You can click the Run button
  •  You can click the View button or you can click the arrow of the View button and click Datasheet View

If you manually write a SQL statement and want to execute it, change the view to Datasheet View.

Practical Learning: Executing a Query

1. In the Navigation Pane, double-click Cars Information to preview the query
2. Right-click the title bar of the Query window and click SQL View
3. Change the code as follows:
   

   SELECT Make,
          Model,
          [Car Year],
          Category,
          Available,
          Condition
   FROM Cars;

4. To execute the query, right-click the window's title bar and click Datasheet View
5. Save and close the query

SELECT EmployeeName, DateHired, Title FROM Employees;

SELECT EmployeeName, Title FROM Employees;

SELECT EmployeeName, DateHired, Title, Salary FROM Employees;

SELECT EmployeeName, DateHired, EmailAddress, Salary FROM Employees;

SELECT EmployeeName, DateHired, EmployeeNumber, Salary FROM Employees;

SELECT EmployeeNumber, EmployeeName, DateHired, Salary FROM Employees;