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.

Introduction to SQL

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.
Author Note 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

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 Query Wizard. This would display the New Query dialog box:
The Mew 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.

Practical Learning: Creating a Query Using The Wizard

  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 Query Wizard
  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

    Simple Query Wizard
  8. Click Next
  9. Change the name of the query to AssetsInventrory

    Simple Query Wizard
  10. Click Finish
Query Design

 
Introduction

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: 
Query Type
Query Setup
Show/Hide
Accessing the SQL Code of 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
SQL View
  • Right-click the query's title bar and click SQL View
  • Right-click anywhere in the query window and click SQL View
The Show Table Dialog Box

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:
Query Design
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 Show Table
  • 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

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 menu of a query window
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:
Using the Splitter
Selecting the Columns

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 Add Columns

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:
Query
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:

    Query Design: Adding One Field
  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

    Query Design: Adding Various Fields
  5. Notice all the selected fields that have been added to the query

    Query Design: Viewing the Fields
  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
Executing a Query

In the Navigation Pane, a query is represented by an icon Query 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 Run
    •  You can click the View button View 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
 
   
 
Selecting a Column

Sometimes, the idea of using a query is to test data or verify a condition. Therefore, a query can provide just a temporary means of studying information on your database. When performing the assignment or when testing values before isolating an appropriate list, you can add, insert, delete, replace or move fields at will. We have already covered different techniques of adding or inserting fields, from the Query Wizard or from a list of fields in the top section of the query window. Some other operations require that you select a column from the bottom section of the query window:
  • To select a field in the lower section of the view, click the tiny bar of the column header:

    Selecting a Column in a Query

    The whole column will be selected
  • To select a range of columns, click the column header of one at one end, press and hold Shift, then click the column header at the other end
Since selecting a column in the Query window is a visual operation, there is no equivalent in SQL.

Removing a Column From a Query

As seen above, a query is built by selecting columns from the originating list and adding them. If you do not need a column anymore on a query, which happens regularly during data analysis, you can either delete it or replace it with another column:
  • To delete a column:
    • Once it is selected, you can press Delete
    • Right-click the column header and click Cut
  • To delete a group of columns, select them and press Delete
To remove a column from a SQL statement, simply delete it. An example would be:
SELECT EmployeeName, DateHired, Title FROM Employees;
To
SELECT EmployeeName, Title FROM Employees;
 
Replacing a Column

To replace a column, click the arrow on the combo box that displays its name and select a different field from the list:
To replace a column, click the arrow on the combo box that displays its name and select a different field from the list
To replace a column from a SQL statement, simply change its name to the name of another existing column of the same table or query. An example would be:
SELECT EmployeeName, DateHired, Title, Salary FROM Employees;
To
SELECT EmployeeName, DateHired, EmailAddress, Salary FROM Employees;
 
Moving a Column

Columns on a query are positioned incrementally as they are added to it. If you do not like the arrangement, you can move them and apply any sequence of your choice. Before moving a column or a group of columns, you must first select it. Then:
  • To move a field, click its column header once. Click it again and hold your mouse down, and drag in the direction on your choice
  • To move a group of columns, first select the group and then proceed as if it were one column
Since moving a column in the query window is a visual operation, there is no equivalent in SQL. Otherwise, in the SQL statement, you can either edit the statement or delete the field in one section to put it in another section. An example would be:
SELECT EmployeeName, DateHired, EmployeeNumber, Salary FROM Employees;
To
SELECT EmployeeNumber, EmployeeName, DateHired, Salary FROM Employees;
 
Practical Learning: Manipulating Fields

  1. The Bethesda Car Rental1 database should still be opened.
    On the Ribbon, click Create and, in the Queries section, click Query Design
  2. In the Show Table dialog box, double-click Cars
  3. Click Close
  4. In the list of fields, double-click Make, Model, Doors, Category, Available, and Condition
  5. To preview the query, on the Ribbon, click the View button View
  6. After viewing the query, to switch back to Design View, on the Ribbon, click the View button Datasheet View
  7. To replace a field, in the lower section of the Design View, click Doors and notice that an arrow of a combo box appears
  8. Click the arrow of the Doors combo box and select Car Year

    Query Design: Selecting a Field
  9. Scroll to the right on the lower section of the view to display the first empty field
  10. To add a new field, in the lower section of the query window, click Condition and press Tab.
    Notice the combo box
  11. Press and hold Alt, then press the down arrow key and release Alt. This displays the list of the combo box
  12. Press the arrow keys a few times until Picture is selected, then press Enter
  13. Scroll back to the left
  14. To insert a field, drag Tag Number from the Cars list and drop it on top of Category
  15. Notice that the newly inserted field has been added to the left of the field it was dropped on
    (In the same way, you can select various fields and decide to insert them to the left of a field of your choice)
  16. Scroll to the right side of the lower section of the view until you can see Picture.
    To delete a field, in the lower section of the view, position the mouse on the tinny horizontal bar above Picture until the mouse turns into a down pointing arrow:

    Query Design: Selecting a Field
  17. Click. Notice that the whole column is selected
  18. Press Delete. Notice that the field is removed from the query
  19. To view the query, click the View button View
  20. To switch the query back to Design View, on the Ribbon, click the View button Datasheet View
  21. Scroll back to the left of the lower view and make sure you can see the Tag Number and the Make columns
    In the lower section of the view, click the bar on top of Tag Number and release the mouse
  22. Click the Tag Number header bar again and hold your mouse down
  23. Notice a vertical line that guides you. Drag left until the vertical guiding line gets between Make and Model:

    Query Design: Moving a Column
  24. Release the mouse 
  25. To view the query, on the Ribbon, click the View button View
  26. Switch the query back to Design View
  27. Click and hold your mouse on the bar on top of Category
  28.  Drag right to Condition to select the Category, the Available, and the Condition columns
  29. Release the mouse 
  30. Click and hold your mouse again on the bar on top of Category
  31. Drag left until the vertical line is between the Tag Number and the Model columns
  32. Release the mouse
  33. To run the query, right-click its title bar and click Datasheet View 
  34. To close the query, double-click its system icon
  35. A message asks you whether you want to save the query, click No (this was a test; it was just a test)
Data Entry on a Query

A query uses the same approach of a table to present its data: it is made of columns and rows whose intersections are cells. Although the main purpose of a query is to either prepare data for analysis or to isolate some fields to make them available to other database objects, as done on a table, data can be entered in a query.
Data entry on a query is done the same as on a table: data is entered into cells. The Enter, Tab and arrow keys are used with the same functionality. Like a table, a query provides navigation buttons on its lower section, allowing you to move to the first, the previous, the next, the last or any record in the range of those available.

Practical Learning: Performing Data Entry on a Query

  1. From the Navigation Pane, double-click the AssetsInventory query to open it
  2. Click the first empty field under the Category column
  3. Type Laptop and press Enter
  4. Complete the query as follows:
     
    Category Make  Model  Date Acquired Purchase Price
    Desktop Computer Dell Precision T3500 10/08/2010 1345.75
    Monitor AOC 19-Inch Class 10/25/2010 135.95
    Printer HP LaserJet CP3525x 10/08/2010 1295.95
    Monitor ASUS VW246H 24-Inch Widescreen 10/09/2010 245.95
    Laptop Gateway  NV79C35u 12/05/2010 895.25
    Desktop Computer  Dell  Vostro 430 10/08/2010 1025.50
    Digital Camera Nikon Coolpix P100 11/06/2010 375.85
    Printer Xerox Phaser 3600/DN 10/22/2010 985.50
    Monitor ViewSonic VA2231WM 22-Inch 10/09/2010 185.85
    Desktop Computer Lenovo Ideacentre K320 3019-1MU 10/08/2010 955.50
    Monitor ViewSonic VA2231WM 22-Inch 10/09/2010 185.85
    Server Dell  PowerEdge T710 10/15/2010 1625.85
    Laptop Dell Latitude E5510 12/22/2010 1118.85
    Monitor Samsung P2250 22-Inch Widescreen 12/22/2010 195.95
  5. After using the query, close it
Query Printing

Like tables, queries provide you with a fast means of printing data. Once again, this should be done when you need a printed sheet but not a professionally printed document. Data printing on a query is done with the exact same approaches and techniques as for a table.

A Query or a SQL Statement as a Record Source

 
A Query as a Record Source

When creating a form or a report that would be used to present data to the user, we selected a table as the source of data. In the same way, if you have created a query that holds some records, you can use it as the base of data for a form or report.
If you delete the form or report, the query would still exist because it is a separate object, but it would lose its data holder.

Practical Learning: Specifying a Query as a Record Source

  1. On the Ribbon, click Create
  2. In the Forms section, click Form Design
  3. Right-click the form and click Form Header/Footer
  4. If the Property Sheet is not displaying, double-click the intersection of the rulers Intersection of the Rulers.
    In the Property Sheet, click the Data tab and click Record Source. Click the arrow of its combo box and select AssetsInventory
  5. Right-click the title bar of the form and click Save
  6. Type Assets Inventory
  7. Click OK
  8. Design the form as you want. Here is an example:

    Assets Inventory
  9. Preview the form

    Form Design
  10. Close the form
  11. When asked whether you want to save the change, click Yes
Using a SQL Statement as a Record Source

Instead of first formally creating a query before using it for a form or report, you can select the data from a table or a query and use it on the form or report. Behind the scenes, Microsoft Access would create the SQL expression that is directly applied to the form or report. Such a query is not saved as an object. This technique is used when you do not need a formal query as the base of data for a form or report.
There are various techniques you can use to create a query specifically made for a form or a report:
  • You can start the Form Wizard or the Report Wizard. Then, in the first page of the wizard, select the Tables/Queries that holds the data, and complete the wizard. When you finish with the wizard, a SQL statement would be created for the form or report
  • You can start by displaying a form or report in Design View. Then, in the Property Sheet, click Record Source and click its ellipsis button Ellipsis. This would open the Query Builder window where you can select a table or a query from the Show Table dialog box and proceed to building a query
After creating the form or report, if you delete it (the form or the report), the expression would be lost also.

Practical Learning: Using a SQL Statement as a Record Source

  1. On the Ribbon, click Create
  2. In the Reports section, click Report Design
  3. Right-click the title bar of the report and click Save
  4. Type Employees Payroll Information and press Enter
  5. Double-click the intersection of the rulers to access the Property Sheet of the report
  6. In the Data tab of the Property Sheet, click Record Source and click its browse button
  7. In the Show Table dialog box, click Employees
  8. Click Add
  9. Click Close
  10. In the list of the Employees window, double-click EmployeeNumber, FirstName, LastName, Title, and BillingRate

    Query Design
  11. To see the SQL statement, right-click the Query Builder title bar and click SQL View
    SELECT  Employees.EmployeeNumber, Employees.FirstName, 
 Employees.LastName, Employees.Title, Employees.BillingRate
FROM  Employees;
  12. To see the result, right-click the title bar and click Datasheet View
  13. Right-click the title bar and click Close
  14. When asked whether you want to save, read the whole message box and click Yes
  15. Notice that the Record Source is filled with a SELECT statement.
    Design the report as you see fit
  16. Print Preview the report
  17. Save and close the report
Lesson Summary

  
Exercises 

 
Yugo National Bank

  1. Open the Yugo National1 Bank
  2. Use the Simple Query Wizard to create a query based on the Customers table and that includes the AccountNumber and CustomerName fields. Save the query as Customers Accounts
  3. Use the Design View to create a query based on the Employees table and that includes the following fields: EmployeeNumber, LastName, FirstName, and Title. Save the query as Employees Identification
Watts A Loan

  1. Open the Watts A Loan1 database
  2. Use the Simple Query Wizard to create a query based on the Employees table and that includes the following fields: FirstName, LastName, City, and HomePhone. Save the query as Employees Contact Information
World Statistics

  1. Open the World Statistics1 database
  2. Use the Simple Query Wizard to create a query that includes the CommonName, the Nationality, and the Capital fields of the Countries table. Save the query as Countries Identities
  3. Using the Design View of the Countries Identities query to add the Independence and the NationalHoliday fields
  4. Save and close the query
US Senate

  1. Open the US Senate1 database 
  2. Use the Design View to create a query based on the Senators table and populate it with SenatorName, Party, State, and WebSite
  3. Save the query as Senators Identifications and close it

No comments:

Post a Comment

Pages (46)1234 Next
Subscribe to: Post Comments (Atom)

Related Scripting

Related Posts Plugin for WordPress, Blogger...

Disqus for Functions