Filtering Records

Setting a Criterion   Introduction A filter is a technique of an expression that behaves like a funnel: it selects some records, based on a condition or rule, and excludes the records that don't follow the rule. The condition of a filter is also called a criterion. The plural is criteria but it can also be used in singular. To create a filter, you write a SQL statement such as those we introduced in the previous lesson. This means that you must specify the column(s) to SELECT and the data source using FROM. Here is an example: SELECT FirstName, LastName, Gender FROM Students;   To specify a criterion in a filter, you use the WHERE keyword. The formula to follow is: SELECT WhatColumn FROM WhatObject WHERE Condition;   The new section in this formula is the WHERE Condition. When you ask the interpreter to execute this type of statement, it will examine the Condition. The Condition must be valid and must produce a Boolean constant. Introduction to Boolean Constants 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 exists or not. The comparison for a True or False value can be performed on a Boolean field, such a case is the SPH (which specifies whether a student lives in a single parent home) field of the Students table of the ROSH1 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. The NULL Constant While the True and False constants are used to find out whether a Boolean expression 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.   Practical Learning: Analyzing Data With Operators Display the MySQL prompt and execute the following statement: USE ROSH1;

 

Filtering Records With Boolean Operators

 

Equality =

The equality operator is used to compare two values for similarity. The syntax of this operation is: Value1 = Value2   If Value1 and Value2 hold the same value, the comparison produces a TRUE result. If they hold different values, the comparison renders a FALSE value.

 

Practical Learning: Applying the Equality Operator

1. To use the equality operator, execute the following statement:
    

   mysql> SELECT FirstName, LastName, Gender -> FROM Students -> WHERE Gender = 'Male'; +-----------+----------+--------+ | FirstName | LastName | Gender | +-----------+----------+--------+ | Sebastien | Porter | Male | | Koko | Domba | Male | | Nehemiah | Dean | Male | | Santos | Pacheco | Male | | Mohamed | Husseini | Male | | Dean | Chen | Male | | Justin | Vittas | Male | | Ismael | Zara | Male | | Anselme | Waters | Male | | Danilo | Chico | Male | | Paul | Farms | Male | | Lester | Bell | Male | | Christian | Liss | Male | | Clint | Fuller | Male | | Thomas | Moore | Male | | Tim | Amorros | Male | | Maurice | Walken | Male | | Charles | Edelman | Male | | Arthur | Milley | Male | | Martin | Davis | Male | | Arthur | Junger | Male | | George | Orion | Male | | Albert | Linken | Male | | Ralph | Hagers | Male | | Charles | Laurel | Male | +-----------+----------+--------+ 25 rows in set (0.07 sec) mysql>

2. Notice the list includes only male students

 

Inequality <>

To find out if two fields hold different values, you can use the inequality operator which is represented by <>. Its syntax is: Value1 <> Value2   This comparison is performed between Value1 and Value2. If they hold different values, then the comparison produces a TRUE value. If they hold the same value, the comparison produces FALSE. This shows that the equality (=) and the inequality (<>) operators are opposite each other.

 

Practical Learning: Applying the Inequality Operator

1. To use the inequality operator, execute the following statement:
    

   SELECT FirstName, LastName, Gender FROM Students WHERE Gender <> 'Female';

2. Notice the list includes the same result as the previous

Less Than <

The "Less Than" operator uses the following syntax: Value1 < Value2   If Value1 holds a value that is lower than that of Value2, the comparison produces TRUE. If Value1 holds a value that is greater than or similar to that of Value2, the comparison renders FALSE.

 

Practical Learning: Applying the Inequality Operator

1. To get a list of students born before June 1988, execute the following statement:
    

   mysql> SELECT FirstName, LastName, Gender, DOB -> FROM Students -> WHERE DOB < '1988-06-01'; +------------+----------+--------+------------+ | FirstName | LastName | Gender | DOB | +------------+----------+--------+------------+ | Sebastien | Porter | Male | 1985-02-12 | | Antoinette | Clarck | Female | 1986-04-10 | | Janet | West | Female | 1986-06-02 | | Catherine | Chang | Female | 1987-06-12 | | Nehemiah | Dean | Male | 1985-12-02 | | Sherryl | Ashburn | Female | 1985-10-07 | | Santos | Pacheco | Male | 1988-05-05 | | Mohamed | Husseini | Male | 1988-01-05 | | Dean | Chen | Male | 1987-05-02 | | Brenda | Lobo | Female | 1986-10-05 | | Suzanna | Verde | Female | 1988-03-12 | | Danilo | Chico | Male | 1987-06-20 | | Paul | Farms | Male | 1986-04-30 | | Lester | Bell | Male | 1985-12-06 | | Arlette | Duma | Female | 1985-09-28 | | Harriette | Sans | Female | 1986-08-02 | | Clint | Fuller | Male | 1986-04-19 | | Thomas | Moore | Male | 1988-04-22 | | Bernadette | Howerson | Female | 1986-06-11 | | Charles | Edelman | Male | 1986-12-26 | | Arthur | Milley | Male | 1985-05-01 | | Martin | Davis | Male | 1986-10-14 | | Ann | Miller | Female | 1987-06-21 | | Victoria | Milchen | Female | 1988-04-18 | | Martine | Quarles | Female | 1986-02-04 | | Martha | Bastens | Female | 1985-02-12 | | Jeannette | Hutchins | Female | 1988-04-24 | | George | Orion | Male | 1985-12-22 | | Albert | Linken | Male | 1988-05-14 | +------------+----------+--------+------------+ 29 rows in set (0.00 sec) mysql>

2. Notice the result

Less Than or Equal <=

When comparing two values, you may want to know whether two fields hold the same value or if one is lower than the other. This comparison can be performed with the "Less Than Or Equal To" operator. It is represented by <= and its formula is: Value1 <= Value2   If both operands (Value1 and Value2) hold the same value, then the comparison produces a TRUE result. If Value1 holds a value that is lower than that of Value2, the comparison still produces a TRUE result. By contrast, if the value of Value1 is higher than that of Value2, the comparison renders a FALSE result. Notice that the > and the <= operators are opposite each other.

 

Practical Learning: Using the Less Than Or Equal Operator

1. To get a list of students born before 1990, execute the following statement:
    

   SELECT FirstName, LastName, DOB, EmailAddress FROM Students WHERE DOB <= '1990-01-01';

2. Notice that the result includes students whose year of birth is less than 1990

Greater Than >

The > operator is used to find out whether one value is "Greater Than" another. Its syntax is: Value1 > Value2   The operation is performed on the values of Value1 and Value2. If Value1 holds a value greater than that of Value2, then the comparison produces TRUE. Otherwise, the comparison produces FALSE. That is, if the value of Value2 is greater than or equal to that of Value1, then the comparison produces FALSE.

Greater Than or Equal >=

If you have two values and want to find out whether they hold similar values or the first is greater than the second, you can use the >= operator whose syntax is: Value1 >= Value2 If both Value1 and Value2 hold the same value, then the comparison renders a TRUE result. Similarly, if the left operand, Value1, holds a value greater than that of the right operand, Value2, the comparison still produces TRUE. If the value of Value1 is less than the value of Value2, the comparison produces a FALSE result. Therefore, < and >= are opposite.

 

Filtering Records With Logical Operators

 

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.

 

Practical Learning: Using the NOT Operator

1. To get a list of female students by denying those who are male gender, execute the following statement:
    

   mysql> SELECT LastName, FirstName, Gender, DOB -> FROM Students -> WHERE NOT (Gender = 'Male'); +--------------+------------+--------+------------+ | LastName | FirstName | Gender | DOB | +--------------+------------+--------+------------+ | Hoak | Suzie | Female | 1991-10-05 | | Clarck | Antoinette | Female | 1986-04-10 | | West | Janet | Female | 1986-06-02 | | Chang | Catherine | Female | 1987-06-12 | | Ashburn | Sherryl | Female | 1985-10-07 | | DeGaram | Ruby | Female | 1988-10-11 | | Chance | Carole | Female | 1990-10-22 | | Lobo | Brenda | Female | 1986-10-05 | | Verde | Suzanna | Female | 1988-03-12 | | Franse | Mincy | Female | 1991-10-08 | | Andriamirano | Arlene | Female | 1989-02-15 | | Ledoux | Gabrielle | Female | 1989-06-23 | | Lobila | Koko | Female | 1990-08-20 | | Duma | Arlette | Female | 1985-09-28 | | Sans | Harriette | Female | 1986-08-02 | | Howerson | Bernadette | Female | 1986-06-11 | | Steinberg | Judith | Female | 1989-02-22 | | Napolis | Ella | Female | 1991-04-24 | | Miller | Ann | Female | 1987-06-21 | | Broadskey | Millicent | Female | 1989-09-10 | | Milchen | Victoria | Female | 1988-04-18 | | Quarles | Martine | Female | 1986-02-04 | | Laurens | Julie | Female | 1988-06-22 | | Bastens | Martha | Female | 1985-02-12 | | Hutchins | Jeannette | Female | 1988-04-24 | | Marlly | Paul | Female | 1990-10-20 | | Mart | Donnie | Female | 1990-06-08 | +--------------+------------+--------+------------+ 27 rows in set (0.02 sec) mysql>

2. Notice that the list includes students only girls

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.

 

Practical Learning: Using the IS Operator

1. To get a list of students where the record doesn't include an emergency name, execute the following statement:
    

   mysql> SELECT LastName, FirstName, Gender, EmergencyName -> FROM Students -> WHERE EmergencyName IS NULL; +-----------+-----------+--------+---------------+ | LastName | FirstName | Gender | EmergencyName | +-----------+-----------+--------+---------------+ | Pacheco | Santos | Male | NULL | | Vittas | Justin | Male | NULL | | Lobo | Brenda | Female | NULL | | Ledoux | Gabrielle | Female | NULL | | Duma | Arlette | Female | NULL | | Davis | Martin | Male | NULL | | Miller | Ann | Female | NULL | | Broadskey | Millicent | Female | NULL | | Junger | Arthur | Male | NULL | | Laurens | Julie | Female | NULL | | Bastens | Martha | Female | NULL | | Hutchins | Jeannette | Female | NULL | | Orion | George | Male | NULL | | Linken | Albert | Male | NULL | | Hagers | Ralph | Male | NULL | +-----------+-----------+--------+---------------+ 15 rows in set (0.00 sec) mysql>

2. To see a list of the students whose records include a number to call in case of emergency, execute the following statement:
    

   mysql> SELECT LastName, FirstName, EmergencyName, EmergencyPhone -> FROM Students -> WHERE EmergencyPhone IS NOT NULL; +--------------+------------+--------------------------+----------------+ | LastName | FirstName | EmergencyName | EmergencyPhone | +--------------+------------+--------------------------+----------------+ | Porter | Sebastien | John Porter | (202) 662-4825 | | Hoak | Suzie | Christianne Hoak | (301) 364-0221 | | Clarck | Antoinette | Henriette Clarck | (301) 598-9292 | | Chang | Catherine | Ernie Change | (202) 215-6663 | | Dean | Nehemiah | Marie Rodnat | (703) 434-8756 | | Ashburn | Sherryl | Shelia Ashburn | (703) 292-8340 | | Pacheco | Santos | NULL | (202) 938-7772 | | Husseini | Mohamed | Dr. Phyllis Husseini | (202) 631-8855 | | DeGaram | Ruby | Anthony DeGaram | (301) 938-1220 | | Chance | Carole | Andy Chance | (301) 422-1001 | | Vittas | Justin | NULL | (301) 549-0020 | | Zara | Ismael | Robert Zara | (202) 978-6642 | | Waters | Anselme | Celestin Waters | (703) 894-6624 | | Lobo | Brenda | NULL | (703) 894-2888 | | Chico | Danilo | Stephen Chico | (202) 299-7001 | | Franse | Mincy | Melanie Franse | (301) 986-4445 | | Andriamirano | Arlene | Christianne Andriamirano | (703) 593-2820 | | Lobila | Koko | Aquilla Lobila | (301) 752-0002 | | Farms | Paul | Rebecca Farms | (301) 696-8281 | | Bell | Lester | Bernadette Bell | (301) 979-0032 | | Liss | Christian | Sandra Liss | (703) 797-9762 | | Duma | Arlette | NULL | (301) 206-8623 | | Sans | Harriette | Thomas Sans | (703) 684-1500 | | Moore | Thomas | Deborah Moore | (410) 730-8100 | | Howerson | Bernadette | Caron Howerson | (301) 899-1800 | | Steinberg | Judith | Jim | (301) 322-7130 | | Napolis | Ella | Sandra Napolis | (301) 925-7041 | | Walken | Maurice | | (202) 583-4228 | | Edelman | Charles | Jasmine Bealieu | (301) 656-4441 | | Milley | Arthur | Jeffrey Milley | (301) 229-1600 | | Milchen | Victoria | Paul Milchen | (301) 540-2300 | | Junger | Arthur | NULL | (301) 656-4060 | | Bastens | Martha | NULL | (301) 780-4424 | | Marlly | Paul | Arlette Hutchins | (301) 661-5050 | | Mart | Donnie | Alan Marly | (301) 276-6068 | | Linken | Albert | NULL | (301) 530-0540 | | Laurel | Charles | Stephen Laurel | (301) 628-3200 | +--------------+------------+--------------------------+----------------+ 37 rows in set (0.00 sec) mysql>

3. Type quit and press Enter to end the lesson

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.

 

Other Operators

 

Introduction

We have seen that the SELECT keyword can be used to create a list of isolated columns. These columns are rendered separate of each other. Instead of having separate columns, you can combine them to create a string or a value that is in fact an expression. For example, you can combine a first name and a last name to create a full name. An expression that combines columns can be performed on text-based columns. such as a first name being added to a last name to get a full name. Another expression can use a date on the table, add a number to it to get a date on another day. An expression can also be used to perform a calculation on two or more columns such as employees weekly hours multiplied by their hourly salary to get their weekly salary. The most common operator used is the addition. It can be used on numeric values. All other arithmetic operators can be used. For example, you can multiply a weekly hours value to an hourly salary to get a weekly salary. The statement of such an expression can be written as follows: SELECT WeeklyHours * HourlySalary FROM Payroll

 

The Assignment Operator

The SQL allows you to specify a different name for any column during data analysis or a name for an expression. This is done using the assignment operator "=". To change the name of a column during data analysis, on the right side of SELECT, type the desired name, followed by the assignment operator, followed by the actual name of the column.