On-line training course catalogue

Oracle Database 10g: Introduction to SQL

Type of course: Classroom

Course details

Fee	£2200
Days	5
Course code	OR10GSQL

Course dates

Location	Aug	Sep	Oct	Nov	Dec	Jan
Altrincham Manchester	|-	|-	|-	|17	|-	|-
London King William Street	|-	|-	|-	|3	|-	|-
Washington Tyne & Wear	|-	|-	|13	|-	|-	|-

Booking: click a course date | View location: click location name | Print this page | Download as Word document |

Overview

Please note that Oracle courses running at London training centres are subject to a 5% surcharge.

This class is applicable to Oracle8i, Oracle9i and Oracle Database 10g users. 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.

Delegates will learn how to

• Run data manipulation statements (DML) to update data in the Oracle Database 10g
• 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
• Control user access and manage schema objects

Outline

Introduction

List the Oracle Database 10g main features

Provide an overview of: components, internet platform, apps server and developer suite

Describe relational and object relational database designs

Review the system development life cycle

Describe different means of storing data

Review the relational database concept

Define the term data models

Show how multiple tables can be related

Retrieving Data Using the SQL SELECT Statement

Define projection, selection, and join terminology

Review the syntaxes for the basic SQL SELECT statements

Use Arithmetic and Concatenation operators in SQL statements

List the differences between SQL and iSQL*Plus

Log into the database using iSQL*Plus

Explain the iSQL*Plus interface

Categorize the different types of iSQL*Plus commands

Save SQL statements to script files

Restricting and Sorting Data

Limit rows using a selection

Using the WHERE clause to retrieve specific rows

Using the comparison conditions in the WHERE clause

Use the LIKE condition to compare literal values

List the logical conditions AND, OR, NOT

Describe the rules of precedence for the conditions shown in this lesson

Sort rows with the ORDER BY clause

Use ampersand substitution in iSQL*Plus to restrict and sort output at run time

Using Single Row Functions to Customize Reports

Show the differences between single row and multiple row SQL functions

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

Controlling User Access

Controlling user access

System versus objects privileges

Creating user sessions and granting system privileges

Using roles to define user groups

Creating and granting privileges to a role

Granting and revoking object privileges

Changing your password

Using Database Links

Manage Schema Objects

Creating directories

Creating and querying external tables

Creating Index Organized Tables

Creating Function based indexes

Dropping Columns

Altering the structure of tables and adding constraints

Performing FLASHBACK Statement

Materialized Views overview

Manipulating Large Data Sets

Using the MERGE Statement

Performing DML with Subqueries

Performing DML with a RETURNING Clause

Overview of Multitable INSERT Statements

Tracking Changes in DML

Generating Reports by Grouping Related Data

Overview of GROUP BY and 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

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

Related Certifications

This course forms part of the following certification track(s):

• Oracle 10g PL/SQL Developer Certified Associate