Overview
This Oracle SQL training class
helps students master SQL using an Oracle database and prepares students for a
PL/SQL course. Note that this course covers SQL, not PL/SQL.
Class Prerequisites
Experience in the following is
required for this Oracle class:
Basic SQL Skills
Prerequisite Courses
Courses that can help you meet
these prerequisites:
Introduction
to SQL Training using Oracle
Goals
1. Learn
to create Users and Schemas.
2. Learn
to use pseudo-columns and functions.
3. Learn
to write simple and correlated subqueries.
4. Learn
to write simple and complex joins.
5. Learn
to use ROLLUP and CUBE.
6. Learn
to use SET operators.
7. Learn
to use character and non-character SQL functions.
8. Learn
to write INSERT, UPDATE, and DELETE statements.
9. Learn
to create tables.
10.
Learn to use keys and constraints to protect data integrity.
11.
Learn to use views, indexes, sequences, and synonyms.
Outline
1. Users
and Schemas
1. Introduction
to Users and Schemas
1. Definitions
and Client Tools
2. SQL
Developer Login
2. About
the HR Schema
1. Sample
Schemas
2. HR
Schema Entities
3. Creating
An Application Schema
4. Exercise:
Create a User
2. Pseudo
Columns & Functions
1. Using
The Dual Table
2. Functions
3. Exercise:
Use the SYSCONTEXT function
4. Pseudo-Columns
5. Using
Rowid
6. Using
Rownum
7. Exercise:
Use ROWNUM
3. Using
Subqueries
1. Simple
Subqueries
1. Definition
2. Nested
Subquery
2. Inline
Views
1. Subquery
in the HAVING clause
3. Correlated
Subqueries
4. Scalar
Subquery
5. Exercise:
Subqueries
4. Joining
Tables
1. Review
of Joins
2. Equijoins
1. Inner
Joins
2. Outer
Joins
3. Cross
Joins
4. Reflexive
Join
5. Non-Key
Join
6. Natural
Joins
7. Semijoins
and Antijoins
8. Using
Named Subqueries
9. Exercise:
Join Exercises
5. Rollup
& Cube
1. About
Group Processing
2. EMP_DETAILS_VIEW
3. Simple
GROUP BY
4. ROLLUP
Function
5. GROUPING
Function
6. Using
Cube
7. Exercise:
Practice GROUP BY, ROLLUP and CUBE
6. Using
Set Operators
1. Set
Operators Defined
2. Relationship
to Mathematical Set Theory
3. Restrictions
on Set Operators
4. Exercise:
Set Operators Exercise
7. Conditional
Processing
1. The
DECODE Function
2. The
Case Expression
3. Exercise:
Conditional Processing
8. SQL
Functions (Character)
1. What
Are SQL Functions?
2. Character
Functions
1. CONCAT
2. LENGTH
3. INSTR
4. REPLACE
5. UPPER
6. LOWER
7. INITICAP
8. LPAD
9. RPAD
10.
TRIM
11.
TO_CHAR
12.
SOUNDEX
3. Regular
Expressions
4. Exercise:
Character Functions
9. SQL
Functions (Non-Character)
1. Numeric
Functions
1. The
TO_CHAR Function with numbers
2. Date/Time
Functions
3. Date
Format Functions
4. Date
Arithmetic Functions
5. Null
Value Functions
6. Exercise:
SQL Non-Character Functions
10.
SQL Data Manipulation Language
1. The
INSERT Statement
1. INSERT
Statement Categories
2. The
UPDATE Statement
3. The
DELETE Statement
4. Transactions
5. Complex
Table References
6. The
MERGE statement
7. Exercise:
Data Manipulation Language (DDL)