Three data types allow you to work with a DATE, TIME, or TIMESTAMP.

DATE represents the date as MM/DD/YYYY, DD.MM.YYYY, or YYYY-MM-DD.
TIME is represented as HH:MM AM, HH.MM.SS, or HH:MM:SS.
TIMESTAMP contains the date and time combined.

> The only arithmetic operations that can be performed on DATE/TIME values are addition and subtraction.
> A labeled duration (key words such as YEAR(S), MONTH(S), DAY(S), HOUR(S), MINUTE(S), and SECOND(S)) and other key words such as CURRENT DATE, and CURRENT TIME can be used as part of the calculation.
> The operands must be DATE/TIME data types, or use DECIMAL defined equivalents of DECIMAL (8,0) for date duration, and DECIMAL (6,0) for time duration.

You will learn more about data types in Data Definition.

Column names can be intermixed with arithmetic symbols to produce an arithmetic result.

SELECT column1 arithoper1 column2,
       column3 arithoper2 column4, ...
  FROM table1, table2, ...;

> Valid expression symbols are: + (addition), - (subtraction), * (multiplication), / (division).
> If any operand contains the NULL value, the entire calculation evaluates as NULL.
> The columns and operands used within the expression must be compatible data types.
> If operands are mixed numeric data-types, the ANSI/ISO standard specifies automatic data-type conversion from integers to decimal numbers, and from decimal numbers to floating point numbers as required. You can mix these data types in a numeric expression.
> DB2 also uses the || symbol to concatenate, or combine two fields into one.

An operator acts on one or more elements. Arithmetic operators give a mathematical result.

-expression                Reverses the sign of an expression.
+expression                Leaves the expression unchanged.
expression1 + expression2  Adds expression1 and expression2.
expression1 - expression2  Subtracts expression2 from expression1.
expression1 * expression2  Multiplies expression1 and expression2.
expression1 / expression2  Divides expression1 by expression2.

Operators with higher precedence are evaluated first. Arithmetic operators (+, -, *, /, etc.) are evaluated before comparison operators (<, =, >, etc.), which are evaluated before logical operators (AND, OR, NOT). You may have to use parentheses to control the calculation order. You may recall having learned the mathematical order of operations.

A * B < C OR D

is the same as

((A * B) < C) OR D 

A function is a named routine that performs a task on an argument passed to it in parentheses.

While these are the arithmetic operators required by the SQL standards, RDBMS implementations by various providers include many functions and operators beyond these basic functions for scientific, trigonometric, financial, statistical, mathematical, concatenation, string, conversion, system, date/time, bit manipulation, security, and other functions.

There are five types of conditions in SQL:

Operators           Conditions       
=, <>, <, >, <=, >= Comparison
IS NULL             Null testing
BETWEEN             Range of values
IN                  In a list
LIKE                Pattern matching

IS NULL, BETWEEN, IN, and LIKE will be covered later.

There are six logical comparison operators or predicates in SQL:

Operator  Comparison                Expression       
=         Equal to                  MAX_THRUST = 500
<>        Not equal to              MAX_THRUST <> 500
<         Less than                 MAX_THRUST < 500
>         Greater than              MAX_THRUST > 500
<=        Less than or equal to     MAX_THRUST <= 500
>=        Greater than or equal to  MAX_THRUST >= 500

Notice that “=<” and “=>”are invalid. Only use “<=” and “>=”.

Combine and negate with:

AND   Both conditions are true
OR     Either condition is true
NOT   Reverse a condition

 

NULL is a marker used when
the value does not exist,
the value exists but we don’t know what the value is,
the value is out of range, or
the value is not appropriate for this row.

> Null values are shown as hyphens in the result of the SELECT statement.
> If a null is returned, it means that there is no value.
> A null is not the same as a zero (0), a blank, or an empty string (”).
> When testing for a specific value in the field, the result can be: TRUE (equal), FALSE (unequal), or UNKNOWN (null).
> If someone refers to a value as being NULL, that is incorrect because null means there is no value.
> In SQL, the keyword NULL represents a null.
> A null does not belong to any data type and can be inserted into any columns that allow a null.
> The capability of not having a null is established by using the keywords NOT NULL in a CREATE TABLE column definition. This will be covered in the DDL table definition course.
> If you do not specify a NOT NULL constraint, the column will accept a null by default.
> One extra byte is used in the SQL file for each field that can have a null value, whether the field is NULL or not.
> In most table definitions, a null is not allowed. This can help data integrity because some valid value must be inserted.
> Multiple null values within a column are not considered equal.
> Null values are considered equal with SELECT DISTINCT.
> A unique index will allow only one null entry for each column.
> Null values are considered greater than or less than non-null values in the collating sequence depending on the manufacturer’s implemented sequence of your RDBMS.
> Nulls are not included in the result of column functions.
> Nulls are handled in a special way in a batch program and will be covered in batch programming.
> Oracle treats an empty string (”) as a null. This can cause conversion problems when converting to another RDBMS. Oracle may change this convention in a future release.

Constants can be embedded within a SELECT statement. This feature can be used with an interactive SQL environment to produce simple reports. The ANSI/ISO standard specifies that SQL constants be enclosed in single quotes (’…’). If a single quote is to be included in the constant text, it is written within the constant as two consecutive single-quote characters (’It’’s done’). Some SQL products such as Informix and SQL Server use constants enclosed in double quotes (”…”). Other SQL engines may have difficulty with the double quote character.

SELECT 'constant1', column1,
       'constant2', column2, ...
  FROM table1, table2, ...;

Notes:

> Constant values are enclosed within quotes and “nested” within column names of the
SELECT statement.

> The constant values are positional and repeated within each row that is returned.

> The DISTINCT option eliminates all duplicate column values for the column names following the word DISTINCT.
> Several columns can be considered for the DISTINCT operator.
> If the DISTINCT clause contains multiple columns, each unique combination of these columns is returned.

> DISTINCT is used in the SELECT clause. UNIQUE is used in the table definition.
> The DISTINCT predicate is similar to the UNIQUE predicate, except in the way that it treats a null value.
> If all non-null values in a result table are DISTINCT, then they are also UNIQUE.
> Multiple null values are not considered DISTINCT from each other, but they are considered to be UNIQUE. See UNIQUE.

SELECT DISTINCT column1, column2, ...
  FROM          table1, table2, ...;

> If a query includes the primary key of a table in its SELECT clause, then every row of the query result table will be unique, because the primary key has a different value in each row.
> If the primary key is not included in the query, duplicate rows can occur.
> In Microsoft Access, DISTINCT can not be used inside a column function parameter.
> In MySQL, DISTINCT can be used inside a COUNT column function, but not inside a SUM or AVG.

A column’s default display name is its column name in the table definition.
You can use the AS clause to create a column alias name.
A column alias is an alternate name that you have picked for the displayed result.
An alias name can be used to give a name to a derived column, such as an expression or a column function.
An alias name does not change the name of a column in a table.

SELECT column1 AS alias1,
       column2 AS alias2, ...
  FROM table1, table2, ...;

> A column alias immediately follows the column name in the SELECT clause.
> A column alias does not change the column name in the table definition.
It only changes the result table and exists only for the duration of the statement.
> Oracle and DB2 display unquoted column names and aliases in uppercase.
> When the default name is displayed, underscores are replaced with spaces in some RDBMS systems such as DB2.
> If the alias is a single word with only letters, digits, and underscores, and it is not a reserved word, you do not need quotes.
> If the alias name contains spaces, special characters, or punctuation, enclose the alias in single or double quotes.
> You should make a habit of using quotes around alias names, just for consistency and partability to other RDBMS systems.
> With Access and PostgreSQL, AS is required. With others, is is optional.
But it should always be included when adding an alias name for portability.
> Oracle’s SQL*Plus command line processor truncates column aliases to the number of characters in the table’s column definition. DECIMAL(3) ‘PERCENT’ becomes ‘PER’.

Specific column names may be specified following the SELECT, thus designating that only those columns are to be returned. This is the way that host-language programs should be coded so that the TABLE may have extra columns added in the future and the program will not need to be modified. The column names may be stated in any order based on how you wish them to be displayed. When a table is defined, the names must be unique within each table, but the same name can be used in other tables. See Qualifying Column Names.

SELECT column1, column2, ...
  FROM table1 , table2, ...;

Note: There is a comma (,) between multiple column names and multiple table names, but there is not a comma after the last column name or table name.

An asterisk (*) is used after the SELECT to indicate that all columns are to be returned. This keeps you from having to specify all column names, so do not enter any column names when you use the * option. SELECT * should not be used in a host-language program, since extra columns may be added to the TABLE later, returning too many columns to the program. Host-language programs will be covered when we get to batch programming.

A simple SELECT statement format is:

SELECT [ALL | DISTINCT] [* | column1[, column2, ...]]
  FROM  table1[, table2, ...];

Remember that the [ | ] symbols are only to show choices and are not a part of the finished statement.

> The asterisk (*) option is used to select all the columns in a table.
> The ALL option is used to select all values for a column, including duplicates.
> The DISTINCT option is used to suppress duplicate row values from being selected.
> The default between DISTINCT and ALL is ALL. Therefore, the ALL option never needs to be specified and is always implied, unless it is overridden by the DISTINCT option.