INF3707 - Database Design & Implementation Summary 2014

Selecting All Columns You can display all columns of data in a table by using an asterisk * after the SELECT keyword. SELECT * FROM departments; You can also display all columns in a table by listing all the columns after the SELECT keyword. SELECT department_id, department_name, manager_id, location_id FROM departments; Selecting Specific Columns You can use the SELECT statement to display specific columns of a table by specifying the column names, separated by commas. Specify the columns that you want, in the order that you want them to appear in the output. SELECT department_id, location_id FROM departments; Writing SQL Statements • SQL statements are not case-sensitive (unless indicated). • SQL statements can be entered on one or many lines. • Keywords cannot be split across lines or abbreviated. • Clauses are usually placed on separate lines for readability and ease of editing. • Indents should be used to make code more readable. • Keywords typically are entered in uppercase; all other words, such as table names and columns, are entered in lowercase. • Each SQL statement should be ended with a semi-colon. Column Heading Defaults • Character and Date column headings are left aligned. • Number column headings are right-aligned. • Default heading display: Uppercase • Can override the column heading display with an Alias. Page 2 of 111 1-L1 - Retrieving data using the SQL SELECT statement 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. Page 3 of 111 1-L1 - Retrieving data using the SQL SELECT statement 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;