Course Topics
PART I: Structured Query Language – SQL
Relational database design
Computer Database Concepts
Relational Database Design – An interactive practical example.
Optimization of database design by using Normalization.
Role of SQL in RDBMS.
Simple SQL statements
Basic SQL construct
Column naming conventions
Importance of NULL
Concatenation operator
Arithmetic operators and expressions
Operator precedence and Parenthesis
Character strings
Tables and Table Joins
Need for joins
Cartesian products
Column aliases
Types of joins
ANSI Syntax and Oracle Syntax for writing Join statements.
Filtering and Sorting data
Limiting rows retrieved
Using LIKE for pattern matching
Using logical operators AND, OR & NOT
Using BETWEEN & IN operators
Sorting data using ORDER BY clause
Oracle SQL Functions part 1
SQL Functions overview
What are single row functions?
Using Character functions
Using Number functions
Using Date functions. Avoiding misinterpretation of YEAR value in Dates.
Using CASE and DECODE
Nested functions
Data type conversion functions
Format masks
Oracle SQL Functions part 2
What are multi row functions?
Various multi row functions
Grouping data with GROUP BY clause
Using the HAVING clause
Sorting GROUP BY data
SQLPlus – queries, formatting and reports
Logging into SQLPlus
Writing queries in SQLPlus
Substitution variables using ‘&’ and ‘&&’
Using DEFINE & UNDEFINE commands
Using SET command
Setting COLUMN format
Editing data from buffer
Creating and running reports
Nested SQL queries
Need for nested queries
Types of nested queries
USING EXISTS, ALL & ANY operators
Database Definition Language – DDL
Creating tables
Modifying tables
Dropping tables
Creating and replacing views
Creating and dropping synonyms
Creating modifying and dropping sequences
Database Manipulation Language – DML
What is data manipulation in a database
Need for data manipulation
INSERT statement
UPDATE statement
DELETE statement
MERGE statement
Database Control Language – DCL
Definition of Transaction
COMMIT & ROLLBACK statements
Using the SAVEPOINT statement
Automatic COMMIT behavior in SQL*Developer and SQL*Plus utilities.
Indexes and Index types
What are Indexes?
Need for Indexes
Types of Indexes
Composite Indexes
Function based Indexes
Analyzing and rebuilding Indexes
Privileges, Users, Roles & Grants
System privileges
Object privileges
Creating and managing users
Creating and assigning roles
Granting Roles
Last but not the least…
Set operators in SQL
RANK() function
ROWID & ROWNUM
Analytic functions
Data dictionary views
PART II: Procedural Language/ Structured Query Language – PLSQL
Introduction to PLSQL
Anonymous PLSQL block
Variables
Data types
Bind variables
PLSQL operators
Nested blocks
Implicit data conversion
SELECT … INTO statement
DBMS_OUTPUT.PUT_LINE
Control structures
F-THEN-ELSE statement
CASE statement. The simple CASE and the searched CASE.
Basic and FOR Loops
WHILE Loop
Nested loops
Composite data types
Using %ROWTYPE
PLSQL Records
PLSQL Table of Records
INDEX BY Table
INDEX BY Table of Records
Creating Functions
Syntax of CREATE FUNCTION
Benefits of user defined functions
Executing user defined functions
Restrictions on function calls
Dropping functions
Exceptions and Exception handling
What are Exceptions
Need for handling exceptions
Predefined exceptions
User defined exceptions
Non predefined exceptions
Raising Exceptions
Trapping Exceptions
Exception propagation in nested calls
The SQLCODE and SQLERRM in-built PL/SQL functions.
Creating Procedures
What are Procedures?
Need for Procedures
Syntax of CREATE PROCEDURE
Parameter dispositions
Parameter passing methods
Executing procedures
Exceptions in procedures
Explicit Cursors.
What is a cursor?
Implicit and explicit cursors
Creating cursors
Using cursors in procedures
OPEN..FETCH CLOSE construct
The %NOTFOUND operator.
FOR UPDATE cursors
Creating Packages
What are packages?
Need for packages
Package Specification
Package Body
Private and Public components
Calling package functions, procedures
Referencing package constants and variables
Package Features
Overloading package subprograms
Forward declaration of subprograms
Creating bodiless package
Persistent state of package variables
Persistent state of package cursor
Oracle Supplied Packages
DBMS_OUTPUT
DBMS_SCHEDULER
UTL_FILE
UTL_RECOMP
UTL_MAIL
EXECUTE IMMEDIATE
Working with Database Links
Remote databases
PUBLIC and PRIVATE Database links
Querying data using database links
Creating views on database links
Oracle utilities
SQL*loader (sqlldr)
SQL Tracing and TKPROF
Profiling PL/SQL code using DBMS_HPROF supplied package.
Oracle Net Services
Understanding Oracle’s networking layer
Configuring net service (TNSNAMES) using NETCA
Bulk Data Processing with PL/SQL
PL/SQL Run Time engine and SQL Run Time engine
Context switching between PL/SQL Run Time engine and SQL Run Time engine.
BULK COLLECT operation in PL/SQL
FOR ALL operation in PL/SQL
Exception Handling during Bulk Data Processing.