Where Statements

Limiting the columns in our SELECT statements is easy. Just don't list it in the enumerated columns. For example, if we only want the ID and disbursed columns from the award table we just write this query.

SELECT
	a.award_id,
	a.award_disbursed
FROM award AS a;

But how do we select specific rows? We do that with WHERE statements. To add a where statement just add to the end of the query the keyword WHERE followed by a conditional statement. A conditional statement is a phrase that can be interpreted by the database program as either true or false. The SQL interpreter will apply the test to every row on the table. If the result is true the row will be included in the output. If the result is false the row will be skipped.

Equalities

As an example, we might want all of the Pell awards from the award table.

SELECT
	a.award_id,
	a.award_fund,
	a.award_offered,
	a.award_accepted,
	a.award_disbursed
FROM award AS a
WHERE a.award_fund = 'PELL';

This will only select the rows where the fund equals the string literal 'PELL'. Note that equality statements are case sensitive. If your WHERE clause is "WHERE award_fund = 'Pell'" or "WHERE award_fund = 'pell'" the select statement will not return any results because all of our records have 'PELL' capitalized. We will talk more about this on the functions page.

The results of this query are below.

award_id	award_fund	award_offered	award_accepted	award_disbursed
X00001	PELL	100.0	100.0	100.0
X00002	PELL	200.0	0.0	0.0
X00003	PELL	3000.0	300.0	300.0
X00005	PELL	555.0	500.0	500.0
X00008	PELL	88.0	0.0	0.0
X00010	PELL	100.0

Inequalities

An inequality is when something is not the same size as something else. In SQL we have a plethora of inequalities. You will probably remember them from math classes. Some of them are greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<> or !=). As an example we can select all of the awards with an offered amount greater than 200.

For not equal to there are two different operators. The "<>" operator is the ISO standard. The "!=" operator was adopted from programming languages such as Python. It may not be available in all database systems.

SELECT a.award_id, a.award_fund, a.award_offered, a.award_accepted, a.award_disbursed FROM award AS a
WHERE a.award_offered > 200;

award_id	award_fund	award_offered	award_accepted	award_disbursed
X00002	SUB	202.0	2202.0	0.0
X00002	UNSUB	203.0	2333.0	0.0
X00003	PELL	3000.0	300.0	300.0
X00004	SUB	402.0	4202.0	0.0
X00004	UNSUB	400.0	4.0	0.0
X00005	PELL	555.0	500.0	500.0

Logical Operations

This is well and good but what if we need something more specific. For example, what if we want the awards offered less than 100 and the accepted is zero? We can specify as many WHERE statements as we want by listing them one after another. But we need to rename all the other WHERE statements to either an AND statement or an OR statement.

These are logical operators. They appear in mathematics, SQL, computer programming, philosophy and more. We will be slowly exploring logical operators throughout these tutorials. But there is still much more for you to learn if you desire. For more information you might want to search on the internet for "logical operators", "truth tables", and "boolean algebra".

All conditional statements are evaluated based on an order or preference. This idea is similar to the PEMDAS order of precedence used in algebra. Statements in parenthesis are evaluated first, just like algebra. Logical AND statements and OR statements have the same precedence so they will be evaluated from left to right.

Logical AND

For an AND statement the database will evaluate both conditional statements to see if they are true. If both are true then the overall statement is true and the row will be included in the output. If either of the conditional statement are false then the entire logical statement will be false and the row will not be included.

SELECT a.award_id, a.award_fund, a.award_offered, a.award_accepted, a.award_disbursed FROM award AS a
WHERE a.award_offered < 100
AND a.award_accepted = 0;

award_id	award_fund	award_offered	award_accepted	award_disbursed
X00008	PELL	88.0	0.0	0.0

Logical OR

Logical OR evaluates the two conditionals and returns true if one of them is true. If both conditionals are true it also returns true. For example, suppose you need a list of possible award problems. You need to know awards offered at greater than 500. But you also need to know the awards that have not disbursed yet (award_disbursed = 0).

SELECT a.award_id, a.award_fund, a.award_offered, a.award_accepted, a.award_disbursed FROM award AS a
WHERE a.award_offered > 500
OR a.award_accepted = 0;

award_id	award_fund	award_offered	award_accepted	award_disbursed
X00002	PELL	200.0	0.0	0.0
X00003	PELL	3000.0	300.0	300.0
X00005	PELL	555.0	500.0	500.0
X00008	PELL	88.0	0.0	0.0

Logical XOR

There is one other logical operation I want to talk about although it is not often used. Logical Exclusive OR is commonly abbreviated XOR. With exclusive OR the evaluation will return true if one conditional is true and the other is false. If both conditionals are true or both conditionals are false it will return false. XOR does not have a dedicated keyword. Instead you put the two conditionals into their own parenthesis to force them to be resolved first. Then you test if they are not equal to each other.

Let's do a query where we use the plain old non-exclusive OR.

SELECT a.award_id, a.award_fund, a.award_offered, a.award_accepted, a.award_disbursed FROM award AS a
WHERE (a.award_offered = 200) OR (a.award_accepted = 0);

award_id	award_fund	award_offered	award_accepted	award_disbursed
X00002	PELL	200.0	0.0	0.0
X00008	PELL	88.0	0.0	0.0

But now let's use XOR.

SELECT a.award_id, a.award_fund, a.award_offered, a.award_accepted, a.award_disbursed FROM award AS a
WHERE (a.award_offered = 200) <> (a.award_accepted = 0);

award_id	award_fund	award_offered	award_accepted	award_disbursed
X00008	PELL	88.0	0.0	0.0

This time we only have one record returned. We can work through the resolution process. For the X00002 Pell record, (award_offered = 200) is true. (award_accepted = 0) is also true. So "(true) <> (true)", or "true is not equal to true", is false. True is equal to true.

Checking Data

With our new found knowledge we can start to do something actually productive (hypothetically). We can do a simple sanity check on the awards. Logically, the offered amount is always going to be greater than or equal to the accepted amount. The student cannot accept an award that is greater than what was offered. Likewise, the disbursed amount cannot be greater than the accepted amount. What we are going to do is select all records where the offered is less than the accepted or the accepted is less than the disbursed.

This will, naturally, return no records indicating that all is like it should be.

SELECT a.award_id, a.award_fund, a.award_offered, a.award_accepted, a.award_disbursed FROM award AS a
WHERE a.award_offered < a.award_accepted
OR a.award_accepted < a.award_disbursed;

award_id	award_fund	award_offered	award_accepted
X00001	SUB	102.0	202.0
X00001	UNSUB	103.0	2002.0
X00002	SUB	202.0	2202.0
X00002	UNSUB	203.0	2333.0
X00004	SUB	402.0	4202.0

Crap! Crap, crap, crap, crap, crap

We have a problems in the database. Loan accepted amounts were entered with typos. At least none of them disbursed. Oh well. I guess it is time to learn update statements.