SQL & SQL*Plus

 Click here

Course Overview

Course Goals

• To build an understanding of SQL and SQL*Plus commands and concepts
• To provide students with the skills necessary for retrieving meaningful data from the Oracle RDBMS
• To obtain hands-on experience through exercises in an instructor-led environment, promoting interactive learning

Prerequisites

• This course assumes some type of programming knowledge or understanding

Who Should Attend?

• Beginning users of the Oracle RDBMS who will be using SQL and SQL*Plus to query corporate data or maintain database objects, such as tables, views and sequences
• Application developers who are new to the Oracle environment or ANSI-standard SQL

Course Materials

• SQL & SQL*Plus Course Notes
• SQL & SQL*Plus Reference Book
• SQL & SQL*Plus Sample Script Disk

Duration

• Two days

Tuition

• $930

Course Outline

• Introduction
• Overview of RDBMS concepts
  • Common problems
  • The solution
  • Features of a RDBMS
  • Table characteristics
  • Joining tables
  • E/R diagrams
  • Structured query language (SQL)
  • SQL's history
  • ANSI SQL and Oracle's SQL*Plus
  • SQL*Plus
  • 10g New Features -- iSQL*PLUS
• Getting started
  • Logging on and off
  • The basic SELECT statement
  • Command line placement
  • Command terminators
  • The buffer
  • Building scripts
  • The default editor
  • Saving or retrieving scripts
  • Default directories
  • Executing saved scripts
  • Putting comments in scripts
  • Infrastructure -Let Exercise
  • Exercise I
• SQL*Plus Commands
  • SQL*Plus session parameters
  • The show and set commands
  • Exercise II
  • The DESC and SPOOL commands
  • More SQL*Plus commands
  • Exercise III
• Data types
  • VARCHAR2
  • NUMBER
  • 10g new features -- BINARY_FLOAT and BINARY_DOUBLE
  • Date
  • Long
  • Raw and long raw
  • Environment settings in SQL*Plus
  • The login.sql setup file
  • SQL*Plus commands
• The SELECT statement
  • The SELECT statement
  • Alias and string expressions
  • Exercise IV
  • Mathematical expressions
  • NULL values
  • Mathematical expressions using NVL()
  • Exercise V
  • Common SELECT statement clauses
  • The FROM clause
  • Table aliases
  • The WHERE clause
  • Logical operators
  • The ORDER BY clause
  • Exercise VI
• Table joins
  • Joins
  • Exercise VII
  • Outer joins
  • Exercise VIII
• Summarizing data
  • Selecting summarized data
  • GROUP functions
  • 10g new features -- statistical analysis of data
  • The GROUP BY and HAVING clause
  • Exercise IX
• Subqueries
  • Subquery
  • Using a subquery to create a table
  • Correlated subqueries
  • Exercise X
• Single-row functions
  • Pseudo columns
  • The DUAL table
  • Introduction to functions
  • Numeric functions
  • 10g new features -- binary floating point functions
  • Character functions
  • Date functions
  • The DECODE function
  • The CASE function
  • Top N queries
  • Exercise XI
  • Exercises XII-XIV
• Formatting output with SQL*Plus commands
  • The COLUMN command
  • The TTITLE and BTITLE commands
  • Edit masks
  • Control breaks with BREAK and COMPUTE
• Substitution variables
  • Overview
  • Exercise XVI
• Data manipulation language (DML) commands
  • The INSERT statement
  • The UPDATE statement
  • The MERGE statement
  • 10g new features -- new MERGE statement syntax
  • The DELETE statement
  • The COMMIT statement
  • Truncating a table
  • Exercise XVII
• Data definition language (DDL) commands
  • Definition
  • DDL statements
  • The CREATE TABLE statement
  • Table and column names
  • Exercise XVIII
  • The Oracle data dictionalry -- tables
  • The ALTER TABLE statement
  • Exercise XIX
  • The DROP statement
  • The CREATE VIEW statement
  • The Oracle data dictionary -- views
  • Exercise XX
  • The CREATE INDEX statement
  • Using indexes
  • The Oracle data dictionary -- indexes
  • Exercise XXI
  • The CREATE SEQUENCE statement
  • The Oracle data dictionary - sequences
  • Using sequence objects
  • Exercise XXII
  • 10g new features -- the PURGE statement
  • 10g new features -- the FLASHBACK TABLE statement
• Data control language (DCL) commands
  • The GRANT and REVOKE commands
  • Object privileges available for tables, views and sequences
• Advanced features
  • Cube and rollup
  • Rank -- aggregate function
  • Sample clause of the SELECT statement
  • 9i INSERT Statement
• Summary
• Further information
• Bibliography