Glossary

Aggregate Function

Performs an operation on a set of records in a column and returns a single value.

Arithmetic operators ARE: * (multiplication), / (division), DIV (integer division), % (MOD) or remainder, + (addition), - (subtraction). These operators can be used in the SELECT, WHERE, and ORDER BY clauses. Operators are evaluated in the same way as arithmetic in other contexts.

AVG function

Returns the average of the non-null columns in the expression.

BETWEEN operator

The BETWEEN operator is similar to >= and <=. BETWEEN includes everything between the two values indicated. BETWEEN works with both text and number.

CEILING function

Returns the next highest whole number no matter what the decimal point.

Column Aliases

A column alias provides a way to create a clean or more descriptive header for a results set. A column alias cannot be used in a SELECT, WHERE, GROUP BY or HAVING clause due to the order of execution. You must refer to the original column name.

Column Specifications

A column specification may be derived from a base table. Or it my be a calculated value as a result of an arithmetic expression or a function.

Comparison Operators

The comparison operators are = (equals), < (less than), > (greater than), <= (less than or equal to), >=, <> (not equal), != (not equal). Comparison operators compare two expressions. The result of a comparison results to true or false. Comparison operators are not case sensitive and are used with text and dates as well as numbers.

Compound condition

When more than one logical operator (AND, OR, NOT) is used in the WHERE clause.

CONCAT function

Combines a list of strings into a single string.

COUNT function

The number of the non-null columns in the expression.

CURRENT_DATE function

Returns current local date.

CURRENT_TIME function

Returns current local time

DATE function

Extracts the date from date/time input. If time is included it is dropped.

DELETE clause

SQL clause that deletes data from a table.

DISTINCT clause

The DISTINCT clause removes duplicate rows from a query.

FLOOR function

Returns the next lowest whole number no matter what the decimal point.

FROM clause

Specifies the base table(s) from which results will be retrieved.

GROUP BY clause

Groups rows of a result set based on columns or expressions separated by commas.

HAVING clause

The HAVING CLAUSE allows you to use an aggregate function as a filter. This is not allowed in a WHERE clause.

IN operator

The IN operator tests whether an expression is equal to a value or values in a list of expressions. The order of the items in the list does not matter. You can use the NOT operator to test for items not in the list. The IN clause may be used with a subquery.

Indexes

A SQL index is like the index of a book. It speeds up the retrieval of a record. The relational database management system (RDBMS) can retrieve a record with the index key instead of having to perform a table scan.

INSERT clause

SQL Clause used to insert data into a table.

IS NULL function

Null values indicate an unknown or non-existent value and is different from an empty string (‘ ‘). To test for a null value you use the IS NULL clause. The test for a value use IS NOT NULL clause

JOIN (OUTER) clause

An outer join will return all the rows from one table and only the rows from the other table that match the join condition

JOIN clause

A JOIN clause allows you to access data from two or more tables in a query.

LEFT function

Returns a substring starting from the left side of the string.

LIKE operator

The LIKE keyword is used with the WHERE clause. The LIKE keyword and can use two symbols as wildcards. The percent ( % ) symbol matches any number of characters and the underscore ( _ ) matches a single character.

LIMIT clause

Specifies the number of rows to be returned.

Logical Operators: AND, OR, NOT

Logical operators are used in the WHERE clause You may use multiple logical operators in a WHERE clause to create a compound condition. The order of evaluation when multiple operators are used is shown in the table above.

LTRIM function

Removes leading spaces from a string.

MIN function

The lowest value off the non-null columns in the expression

NOW function

Returns current local date and time.

ORDER BY clause

SQL clause that orders a result set.

REGEXP operator

REGEXP operator allows you to do more complex pattern matching than a LIKE keyword/ Some version of REGEXP exists in many computer languages. Refer to the “LIKE and REGEXP” handout for a full list of examples.