Introduction to Oracle 9i Sql Chapter 4-10 free essay sample

Introduction to Oracle 9i: SQL Name: _______ Junel L Cofino Table of Contents Introduction: I-7 Relational and Object Relational Database Management System I-10 System Development Life Cycle I-12 Relational Database Concept I-20 Relational Database Properties I-24 Tables used in the course Chapter 1: Writing Basic SELECT SQL Statements 1-4- Basic Select Statement 1-5- Selecting All Columns 1-6- Selecting Specific Columns 1-7- Writing SQL Statements 1-8- Column Heading Defaults 1-9- Arithmetic Expressions 1-15- Null Values in Arithmetic Expressions 1-16- Defining A Column Alias -17- Using Column Alias 1-18- Concatenation Operator 1-19- Using Concatenation Operator 1-22- Duplicate Rows 1-23- Eliminating Duplicate Rows Chapter 2: Restricting and Sorting Data 2-4-Limiting the Rows Selected 2-5-Using WHERE Clause 2-6- Character Strings and Dates 2-7- Comparison Conditions 2-8- Using Comparison Conditions 2-9- Other Comparison Conditions 2-10- Using the BETWEEN Condition 2-11- Using t he IN Condition 2-13- Using the LIKE Condition 2-14- Using the NULL Condition 2-15- Logical Conditions 2-16- Using AND Operator 2-17- Using OR Operator 2-18- Using NOT Operator 2-23- Sorting in Descending Order -24- Sorting by Column Alias 2-25- Sorting by Multiple Columns Chapter 3: Single Row Functions 3-3- SQL Functions 3-4- Two Types of SQL Functions 3-5- Single Row Functions 3-6- Single Row Functions 3-8- Character Functions 3-9- Case Manipulation Functions 3-11- Character Manipulation Functions 3-13- Number Functions 3-14- Using ROUND Functions 3-15- Using TRUNC Functions 3-16- Using MOD Functions 3-17- Working with Dates 3-18- Working with Dates 3-23- Arithmetic with Dates 3-25- Conversion Functions 3-26- Implicit Data Type Conversion 3-27- Implicit Data Type Conversion 3-28- Explicit Data Type Conversion -31- Using the TO_CHAR Function with Dates 3-32- Elements of the Date Format Model 3-52- Conditional Expression 3-53- The CASE Expression 3-54- Using the CASE Expression 3-5 5- The DECODE Function 3-56- Using the DECODE Function Chapter 4 Displaying Data from Multiple Tables Joining Tables Using Oracle Syntax Use a join to query data from more than one table. SELECTtable1. column, table2. column FROMtable1, table2 WHEREtable1. column1 = table2. column2; * Write the join condition in the WHERE clause. * Prefix the column name with the table name when the same column name appears in more than one table. 4-7 Retrieving Records with Equijoins SELECT employees. employee_id, employees. last_name, employees. department_id, departments. department_id, departments. location_id FROM employees, departments WHERE employees. department_id = departments. department_id; Using Table Aliases * Simplify queries by using table aliases. * Improve performance by using table prefixes. SELECT e. employee_id, e. last_name, e. department_id, d. department_id, d. location_id FROM employees e , departments d WHERE e. department_id = d. department_id; 4-12 Joining Tables Using SQL: 1999 Syntax Use a join to query data from more than one table. SELECT table1. column, table2. column FROM table1 [CROSS JOIN table2] | [NATURAL JOIN table2] | [JOIN table2 USING (column_name)] | [JOIN table2 ON(table1. column_name = table2. column_name)] | [LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1. column_name = table2. column_name)]; Creating Cross Joins * The CROSS JOIN clause produces the cross-product of two tables. * This is the same as a Cartesian product between the two tables. * SELECT last_name, department_name FROM employees CROSS JOIN departments ; 4-23 Retrieving Records with Natural Joins SELECT department_id, department_name, location_id, city FROM departments NATURAL JOIN locations ; Retrieving Records with the USING Clause SELECT e. employee_id, e. last_name, d. location_id FROM employees e JOIN departments d USING (department_id) ; 4-27 Retrieving Records with the ON Clause SELECT e. employee_id, e. last_name, e. department_id, d. department_id, d. location_id FROM employees e JOIN departments d ON (e. department_id = d. department_id); Creating Three-Way Joins with the ON Clause SELECT employee_id, city, department_name FROM employees e JOIN departments d ON d. department_id = e. department_id JOIN locations l ON d. location_id = l. ocation_id; Chapter 5 Aggregating Data Using Group Functions Group Functions Syntax SELECT[column,] group_function(column), FROMtable [WHEREcondition] [GROUP BYcolumn]; 5-5 Using the AVG and SUM Functions You can use AVG and SUM for numeric data. SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE %REP%; Using the MIN and MAX Functions You can use MIN and MAX for any data type. SELECT MIN(hire_date), MAX(hire_date) FROM employees; 5-7 Using the COUNT Function COUNT(*) returns the number of rows in a table. SELECT COUNT(*) FROM employees WHERE department_id = 50; Using the COUNT Function * COUNT(expr) returns the number of rows with non-null values for the expr. * Display the number of department values in the EMPLOYEES table, excluding the null values. SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80; 5-9 Using the DISTINCT Keyword * COUNT(DISTINCT expr) returns the number of distinct non-null values of the expr. * Display the number of distinct department values in the EMPLOYEES table. SELECT COUNT(DISTINCT department_id) FROM employees; Creating Groups of Data The average salary in EMPLOYEES table for each department. Using the GROUP BY Clause All columns in the SELECT list that are not in group functions must be in the GROUP BY clause. SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ; 5-15 Illegal Queries Using Group Functions Any column or expression in the SELECT list that is not an aggregate function must be in the GROUP BY clause. SELECT department_id, COUNT(last_name) FROM employees; SELECT department_id, COUNT(last_name) * ERROR at line 1: ORA-00937: not a single-group group function Illegal Queries Using Group Functions * You cannot use the WHERE clause to restrict groups. * You use the HAVING clause to restrict groups. You cannot use group functions in the WHERE clause. SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) 8000 GROUP BY department_id; WHERE AVG(salary) 8000 * ERROR at line 3: ORA-00934: group function is not allowed here Cannot use the WHERE clause to restrict groups 5-20 Using the HAVING Clause SELECT department_id, MAX(salary) FROM employees GROUP BY depart ment_id HAVING MAX(salary)10000 ; Nesting Group Functions Display the maximum average salary. SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id; Chapter 6 Sub queries