Oracle Database 11g SQL & PL/SQL Expert
Prerequisite: None
Course Duration: 30 Hours
Oracle 11g SQL and PL/SQL Course Content:
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-Equijoin
- 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
