CPSC340A - Day 3 Notes

Filtering Rows

We can filter columns by specifying column names after the SELECT
We can also filter rows with WHERE and a boolean expression

Can compare columns to literals

SELECT number FROM TableName WHERE number = 0;

Or to other columns

SELECT number1 FROM TableName WHERE number1 < number2;

See the documentation for a list of available comparison operations
Comparison Functions and Operators
Note that equality is ‘=’, a single equal symbol, not two
Also note that the SQL standard not equal symbol is ‘<>’ but that ‘!=’ works in MySQL
Can also use columns that are not in selected in the WHERE expression
```
SELECT string FROM TableName WHERE number1 <> number2;
```

Note that you can not use calculated columns in WHERE expression

SELECT number1 + number2 AS total FROM TableName WHERE total >= 0; -- error

You can, however, put calculations in the comparison

SELECT string FROM TableName WHERE number1 + number2 >= 0;

Can also compare strings, dates, and times

SELECT string FROM TableName WHERE string > 'something';
SELECT date FROM TableName WHERE date > '2018-01-01';
SELECT time FROM TableName WHERE time > '06:00:00';

Can also create compound conditionals

SELECT string FROM TableName WHERE number1 > 0 AND number1 < 10;
SELECT string FROM TableName WHERE number1 < 0 OR number1 > 10;

Note that WHERE goes before ORDER in the SELECT Syntax

SELECT number, string FROM TableName WHERE string = 'something' ORDER BY number;

Range

There is a special syntax for checking if a value is in a range
```
SELECT string FROM TableName WHERE number BETWEEN 0 AND 10;
```

Which is equivalent to

SELECT string FROM TableName WHERE number1 >= 0 AND number1 <= 10;

This also works for strings, dates, and times

SELECT string FROM TableName WHERE string BETWEEN 'A' AND 'Az';
SELECT date FROM TableName WHERE date BETWEEN '2000-01-01' AND '2018-01-01';
SELECT time FROM TableName WHERE date BETWEEN '00:00:00' AND '06:00:00';

Note that the left value in the range must be less than the right for some implementations of SQL

Membership

There is also a special syntax for testing if something is in a set
```
SELECT string FROM TableName WHERE string IN ('A', 'B', 'C');
```

This is equivalent to

SELECT string FROM TableName WHERE string = 'A' OR string = 'B' OR str = 'C';

Pattern Match

Can do a fuzzy string comparison
Not as powerful as a regular expression, but it is simple to use
Can include wildcard characters
The ‘%’ symbol matches any 0 or more characters

The ’_’ symbol matches any 1 character

SELECT string FROM TableName WHERE string LIKE 'a';
SELECT string FROM TableName WHERE string LIKE 'a%';
SELECT string FROM TableName WHERE string LIKE 'a_';

If you need to match a ‘%’, ’_‘, or’', can escape them

SELECT string FROM TableName WHERE string LIKE 'a\%';
SELECT string FROM TableName WHERE string LIKE 'a\_';
SELECT string FROM TableName WHERE string LIKE 'a\\';

Null

Recall that any expression with NULL evaluates to NULL
So the expression
```
SELECT NULL = NULL;
```
Evaluates to NULL instead of 1

So there is special syntax for testing for null

SELECT string FROM TableName WHERE string IS NULL;

Not

Can use the NOT operator in two ways

Like in English

SELECT string FROM TableName WHERE number NOT BETWEEN 0 AND 10;
SELECT string FROM TableName WHERE string NOT IN ('A', 'B', 'C');
SELECT string FROM TableName WHERE string NOT LIKE 'a';
SELECT string FROM TableName WHERE string IS NOT NULL;

Or like in mathematics

SELECT string FROM TableName WHERE NOT string = 'something';
SELECT string FROM TableName WHERE NOT number BETWEEN 0 AND 10;
SELECT string FROM TableName WHERE NOT string IN ('A', 'B', 'C');
SELECT string FROM TableName WHERE NOT string LIKE 'a';
SELECT string FROM TableName WHERE NOT string IS NULL;