inf3707_summary notes

INF3707 - Database Design & Implementation Summary 2014 Ref. Lesson Title Page 1. 1-L1 Retrieving data using the SQL select statement 2 2. 1-L2 Restricting and sorting data 7 3. 1-L3 Using single row functions to customize output 15 4. 1-L4 Reporting aggregated data using the group functions 25 5. 1-L5 Displaying data from multiple tables 30 6. 1-L6 Using sub-queries to solve queries 37 7. 1-L7 Using the set operators 42 8. 1-L8 Manipulating data 46 9. 1-L9 Using DDL statements to create and manage tables 54 10. 1-L10 Creating other schema objects 62 11. 1-L11 Managing objects with data dictionary 69 12. 2-L1 Controlling user access 73 13. 2-L2 Managing schema objects 79 14. 2-L3 Manipulating large data sets 86 15. 2-L4 Generating reports by grouping data 92 16. 2-L5 Managing data in different time zones 98 17. 2-L6 Retrieving data using sub-queries 101 18. 2-L7 Hierarchical retrieval 107 19. 2-L8 Regular expression support 108 Oracle 10g - Introduction to SQL (Part 1 & 2) Ver 1.00 - April 2014 Ron Barnard 1-L1 - Retrieving data using the SQL SELECT statement Retrieving data using the SQL select statement Objectives • List the capabilities of SQL SELECT statements • Execute a basic SELECT statement • Differentiate between SQL statements and iSQL*Plus commands Content 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; 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