Oracle Database SQL Expert – Certification Training

(Exam Number: 1Z0-047)

This course is designed to give students an understanding of Oracle SQL and Oracle PL/SQL languages. The course covers SQL commands for DML, DDL, Query, and Transaction Control operations. Students are also introduced to procedural programming using PL/SQL. The course topics are applicable to all versions of Oracle through Oracle 10g.

Introduction to RDBMS

  • Database Models
  • Some Introductory Terminology
  • Codd’s 12 Rules
  • Normalization

Data Modeling

  • The Entity Relationship Model
  • Entities
  • Attributes
  • Types of Relationships
  • Handling many-to-many Relationships

Data Definition Language (DDL)

  • The CREATE TABLE Statement
  • DESCRIBE keyword
  • Oracle Datatypes
  • Integrity Constraints
  • Defining Constraints
  • The ALTER TABLE Statement
  • The DROP Command

Data Manipulation Language (DML)

  • The INSERT Statement
  • INSERT statement with a subquery
  • The UPDATE Statement
  • The DELETE Statement
  • Difference between Drop, Truncate, Delete

Database Transactions

  • COMMIT and ROLLBACK
  • SAVEPOINT

SQL SELECT Statement

  • Basic SELECT Statement
  • Displaying Unique Values Using the Distinct keyword
  • Comparison Operators
  • BETWEEN Operator
  • IN and NOT IN Operators
  • The LIKE Operator
  • IS NULL and IS NOT NULL
  • Logical Operators
  • ORDER BY Clause

Single-Row Functions

  • Character functions
  • Number functions
  • Date functions
  • Conversion functions
  • General functions

Group Functions

  • Group Functions
  • GROUP BY Clause
  • HAVING Clause

Joining Tables

  • Natural Joins
  • Equijoins / simple joins/ inner joins
  • USING Clause
  • ON Clause
  • Table Aliases
  • Self-Joins
  • Non-Equijoins
  • LEFT/RIGHT/FULL OUTER Joins
  • CROSS JOIN

Set Operators

  • Union
  • Union All
  • Intersect
  • Minus

SQL Subqueries

  • Introduction
  • Types of Subqueries
  • Single-Row Subqueries
  • Group Functions in a Subquery
  • The HAVING Clause with Subqueries
  • Multiple-Row Subqueries
  • IN, ANY, ALL operators

Views

  • Advantages of Views
  • Creating views
  • Retrieve data from views
  • Updating a View
  • Creating a Complex View
  • Removing a View

Introduction to PL/SQL

Block Structure, Basic Syntax

Data Types, Variables, Constants, Operators, Conditions

Loops, Case

Cursors

  • Implicit cursors
  • Explicit cursors

Stored Procedures, Functions

Exception Handling

Triggers