Summary NOTES for INF3707 - Database Design And Implementation (INF3707) 2021

Arithmetic Expressions You may need to modify the way in which number and date data are displayed, or you may want to perform calculations or look at what-if scenarios. These are all possible using arithmetic expressions. An arithmetic expression can contain column names, constant numeric values, and the arithmetic operators. Available arithmetic operators - + Add, - Subtract, * Multiply, / Divide. You can use arithmetic operators in any clause of an SQL statement, except the FROM clause. DATE and TIMESTAMP data types can only use the ADD and SUBTRACT operators. Using Arithmetic Operators SELECT last_name, salary, salary + 300 FROM employees; The output also displays a "SALARY + 300" column. The calculated column is NOT a new column in the EMPLOYEES table, it is for display only. Blank spaces before and after the arithmetic operator are ignored. Operator Precedence • Multiplication and division are evaluated before addition and subtraction. • Operators of the same priority are evaluated from left to right. • Parentheses are used to override the default precedence or to clarify the statement Null Values If a row lacks a data value for a particular column, that value is said to be null or to contain a null. A null is a value that is unavailable, unassigned, unknown, or inapplicable. A null is not the same as a zero or a space. Zero is a number, and a space is a character. Columns of any data type can contain nulls. However, some constraints (NOT NULL and PRIMARY KEY) prevent nulls from being used in the column. Null Values in Arithmetic Expressions If any column value in an arithmetic expression is null, the result is null. For example, if you attempt to perform division by zero, you get an error. However, if you divide a number by null, the result is a null or unknown. Defining a Column Alias A column Alias - • Renames a column heading; • Is useful with calculations; • Immediately follows the column name. (There can also be the optional AS keyword between the column name and alias.) • Requires double quotation marks if it contains spaces or special characters, or if it is case sensitive. • Uppercase by default. Concatenation Operator A concatenation operator - • Links columns or character strings to other columns; • Is represented by two vertical bars (||); • Creates a resultant column that is a character expression. SELECT last_name || job_id AS "Employees" FROM employees; You can link columns to other columns, arithmetic expressions, or constant values to create a character expression by using the concatenation operator (||). Columns on either side of the operator are combined to make a single output column. If you concatenate a null value with a character string, the result is a character string. LAST_NAME || NULL results in LAST_NAME. Literal Character Strings • A literal is a character, a number, or a date that is included in the SELECT statement, and that is not a column name or a column alias. • Literal strings of free-format text can be included in the query result, and are treated the same as a column in the SELECT list. • Date and character literal values must be enclosed by single quotation marks, number literals need not be enclosed. • Each character string is output once for each row returned. SELECT last_name || ' is a ' || job_id AS "Employee Details" FROM employees; Alternative Quote (q) Operator Many SQL statements use character literals in expressions or conditions. If the literal itself contains a single quotation mark, you can use the quote (q) operator and choose your own quotation mark delimiter. You can choose any convenient delimiter, single-byte or multibyte, or any of the following character pairs - [ ], { }, ( ), or < >. SELECT department_name || q' [, it's assigned Manager Id: ] ' || manager_id AS "Department and Manager" FROM departments; Duplicate Rows The default display of queries is all rows, including duplicate rows. To eliminate duplicate rows in the result, include the DISTINCT keyword in the SELECT clause immediately after the SELECT keyword.