DBMS

Overview
Curriculum
Instructor

COURSE DESCRIPTION

This course introduces students to the fundamentals of SQL using Oracle Database 11g database technology. In this course, students learn the concepts of relational databases and the powerful SQL programming language. This course provides the essential SQL skills that allow developers to write queries against single and multiple tables, manipulate data in tables, and create database objects.

The students also learn to use single-row functions to customize the output, use conversion functions and conditional expressions, and use group functions to report aggregated data. Demonstrations and hands-on practice reinforce the fundamental concepts.

In this course, students use Oracle SQL Developer as the main tool, and SQL *Plus is available as an optional tool.

LEARN TO:

Retrieve row and column data from tables with the SELECT statement
Create reports of sorted and restricted data
Display data from multiple tables.
Use DML statements to manage data.
Use DDL statements to manage database objects

AUDIENCE:

Developer
End-User
Implementer

OBJECTIVES:

Employ SQL functions to generate and retrieve customized data
Display data from multiple tables using the ANSI SQL 99 JOIN syntax
Create reports of aggregated data
Use the SET operators to create subsets of data
Run data manipulation statements (DML) to update data in the Oracle Database 11g
Run data definition language (DDL) statements to create and manage schema objects
Identify the major structural components of the Oracle Database 11g
Retrieve row and column data from tables with the SELECT statement
Create reports of sorted and restricted data

Curriculum

28 Sections
100 Lessons
60 Days

Expand all sectionsCollapse all sections

Introduction
3
- 2.1
  Oracle Application Express
- 2.2
  Relational Database Technology
- 2.3
  Anatomy of a SQL Statement
SELECT and WHERE
3
- 3.1
  Columns, Characters, and Rows
- 3.2
  Limit Rows Selected
- 3.3
  Comparison Operators
WHERE, ORDER BY, and Intro to Functions
3
- 4.1
  Logical Comparisons and Precedence Rules
- 4.2
  Sorting Rows
- 4.3
  Introduction to Functions
Single Row Functions Part I
3
- 5.1
  Case and Character Manipulation
- 5.2
  Number Functions
- 5.3
  Date Functions
Single Row Functions Part II
3
- 6.1
  Conversion Functions
- 6.2
  NULL Functions
- 6.3
  Conditional Expressions
JOINs
6
- 7.1
  Cross Joins and Natural Joins
- 7.2
  Join Clauses
- 7.3
  Inner versus Outer Joins
- 7.4
  Self-Joins and Hierarchical Queries
- 7.5
  Oracle Equijoin and Cartesian Product
- 7.6
  Oracle Nonequijoins and Outer Joins
Group Functions
5
- 8.1
  Group Functions
- 8.2
  Oracle Nonequijoins and Outer Joins
- 8.3
  Using Group By and Having Clauses
- 8.4
  Using Rollup and Cube Operations, and Grouping Sets
- 8.5
  Using Set Operators
Subqueries
4
- 9.1
  Fundamentals of Subqueries
- 9.2
  Single-Row Subqueries
- 9.3
  Multiple-Row Subqueries
- 9.4
  Correlated Subqueries
DML
3
- 10.1
  INSERT Statements
- 10.2
  Updating Column Values and Deleting Rows
- 10.3
  DEFAULT Values, MERGE, and Multi-Table Inserts
DDL
3
- 11.1
  Creating Tables
- 11.2
  Using Data Types
- 11.3
  Modifying a Table
Constraints
3
- 12.1
  Intro to Constraints; NOT NULL and UNIQUE Constraints
- 12.2
  PRIMARY KEY, FOREIGN KEY, and CHECK Constraints
- 12.3
  Managing Constraints
Views
3
- 13.1
  Creating Views
- 13.2
  DML Operations and Views
- 13.3
  Managing Views
Sequences and Synonyms
2
- 14.1
  Working With Sequences
- 14.2
  Indexes and Synonyms
Privileges and Regular Expressions
3
- 15.1
  Controlling User Access
- 15.2
  Creating and Revoking Object Privileges
- 15.3
  Regular Expressions
TCL
1
- 16.1
  Database Transactions
Fundamentals of PL/SQL
3
- 17.1
  Introduction to PL/SQL
- 17.2
  Benefits of PL/SQL
- 17.3
  Creating PL/SQL Blocks
Defining Variables and Datatypes
7
- 18.1
  Using Variables in PL/SQL
- 18.2
  Recognizing PL/SQL Lexical Units
- 18.3
  Recognizing Data Types
- 18.4
  Using Scalar Data Types
- 18.5
  Writing PL/SQL Executable Statements
- 18.6
  Nested Blocks and Variable Scope
- 18.7
  Good Programming Practices
Using SQL in PL/SQL
4
- 19.1
  Review of SQL DML
- 19.2
  Retrieving Data in PL/SQL
- 19.3
  Manipulating Data in PL/SQL
- 19.4
  Using Transaction Control Statements
Program Structures to Control Execution Flow
5
- 20.1
  Conditional Control: IF Statements
- 20.2
  Conditional Control: CASE Statements
- 20.3
  Iterative Control: Basic Loops
- 20.4
  Iterative Control: WHILE and FOR Loops
- 20.5
  Iterative Control: Nested Loops
Using Cursors and Parameters
6
- 21.1
  Introduction to Explicit Cursors
- 21.2
  Using Explicit Cursor Attributes
- 21.3
  Cursor FOR Loops
- 21.4
  Cursors with Parameters
- 21.5
  Using Cursors for UPDATE
- 21.6
  Using Multiple Cursors
Using Composite Datatypes
2
- 22.1
  User-Defined Records
- 22.2
  Indexing Tables of Records
Exception Handling
4
- 23.1
  Handling Exceptions
- 23.2
  Trapping Oracle Server Exceptions
- 23.3
  Trapping User-Defined Exceptions
- 23.4
  Recognizing the Scope of Exceptions
Using and Managing Procedures
3
- 24.1
  Creating Procedures
- 24.2
  Using Parameters in Procedures
- 24.3
  Passing Parameters
Using and Managing Functions
6
- 25.1
  Creating Functions
- 25.2
  Using Functions in SQL Statements
- 25.3
  Review of the Data Dictionary
- 25.4
  Managing Procedures and Functions
- 25.5
  Review of Object Privileges
- 25.6
  Using Invoker’s Rights and Autonomous Transactions
Using and Managing Packages
3
- 26.1
  Creating Packages
- 26.2
  Managing Package Concepts
- 26.3
  Advanced Package Concepts
Getting the Best out of Packages
2
- 27.1
  Persistent State of Package Variables
- 27.2
  Using Oracle-Supplied Packages
Improving PL/SQL Performance
2
- 28.1
  Using Dynamic SQL
- 28.2
  Improving PL/SQL Performance
Using and Managing Triggers
5
- 29.1
  Creating DML Triggers, Part I
- 29.2
  Introduction To Triggers
- 29.3
  Creating DML Triggers, Part II
- 29.4
  Creating DDL and Database Event Triggers
- 29.5
  Managing Triggers