Operators

Arithmetic operators

The following arithmetic operators are supported:

operator	description
+	Add
-	Subtract
*	Multiply
/	Divide
=	Equals to
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
<>	Not equal to

Examples

Addition: Adding two columns together and creating a new column:
```
.s.e"SELECT fare,tip,fare+tip as new_total FROM trips"
```
Subtraction: Subtracting one column from another and creating a new column:
```
.s.e"SELECT month,month-1 as prev_month FROM trips"
```

Multiplying: Multiplying one column from another and creating a new column:

// multiply *
.s.e"SELECT distance,5280*distance as distance_feet FROM trips"

Dividing: Dividing a column by a fixed value and creating a new column:
```
// divide /
.s.e"SELECT fare, fare/100 as fare_cents FROM trips"
```
Equals: Returning only records that have a specific value in a column:
```
// equal to =
.s.e"SELECT * FROM trips where passengers=2"
```

Inequalities: Applying filters:

// equal to =
.s.e"SELECT * FROM trips where passengers=2"

// greater than >=
.s.e"SELECT fare,tip,fare+tip as total FROM trips WHERE tip>0"

// less than <=
.s.e"SELECT * FROM trips WHERE fare<5"

// greater than or equal to >=
.s.e"SELECT * FROM trips WHERE distance>=12"

// less than or equal to <=
.s.e"SELECT * FROM trips WHERE fare<=5"

Not equal: Do not return any records that meet a certain set of criteria:

// not equal to <>
.s.e"SELECT * FROM trips WHERE passengers<>1"

// not equal to !=
.s.e"SELECT * FROM trips WHERE passengers!=1"

Logical operators

The following logical operators supported are:

operator	description
AND	TRUE if all the conditions separated by AND are TRUE
OR	TRUE if any of the conditions separated by OR is TRUE
NOT	Displays a record if the condition(s) is NOT TRUE
BETWEEN	TRUE if the operand is within the range of comparisons
IN	TRUE if the operand is equal to one of a list of expressions
LIKE	TRUE if the operand matches a pattern
IS NULL	TRUE if the value is NULL

Examples

AND: only returning records that meet ALL criteria:

// AND
.s.e"SELECT * FROM trips  WHERE passengers=3 AND vendor='DDS'"

OR: returning records that meet ONE or MORE of the criteria:
```
// OR
.s.e"SELECT * FROM trips WHERE tip>20 OR fare>100"
```
IN: specifying multiple values in a WHERE clause, which is a shorthand for multiple OR clauses:
```
.s.e"SELECT * FROM trips WHERE payment_type IN ('CASH', 'CREDIT');"
```
NOT: returning records that DO NOT meet the criteria:
```
// NOT
.s.e"SELECT * FROM trips WHERE NOT passengers=1"
```
Info

This a option is equivalent to <> and !=.

Between

Using between selects values within a given range. The values can be numbers, text, dates or datetimes.

Examples

Records that fall between two numbers:

.s.e"SELECT * FROM trips WHERE fare BETWEEN 10 AND 12;"

Records that fall alphabetically between two text values:

.s.e"SELECT * FROM trips WHERE payment_type BETWEEN 'CASH' AND 'DISPUTE';"

Records that fall between two datetimes:

.s.e"SELECT * FROM trips WHERE pickup_time BETWEEN '2009-01-01 00:30:00' AND '2009-01-01 00:35:00';"

Records for a single day:

.s.e"SELECT * FROM trips WHERE date BETWEEN '2009-01-01' AND '2009-01-02;"

Like

Using like is used in a WHERE clause to search for a specified pattern in a column. As in standard SQL syntax the following two wildcards are supported:

The percent sign % represents zero, one, or multiple characters.
The underscore sign _ represents one, single character.

Examples

Use % to return all records where a field begins with a letter:
```
.s.e"SELECT * FROM trips WHERE payment_type LIKE 'C%';"
```

Use _ to search just leaving out one single character:

.s.e"SELECT * FROM trips WHERE payment_type LIKE 'C_EDIT';"

Conditional operators

The following conditional operators are supported:

case [a] when b then x ... else y
coalesce
nullif

Note

Both the general and 'simple' comparison forms of case are supported.