• Decrease font size
  • Default font size
  • Increase font size

Intro to Oracle 9i SQL

 Print E-mail
Course Length - 3 Days
 
Course Description

The foundation for all Oracle professionals is the SQL language along with Oracle's SQL*Plus extensions. A detailed consideration of these languages includes such topics as creating and maintaining table definitions in the data dictionary, specifying data types, manipulating tables and developing sophisticated queries quickly and accurately.

 

You Will Learn How To:

  • Understand the concepts of relational databases
  • Create and alter table definitions including column data type declarations
  • Maintain data within tables using INSERT, DELETE and UPDATE statements
  • Build queries using both basic and advanced SQL techniques
 

Course Prerequisites

  • A basic understanding of computer programming concepts is helpful
  • Oracle Designer Data Modeling is recommended.
 

Who Should Attend?

All Oracle professionals including developers, database administrators, designers and analysts.

 

Course Outline

SQL DATA DEFINITION LANGUAGE

  • CREATE TABLE STATEMENT
  • SELECT
  • NOT NULL
  • DEFAULT
  • ALTER, DROP
  • DESCRIBE, RENAME

USING THE SQL*PLUS TOOL

  • INTERACTIVE SQL WITH SQL*PLUS
  • Graphical SQL*Plus
  • EXIT or QUIT
  • HOST or $
  • SPOOL
  • START or @
  • LIST, SAVE, GET
  • RUN or /
  • EDIT, APPEND, CHANGE, DEL, INPUT

SQL DATA MANIPULATION LANGUAGE

  • ABOUT SQL DML
  • SELECT, INSERT
  • INSERT Using SELECT
  • DELETE, UPDATE
  • ABOUT TRANSACTIONS
  • ROLLBACK, COMMIT, SAVEPOINT
  • COMPLEX TABLE REFERENCES

INTRODUCTION TO SQL QUERIES

  • SELECT
  • DISTINCT
  • ALIAS NAMES

BUILDING A WHERE CLAUSE

  • EQUALITY, Inequality
  • BOOLEAN
  • NULL AND BETWEEN
  • IS [NOT] NULL
  • [NOT] BETWEEN
  • [NOT] LIKE
  • IN

ORDERING THE RESULT TABLE

  • ABOUT THE ORDER BY CLAUSE
  • INCLUDING MULTIPLE COLUMNS
  • SPECIFYING THE SORT SEQUENCE
  • NULL VALUES AND ORDER BY
  • SORTING BY UN-SELECTED COLUMNS
  • REFERRING TO COLUMN ALIASES

PSEUDO COLUMNS AND FUNCTIONS

  • THE PSEUDO COLUMNS
  • ROWNUM, SYSDATE, USER & UID
  • THE DUAL TABLE

JOINING TABLES

  • EQUI-JOIN
  • REFLEXIVE JOIN
  • NON KEY-JOIN
  • OUTER JOIN

USING SET OPERATORS UPON TABLES

  • SET OPERATOR EXAMPLES
  • UNION, INTERSECT, MINUS
  • PERFORMING A FULL OUTER JOIN

ABOUT SUMMARY FUNCTIONS

USING SUBQUERIES

CREATING & SELECTING GROUPS

  • GROUP BY Example
  • SELECTING DATA FROM THE BASE TABLES
  • HAVING

CREATING & USING VIEWS

INDEXES & SYNONYMS

UNDERSTANDING THE DATA DICTIONARY

  • ABOUT THE DATA DICTIONARY
  • Query DD: USER_TABLES, USER_TAB_COLUMNS, USER_OBJECTS

BUILD SIMPLE SQL*PLUS REPORTS