Oracle Database 10g: SQL Fundamentals

What you will learn:

Audience:

• Application Developers
• Business Intelligence Developer
• Database Administrators
• Database Designers
• End Users
• Forms Developer
• PL/SQL Developer
• Portal Developer

Prerequisites:

Required Prerequisites:

• Familiarity with Data Processing Concepts and Techniques
• Ability to use a graphical user interface (GUI)

Course Objectives:

• Identify the major structural components of Oracle Database 10g
• Retrieve row and column data from tables with the SELECT statement
• Create reports of sorted and restricted data
• Employ SQL functions to generate and retrieve customized data
• Create reports of aggregated data
• Write SELECT statements that include queries
• Use the SET operators to create subsets of data
• Run data manipulation statements (DML) to update data in the Oracle Database 10g
• Create tables to store data
• Utilize views to display and retrieve data
• Create sequences to generate numbers
• Obtain meta data by querying the dictionary views.
• Controlling User Access
• Managing Schema Objects
• Manipulating large Datasets
• Generating Reports by Grouping Related Data
• Managing Data in Different Time zones
• Retrieving data using Sub-queries
• Hierarchical data retrieval
• Performing Regular Expression Support

Course Topics:

Introduction

• List the Oracle Database 10g Main Features
• An Overview of: components, internet platform, app server and developer suite
• Describe Relational and Object Relational Database Designs
• Review the System Development Life Cycle
• Define the term Data Models
• Describe different means of Sorting Data
• Show how Multiple Tables can be related
• Describe how SQL Communicates to the Database

Writing SQL SELECT statements

• Define projection, selection, and join terminology
• Review the basic SQL SELECT statement syntax
• Select all columns using a wildcard notation from a table
• State simple rules and guidelines for writing SQL statements
• Write a query containing the arithmetic operators
• Use aliases to customize column headings
• Create a character expression with the concatenation operator
• Use the quote operator q to control text strings containing the quote character

Using the iSQL*Plus Environment

• SQL statements versus iSQL*Plus commands
• Explaining the iSQL*Plus interface
• Interacting with script files
• Setting iSQL*Plus Preferences

Restricting Data

• Limit Rows Using a Selection
• Use the WHERE Clause
• List the main Comparison Conditions
• Use the LIKE Condition to Compare Literal Values
• List the Logical Conditions AND, OR, NOT
• Use Multiple Conditions in the WHERE clause
• Describe the Rules of Precedence

Sorting Data

• Sort Rows with the ORDER BY Clause
• Use the && Substitution Variable
• Use the iSQL*Plus DEFINE Command
• Use the VERIFY Command

SQL Functions

• Differentiate between Single Row and Multiple Row SQL Functions
• Categorize the Character Functions into Case Manipulation and Character Manipulation types
• Explain the Numeric Functions ROUND, TRUNC, and MOD
• List the Rules for Applying the Arithmetic Operators on Dates
• Use the Arithmetic Operators with Dates in the SELECT Clause
• Explain the DATE functions MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, and TRUNC
• Explain Implicit and Explicit conversion
• Nest Functions to Perform Multiple Tasks in One Statement

Conditional Expressions

• Use the CASE Expression
• Explain the DECODE Expression

Aggregating Data Using Group Functions

• Categorize the Types of Group Functions
• Use the AVG, SUM, MAX, MIN, and COUNT Functions in a Query
• Utilize the DISTINCT Keyword with the Group Functions
• Describe how Nulls are handled with the Group Functions
• Create Groups of Data with the GROUP BY Clause
• Group Data by more than one column
• Avoid Illegal Queries with the Group Functions
• Exclude Groups of Data with the HAVING Clause

Displaying Data from Multiple Tables

• Identify Types of Joins
• Retrieve Records with Natural Joins
• Use Table Aliases to write shorter code and explicitly identify columns from multiple tables
• Create a Join with the USING clause to identify specific columns between tables
• Use the ON clause to specify arbitrary conditions or specify columns to Join
• Create a Three-way join with the ON clause to retrieve information from 3 tables
• List the Types of Outer Joins LEFT, RIGHT, and FULL
• Generating a Cartesian Product

Using Subqueries

• Use a sub query to solve a problem
• Identify where sub queries can be placed in a SELECT statement
• Describe the types of sub queries (single row, multiple row)
• Show the single row sub query operators
• Use the group functions in a sub query
• Identify illegal statements with sub queries
• Show the multiple row sub query operators
• Explain how null values are handled in sub queries

Using the Set Operators

• Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows
• Use the UNION ALL operator to return all rows from multiple tables (with duplicates)
• Describe the INTERSECT operator
• Use the INTERSECT operator
• Explain the MINUS operator
• Use the MINUS operator
• List the SET operator guidelines
• Order results when using the UNION operator

Inserting and Updating Data

• Write INSERT statements to add rows to a table
• Insert Special Values
• Copy Rows from Another Table
• Update Rows in a Table

Deleting Data

• Use DELETE statements to remove rows from a table
• Delete Rows Based on Another Table
• Describe the TRUNCATE Statement

Database Transactions

• Save and Discard Changes to a Table through Transaction Processing (COMMIT, ROLLBACK, and SAVEPOINT)
• Show how Read Consistency works

Using DDL Statements

• List the main database objects
• Identify the Naming Rules
• Display the basic Syntax for Creating a Table
• Show the DEFAULT option
• List the Data Types that are available for Columns

Managing Tables

• Explain the different types of constraints
• Show resulting exceptions when constraints are violated with DML statements
• Create a table with a sub query
• Describe the ALTER TABLE functionality
• Remove a table with the DROP statement

Creating Other Schema Objects

• Categorize simple and complex views and compare them
• Create a view
• Retrieve data from a view
• Explain a read-only view
• List the rules for performing DML on complex views
• Create a sequence
• List the basic rules for when to create and not create an index
• Create a synonym

Managing Objects with Data Dictionary Views

• Describe the structure of each of the dictionary views
• List the purpose of each of the dictionary views
• Write queries that retrieve information from the dictionary views on the schema objects
• Use the COMMENT command to document objects

Appendices

• Oracle Join Syntax
• Using SQL*Plus
• Using SQL Developer
• Additional Practices

Controlling User Access

• Controlling User Access
• System versus Objects Privileges
• Using Roles to define user groups
• Changing Your Password
• Granting Object Privileges
• Confirming Privileges Granted
• Revoking Object Privileges
• Using Database Links

Manage Schema Objects

• Using the ALTER TABLE statement
• Adding a Column
• Modifying a Column
• Dropping a Column, Set Column UNUSED
• Adding, Enabling and Disabling Constraints
• Creating Function-Based Indexes
• Performing FLASHBACK operations
• External Tables

Manipulating Large Data Sets

• Using the MERGE Statement
• Performing DML with Sub queries
• Performing DML with a RETURNING Clause
• Overview of Multi-table INSERT Statements
• Tracking Changes in DML

Generating Reports by Grouping Related Data

• Overview of GROUP BY Clause
• Overview of Having Clause
• Aggregating data with ROLLUP and CUBE Operators
• Determine subtotal groups using GROUPING Functions
• Compute multiple groupings with GROUPING SETS
• Define levels of aggregation with Composite Columns
• Create combinations with Concatenated Groupings

Managing Data in Different Time Zones

• Time Zones
• Using date-time functions
• Identifying TIMESTAMP Data Types
• Differentiating between DATE and TIMESTAMP
• Performing Conversion Operations

Searching Data Using Advanced Sub queries

• Sub query Overview
• Using a Sub query
• Comparing several columns using Multiple-Column Sub queries
• Defining a Data source Using a Sub query in the FROM Clause
• Returning one Value using Scalar Sub query Expressions
• Performing ROW by-row processing with Correlated Sub queries
• Reusing query blocks using the WITH Clause

Hierarchical Retrieval

• Sample Data from the EMPLOYEES Table
• The Tree Structure of Employee data
• Hierarchical Queries
• Ranking Rows with LEVEL
• Formatting Hierarchical Reports Using LEVEL and LPAD
• Pruning Branches with the WHERE and CONNECT BY clauses

Regular Expression Support

• Regular Expression Support Overview
• Describing simple and complex patterns for searching and manipulating data

Appendices

• Writing Advanced Scripts
• Oracle Architectural Components
• Using SQL Developer
• Additional Practices