Introduction to Oracle 9i: Sql Chapter 4-10

Only available on StudyMode
  • Download(s) : 351
  • Published : August 28, 2011
Open Document
Text Preview
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
1-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 the 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
2-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
3-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-55- 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...
tracking img