Queries and Data Analysis

Instead of displaying all data as we have done so far using the SELECT keyword, you can present a condition that the database would follow to isolate specific records.

If you are creating a query using the Data In Table window, you can type an expression under the Criteria column for the corresponding column. The expression can follow the logical operators we reviewed in the previous lesson. Here is an example of an expression

> '12/31/1993'

This means that the dates that occur after 1993 would be selected. Here is an example:

If you are writing your SELECT statement, to formulate a condition, you use the WHERE keyword with a basic formula as follows:

SELECT What FROM WhatObject WHERE Expression;

The expressions used in conditions are built using algebraic, logical, and string operators. The Expression factor is called a criterion. Although a group of expressions, making it plural is called criteria, the word criteria is sometimes used for a singular expression also. The expression is written using the formula:

ColumnName=Value

The ColumnName factor must be an existing column of the table. It is followed by the assignment operator. The Value factor is the value that would set the condition. If the value is a word or a group of words (also called a string), you must include it in single-quotes. If it is a number, you can type its numeric value.

To get a list of students who live in Maryland, you would use a statement as follows:

SELECT * FROM Students WHERE State='MD'

To get a list of girls with their names and email addresses, you would execute the following statement:

SELECT LastName, FirstName, Gender, EmailAddress FROM Students WHERE Gender='Female'

Practical Learning: Using the WHERE Condition

1. To get a list of customers who live in Canada, change the statement as follows:
    

   SELECT * FROM Customers WHERE Country='Brazil'

2. Execute it to see the result
    
   
3. Click the Messages tab to see how the number of customers who live in Brazil
    
   
4. To get a list of customers who own their company but the list including only necessary contact information and the list in alphabetical order based on the names of countries, change and execute the following statement:
    

   SELECT CompanyName, ContactName, ContactTitle, Country FROM Customers WHERE ContactTitle='Owner' ORDER BY Country

5. Execute the statement to see the result
    
   
6. Since we already know that the list only includes company owners, we don't need to have that column in the statement. Therefore, to omit the ContactTitle in the list change the statement as follows:
    

   SELECT CompanyName, ContactName, Country FROM Customers WHERE ContactTitle='Owner' ORDER BY Country

7. Execute the statement to see the result
    
   

Boolean Constants

Introduction

Databases and other programming environments provide operators you can use to perform data analysis. The operators used are called logical operators because they are used to perform comparisons that produce a result of true or false (there is no middle result; in other words, something is not half true or half false or "Don't Know": either it is true or it is false).

The TRUE and FALSE Constants

In Boolean algebra, something is considered TRUE when it holds a value. The value is also considered as 1 or Yes. By contrast, if something doesn't hold a value, it is considered non-existent and non-worthy of consideration. Such a thing has a value of FALSE, 0, or No. To retrieve such a value, you can just find out if the value of a field is existent or not.

The comparison for a True or False value is mostly performed on Boolean fields, such a case is the SPHome (which specifies whether a student lives in a single parent home) field of the Students table of the HighSchool database. If a record has a value of 1, the table considers that such a field is True. If the field has a 0 value, then it holds a FALSE value.

To perform a TRUE or FALSE comparison, you can use the WHERE condition.

The NULL Constant

While the True and False constants are used to find out whether a Boolean field has a positive or a negative value, the database provides another constant used to find out whether a field is empty. This can be done using the NULL constant.

When a field holds a value, the value would be considered using the comparison operators we will learn shortly. If the field is empty, it holds a value of NULL.

The NOT Operator

To deny the presence, the availability, or the existence of a value, you can use the NOT operator. This operator is primarily used to reverse a Boolean value. For example, we have learned that FALSE is the opposite of TRUE. In the same way, TRUE is the opposite of FALSE. If you want to compare a value as not being TRUE, the NOT TRUE would produce the same result as the FALSE value. For the same reason, the expression NOT FALSE is the same as TRUE.

The IS Operator

To validate something as being possible, you can use the IS operator. For example, to acknowledge that something is NULL, you can use the IS NULL expression. In the same way, to validate that something is not null, you can use the expression IS NOT NULL.

Neither Microsoft Access nor Microsoft Visual Basic are case-sensitive. Therefore, the words TRUE, True, and true represent the same word. In the same way, FALSE, False, and false represent the same word. This also applies to NULL, Null, and null or NOT, Not, and not or IS, Is, and is.

To get a list of records where the student's email address is not specified, you can use a statement as follows:

SELECT LastName, FirstName, Gender, EmailAddress FROM Students WHERE EmailAddress IS NULL

To see a list of only the students whose records have an email address, you can use a statement as follows: 

SELECT LastName, FirstName, State, EmrgName, EmrgPhone FROM Students WHERE EmrgPhone IS NOT NULL

Logical Operators

Comparison Operators: IN

If you have a series of records and want to find a record or a group of records among them, you can use the IN operator.

 SQL provides many and many other operators that we have not reviewed here.