Oracle SQL — A 3 day course

Synopsis

This course is designed to give delegates practical experience in writing SQL statements and scripts using Oracle SQL*Plus. The basic SELECT statement, the use of SQL functions and the basic table and view handling statements are introduced.

The course covers the major topics required for the following Oracle examination 1Z0-051: Oracle Database 11g: SQL Fundamentals I (currently on Beta test). Together with the SQL Advanced course, this course also covers the major topics required for the following Oracle examinations: 1Z0-007: Introduction to Oracle9i SQL 1Z0-047: Oracle Database SQL Expert.

Examinations are not included as part of the course and anyone intending to sit examinations should check directly on the Oracle website for up to date requirements.

Objectives

Creating SQL statements to query database tables
Using standard aggregate functions and related SELECT statement clauses
Joining Tables
Using basic subqueries and the set operators
Using numeric, character and date functions
Using conversion and other miscellaneous functions
Using SQL parameters
Using complex subqueries
Creating and altering tables and views
Inserting, updating and deleting rows in database tables
Managing sequences and synonyms

Suitable For

Anyone who needs to use and understand SQL to query and update an Oracle database

Individuals who intend to sit the Oracle Database 11g: SQL Fundamentals examination

Prerequisites

There are no formal pre-requisites, although an understanding of databases and exposure to information technology in general would be useful.

Publicly scheduled dates, locations, and prices

A schedule of dates for this subject is not currently available. Please call 0333 210 0140 or use our contact form to enquire about places and availability.

Relational Database Concepts

What is an Oracle Database
Relational Database Structures
Tables, Rows and Columns
Indexes, Primary Keys and Foreign Keys
Supported Datatypes
The Data Dictionary

Using SQL*Plus

What is SQL*Plus
Getting Started
Entering and Executing SQL Statements
Editing SQL Statements
Creating, Editing and Executing SQL Files

Using SQL Developer

What is Oracle SQL Developer
Starting SQL Developer
Configure a Connection
Navigation Tabs
SQL Worksheet

Retrieving Data with the SELECT Statement

The SELECT Statement
The SELECT and FROM Clauses
Conditions and the WHERE Clause
Other Conditional Operators
Logical Operators
The ORDER BY Clause
Column Aliases
Arithmetic Expressions
Precedence of Operators

Aggregate Functions

Overview of Built In Aggregate Functions
The GROUP BY Clause
The HAVING Clause

Joining Tables

Overview of Table Joins
Inner Joins
Table Aliases
Outer Joins
Self Joins
ANSI Standard Joins
Set Operators

Numeric, Character and Date Functions

Function Types
Using the Table dual to try out Functions
Numeric Functions
Character Functions
String Concatenation
Date Arithmetic and Date Functions

Conversion and Miscellaneous Functions

Conversion Functions
The NVL and NVL2 Functions
The DECODE Function
CASE Expressions
The COALESCE and NULLIF Functions

SQL*Plus Parameters

Command Line Substitution Parameters
The Accept Command
The Define and Undefine Commands

Using Subqueries

Overview of Subqueries
In-Line Views
Top-N Queries
Complex Subqueries
Multi Column Subqueries
Correlated Subqueries
Subquery Rules
Using the ANY, ALL and SOME Operators

Managing Data

Inserting Rows
Updating Rows
Deleting Rows
Verifying Updates
Transaction Control
Commit and Rollback
Savepoints
Commits and Constraints
Amending Data in SQL Developer

Managing Tables

Creating Tables
Specifying Constraints
Altering Tables, Columns and Constraints
Dropping Tables, Columns and Constraints
Copying Tables

Managing Indexes and Views

Creating Indexes
Dropping Indexes
Listing Indexes
Creating and Using Views
Dropping Views
Listing Views

Managing Sequences and Synonyms

Create a Sequence
View Sequence Details
Create a Synonym
List Synonyms