Glossary

Aggregate FunctionSummary Queries and Aggregate Functions

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

Arithmetic OperatorsArithmetic Operators

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 functionSummary Queries and Aggregate Functions

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

BETWEEN operatorIS NULL, BETWEEN, IN Operators

The BETWEEN operator is similar to >= and

CEILING functionNumeric Functions

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

Column AliasesColumn 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 SpecificationsColumn 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 OperatorsComparison Operators

The comparison operators are = (equals), (greater than), =, (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 conditionAND, OR, NOT Logical Operators

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

CONCAT functionString Functions

Combines a list of strings into a single string.

COUNT functionSummary Queries and Aggregate Functions

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

CURRENT_DATE functionDate Functions

Returns current local date.

CURRENT_TIME functionDate Functions

Returns current local time

DATE functionDate Functions

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

DELETE clauseThe DELETE Clause

SQL clause that deletes data from a table.

DISTINCT clauseDISTINCT Clause

The DISTINCT clause removes duplicate rows from a query.

FLOOR functionNumeric Functions

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

FROM clauseThe Five Clauses of the SELECT Statement

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

GROUP BY clauseGrouping Data

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

HAVING clauseUsing the HAVING Clause

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

IN operatorIS NULL, BETWEEN, IN Operators

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.

IndexesSQL 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 clauseThe INSERT Clause With a Column List

SQL Clause used to insert data into a table.

IS NULL functionIS NULL, BETWEEN, IN Operators

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) clauseThe OUTER JOIN 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 The JOIN Clause

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

LEFT functionString Functions

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

LIKE operator LIKE and REGEXP Operators

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 clauseThe Five Clauses of the SELECT Statement

Specifies the number of rows to be returned.

Logical Operators: AND, OR, NOTAND, OR, NOT Logical Operators

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 functionString Functions

Removes leading spaces from a string.

MIN functionSummary Queries and Aggregate Functions

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

NOW functionDate Functions

Returns current local date and time.

ORDER BY clauseThe Five Clauses of the SELECT Statement

SQL clause that orders a result set.

REGEXP operator LIKE and REGEXP Operators

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.

RIGHT function

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

ROUND functionNumeric Functions

Rounds to the decimal specified.

RTRIM functionString Functions

Removes trailing spaces from a string.

SELECT clauseThe Five Clauses of the SELECT Statement

Specifies the columns that will appear in a SQL query result set.

SubqueryWorking With Subqueries

A subquery is a SELECT statement coded within another SELECT statement.

SUM functionSummary Queries and Aggregate Functions

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

Summary QueryAggregate Functions

A query that uses at least one aggregate function.

TRIM functionString Functions

Removes leading and trailing spaces from a string.

TRUNCATE functionNumeric Functions

Returns the number truncated to the precision specified.

UNION clauseHow to Code a UNION

A UNION combines the results of two or more queries into a single result set.

UPDATE clause

SQL clause that updates data in a table.

UTC_DATE functionDate Functions

Returns current UTC date.

UTC_time functionDate Functions

Returns current UTC time.

VIEWSSQL Views

A SQL view is a SELECT statement that is stored as a database object.

WHERE functionThe Five Clauses of the SELECT Statement

Specifies any conditions for the results set (filter).

CC BY-NC-ND International 4.0: This work is released under a CC BY-NC-ND International 4.0 license, which means that you are free to do with it as you please as long as you (1) properly attribute it, (2) do not use it for commercial gain, and (3) do not create derivative works.