Training

 

 

Popular Courses

Browse Our Free Resources

  • whitepapers
  • whitepapers
  • webinars
  • blogs

Our Locations

Training Centres

Vancouver, BC
Calgary, AB
Edmonton, AB
Toronto, ON
Ottawa, ON
Montreal, QC
Hunt Valley
Columbia

locations map

Calgary

550 6th Av SW
Suite 475
Calgary, AB
T2P 0S2

Toronto

821A Bloor Street West
Toronto, ON
M6G 1M1

Vancouver

409 Granville St
Suite 902
Vancouver, BC
V6C 1T2

U.S. Office

436 York Road
Suite 1
Jenkintown, PA
19046

Other Locations

Dallas, TX
Miami, FL

Home > Training > Oracle 10g > Oracle Database 10g: Introduction to SQL Training

Oracle Database 10g: Introduction to SQL Training

Course#: WA1373

This course is a common starting point in the Oracle database curriculum for administrators, developers and business users.

Part I:

Objectives

The objective of this course is to provide an introduction to the SQL database language within the context of an Oracle database. Specific subject areas to be explored and objectives to be reached are:

  • A summation of relational database principles and an introduction to the Oracle relational database server.
  • Using the iSQL*Plus web-based database interface and other traditional interfaces.
  • Writing simple SQL queries and moderately complex SQL queries using various join techniques.
  • Summarizing, grouping and sorting data.
  • Advanced query techniques such as correlated sub queries.
  • Developing simple SQL*Plus reports.
  • Creating and maintaining database tables.
  • Taking advantage of new features such as the use of regular expressions.
  • Moving beyond basic table definitions, consideration of defining the full range of database objects, such as advanced table definitions, indexes, sequences, views and others.
  • Understanding basic database security and object privileges.
  • Using the powerful set of built-in SQL functions.
  • Protecting the database and extending the basic data model using declarative constraints.
  • Performing advanced SQL queries such as grouping and cube operations and SQL99 join syntax.
  • Developing complex SQL*Plus reports and using SQL*Plus scripts.

Prerequisites

No mandatory prerequisites exist for this course.

Audience

The target audience for this course is all Oracle professionals, both business and systems professionals. Among the specific groups for whom this course will be helpful are:

  • Application designers and developers.
  • Database administrators.
  • Business users and non-technical end users.

Objectives

The primary objective of this course is to consider advanced subjects and techniques pertaining to the SQL database language. Even professionals experienced in other implementations of the industry-standard SQL language will benefit from the advanced and Oracle-specific features of SQL discussed in this course. Major subject areas to be explored are:

  • Learn to use the SQL99 join syntax. Also, performing advanced SQL queries using techniques such as grouping and cube operations.
  • Moving beyond basic table definitions, consideration of defining the full range of database objects, such as advanced table definitions, indexes, sequences, views and others.
  • Understanding basic database security and object privileges.
  • Using the powerful set of built-in SQL functions.
  • Protecting the database and extending the basic data model using declarative constraints.
  • Developing complex SQL*Plus reports and using SQL*Plus scripts.

Prerequisites

ORACLE DATABASE 10G: INTRODUCTION TO SQL –PART I is a mandatory prerequisite for this course.

Audience

The target audience for this course is all Oracle professionals. Further, this course is well suited for non-Oracle professionals already experienced in the SQL but who wish to become acquainted with the unique and advanced features of Oracle SQL. Among the specific groups for whom this course will be helpful are application developers and database administrators.

Duration

Five days.

Outline of WA1373 Oracle Database 10g: Introduction to SQL Training

Part I

1. RELATIONAL DATABASES & SQL

  • ABOUT RELATIONAL DATABASES
  • ELEMENTS OF SQL

2. USING SQL*PLUS

  • ABOUT SQL*PLUS
  • USING THE MS WINDOWS SQL*PLUS TOOL
  • USING THE ISQL*PLUS WEB TOOL
  • ENTERING COMMANDS & SYNTAX

3. BUILDING A SELECT STATEMENT

  • ABOUT THE SELECT STATEMENT
  • SELECTING COLUMNS
  • USING ALIAS NAMES

4. BUILDING A WHERE CLAUSE

  • ABOUT LOGICAL OPERATORS
  • EQUALITY OPERATOR
  • BOOLEAN OPERATORS
  • NULL & BETWEEN OPERATORS
  • FINDING TEXT STRINGS
  • IN OPERATOR

5. ORDERING THE RESULT TABLE

  • ABOUT THE ORDER BY CLAUSE
  • MULTIPLE COLUMN SORTS
  • SPECIFYING THE SORT SEQUENCE
  • ABOUT NULL VALUES WITHIN SORTS
  • USING COLUMN ALIASES

6. PSEUDO COLUMNS & FUNCTIONS

  • ABOUT PSEUDO COLUMNS & FUNCTIONS
  • USING ROWID
  • USING ROWNUM
  • USING THE FUNCTIONS
  • USING THE DUAL TABLE
  • SESSIONTIMEZONE FUNCTION

7. JOINING TABLES

  • ABOUT JOINS
  • INNER JOIN
  • REFLEXIVE JOIN
  • NON-KEY JOIN
  • OUTER JOIN

8. USING SET OPERATORS

  • ABOUT THE SET OPERATORS
  • SET OPERATOR EXAMPLES

9. SUMMARY FUNCTIONS

  • ABOUT SUMMARY FUNCTIONS
  • SUMMARY FUNCTIONS WITH DISTINCT

10. USING SUBQUERIES

  • ABOUT SUBQUERIES
  • STANDARD SUBQUERIES
  • CORRELATED SUBQUERIES

11. CREATING & SELECTING GROUPS

  • ABOUT SUMMARY GROUPS
  • FINDING GROUPS WITHIN THE BASE TABLES
  • SELECTING DATA FROM THE BASE TABLES
  • SELECTING GROUPS FROM THE RESULT TABLE

12. SQL*PLUS COMMANDS

  • ABOUT SQL*PLUS COMMANDS
  • MORE ABOUT SCRIPT FILES
  • INVOKING OPERATING SYSTEM COMMANDS
  • SAVING SESSION OUTPUT

13. BUILD SIMPLE SQL*PLUS REPORTS

  • ABOUT THE SQL*PLUS REPORT FEATURES
  • SESSION OUTPUT WITH SET
  • FORMAT COLUMN OUTPUT WITH COLUMN
  • DEFINING REPORT BREAKS WITH BREAK
  • PRODUCE SUBTOTALS WITH COMPUTE

14. SQL DATA DEFINITION LANGUAGE

  • ABOUT SQL DDL
  • CREATE TABLE STATEMENT
  • ALTER TABLE STATEMENT
  • DROP TABLE STATEMENT
  • SUPPORTIVE STATEMENTS

15. SQL DATA MANIPULATION LANGUAGE

  • ABOUT SQL DML
  • ABOUT THE INSERT STATEMENT
  • ABOUT THE DELETE STATEMENT
  • ABOUT THE UPDATE STATEMENT
  • ABOUT TRANSACTIONS
  • TRUNCATE TABLE
  • COMPLEX TABLE REFERENCES

Part II

1. SQL99 JOIN TECHNIQUES

  • CROSS JOINS
  • NATURAL JOINS
  • INNER JOINS
  • OUTER JOINS
  • ANTI JOINS
  • USING NAMED SUBQUERIES

2. ENHANCING GROUPS WITH ROLLUP & CUBE

3. USING THE CASE EXPRESSION

4. SQL FUNCTIONS: CHARACTER

  • WHAT ARE THE SQL FUNCTIONS?
  • CHARACTER FUNCTIONS
  • About GREATEST(), LEAST()

5. SQL FUNCTIONS: NUMERIC

6. SQL FUNCTIONS: DATE

  • DATE FORMAT FUNCTIONS
  • DATE ARITHMETIC FUNCTIONS

7. DATABASE OBJECTS: RELATIONAL VIEWS

  • ABOUT RELATIONAL VIEWS
  • UPDATING VIEW DATA
  • MAINTAINING VIEW DEFINITIONS
  • ALTER VIEW
  • DROP VIEW

8. DATABASE OBJECTS: DATA DICTIONARY STORAGE

  • USER_UPDATABLE_COLUMNS
  • UNDERSTANDING THE DATA DICTIONARY STRUCTURE

9. DATABASE OBJECTS: INDEXES

  • ABOUT INDEXES
  • USE B-TREE INDEXES

10. DATABASE OBJECTS: OTHER OBJECTS

  • CREATING AN APPLICATION SCHEMA
  • MORE ABOUT CREATING TABLES
  • DEFINING SEQUENCES
  • ALTER SEQUENCE & DROP SEQUENCE
  • ALTER SEQUENCE
  • DROP SEQUENCE
  • ABOUT SYNONYMS
  • DROP SYNONYM
  • CREATE SCHEMA AUTHORIZATION

11. DATABASE OBJECTS: SECURITY

  • USER ID PASSWORDS
  • OBJECT SECURITY

12. DATA INTEGRITY USING CONSTRAINTS

  • NOT NULL CONSTRAINT
  • CHECK CONSTRAINT
  • UNIQUE CONSTRAINT
  • PRIMARY KEY CONSTRAINT
  • REFERENCES CONSTRAINT

13. MANAGING CONSTRAINT DEFINITIONS

  • MANAGING CONSTRAINTS
  • RENAMING & DROPPING CONSTRAINTS
  • ENABLING & DISABLING CONSTRAINTS
  • DEFERRED ENFORCEMENT
  • SET CONSTRAINTS
  • HANDLING CONSTRAINT EXCEPTIONS
  • DATA DICTIONARY STORAGE

14. OPTIONAL – ADVANCED SQL*PLUS REPORTS

  • BREAK
  • COMPUTE
  • COLUMN
  • TTITLE & BTITLE

15. OPTIONAL – BUILDING SQL*PLUS SCRIPTS

  • USING SQL*PLUS VARIABLES
  • DEFINE & UNDEFINE
  • ACCEPT
  • PROMPT
  • PAUSE

 

We regularly offer classes in these and other cities. Atlanta, Austin, Baltimore, Calgary, Chicago, Cleveland, Dallas, Denver, Detroit, Houston, Jacksonville, Miami, Montreal, New York City, Orlando, Ottawa, Philadelphia, Phoenix, Pittsburgh, Seattle, Toronto, Vancouver, Washington DC.
*Your name:

*Your e-mail:

*Phone:

*Company name:

Additional notes:

We have received your message. A sales representative will contact you soon.

Thank you!.

more details
buy this course

Register for a courseware sample

It's simple, and free.

 

Thank You!

You will receive an email shortly containing a link to download the requested sample of the labs for this course.