Oracle contents

Writing Basic SQL SELECT Statements

•  Basic SELECT Statement
•  Selecting All Columns
•  Selecting Specific Columns
• Writing SQL Statements
• Column Heading Defaults
• Arithmetic Expressions
• Using Arithmetic Operators
• Operator Precedence
• Using Parentheses
• Defining a Null Value
• Null Values in Arithmetic Expressions
• Defining a Column Alias
• Using Column Aliases
• Concatenation Operator
• Using the Concatenation Operator
• Literal Character Strings
• Using Literal Character Strings
• Duplicate Rows
• Eliminating Duplicate Rows 

Restricting and Sorting Data

• Limiting Rows Using a Selection
• Limiting the Rows Selected
• Using the WHERE Clause
• Character Strings and Dates
• Comparison Conditions
• Using Comparison Conditions
• Other Comparison Conditions
• Using the BETWEEN Condition
• Using the IN Condition
• Using the LIKE Condition
• Using the NULL Conditions
• Logical Conditions
• Using the AND Operator
• Using the OR Operator
• Using the NOT Operator
• Rules of Precedence
• ORDER BY Clause
• Sorting in Descending Order
• Sorting by Column Alias
• Sorting by Multiple Columns 

Single-Row Functions

• SQL Function
• Two Types of SQL Functions
• Single-Row Functions
• Single-Row Functions
• Character Functions
• Character Functions
• Case Manipulation Functions
• Using Case Manipulation Functions
• Character-Manipulation Functions
• Using the Character-Manipulation Functions
• Number Functions
• Using the ROUND Function
• Using the TRUNC Function
• Using the MOD Function
• Working with Dates
• Arithmetic with Dates
• Using Arithmetic Operators with Dates
• Date Functions
• Using Date Functions
• Practice 3, Part One:
• Conversion Functions
• Implicit Data Type Conversion
• Explicit Data Type Conversion
• Using the TO_CHAR Function with Dates
• Elements of the Date Format Model
• Using the TO_CHAR Function with Dates
• Using the TO_CHAR Function with Numbers
• Using the TO_NUMBER and TO_DATE Functions
• RR Date Format
• Example of RR Date Format
• Nesting Functions
• General Functions
• NVL Function
• Using the NVL Function
• Using the NVL2 Function
• Using the NULLIF Function
• Using the COALESCE Function
• Conditional Expressions
• The CASE Expression
• Using the CASE Expression
• The DECODE Function
• Using the DECODE Function 

Displaying Data from Multiple Tables

• Obtaining Data from Multiple Tables
• Cartesian Products
• Generating a Cartesian Product
• Types of Joins
• Joining Tables Using Oracle Syntax
• What is an Equijoin?
• Retrieving Records with Equijoins
• Additional Search Conditions Using the AND Operator
• Qualifying Ambiguous Column Names
• Using Table Aliases
• Joining More than Two Tables
• Non-Equijoins
• Retrieving Records with Non-Equijoins
• Outer Joins Outer Joins Syntax
• Using Outer Joins
• Self Joins
• Joining a Table to Itself
• Joining Tables Using SQL: 1999 Syntax
• Creating Cross Joins
• Creating Natural Joins
• Retrieving Records with Natural Joins
• Creating Joins with the USING Clause
• Retrieving Records with the USING Clause
• Creating Joins with the ON Clause
• Retrieving Records with the ON Clause
• Creating Three-Way Joins with the ON Clause
• INNER Versus OUTER Joins
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN
• Additional Conditions 

Aggregating Data Using Group Functions

• What Are Group Functions?
• Types of Group Functions
• Group Functions Syntax
• Using the AVG and SUM Functions
• Using the MIN and MAX Functions
• Using the COUNT Function
• Using the DISTINCT Keyword
• Group Functions and Null Values
• Using the NVL Function with Group Functions
• Creating Groups of Data
• Creating Groups of Data: The GROUP BY Clause Syntax
• Using the GROUP BY Clause
• Grouping by More Than One Column
• Using the GROUP BY Clause on Multiple Columns
• Illegal Queries Using Group Functions
• Excluding Group Results
• Excluding Group Results: The HAVING Clause
• Using the HAVING Clause
• Nesting Group Functions 

Subqueries

• Objectives
• Using a Subquery to Solve a Problem
• Subquery Syntax
• Using a Subquery
• Guidelines for Using Subqueries
• Types of Subqueries
• Single-Row Subqueries
• Executing Single-Row Subqueries
• Using Group Functions in a Subquery
• The HAVING Clause with Subqueries
• What is Wrong with this Statement?
• Will this Statement Return Rows?
• Multiple-Row Subqueries
• Using the ANY Operator in Multiple-Row Subqueries
• Using the ALL Operator in Multiple-Row Subqueries
• Null Values in a Subquery 

Manipulating Data

• Data Manipulation Language
• Adding a New Row to a Table
• The INSERT Statement Syntax 8-5
• Inserting New Rows
• Inserting Rows with Null Values
• Inserting Special Values
• Inserting Specific Date Values
• Creating a Script
• Copying Rows from Another Table
• Changing Data in a Table
• The UPDATE Statement Syntax
• Updating Rows in a Table
• Updating Two Columns with a Subquery
• Updating Rows Based on Another Table
• Updating Rows: Integrity Constraint Error
• Removing a Row from a Table
• The DELETE Statement
• Deleting Rows from a Table
• Deleting Rows Based on Another Table
• Deleting Rows: Integrity Constraint Error
• Using a Subquery in an INSERT Statement
• Using the WITH CHECK OPTION Keyword on DML Statements
• Overview of the Explicit Default Feature
• Using Explicit Default Values
• The MERGE Statement
• The MERGE Statement Syntax
• Merging Rows
• Database Transactions
• Advantages of COMMIT and ROLLBACK Statements
• Controlling Transactions
• Rolling Back Changes to a Marker
• Implicit Transaction Processing
• State of the Data Before COMMIT or ROLLBACK
• State of the Data after COMMIT
• Committing Data
• State of the Data After ROLLBACK
• Statement-Level Rollback
• Read Consistency
• Implementation of Read Consistency
• Locking
• Implicit Locking
• Read Consistency Example 

Creating and Managing Tables

• Database Objects
• Naming Rules
• The CREATE TABLE Statement
• Referencing Another User?s Tables
• The DEFAULT Option
• Creating Tables
• Tables in the Oracle Database
• Querying the Data Dictionary 9-10
• Data Types
• DateTime Data Types
• TIMESTAMP WITH TIME ZONE Data Type
• TIMESTAMP WITH LOCAL TIME Data Type
• INTERVAL YEAR TO MONTH Data Type
• INTERVAL DAY TO SECOND Data Type
• Creating a Table by Using a Subquery Syntax
• Creating a Table by Using a Subquery
• The ALTER TABLE Statement
• Adding a Column
• Modifying a Column
• Dropping a Column
• The SET UNUSED Option
• Dropping a Table
• Changing the Name of an Object
• Truncating a Table
• Adding Comments to a Table 

Including Constraints

• What are Constraints?
• Constraint Guidelines
• Defining Constraints
• The NOT NULL Constraint
• The UNIQUE Constraint
• The PRIMARY KEY Constraint
• The FOREIGN KEY Constraint
• FOREIGN KEY Constraint Keywords
• The CHECK Constraint
• Adding a Constraint Syntax
• Adding a Constraint
• Dropping a Constraint
• Disabling Constraints
• Enabling Constraints
• Cascading Constraints
• Viewing Constraints
• Viewing the Columns Associated with Constraints 

Creating Views

• Database Objects
• What is a View?
• Why use Views?
• Simple Views and Complex Views
• Creating a View
• Retrieving Data from a View
• Querying a View
• Modifying a View
• Creating a Complex View
• Rules for Performing DML Operations on a View
• Using the WITH CHECK OPTION Clause
• Denying DML Operations
• Removing a View
• Inline Views
• Top-N Analysis
• Performing Top-N Analysis
• Example of Top-N Analysis 

Other Database Objects

• Database Objects
• What is a Sequence?
• The CREATE SEQUENCE Statement Syntax
• Creating a Sequence
• Confirming Sequences
• NEXTVAL and CURRVAL Pseudocolumns
• Using a Sequence
• Modifying a Sequence
• Guidelines for Modifying a Sequence
• Removing a Sequence
• What is an Index?
• How Are Indexes Created?
• Creating an Index
• When to Create an Index
• When Not to Create an Index
• Confirming Indexes
• Function-Based Indexes
• Removing an Index
• Creating and Removing Synonyms 

Controlling User Access

• Objectives
• Controlling User Access
• Privileges
• System Privileges
• Creating Users
• User System Privileges
• Granting System Privileges
• What is a Role?
• Creating and Granting Privileges to a Role
• Changing Your Password
• Object Privileges
• Granting Object Privileges
• Using the WITH GRANT OPTION and PUBLIC Keywords
• Confirming Privileges Granted
• How to Revoke Object Privileges
• Revoking Object Privileges
• Database Links 

SQL Workshop

• Workshop Overview 

Using SET Operators

• The SET Operators
• Tables Used in This Lesson
• The UNION Operator
• Using the UNION Operator
• The UNION ALL Operator
• Using the UNION ALL Operator
• The INTERSECT Operator
• Using the INTERSECT Operator
• The MINUS Operator
• SET Operator Guidelines
• The Oracle Server and SET Operators
• Matching the SELECT Statements
• Controlling the Order of Rows 

Enhancements to the GROUP BY Clause

• Review of Group Functions
• Review of the GROUP BY Clause
• Review of the HAVING Clause
• GROUP BY with ROLLUP and CUBE Operators
• ROLLUP Operator
• ROLLUP Operator Example
• CUBE Operator
• CUBE Operator: Example
• GROUPING Function
• GROUPING Function: Example
• GROUPING SETS
• GROUPING SETS: Example
• Composite Columns
• Composite Columns: Example
• Concatenated Groupings
• Concatenated Groupings Example 

Advanced Subqueries

• What Is a Subquery?
• Subqueries
• Using a Subquery
• Multiple-Column Subqueries
• Column Comparisons
• Pairwise Comparison Subquery
• Nonpairwise Comparison Subquery
• Using a Subquery in the FROM Clause
• Scalar Subquery Expressions
• Scalar Subqueries: Examples
• Correlated Subqueries
• Using Correlated Subqueries
• Using the EXISTS Operator
• Using the NOT EXISTS Operator
• Correlated UPDATE
• Correlated DELETE
• The WITH Clause
• WITH Clause: Example 

Hierarchical Retrieval

• Sample Data from the EMPLOYEES Table
• Natural Tree Structure
• Hierarchical Queries
• Walking the Tree
• Walking the Tree: From the Bottom Up
• Walking the Tree: From the Top Down
• Ranking Rows with the LEVEL Pseudocolumn
• Formatting Hierarchical Reports Using LEVEL and LPAD
• Pruning Branches 

Extensions to DML and DDL Statements

• Review of the INSERT Statement
• Review of the UPDATE Statement
• Overview of Multitable INSERT Statements
• Overview of Multitable INSERT Statements
• Types of Multitable INSERT Statements
• Multitable INSERT Statements
• Unconditional INSERT ALL
• Conditional INSERT ALL
• Conditional FIRST INSERT
• Pivoting INSERT
• External Tables
• Creating an External Table
• Example of Creating an External Table
• Querying External Tables
• CREATE INDEX with CREATE TABLE Statement 

Plsql syllabus

Part I: Programming in PLSQL

• Introduction to PLSQL
• What Is PLSQL?
• The Origins of PLSQL
• About PLSQL Versions
• Resources for PLSQL Developers 

Creating and Running the PLSQL Code

• SQL*Plus
• Performing Essential PLSQL Tasks
• Calling PLSQL from Other Languages
• Language Fundamentals
• PLSQL Block Structure
• The PLSQL Character Set
• Identifiers
• Literals
• The Semicolon Delimiter
• Comments
• The PRAGMA Keyword
• Labels 

Part II: PLSQL Program Structure

• Conditional and Sequential Control
• IF Statements
• CASE Statements and Expressions
• The GOTO Statement
• The NULL Statement
• Iterative Processing with Loops 

Loop Basics

• The Simple Loop
• The WHILE Loop
• The Numeric FOR Loop
• The Cursor FOR Loop
• Loop Labels
• Tips for Iterative Processing 

Exception Handlers

• Exception-Handling Concepts and Terminology
• Defining Exceptions
• Raising Exceptions
• Handling Exceptions
• Building an Effective Error Management Architecture
• Making the Most of PLSQL Error Management 

Part III: PLSQL Program Data

• Working with Program Data
• Naming Your Program Data
• Overview of PLSQL Datatypes
• Declaring Program Data
• Programmer-Defined Subtypes
• Conversion Between Datatypes 

Strings

• String Datatypes
• Working with Strings
• String Function Quick Reference 

Numbers

• Numeric Datatypes
• Number Conversions
• Numeric Functions 

Records

• Records in PLSQL 

Collections

• Collections Overview
• Collection Methods (Built-Ins)
• Working with Collections
• Nested Table Multiset Operations
• Maintaining Schema-Level Collections 

Miscellaneous Datatypes

• The BOOLEAN Datatype
• The RAW Datatype
• The UROWID and ROWID Datatypes
• The LOB Datatypes
• Working with LOBs
• Predefined Object Types 

Part IV: SQL in PLSQL

• DML and Transaction Management
• DML in PLSQL
• Bulk DML with the FORALL Statement
• Transaction Management
• Autonomous Transactions 

Data Retrieval

• Cursor Basics
• Working with Implicit Cursors
• Working with Explicit Cursors
• BULK COLLECT
• SELECT ... FOR UPDATE
• Cursor Variables and REF CURSORs
• Cursor Expressions 

Procedures, Functions, and Parameters

• Procedures
• Functions
• Parameters
• Local Modules
• Module Overloading
• Forward Declarations
• Advanced Topics
• Go Forth and Modularize! 

Packages

• Why Packages?
• Rules for Building Packages
• Rules for Calling Packaged Elements
• Working with Package Data
• When to Use Packages
• Packages and Object Types 

Triggers

• DML Triggers
• DDL Triggers
• Database Event Triggers
• INSTEAD OF Triggers
• AFTER SUSPEND Triggers
• Maintaining Triggers 

Managing the PLSQL Code

• Managing Code in the Database
• Using Native Compilation
• Using the Optimizing Compiler and Compile-Time Warnings
• Conditional Compilation
• Testing PLSQL Programs
• Debugging PLSQL Programs
• Tuning PLSQL Programs
• Protecting Stored Code