Oracle Database 10g: Introduction to SQL

5 day Instructor-Led Course (40 hours)
 

Course Overview

This course introduces Oracle Database 10g technology and the relational database concepts and the powerful SQL programming language. This course provides the learners with the essential SQL skills of querying the database, the meta data and creating database objects.  In addition, the course also delves into the advanced querying and reporting techniques, data warehousing concepts and manipulating large data sets in different time zones.  This is the prerequisite to the OCP DBA and OCP Developer tracks.

Audience:
Database Administrators, PL/SQL Programmer, Forms Developer, System Analysts, Technical Consultant

Course Objectives:
Search data using advanced sub queries
Retrieve row and column data from tables with the SELECT statement
Employ SQL functions to generate and retrieve customized data
Run data manipulation statements (DML) to update data in the Oracle Database 10g
Control user access and manage schema objects

Course Outline:

Categorize the character functions into case manipulation and character manipulation types
Use the character manipulation functions in the SELECT and WHERE clauses
Explain and use the DATE and numeric functions
Use the SYSDATE function to retrieve the current date in the default format
Introduce the DUAL table as a means to view function results
List the rules for applying the arithmetic operators on dates
Use the arithmetic operators with dates in the SELECT clause

Reporting Aggregated Data Using the Group Functions
Describe and categorize the group functions
Use the group functions
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
Show the join tables syntax using SQL 99 syntax
Use table aliases to write shorter code and explicitly identify columns from multiple tables
Issue a SQL CROSS JOIN statement to produce a cartesian product

Use the NATURAL JOIN clause to retrieve data from tables with the same named columns

Create a join with the USING clause to identify specific columns between tables

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
Add additional conditions when joining tables with the AND clause  

Using Sub queries to Solve Queries
List the syntax for sub queries in a SELECT statements WHERE clause
List the guidelines for using sub queries
Describe the types of sub queries
Execute single row sub queries and use the group functions in a sub query
Identify illegal statements with sub queries
Execute multiple row sub queries
Analyze how the ANY and ALL operators work in multiple row sub queries
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
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

Manipulating Data
Write INSERT statements to add rows to a table
Copy rows from another table
Create UPDATE statements to change data in a table
Generate DELETE statements to remove rows from a table
Use a script to manipulate data
Save and discard changes to a table through transaction processing
Show how read consistency works
Describe the TRUNCATE statement

Using DDL Statements to Create and Manage Tables
List the main database objects and describe the naming rules for database objects
Introduce the schema concept
Display the basic syntax for creating a table and show the DEFAULT option
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 and Rename a table
 

Creating Other Schema Objects
List the main database objects and describe the naming rules for database objects
Introduce the schema concept
Display the basic syntax for creating a table and show the DEFAULT option

Explain the different types of constraints
Show resulting exceptions when constraints are violated with DML statements
Create a table with a sub query and remove a table with the DROP statement
Describe the ALTERTABLE functionality
Rename a table
 

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

 

Managing Data in Different Time Zones
TIME ZONES
Oracle9i Date time Support
Conversion operations

Searching Data Using Advanced Sub queries
Subquery 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 Data 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

Performing Regular Expression Support and Case Insensitive
Regular Expression Support Overview
Describing simple and complex patterns for searching and manipulating data