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.
Returns the average of the non-null columns in the expression.
The BETWEEN operator is similar to >= and <=. BETWEEN includes everything between the two values indicated. BETWEEN works with both text and number.
Returns the next highest whole number no matter what the decimal point.
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.
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.
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.
When more than one logical operator (AND, OR, NOT) is used in the WHERE clause.
Combines a list of strings into a single string.
The number of the non-null columns in the expression.
Returns current local date.
Returns current local time
Extracts the date from date/time input. If time is included it is dropped.
SQL clause that deletes data from a table.
The DISTINCT clause removes duplicate rows from a query.
Returns the next lowest whole number no matter what the decimal point.
Specifies the base table(s) from which results will be retrieved.
Groups rows of a result set based on columns or expressions separated by commas.
The HAVING CLAUSE allows you to use an aggregate function as a filter. This is not allowed in a WHERE clause.
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.
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.
SQL Clause used to insert data into a table.
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
An outer join will return all the rows from one table and only the rows from the other table that match the join condition
A JOIN clause allows you to access data from two or more tables in a query.
Returns a substring starting from the left side of the string.
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.
Specifies the number of rows to be returned.
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.
Removes leading spaces from a string.
The lowest value off the non-null columns in the expression
Returns current local date and time.
SQL clause that orders a result set.
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.
Returns a substring starting from the right side of the string.
Rounds to the decimal specified.
Removes trailing spaces from a string.
Specifies the columns that will appear in a SQL query result set.
A subquery is a SELECT statement coded within another SELECT statement.
The total of the non-null columns in the expression.
A query that uses at least one aggregate function.
Removes leading and trailing spaces from a string.
Returns the number truncated to the precision specified.
A UNION combines the results of two or more queries into a single result set.
SQL clause that updates data in a table.
Returns current UTC date.
Returns current UTC time.
A SQL view is a SELECT statement that is stored as a database object.
Specifies any conditions for the results set (filter).